- 浏览: 244593 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
Orc3Ming:
哦,脚本的解释路径,不是注释。
RHEL6.4更改为CentOS6.4的yum源 -
Orc3Ming:
将第一行 #!/usr/bin/python 修改为 #!/u ...
RHEL6.4更改为CentOS6.4的yum源 -
anniecharlotte:
请问:如果登录某个节点
执行nodetool ring查看集群 ...
Cassandra集群 -
Q_Qkings8:
给了我好的提示,已经成功的解决了问题
Eclipse Debug Source not found for -
kevin.wang:
feijin1981 写道我也有同样的问题,但是Window ...
Eclipse Debug Source not found for
REF CURSOR 小结
利用REF CURSOR,可以在程序间传递结果集(一个程序里打开游标变量,在另外的程序里处理数据)。
也可以利用REF CURSOR实现BULK SQL,提高SQL性能。
REF CURSOR分两种,Strong REF CURSOR 和 Weak REF CURSOR。
Strong REF CURSOR:指定retrun type,CURSOR变量的类型必须和return type一致。
DECLARE
TYPE strongcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv strongcurtyp;
Weak REF CURSOR:不指定return type,能和任何类型的CURSOR变量匹配。
DECLARE
TYPE weakcurtyp IS REF CURSOR;
weak_cv weakcurtyp;
any_cv SYS_REFCURSOR; --使用SYS_REFCURSOR可以使变量any_cv跟任何weak REF CURSOR类型匹配。
使用Strong REF CURSOR例子
CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSOR
PROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv,返回指定的结果集。
END emp_data;
=============================================
CREATE OR REPLACE PACKAGE BODY emp_data AS
--procedure open_emp_cv-----------------------------
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETER
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE empno < 7800;
ELSIF choice = 2 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE SAL < 1000;
ELSIF choice = 3 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE ename like 'J%';
END IF;
END;
--procedure retrieve_data----------------------------------
PROCEDURE retrieve_data(choice INT) IS
return_cv empcurtyp; --定义传入open_emp_cv的CURSOR变量
return_row emp%ROWTYPE;
invalid_choice EXCEPTION;
BEGIN
open_emp_cv(return_cv, choice); --调用 procedure OPEN_EMP_CV
IF choice = 1 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less than 7800');
ELSIF choice = 2 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
ELSIF choice = 3 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
ELSE
RAISE invalid_choice;
END IF;
LOOP
FETCH return_cv
INTO return_row;
EXIT WHEN return_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
return_row.sal);
END LOOP;
EXCEPTION
WHEN invalid_choice THEN
DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
END;
END emp_data;
================================
执行:
SQL> EXEC emp_data.retrieve_data(1);
EMPLOYEES with empno less than 7800
7369--SMITH--800
7499--ALLEN--1600
7521--WARD--1250
7566--JONES--2975
7654--MARTIN--1250
7698--BLAKE--2850
7782--CLARK--2450
7788--SCOTT--3000
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(2);
EMPLOYEES with salary less than 1000
7369--SMITH--800
7900--JAMES--950
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(3);
EMPLOYEES with name starts with 'J'
7566--JONES--2975
7900--JAMES--950
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(34);
The CHOICE should be in one of (1,2,3)!
PL/SQL procedure successfully completed
使用Weak REF CURSOR例子
--procedure open_cv---------------------------------------
create or replace procedure open_cv(choice IN INT,
return_cv OUT SYS_REFCURSOR) is
--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义
begin
if choice = 1 then
open return_cv for 'select * from emp';
elsif choice = 2 then
open return_cv for 'select * from dept';
end if;
end open_cv;
--procedure retrieve_data------------------------------------
create or replace procedure retrieve_data(choice IN INT) is
emp_rec emp%rowtype;
dept_rec dept%rowtype;
return_cv SYS_REFCURSOR;
invalid_choice exception;
begin
if choice=1 then
dbms_output.put_line('employee information');
open_cv(1,return_cv); --调用procedure open_cv;
loop
fetch return_cv into emp_rec;
exit when return_cv%notfound;
dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);
end loop;
elsif choice=2 then
dbms_output.put_line('department information');
open_cv(2,return_cv);
loop
fetch return_cv into dept_rec;
exit when return_cv%notfound;
dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);
end loop;
else
raise invalid_choice;
end if;
exception
when invalid_choice then
dbms_output.put_line('The CHOICE should be one of 1 and 2!');
when others then
dbms_output.put_line('Errors in procedure retrieve_data');
end retrieve_data;
-----------------------------------------------------------------
执行:
SQL> exec retrieve_data(1);
employee information
7369-SMITH-800
7499-ALLEN-1600
7521-WARD-1250
7566-JONES-2975
7654-MARTIN-1250
7698-BLAKE-2850
......
PL/SQL procedure successfully completed
SQL> exec retrieve_data(2);
department information
10-ACCOUNTING-NEW YORK
20-RESEARCH-DALLAS
30-SALES-CHICAGO
40-OPERATIONS-BOSTON
PL/SQL procedure successfully completed
用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.
SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;
Table created
-------------------------------------------------------
create or replace procedure REF_BULK is
type empcurtyp is ref cursor;
type idlist is table of emp.empno%type;
type namelist is table of emp.ename%type;
type sallist is table of emp.sal%type;
emp_cv empcurtyp;
ids idlist;
names namelist;
sals sallist;
row_cnt number;
begin
open emp_cv for
select empno, ename, sal from emp;
fetch emp_cv BULK COLLECT
INTO ids, names, sals; --BULK COLLECT INTO instead of INTO
close emp_cv;
for i in ids.first .. ids.last loop
dbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||
' salary=' || sals(i));
end loop;
forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOP
insert into tab2 values (ids(i), names(i), sals(i));
commit;
select count(*) into row_cnt from tab2;
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('The row number of tab2 is ' || row_cnt);
end REF_BULK;
------------------------------------------------------------
执行:
SQL> exec ref_bulk;
id=7369 name=SMITH salary=800
id=7499 name=ALLEN salary=1600
id=7521 name=WARD salary=1250
id=7566 name=JONES salary=2975
id=7654 name=MARTIN salary=1250
id=7698 name=BLAKE salary=2850
id=7782 name=CLARK salary=2450
id=7788 name=SCOTT salary=3000
id=7839 name=KING salary=5000
id=7844 name=TURNER salary=1500
id=7876 name=ADAMS salary=1100
id=7900 name=JAMES salary=950
id=7902 name=FORD salary=3000
id=7934 name=MILLER salary=1300
-----------------------------------
The row number of tab2 is 14
PL/SQL procedure successfully completed
利用REF CURSOR,可以在程序间传递结果集(一个程序里打开游标变量,在另外的程序里处理数据)。
也可以利用REF CURSOR实现BULK SQL,提高SQL性能。
REF CURSOR分两种,Strong REF CURSOR 和 Weak REF CURSOR。
Strong REF CURSOR:指定retrun type,CURSOR变量的类型必须和return type一致。
DECLARE
TYPE strongcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv strongcurtyp;
Weak REF CURSOR:不指定return type,能和任何类型的CURSOR变量匹配。
DECLARE
TYPE weakcurtyp IS REF CURSOR;
weak_cv weakcurtyp;
any_cv SYS_REFCURSOR; --使用SYS_REFCURSOR可以使变量any_cv跟任何weak REF CURSOR类型匹配。
使用Strong REF CURSOR例子
CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSOR
PROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv,返回指定的结果集。
END emp_data;
=============================================
CREATE OR REPLACE PACKAGE BODY emp_data AS
--procedure open_emp_cv-----------------------------
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETER
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE empno < 7800;
ELSIF choice = 2 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE SAL < 1000;
ELSIF choice = 3 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE ename like 'J%';
END IF;
END;
--procedure retrieve_data----------------------------------
PROCEDURE retrieve_data(choice INT) IS
return_cv empcurtyp; --定义传入open_emp_cv的CURSOR变量
return_row emp%ROWTYPE;
invalid_choice EXCEPTION;
BEGIN
open_emp_cv(return_cv, choice); --调用 procedure OPEN_EMP_CV
IF choice = 1 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less than 7800');
ELSIF choice = 2 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
ELSIF choice = 3 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
ELSE
RAISE invalid_choice;
END IF;
LOOP
FETCH return_cv
INTO return_row;
EXIT WHEN return_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
return_row.sal);
END LOOP;
EXCEPTION
WHEN invalid_choice THEN
DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
END;
END emp_data;
================================
执行:
SQL> EXEC emp_data.retrieve_data(1);
EMPLOYEES with empno less than 7800
7369--SMITH--800
7499--ALLEN--1600
7521--WARD--1250
7566--JONES--2975
7654--MARTIN--1250
7698--BLAKE--2850
7782--CLARK--2450
7788--SCOTT--3000
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(2);
EMPLOYEES with salary less than 1000
7369--SMITH--800
7900--JAMES--950
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(3);
EMPLOYEES with name starts with 'J'
7566--JONES--2975
7900--JAMES--950
PL/SQL procedure successfully completed
SQL> EXEC emp_data.retrieve_data(34);
The CHOICE should be in one of (1,2,3)!
PL/SQL procedure successfully completed
使用Weak REF CURSOR例子
--procedure open_cv---------------------------------------
create or replace procedure open_cv(choice IN INT,
return_cv OUT SYS_REFCURSOR) is
--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义
begin
if choice = 1 then
open return_cv for 'select * from emp';
elsif choice = 2 then
open return_cv for 'select * from dept';
end if;
end open_cv;
--procedure retrieve_data------------------------------------
create or replace procedure retrieve_data(choice IN INT) is
emp_rec emp%rowtype;
dept_rec dept%rowtype;
return_cv SYS_REFCURSOR;
invalid_choice exception;
begin
if choice=1 then
dbms_output.put_line('employee information');
open_cv(1,return_cv); --调用procedure open_cv;
loop
fetch return_cv into emp_rec;
exit when return_cv%notfound;
dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);
end loop;
elsif choice=2 then
dbms_output.put_line('department information');
open_cv(2,return_cv);
loop
fetch return_cv into dept_rec;
exit when return_cv%notfound;
dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);
end loop;
else
raise invalid_choice;
end if;
exception
when invalid_choice then
dbms_output.put_line('The CHOICE should be one of 1 and 2!');
when others then
dbms_output.put_line('Errors in procedure retrieve_data');
end retrieve_data;
-----------------------------------------------------------------
执行:
SQL> exec retrieve_data(1);
employee information
7369-SMITH-800
7499-ALLEN-1600
7521-WARD-1250
7566-JONES-2975
7654-MARTIN-1250
7698-BLAKE-2850
......
PL/SQL procedure successfully completed
SQL> exec retrieve_data(2);
department information
10-ACCOUNTING-NEW YORK
20-RESEARCH-DALLAS
30-SALES-CHICAGO
40-OPERATIONS-BOSTON
PL/SQL procedure successfully completed
用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.
SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;
Table created
-------------------------------------------------------
create or replace procedure REF_BULK is
type empcurtyp is ref cursor;
type idlist is table of emp.empno%type;
type namelist is table of emp.ename%type;
type sallist is table of emp.sal%type;
emp_cv empcurtyp;
ids idlist;
names namelist;
sals sallist;
row_cnt number;
begin
open emp_cv for
select empno, ename, sal from emp;
fetch emp_cv BULK COLLECT
INTO ids, names, sals; --BULK COLLECT INTO instead of INTO
close emp_cv;
for i in ids.first .. ids.last loop
dbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||
' salary=' || sals(i));
end loop;
forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOP
insert into tab2 values (ids(i), names(i), sals(i));
commit;
select count(*) into row_cnt from tab2;
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('The row number of tab2 is ' || row_cnt);
end REF_BULK;
------------------------------------------------------------
执行:
SQL> exec ref_bulk;
id=7369 name=SMITH salary=800
id=7499 name=ALLEN salary=1600
id=7521 name=WARD salary=1250
id=7566 name=JONES salary=2975
id=7654 name=MARTIN salary=1250
id=7698 name=BLAKE salary=2850
id=7782 name=CLARK salary=2450
id=7788 name=SCOTT salary=3000
id=7839 name=KING salary=5000
id=7844 name=TURNER salary=1500
id=7876 name=ADAMS salary=1100
id=7900 name=JAMES salary=950
id=7902 name=FORD salary=3000
id=7934 name=MILLER salary=1300
-----------------------------------
The row number of tab2 is 14
PL/SQL procedure successfully completed
发表评论
-
connect by prior
2012-01-29 14:41 709http://www.blogjava.net/freeman ... -
Oracle 解锁
2011-08-22 11:32 784在for update 查询时,线程被阻塞 查询阻塞线程: ... -
exists与in
2011-07-27 20:53 9931. exists: 不缓存exists() ... -
outer join , inner join , cross join
2010-11-30 13:18 859select * from table1; ID NAME 1 ... -
查看Oracle表空间 tablespace
2010-10-29 09:32 10991. 查看所有表空间大小 SQL> select ta ... -
Oracle--create user& tablespace
2010-10-29 09:22 2569-建立表空间(oracle中的tablespace(表空间)就 ... -
win7下安装Oracle10g
2010-10-03 01:01 1029解决方案: 步骤一:在解压出的oracle文件夹中搜 ... -
Oracle 关于密码及密码文件
2010-09-29 10:49 1588以安装Oracle的用户进入系统: 忘记密码: 1.在命令行下 ... -
修改Oracle端口
2010-09-02 19:20 1364<script>function StorePag ... -
关于Oracle 10g 默认用户、密码及登录问题
2010-09-02 19:02 1897Oracle 10g 默认用户、密码 参见http://sap ... -
Oracle 之 Sequence
2010-08-13 09:01 7981、Create Sequence 你首先要有CREATE ... -
存储过程中 调用 Java
2010-08-12 11:39 837一、如何创建java存储过程? 通常有三种方法来创建java存 ... -
Java 调用Oracle 存储过程
2010-08-12 10:45 15911、存储过程是指保存在 ... -
Java 调用Oracle 函数
2010-08-12 10:26 1478oracle的存储过程和函数区别 1、都可以使用out参数返 ... -
Merge Into
2010-08-12 10:22 1339用途 merge 命令可以用 ... -
Returnning Into 总结
2010-08-12 09:45 932ORACLE的DML语句中可以指定RETURNING语句。使用 ... -
Pro * C 的使用(三)
2010-08-11 18:05 1067(7) 动态定义语句 SQL语句分动态定义语句和静态 ... -
Pro * C 的使用(二)
2010-08-11 18:02 12922.应用程序体 在Pro*C ... -
Pro * C 的使用(一)
2010-08-11 17:59 1112一 Pro*C 程序概述: ... -
fetch bulk collect into 批量效率的读取游标数据
2010-08-11 16:14 1159通常我们获取游标数据是用 fetch some_cursor ...
相关推荐
本文档演示如何从 Java 存储过程将 JDBC ResultSet 作为 REF CURSOR 返回。JDBC ResultSet 是一个表示数据库的数据表,通常通过执行查询数据库的语句产生该表。REF CURSOR 是 PL/SQL 中相应的类型。Java 存储过程的...
在数据开发中,有时你需要合并两个动态游标sys_refcursor。 开发一个存储过程PROC_A,这个过程业务逻辑相当复杂,代码篇幅较长。一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中,还要循环调用...
使用此程序包,您现在可以运行返回RefCursor的存储过程,或使用数组绑定计数来执行带有参数数组的sql语句。 受支持的Oracle特定属性 OracleParameter(托管和非托管) OracleDbType枚举(托管提供者使用的所有成员...
type qdy_cursor is ref cursor; end qdy_package; create or replace procedure qdy_pro2(p_cursor out qdy_package.qdy_cursor) is begin open p_cursor for select * from emp; end; --函数 create function ...
type type_cur is ref cursor; --定义游标变量用于返回记录集 procedure Pagination(Pindex in number, --要显示的页数索引,从0开始 Psql in varchar2, --产生分页数据的查询语句 Psize in number, --每页...
Devart_UniDAC_7.4.12_Professional_D7-D10.3_Rio_一键安装版 ...Universal Data Access Components History unidac@devart....REFCURSOR is fixed SQLServer data provider Bug with the "Cannot modify a read...
var c_cur refcursor; exec proc_SpPag(1,'SELECT * FROM T_PERSONINFO_TYPE ',10,:c_Preccount,:c_Ppagecount,:c_cur); print c_Preccount; print c_Ppagecount; print c_cur; 参数说明: 第一个参数为当前页; 第...
Bug with open REFCURSOR is fixed SQLServer data provider Bug with the "Cannot modify a read-only dataset" error in Android is fixed MySQL data provider Bug with setting the data type of the parameter ...
Oracle.DataAccess.dll ... Oracle.ManagedDataAccess.dll ... 两者均可定义Oracle存过游标参数 ...OracleParameter ps_listcsr = new OracleParameter();...ps_listcsr.OracleDbType = OracleDbType.RefCursor;
CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)as BEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual; END; ...
文章目录游标1.1 显式游标1.1.1 创建游标1.1.2 打开游标1.1.3 获取数据1.1.4 关闭游标1.1.5 使用实例1.2 隐式游标1.2.2 游标属性1.3 ...详情参考oracle中cursor与refcursor及sys_refcursor的区别 1.1 显式游标 1.1.1 创
1. DECLARE TYPE ref_cursor_type IS ref CURSOR; v_mycursor ref_cursor_type; TYPE id_list IS TABLE OF integer; TYPE name_list IS TABLE OF varchar2(30); v_tabid id_list:=id_list(); v_tabname name_list:=...
第一种情况是返回的游标是某个具体的表或视图的数据,如: SQL-Code: 代码如下: CREATE OR REPLACE PROCEDURE P_TESTA ( PRESULT OUT SYS_REFCURSOR ) AS BEGIN OPEN PRESULT FOR SELECT * FROM USERS; END P_TESTA;...
由于需要返回查询出来的结果集,需要在PL/SQL中创建一个package,这个包里面定义一个refcursor类型,用于记录sql语句查询出来的结果集。创建包的代码如下: create or replace package pagingPackage as type ...
实际上是利用REF CURSOR 代码如下: –procedure返回记录集: ———————-声明一个Package————– CREATE OR REPLACE PACKAGE pkg_test AS TYPEmyrctypeIS REF CURSOR; PROCEDURE get_r(p_id NUMBER,p_rc OUT ...
有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor;...
3) 参考型:REF CURSOR(游标)、REF object_type 4) LOB(Large Object) 2. %TYPE [变量名] [表名.字段名] [%TYPE] 表示变量具有与数据库的表中某一字段相同的类型 例:v_FirstName s_emp.first_name%TYPE;...
PostgreSQL R2DBC驱动程序 该项目包含的实现。 此实现不打算直接使用,而应用作要委派给人道的客户端库的支持实现。...获取的REFCURSOR使用io.r2dbc.postgresql.api.RefCursor 用于注册Codec的扩
my_cur sys_refcursor; val varchar2(1000); v_year varchar2(50); v_max_part_name varchar2(256); v_maxdate_part_name varchar2(256); v_part_tablespace varchar2(256); v_part_sums int; begin for v_...