REFCURSOR总结
REF 游标:
REF游标又称为动态游标,在运行时使不同的语句与之关联,动态关联结果集的临时对象,即在运行的时候动态决定执行查询。REF游标可以使用游标变量。
游标变量:
游标变量是一种引用REF游标类型的变量,只想动态关联的结果集。
游标变量的类型:
1.具有约束的游标变量,具有返回类型的游标变量也称为强游标。
2.无约束的游标变量,没有返回类型的游标变量也称为弱游标。
REF游标的作用:
实现程序间传递结果集的功能,利用REF cursor 也可以实现bulk sql 从而提高sql性能。
静态游标和REF游标的区别:
1.静态游标是静态定义,REF游标是动态关联
2.使用REF游标需要REF游标变量
3.REF游标可以作为参数进行传递,而静态游标是不能作为参数传递的。
REF游标的语法:
1.强类型REF游标:指定return datatype,REF游标变量的类型必须和return datatype一致。
语法:TYPE TYPE_NAME IS REF CURSOR RETURN DATATYPE;
2.弱类型REF游标:不指定return datatype 能和任何类型的cursor 变量匹配,用于获取任何结果集。
语法:TYPE TYPE_NAME IS REF CURSOR;
SYS_REFCURSOR:
主要用在过程中返回结果集,如果仅仅为了返回值,无需自己在包头中定义游标类型,只需直接使用sys_refcursor 即可轻松返回结果。
提示:
使用静态光标--通过静态SQL(但不用ref光标)--比使用ref光标效率高,而ref光标的使用仅限于以下几种情况:
把结果集返回给客户端;
在多个子例程之间共享光标(实际上与上面提到的一点非常类似);
没有其他有效的方法来达到你的目标时,则使用ref光标,正如必须用动态SQL时那样;
简言之,首先考虑使用静态SQL,只有绝对必须使用ref光标时才使用ref光标,也有人建议尽量使用隐式游标,避免编写附加的游标控制代码(声明,打开,获取,关闭),也不需要声明变量来保存从游标中获取的数据。
PACKAGE
示例中涉及到包(package)和包体(package body)以下为package 用法的说明:
包结构:
一个包由两个分开的部分组成:包规范和包体
1.包定义(package):包定义部分是为应用程序的接口,声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素,这些元素为包的公有元素。
语法:
CREATE [OR REPLACE] PACKAGE PACKAGE_NAME
{IS|AS}
[公有数据类型定义]
[公有游标声明]
[公有变量、常量声明]
[公有子程序声明]
END [PACKAGE_NAME];
2.包主体(package body):包主体则是包定义部分的具体实现,它定义了包定义部分所声明的游标和子程序,在包主体中还可以声明包的私有元素。如果在包主体中的游标或子程序并没有在包头中定义,那么这个游标或子程序是私有的。
语法:
CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME
{IS |AS}
[私有数据类型定义]
[私有变量、常量]
[私有子程序声明和定义]
[公有子程序定义]
BEGIN
执行部分(初始化部分);
END [PACKAGE_NAME];
与类相同,包中的程序元素也分为公有元素和私有元素两种,这两种元素的区别是他们允许访问程序范围不同,即他们的作用域不同。公有元素不仅可以被包中的函数、过程所调用也可以被包外的PL/SQL程序访问,而私有元素只能被包内的函数和过程所访问。包定义和包主体分开编译,并作为两部分分开的对象存放在数据库字典中。包定义一定要在包主体前面编译,包主体可以没有,但是包定义一定要有,包的名称和包体的名称要保持一致。
创新互联从2013年开始,是专业互联网技术服务公司,拥有项目网站制作、网站建设网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元丰宁做网站,已为上家服务,为丰宁各地企业和个人服务,联系电话:18980820575
示例1:强类型REF游标
DECLARE
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
------游标仅能打开DJ_DJB表的数据
REF_C REF_CURSOR;
----游标变量
V_DJB DJ_DJB%ROWTYPE;
SELECTION VARCHAR2(1):=('&请输入:');
BEGIN
IF SELECTION='1' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
ELSIF SELECTION='2' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
END IF;
LOOP
FETCH REF_C INTO V_DJB;
EXIT WHEN REF_C%NOTFOUND;
DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
END LOOP;
CLOSE REF_C;
END;
示例2:弱类型REF游标
DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;
V_V1 VARCHAR2(100);
SELECTION VARCHAR2(1):=('&请输入:');
BEGIN
IF SELECTION='1' THEN
OPEN REF_C FOR SELECT TO_CHAR(BDCZH) FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
-------弱类型游标对目标表没有限制,数据可以使来自任何表
ELSIF SELECTION='2' THEN
OPEN REF_C FOR SELECT TO_CHAR(QLRMC) FROM DJ_QLRGL WHERE SLBH LIKE '2017%' AND ROWNUM<10 AND QLRLX='权利人';
-------弱类型游标对目标表没有限制,数据可以使来自任何表
END IF;
LOOP
FETCH REF_C INTO V_V1;
EXIT WHEN REF_C%NOTFOUND;
DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
DBMS_OUTPUT.PUT_LINE('输出结果值:'||V_V1);
END LOOP;
CLOSE REF_C;
END;
示例3:SYS_REFCURSOR
DECLARE
V_DJB DJ_DJB%ROWTYPE;
REF_C SYS_REFCURSOR; ---利用SYS_REFCUSOR 来声明游标变量
SELECTION VARCHAR2(1):=('&请输入:');
BEGIN
IF SELECTION='1' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
ELSIF SELECTION='2' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<11;
END IF;
LOOP
FETCH REF_C INTO V_DJB;
EXIT WHEN REF_C%NOTFOUND;
DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
END LOOP;
CLOSE REF_C;
END;
示例4:REF 游标作为参数传递
CREATE OR REPLACE PACKAGE EMP_T
IS
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
PROCEDURE GET_BDCZH(REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2);
PROCEDURE RETURN_C(SELECTION VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY EMP_T
IS
PROCEDURE GET_BDCZH (REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2)
IS
BEGIN
IF SELECTION =1 THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
ELSIF SELECTION=2 THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
END IF;
END;
PROCEDURE RETURN_C (SELECTION VARCHAR2)
IS
V_1 REF_CURSOR;
V_DJB dj_djb%ROWTYPE;
BEGIN
GET_BDCZH(V_1,SELECTION);
LOOP
FETCH V_1 INTO V_DJB;
EXIT WHEN V_1%NOTFOUND;
DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
END LOOP;
CLOSE V_1;
END;
END;
---调用
begin
EMP_T.RETURN_C(1);
END;
示例5:使用BULK COLLECT INTO.......批量提取数据
DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;
TYPE R_DJB IS TABLE OF DJ_DJB%ROWTYPE INDEX BY BINARY_INTEGER;
V_DJB R_DJB;
SELECTION VARCHAR2(1):=('&请输入:');
BEGIN
IF SELECTION='1' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<100;
ELSIF SELECTION='2' THEN
OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<100;
END IF;
LOOP
FETCH REF_C BULK COLLECT INTO V_DJB LIMIT 10;
---EXIT WHEN REF_C%NOTFOUND;
DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
---FOR I IN 1..V_DJB.COUNT LOOP
FOR I IN V_DJB.FIRST..V_DJB.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V_DJB(I).SLBH||','||V_DJB(I).BDCZH||'I 的值为:'||I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('ROWCOUNT的值:'||REF_C%ROWCOUNT);
EXIT WHEN REF_C%NOTFOUND;
END LOOP;
END;
BY WOLIAHAITO 2018.04.03
网页名称:REFCURSOR总结
网页地址:http://myzitong.com/article/pedsid.html