`

PL/SQL正确选择游标类型

 
阅读更多
PL/SQL里的游标可以分为显式和隐式两种,而隐式有分为select into隐式游标和for .. in 隐式游标两种。所以,我们可以认为,有3种游标用法:

A. 显式游标

B. select into隐式游标

C. for .. in 隐式游标

如何正确的选择使用哪种游标,将对你的程序的性能,可读性,代码量等产生重大影响……

--By RollingPig,

本文简单的列举了PL/SQL中用到的几种不同类型的游标写法,并简单对比了不同游标写法的优缺点,同时给出了一个选择的基本原则。

本文并不包括太多的实际运行/性能测试,有兴趣的话,大家可以根据示例自己测试。

1.三种游标形式的简单例子

A. 显式游标

普通显式游标,指的是通过定义获得游标,并通过open,fetch,close的等方法来操作游标

代码:



declare

cursor c is select tname from tab ;

l_tname varchar2(64);

begin

open c ;

loop

fetch c into l_tname ;

exit when c%notfound ;

dbms_output.put_line(l_tname);

end loop;

close c;

end;

/
 

Bulk Collect的 显式游标




代码:





declare

cursor c is select tname from tab ;

l_tname_array dbms_sql.varchar2_table;

begin

open c ;

fetch c bulk collect into l_tname_array ;

for i in 1 .. l_tname_array.count loop

dbms_output.put_line(l_tname_array(i) );

end loop;

close c;

end;

/
 

Bulk Collect的 显式游标 + limit

代码:





declare

cursor c is select tname from tab ;

l_tname_array dbms_sql.varchar2_table;

begin

open c ;

loop

fetch c bulk collect into l_tname_array limit 10 ;

exit when c%notfound ;

for i in 1 .. l_tname_array.count loop

dbms_output.put_line(l_tname_array(i) );

end loop;

end loop;

close c;

end;

/
 

隐式游标相对于显式游标而言,指的是不需要事先Declare,也无须用open,fetch,close的等方法来操作,而是通过其它的方式来操作游标

B. select into隐式游标

代码:





declare

l_tname varchar2(100);

begin

select tname into l_tname from tab where rownum = 1 ;

dbms_output.put_line(l_tname);

end;

/


动态SQL 的 select into隐式游标

代码:




declare

l_tname varchar2(100);

l_table_name varchar2(100);

l_sql varchar2(200);

begin

l_table_name := 'TAB' ;

l_sql := 'select tname from '||l_table_name ||' where rownum = 1 ' ;

execute immediate l_sql into l_tname;

for i in 1 .. l_tname_array.count loop

dbms_output.put_line(l_tname_array(i) );

end loop;

end;

/


动态SQL 的 select into隐式游标 + Bulk Collect

代码:




declare

l_tname_array dbms_sql.varchar2_table;

l_table_name varchar2(100);

l_sql varchar2(200);

begin

l_table_name := 'TAB' ;

l_sql := 'select tname from '||l_table_name ;

execute immediate l_sql bulk collect into l_tname_array;

for i in 1 .. l_tname_array.count loop

dbms_output.put_line(l_tname_array(i) );

end loop;

end;

/


C. for .. in 隐式游标

for .. in 隐式游标通过loop的开始于结束来控制cursor的Open与Close.

代码:




begin

for c in (select tname from tab) loop

dbms_output.put_line(c.tname);

end loop;

end;

/


2.三种用法的优劣

A. 显式游标

优点:

可以用于Bulk Collect的批量处理句式以提高性能

可以用于动态SQL的游标处理

缺点:

麻烦,需要定义,打开,Fetch,Close一堆代码,增加代码复杂度,从而增加出错的可能性

B. select into隐式游标B

优点

代码量最少

可以自动Detect 返回数据超过一行或少于一行的错误

可以使用Bulk Collect 批量处理,但是无法使用Limit 关键字

缺点

如果不使用Bulk Collect 批量处理,仅仅只能用于返回数据正好一行的情况,无法使用于返回数据超过一行或少于一行的环境

使用Bulk Collect 批量处理时,无法使用limit 关键字,无法处理返回行数太多的情况(不好处理,容易造成PGA过大)

C. for .. in 隐式游标

优点

代码量远少于显式游标

代码可读性优于显式游标

代码的出错可能性也小于显式游标

缺点:

无法用于动态SQL的游标处理

在返回行数超过10行的情况下,性能明显不如使用Bulk Collect的显式游标

在性能对比方面,除非是使用了Bulk Collect,否则,三种方式没有明显性能差距。

3.具体的选择

在返回数据为一行的情况下,尽量使用select into 的隐式游标

返回0行或者 100, 应选用显示游标+Bulk collect ,以获得较高的Fetch 性能,同时不至于使用太大的PGA内存。

如国使用动态SQL, 则根据select list (column list) 是否固定,如果固定,仍然可以考虑使用select into 的隐式游标+动态SQL的方式。当然,仍然需要考虑返回行数的问题。

如果select list (column list) 不固定,则只好使用显式游标

或者动态语句返回行数太多,必须用limit,那么也只好用显式游标了。



分享到:
评论

相关推荐

    Oracle PL/SQL实战(待续)

    同时,PL/SQL支持多种数据类型,包括数值类型(NUMBER)、字符类型(VARCHAR2, CHAR)、日期类型(DATE)等,以及复合类型如记录(RECORD)和表类型(TABLE)。 此外,PL/SQL的包(PACKAGE)概念是将相关的过程和...

    一个对数据库的操作工具PL/SQLpl/sqL工具

    3. **游标**:PL/SQL支持游标,允许用户逐行处理查询结果,这对于处理大量数据时非常有用。 4. **事务管理**:PL/SQL提供BEGIN、COMMIT、ROLLBACK等语句来管理事务,确保数据的一致性和完整性。 5. **异常处理**:...

    pl/sql最新中文手册

    1. **PL/SQL基础**:手册可能会从基础开始,介绍PL/SQL的基本结构,包括声明变量、常量、游标、记录类型等。还会讲解如何编写存储过程、函数和触发器。 2. **控制流程语句**:这包括条件判断(IF-THEN-ELSIF-ELSE)...

    oracle10g_pl/sql

    - **变量和常量**:PL/SQL支持各种数据类型,如NUMBER、VARCHAR2、DATE等,以及声明变量和常量的方式。 - **流程控制**:包括IF...ELSIF...ELSE、CASE、FOR循环、WHILE循环等结构。 2. **PL/SQL程序单元** - **...

    pl/sql笔记/sql笔记(游标创建存储过程)

    Pl/SQL简介 控制结构 复合数据类型 游标 异常 创建存储过程 函数的概念 包的概念 触发器概念

    oracle pl/sql从入门到精通 配套源代码

    此外,PL/SQL还支持集合类型,如VARRAY(可变数组)和TABLE(表类型),这对于处理大量数据非常有用。 这本书的配套源代码可能会涵盖以下几个方面: 1. **基础操作**:包括简单的SQL查询、变量声明、流程控制...

    PL/SQL安装包

    7. **记录类型和变量**:PL/SQL支持自定义记录类型和变量,使得可以创建更贴近业务需求的数据结构。 这个"PL/SQL安装包"可能包含了以下组件: - **PL/SQL Developer**:一个强大的Oracle数据库开发工具,支持PL/...

    PL/SQL中用到的几种不同类型的游标写法(转)

    总结起来,PL/SQL中的游标类型主要包括隐式游标、显式游标、匿名块游标、游标变量和FOR-LOOP游标。每种游标类型都有其特定的应用场景,熟练掌握这些游标用法将有助于编写更加高效和灵活的数据库应用程序。

    Oracle PL/SQL程序设计(第5版)(上下册)

    - **集合与记录**:学习如何使用PL/SQL中的集合和记录类型,这些结构可以帮助更高效地处理数据集。 - **游标**:介绍游标的使用方法,包括隐式游标和显式游标,以及如何利用游标遍历查询结果。 #### 四、PL/SQL中的...

    Oracle PL/SQL实例编程(PL/SQL经典书籍)

    2. **变量和数据类型**:PL/SQL支持多种数据类型,包括数值型(NUMBER、BINARY_INTEGER、PLS_INTEGER)、字符型(VARCHAR2、CHAR)、日期型(DATE)、布尔型(BOOLEAN)以及复合数据类型如记录和数组。 3. **控制...

    ORACLE PL/SQL从入门到精通

    PL/SQL提供了隐式游标和显示游标的两种类型,分别用于处理SELECT语句的返回结果。使用游标的FOR循环可以简化对游标的操作。 循环语句是PL/SQL编程中用来重复执行一组语句的结构,包括loop、while、for三种形式,...

    PL/SQL 基本知识

    PL/SQL是Oracle数据库系统中的一个核心组件,全称为“Procedural Language/Structured Query Language”,它是SQL的扩展,增加了编程元素,使得开发者能够编写存储过程、函数、触发器等数据库应用程序。这篇博客主要...

    PL/SQL编程基础知识

    - **程序包**:集合了一系列相关的子程序、类型定义和其他PL/SQL元素,便于管理和复用。 **2.3 标识符规则** PL/SQL中的标识符遵循以下规则: - 标识符长度不能超过30个字符。 - 第一个字符必须是字母。 - 不区分...

    PL/SQL下载

    1. **基础语法**:理解PL/SQL的基本数据类型、变量声明、流程控制结构等。 2. **函数和过程**:掌握如何定义和调用自定义函数和过程。 3. **游标**:了解如何使用游标处理结果集。 4. **异常处理**:学习如何编写...

    PL/SQL入门到精通书的源代码

    12. **游标变量**:游标变量是PL/SQL中的一种特殊变量,可以直接存储查询结果,用于在程序中传递查询结果。 13. **PL/SQL与Oracle数据库对象**:学习如何在PL/SQL中创建、修改和删除数据库对象,如表、视图、索引等...

    Oracle Database 12c PL/SQL开发指南 实例源代码

    3. **游标**:在PL/SQL中如何使用游标处理单行或多行数据,以及游标的声明、打开、关闭和循环遍历。 4. **记录类型**:定义和操作记录类型,用于处理动态或不确定的数据结构。 5. **集合类型**:包括数组(PLS_...

    Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发

    《Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发》是一本深入探讨Oracle数据库中的PL/SQL编程的专业书籍。PL/SQL是Oracle数据库特有的编程语言,它结合了SQL的查询能力与过程式编程语言的功能,使得数据库...

    精通pl/sql

    2. **PL/SQL数据类型**:详细讲解PL/SQL支持的数据类型,如数值型、字符型、日期时间型、布尔型、记录类型、游标类型等,以及如何进行数据转换和操作。 3. **PL/SQL控制结构**:深入探讨流程控制语句,如循环...

    pl/sql 学习资料

    - 数据类型:熟悉PL/SQL内置的数据类型,如NUMBER、VARCHAR2、DATE等。 - 变量和常量:声明和使用变量,以及定义常量。 - 控制流程语句:学习IF-THEN-ELSIF-ELSE、CASE、FOR循环、WHILE循环等。 - 函数和过程:...

    PL/SQL学习课件

    第五章“游标”讨论了如何在PL/SQL中使用游标来遍历查询结果集,这在处理多行数据时非常有用,尤其是在进行迭代或逐行操作时。 第六章“子程序”涵盖了过程和函数的创建、调用和参数传递。通过编写子程序,可以将...

Global site tag (gtag.js) - Google Analytics