PL/SQL 游标变量
来源:互联网 发布:we chat for mac 编辑:程序博客网 时间:2024/05/25 19:56
游标变量与游标相似,有其共性,也有其不同点。就其共性来说两者都是指向多行查询的结果集中的当前行。都要经历声明,打开,检索与关闭的过程。所不同的是游标与游标变量类似于常量与变量。游标是静态的,而游标变量是动态的,因为游标变量并不与某个特定的查询相绑定。所以,游标变量可以打开任何类型兼容的查询。其次可以将游标变量作为参数传递给本地和存储子程序。本文主要描述游标变量的使用。
一、什么是游标变量
显示游标用于命名一个工作区域,其中保存多行查询的信息,而且该游标始终指向工作区域的内容。而游标变量类似于C 或Pascal 语言中的指针,它指向一块内存地址,而不是地址中的内容本身。所以,声明一个游标变量可以创建一个指针,而不是具体的内容。
在PL/SQL 中,为创建游标变量,首先需要申明一个REF CURSOR类型,然后声明该类型的一个变量。
为了执行多行查询,Oracle 会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息;或者声明指向工作区的一个游标变量。无论在什么地方使用游标,它总是指向同一个查询工作区,而游标变量则可以指向不同的工作区。
所以,游标和游标变量不能交互使用;也就是说,我们不能在该使用游标的地方使用游标变量,不能在该使用游标变量的地方使用游标。
二、游标变量使用的情形
PL/SQL 存储子程序和各种客户端之间可以使用游标变量来传递查询结果,这是游标变量最主要的作用。PL/SQL 和其他客户端程序都不拥有结果集,它们只是共享一个指向存放结果集工作区的指针而已。例如,一个OCI 客户端,一个Oracle Forms 应用程序和Oracle 服务器可以引用同一个工作区。只要有游标变量指向查询工作区,我们就可以引用它。因此,我们可以把游标变量的值自由地从一个作用域传递到另一个。
例如,我们把主游标变量传递到嵌套在Pro*C 程序中的PL/SQL 块,游标变量指向的工作区就可以被访问。
如果客户端含有PL/SQL 引擎,那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量,在服务器端打开并取得数据,然后把取得的结果返回给客户端。这些操作都是在服务器端完成,从而也减少了网络流量。
三、使用游标变量的几个关键步骤
1、定义和声明游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type]; --必须先定义REF CURSOR类型
cursor_variable ref_type_name; --接下来再定义游标变量
ref_type_name: 指定自定义的类型名
RETURN: 指定REF CURSOR返回结果的数据类型
cursor_variable: 定义游标变量的名字
注:若指定RETURN子句,其数据类型必须是记录类型,此外,不能在包规范中定义游标变量。
其次若指定RETURN子句则为强游标类型,否则,为弱游标类型。
能够把一个强类型与类型兼容的查询相关联,而若类型可以与任何查询相关联。故强类型游标变量出错概率低,而弱类型更灵活。
2、打开游标变量
当打开游标变量时,则此时游标变量便与特定的SELECT语句关联,执行该查询,标识结果集。使用OPEN FOR可以为不同的查询打开相同的游标变量。再次打开它之前,无需关闭游标变量,但之前的查询会全部丢失。
OPEN cursor_variable FOR select_statement;
3、从结果集检索数据行
每次从结果集检索一次。需要注意的是强类型返回的数据类型必须与FETCH 语句中INTO所使用的变量类型兼容。
其次查询列值的数量必须等于变量的数量,如果数量不匹配,则强类型在编译时出错,而弱类型则在运行时出错。
FETCH cursor_variable INTO variable1,...variable2 ; --提取单行数据,需要配合循环语句来使用
FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows]; --提取多行数据,collect为集合变量
4、关闭游标变量
CLOSE cursor_vairable;
四、定义REF CURSOR与声明游标变量示例
--PL/SQL块内声明游标变量DECLARE TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; -->定义具有返回类型的游标类型,此为强类型 TYPE dept_cur_type IS REF CURSOR; -->定义无返回类型的游标类型,此为弱类型 emp_cv emp_cur_type; -->接下来声明两个游标变量 dept_cv dept_cur_type;BEGIN NULL;END;--使用%type来定义游标变量的返回类型DECLARE emp_type emp%ROWTYPE; -->定义了一个隐式记录类型 TYPE emp_cur_type IS REF CURSOR RETURN emp_type%TYPE; -->定义游标类型且使用%TYPE来返回的数据类型 emp_cv emp_cur_type; -->声明游标变量BEGIN NULL;END;--基于自定义的记录类型作为游标变量的返回类型DECLARE TYPE emp_rec_type IS RECORD -->定义了一个用户自定义的记录类型 ( empno NUMBER( 4 ) ,ename VARCHAR2( 10 ) ,hiredate emp.hiredate%TYPE ); TYPE emp_cur_type IS REF CURSOR RETURN emp_rec_type; --定义具有返回类型的游标类型,且返回类型为用户自定义的记录类型 emp_cv emp_cur_type; --声明游标变量 BEGIN NULL;END;--游标变量作为函数或过程的参数DECLARE TYPE emp_cur_type IS REF CURSOR -->定义一个游标类型,其返回类型为emp的记录类型 RETURN emp%ROWTYPE; emp_cur emp_cur_type; -->声明游标变量--下面的本地过程用于处理游标变量的结果集--注,对于游标变量返回的结果集是一次性处理,而非对返回的每一行记录调用一次过程 PROCEDURE process_emp_cv( emp_cv IN emp_cur_type ) IS -->形参emp_cv使用了emp_cur_type游标类型 person emp%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.ename || ' ' || person.hiredate ); END LOOP; END;BEGIN OPEN emp_cur FOR SELECT * FROM emp WHERE deptno = 10; -->使用游标变量打开游标 process_emp_cv( emp_cur ); -->调用本地过程处理打开的游标变量 CLOSE emp_cur; -->显示关闭游标变量 OPEN emp_cur FOR SELECT * FROM emp WHERE job LIKE 'CLERK'; -->再次打开游标变量且返回了不同的结果集 process_emp_cv( emp_cur ); -->调用本地过程处理打开的游标变量 CLOSE emp_cur; -->显示关闭游标变量END;
五、游标变量使用示例
1、包中使用游标变量CREATE PACKAGE emp_data AS TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp );END emp_data;CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp ) IS each_emp emp%ROWTYPE; BEGIN OPEN emp_cv FOR SELECT * FROM emp WHERE ename LIKE 'A%'; END open_emp_cv;END emp_data;2、游标变量绑定到不同的返回类型(弱类型)CREATE PACKAGE get_data AS TYPE ref_cur_type IS REF CURSOR; PROCEDURE open_cv( ref_cv IN OUT ref_cur_type, choice INT );END get_data;CREATE PACKAGE BODY get_data AS PROCEDURE open_cv (ref_cv IN OUT ref_cur_type, choice INT) IS BEGIN IF choice = 1 THEN OPEN ref_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN ref_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN ref_cv FOR SELECT * FROM bonus; END IF; END;END get_data;3、强类型DECLARE TYPE emp_rec_type IS RECORD -->定义一个记录类型 ( empno emp.empno%TYPE ,ename emp.ename%TYPE ,hiredate emp.hiredate%TYPE ); TYPE ref_cur_type IS REF CURSOR -->定义了一个游标变量且返回类型为emp_rec_type的记录类型 RETURN emp_rec_type; emp_cv ref_cur_type; -->声明游标变量 emp_rec emp_rec_type; -->声明记录类型变量BEGIN OPEN emp_cv FOR SELECT empno, ename, hiredate FROM emp WHERE ename LIKE 'A%'; LOOP FETCH emp_cv INTO emp_rec; -->将游标变量的结果保存到记录变量中 EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.put_line( 'Name = ' || emp_rec.ename || '; ' || 'Hire Date = ' || emp_rec.hiredate ); END LOOP; CLOSE emp_cv;END;4、绑定游标变量的结果到集合 DECLARE TYPE emp_rec_type IS RECORD -->定义一个记录类型 ( empno emp.empno%TYPE ,ename emp.ename%TYPE ,hiredate emp.hiredate%TYPE ); TYPE emp_nst_type IS TABLE OF emp_rec_type -->定义基于记录类型的联合数组 INDEX BY PLS_INTEGER; TYPE ref_cur_type IS REF CURSOR -->定义游标变量并返回记录类型 RETURN emp_rec_type; -->此处如果使用emp_nst_type会收到错误 emp_cv ref_cur_type; -->声明游标变量 emp_collect emp_nst_type; -->声明复合数据类型变量BEGIN OPEN emp_cv FOR SELECT empno, ename, hiredate FROM emp WHERE ename LIKE 'A%'; FETCH emp_cv BULK COLLECT INTO emp_collect; -->使用bulk collect into将游标记录批量提取到复合变量中 CLOSE emp_cv; FOR i IN emp_collect.FIRST .. emp_collect.LAST -->输出复合变量中的结果 LOOP DBMS_OUTPUT.put_line( 'Name = ' || emp_collect( i ).ename || ', hiredate = ' || emp_collect( i ).hiredate ); END LOOP;END;5、SQL*Plus中操作游标变量--下面基于前面定义的包get_data,我们在SQL*Plus中来调用包中的游标变量并返回数据scott@CNMMBO> variable lv_ref_cv refcursor;scott@CNMMBO> variable lv_choice number;scott@CNMMBO> exec :lv_choice:=2;PL/SQL procedure successfully completed.scott@CNMMBO> exec get_data.open_cv(:lv_ref_cv,:lv_choice);PL/SQL procedure successfully completed.scott@CNMMBO> print lv_ref_cv DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON6、PL/SQL中调用包中的游标变量DECLARE v_ref_cv get_data.ref_cur_type; -->声明一个基于包的弱类型游标变量 v_ref_cv_rec dept%ROWTYPE; -->由于为弱类型,所以我们使用了对应表的记录类型作为返回类型来声明记录变量 -->如果包中定义的为强类型游标变量,则上面的声明可以直接写为return_type%rowtype v_choice PLS_INTEGER := 2; BEGIN get_data.open_cv( v_ref_cv, v_choice ); LOOP FETCH v_ref_cv INTO v_ref_cv_rec; EXIT WHEN v_ref_cv%NOTFOUND; DBMS_OUTPUT.put_line( 'current rec is ' || v_ref_cv_rec.dname || ',' || v_ref_cv_rec.loc ); END LOOP;END;-->Author : Robinson Cheng-->Blog : http://blog.csdn.net/robinson_0612current rec is ACCOUNTING,NEW YORKcurrent rec is RESEARCH,DALLAScurrent rec is SALES,CHICAGOcurrent rec is OPERATIONS,BOSTONPL/SQL procedure successfully completed.7、基于弱类型定义返回类型导致异常DECLARE TYPE weak_ref_cur_type IS REF CURSOR; weak_ref_cur weak_ref_cur_type; weak_ref_rec weak_ref_cur%ROWTYPE; -->产生一个 PL/SQL 320 错误-- weak_ref_rec dept%ROWTYPE; --> 正确,使用自定义的返回类型-- weak_ref_rec emp%ROWTYPE; -->如果定义了与返回类型不兼容的类型则在运行时出现异常 -->ORA-06504: PL/SQL: Return types of Result Set variables or query do not matchBEGIN OPEN weak_ref_cur FOR SELECT * FROM dept; FETCH weak_ref_cur INTO weak_ref_rec; DBMS_OUTPUT.put_line( 'Current Rec is ' || weak_ref_rec.dname || ',' || weak_ref_rec.loc ); CLOSE weak_ref_cur;END;ERROR at line 5:ORA-06550: line 5, column 24:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 5, column 24:PL/SQL: Item ignoredORA-06550: line 10, column 28:PLS-00320: the declaration of the type of this expression is incomplete or malformed8、强类型编译时异常-->下面定义的强类型游标变量中返回类型不兼容,则编译时抛出异常DECLARE TYPE strong_ref_cur_type IS REF CURSOR RETURN emp%ROWTYPE; strong_ref_cur strong_ref_cur_type; strong_ref_rec emp%ROWTYPE;BEGIN OPEN strong_ref_cur FOR SELECT * FROM dept; -->定义的返回类型为emp%ROWTYPE,而此时的查询为dept表类型 FETCH strong_ref_cur INTO strong_ref_rec; CLOSE strong_ref_cur;END;ERROR at line 8:ORA-06550: line 8, column 28:PLS-00382: expression is of wrong typeORA-06550: line 8, column 4:PL/SQL: SQL Statement ignored
六、使用游标变量注意事项
1、不能在包规范中定义游标变量
2、不能在其它服务器的远程子程序中使用游标变量,不能把游标变量传给通过数据库连接被调用的过程
3、当处理游标变量时,不要一起使用FOR UPDATE和OPEN FOR
4、不能使用比较运算符来测试游标变量的等价性、不等价性或者非空性
5、游标变量不能被赋予NULL值
6、REF CURSOR类型不能在CREATE TABLE或者VIEW语句中使用,因为不存在数据库列的等价数据类型
7、使用游标变量的存储过程只能被用作查询块数据源,它不能用于DML块数据源。REF CURSOR适合于只依赖于SQL语句中(不是PL/SQL语句中)变
量的查询
8、不能在联合数组、嵌套表、或者变长数组中存储游标变量
9、如果向PL/SQL传递主机游标变量,不能在服务器检索它,除非是在相同服务器调用中打开它
七、更多参考
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
- PL/SQL 游标变量
- PL/SQL 游标变量
- PL/SQL 游标变量
- PL/SQL 游标变量
- pl/sql 游标变量实例
- PL/SQL 游标变量
- Oracle pl/sql编程 15---游标变量
- PL/SQL中游标和游标变量的使用
- PL/SQL中游标和游标变量的使用
- PL/SQL中游标和游标变量的使用
- PL/SQL中游标和游标变量的使用
- 使用PL/SQL 记录变量接收游标数据
- PL/SQL游标
- PL/SQL中的游标
- PL/SQL游标
- Oracle PL/SQL游标
- PL/SQL:游标
- PL/SQL游标总结
- Android中如何查看内存(上)
- CXF
- 为什么现在的外贸建站这么的难呢
- Struts2拦截器
- java.util.concurrent 多线程框架---线程池编程(一)
- PL/SQL 游标变量
- asp.net,cookie,写cookie,取cookie
- Linux服务器上11种网络连接状态
- VC相关学习笔记
- 线程池--java.util.concurrent 多线程框架(二)
- 学VC时做的一些笔记
- 22
- java.util.concurrent 多线程框架---线程池编程(三)
- CreateDocumentFragment的用处