oracle 游标的优缺点,游标的各种用法和各自特点

最新推荐文章于 2023-05-08 16:28:12 发布

AI量化数据之佳

最新推荐文章于 2023-05-08 16:28:12 发布

阅读量1.6k

点赞数

文章标签： oracle 游标的优缺点

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

游标的概念:

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

隐式游标

如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：

插入操作：INSERT。更新操作：UPDATE。删除操作：DELETE。单行查询操作：SELECT ... INTO ...。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

隐式游标的属性返回值类型

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数

SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反

SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

显式游标

游标的定义和操作

游标的使用分成以下4个步骤。

1．声明游标

在DECLEAR部分按以下格式声明游标：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]

IS SELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

2．打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。

3．提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...];

或

FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

下面对这两种格式进行说明：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。

定义记录变量的方法如下：

变量名表名|游标名%ROWTYPE；

其中的表必须存在，游标名也必须先定义。

4．关闭游标

CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

declare

cursor c_dept is select * from t_dept;

v_ID number;

v_name varchar2(20);

begin

open c_dept;

loop

fetch c_dept into v_ID,v_name;

exit when c_dept%notfound;

dbms_output.put_line(to_char(v_ID)||' '||v_name);

end loop;

close c_dept;

end;

declare

cursor c_dept is select * from t_dept;

begin

for v_dept in c_dept loop

dbms_output.put_line(to_char(v_dept.p_n_deptID)||' '||v_dept.c_deptname);

end loop;

end;

--参数游标

declare

v_ID number;

v_name varchar2(20);

cursor c_dept(v_deptid number) is select * from t_dept where p_n_deptid>=v_deptid;

begin

open c_dept(3);

loop

fetch c_dept into v_ID,v_name;

exit when c_dept%notfound;

dbms_output.put_line(to_char(v_ID)||' '||v_name);

end loop;

close c_dept;

end;

--游标变量

declare

v_dept t_dept%rowtype;

v_emp t_emp%rowtype;

type c_cur is ref cursor;

v_temp c_cur;

v_num number;

begin

v_num := 10;

if v_num >10 then

open v_temp for select * from t_dept;

loop

fetch v_temp into v_dept;

exit when v_temp%notfound;

dbms_output.put_line(to_char(v_dept.p_n_deptid)||' '||v_dept.c_deptname);

end loop;

close v_temp;

else

open v_temp for select * from t_emp;

loop

fetch v_temp into v_emp;

exit when v_temp%notfound;

dbms_output.put_line(to_char(v_emp.p_n_empid)||' '||v_emp.c_empname);

end loop;

close v_temp;

end if;

end;

--动态游标游标本来就比较慢，所以尽量不要使用动态游标。

declare

v_cur number;

v_sql varchar2(200);

v_id varchar(20);

v_name varchar(20);

v_dummy number;

begin

v_sql := 'select * from t_dept' ;

v_cur := dbms_sql.open_cursor() ;

dbms_sql.parse( v_cur , v_sql , dbms_sql.V7 );

dbms_sql.define_column( v_cur , 1 , v_id , 100 );

dbms_sql.define_column( v_cur , 2 , v_name , 100 );

v_dummy := dbms_sql.execute( v_cur );

loop

if dbms_sql.fetch_rows( v_cur ) = 0 then

exit;

end if;

dbms_sql.column_value( v_cur , 1 , v_id );

dbms_sql.column_value( v_cur , 2 , v_name );

dbms_output.put_line(v_id||'---'||v_name);

end loop;

dbms_sql.close_cursor(v_cur) ;

end;

sys_refcursor 和 cursor 优缺点比较

优点比较

优点一：sys_refcursor，可以在存储过程中作为参数返回一个table格式的结构集(我把他认为是table类型，容易理解，其实是一个游标集)， cursor 只能用在存储过程，函数，包等的实现体中，不能做参数使用。

优点二：sys_refcursor 这东西可以使用在包中做参数，进行数据库面向对象开放。哈哈。我喜欢。cursor就不能。

缺点比较：

缺点：sys_refcursor 不能用open，close ，fetch 进行操作。不好学，难理解。

cursor可以用 open，close ，fetch操作，容易学，易懂

其他就目前不知道，至于游标的的基础概念，去google，百度一大堆的。这里就不累赘了。看例子：

建立一个存储过程

create or replace procedure up_test(o out sys_refcursor) is

begin

open o for select * from lq_test;

end;

返回的类型是sys_refcursor；

建立第二个存储过程

create or replace procedure up_getData(aMsg out varchar2) is

type p_table_type is table of lq_test%rowtype;

p_table p_table_type;

v sys_refcursor;

begin

up_test(v);

fetch v bulk collect into p_table;

for i in 1..p_table.count loop

dbms_output.put_line('字段1：'||p_table(i).v1 || ' 字段2：' || p_table(i).v2);

end loop;

end;

这里要注意fetch 带参数的用法，bulk collect ，这是第集合的操作，必须先定义一个结合类。见上面的例子，还不懂就google了。用法就简单，没啥好介绍的。

取集合的值应该这样p_table(i).v1，其中i标识几行，带上字段，即可了，容易理解。

像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。

其主要运用于PLSQL函数或存储过程以及其他编程语言java等程序之间作为参数传递。

不像游标的一点，游标变量没有参数。

游标变量具有以下属性：

(%FOUND, %NOTFOUND,%ISOPEN, %ROWCOUNT)

二用法介绍：

1、声明格式：

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

2、游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型

TYPE genericcurtyp IS REF CURSOR; -- 弱类型

cursor1 empcurtyp;

cursor2 genericcurtyp;

my_cursor SYS_REFCURSOR; -- 使用预定义游标变量sys_refcursor

TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;

dept_cv deptcurtyp; -- 声明游标变量

或是返回record类型：

DECLARE

TYPE EmpRecTyp IS RECORD (

employee_id NUMBER,

last_name VARCHAR2(25),

salary NUMBER(8,2));

TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;

emp_cv EmpCurTyp; -- declare cursor variable

3、使用游标变量作为参数传递：

view plaincopy在CODE上查看代码片派生到我的代码片

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

emp empcurtyp;

-- after result set is built, process all the rows inside a single procedure

-- rather than calling a procedure for each row

PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS

person employees%ROWTYPE;

BEGIN

DBMS_OUTPUT.PUT_LINE('-----');

DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');

LOOP

FETCH emp_cv INTO person;

EXIT WHEN emp_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||

' ' || person.last_name);

END LOOP;

END;

BEGIN

-- First find 10 arbitrary employees.

OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;

process_emp_cv(emp);

CLOSE emp;

-- find employees matching a condition.

OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';

process_emp_cv(emp);

CLOSE emp;

END;

4、使用游标熟悉检查游标变量是否打开

view plaincopy在CODE上查看代码片派生到我的代码片

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

emp_cv empcurtyp;

BEGIN

IF NOT emp_cv%ISOPEN THEN -- open cursor variable

OPEN emp_cv FOR SELECT * FROM employees;

END IF;

CLOSE emp_cv;

END;

5、在包package中声明游标变量：

view plaincopy在CODE上查看代码片派生到我的代码片

CREATE PACKAGE emp_data AS

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);

END emp_data;

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS

BEGIN

OPEN emp_cv FOR SELECT * FROM employees;

END open_emp_cv;

END emp_data;

6、提取游标变量到集合类型collection：

view plaincopy在CODE上查看代码片派生到我的代码片

DECLARE

TYPE empcurtyp IS REF CURSOR;

TYPE namelist IS TABLE OF employees.last_name%TYPE;

TYPE sallist IS TABLE OF employees.salary%TYPE;

emp_cv empcurtyp;

names namelist;

sals sallist;

BEGIN

OPEN emp_cv FOR SELECT last_name, salary FROM employees

WHERE job_id = 'SA_REP';

FETCH emp_cv BULK COLLECT INTO names, sals;

CLOSE emp_cv;

-- loop through the names and sals collections

FOR i IN names.FIRST .. names.LAST

LOOP

DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));

END LOOP;

END;

三游标变量的使用限制：

1、不能再包说明中声明游标变量；

2、不能用“=”运算符比较游标变量相等性、不等性及是否为空；

3、不能存储于表列中；

4、不能将游标变量存在于关联数组、嵌套表或数组；

5、游标和游标变量之前是不可互操作的！

1、强类型游标：

view plaincopy在CODE上查看代码片派生到我的代码片

CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;

PROCEDURE child(p_return_rec OUT return_cur);

PROCEDURE parent(p_NumRecs PLS_INTEGER);

END strongly_typed;

view plaincopy在CODE上查看代码片派生到我的代码片

CREATE OR REPLACE PACKAGE BODY strongly_typed IS

PROCEDURE child(p_return_rec OUT return_cur) IS

BEGIN

OPEN p_return_rec FOR

SELECT * FROM all_tables;

END child;

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS

p_retcur return_cur;

at_rec all_tables%ROWTYPE;

BEGIN

child(p_retcur);

FOR i IN 1 .. p_NumRecs

LOOP

FETCH p_retcur

INTO at_rec;

dbms_output.put_line(at_rec.table_name ||

' - ' || at_rec.tablespace_name ||

' - ' || TO_CHAR(at_rec.initial_extent) ||

' - ' || TO_CHAR(at_rec.next_extent));

END LOOP;

END parent;

END strongly_typed;

view plaincopy在CODE上查看代码片派生到我的代码片

2、弱类型游标：

view plaincopy在CODE上查看代码片派生到我的代码片

CREATE OR REPLACE PROCEDURE child (

p_NumRecs IN PLS_INTEGER,

p_return_cur OUT SYS_REFCURSOR)

BEGIN

OPEN p_return_cur FOR

'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;

CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS

p_retcur SYS_REFCURSOR;

at_rec all_tables%ROWTYPE;

BEGIN

child(pNumRecs, p_retcur);

FOR i IN 1 .. pNumRecs

LOOP

FETCH p_retcur

INTO at_rec;

dbms_output.put_line(at_rec.table_name ||

' - ' || at_rec.tablespace_name ||

' - ' || TO_CHAR(at_rec.initial_extent) ||

' - ' || TO_CHAR(at_rec.next_extent));

END LOOP;

END parent;

3、预定义游标变量：

view plaincopy在CODE上查看代码片派生到我的代码片

CREATE TABLE employees (

empid NUMBER(5),

empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');

INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');

INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');

COMMIT;

CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)

INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN

FETCH p_cursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST

LOOP

dbms_output.put_line(rec_array(i));

END LOOP;

END pass_ref_cur;

set serveroutput on

DECLARE

rec_array SYS_REFCURSOR;

BEGIN

OPEN rec_array FOR

'SELECT empname FROM employees';

pass_ref_cur(rec_array);

CLOSE rec_array;

END;

在上面的代码中，声明了一个弱类型的REF cursor，下面再看一个强类型(受限)的REF cursor，这种类型的REF cursor在实际的应用系统中用的也是比较多的。

create table gsm_resource

(

gsmno varchar2(11),

status varchar2(1),

price number(8,2),

store_id varchar2(32)

);

insert into gsm_resource values('13905310001','0',200.00,'SD.JN.01');

insert into gsm_resource values('13905312002','0',800.00,'SD.JN.02');

insert into gsm_resource values('13905315005','1',500.00,'SD.JN.01');

insert into gsm_resource values('13905316006','0',900.00,'SD.JN.03');

commit;

declare

type gsm_rec is record(

gsmno varchar2(11),

status varchar2(1),

price number(8,2));

type app_ref_cur_type is ref cursor return gsm_rec;

my_cur app_ref_cur_type;

my_rec gsm_rec;

begin

open my_cur for select gsmno,status,price

from gsm_resource

where store_id='SD.JN.01';

fetch my_cur into my_rec;

while my_cur%found loop

dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);

fetch my_cur into my_rec;

end loop;

close my_cur;

end;

普通cursor与REF cursor还有一些大家应该都熟悉的区别，我再浪费点唾沫。

1)PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。

2)PL/SQL静态光标可以是全局的，而ref光标则不是。也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。只能在定义ref光标的过程中处理它，或返回到客户端应用程序。

3)ref光标可以从子例程传递到子例程，而光标则不能。为了共享静态光标，必须在包说明或包体中把它定义为全局光标。因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。

最后，使用静态光标--通过静态SQL(但不用ref光标)--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况：

把结果集返回给客户端；

在多个子例程之间共享光标(实际上与上面提到的一点非常类似)；

没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样；

简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码(声明，打开，获取，关闭)，也不需要声明变量来保存从游标中获取的数据。这个就因人因具体的case大家去酌定吧。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27924079/viewspace-1784260/，如需转载，请注明出处，否则将追究法律责任。

AI量化数据之佳

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
oracle 游标的优缺点,游标的各种用法和各自特点

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。游标的概念:游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。游标有两种类型：显式...
复制链接

扫一扫