ref cursor sys_refcursor传递结果集

最新推荐文章于 2019-12-06 18:47:03 发布

原创最新推荐文章于 2019-12-06 18:47:03 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

Oralce开发同时被 2 个专栏收录

19 篇文章

订阅专栏

PLSQL

11 篇文章

订阅专栏

本文详细介绍了PL/SQL中REF Cursor的使用方法，包括如何创建和使用不同类型的REF Cursor，如弱类型和强类型REF Cursor。通过示例展示了如何通过REF Cursor在PL/SQL过程中传递结果集，以及如何与表类型结合使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> set linesize 200 pages 100
SQL> variable v refcursor;
SQL> exec open :v for 'select * from emp';

PL/SQL procedure successfully completed.

SQL> print :v;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------------------------------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 noname
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 noname
7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 noname
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 noname
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 RESEARCH
7839 KING PRESIDENT 17-NOV-81 5000 10 ACCOUNTING
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 noname
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 RESEARCH
7900 JAMES CLERK 7698 03-DEC-81 950 30 noname
7902 FORD ANALYST 7566 03-DEC-81 3000 20 RESEARCH
7934 MILLER CLERK 7782 23-JAN-82 1300 10 ACCOUNTING
1111 YODA JEDI 17-NOV-81 5000 noname

15 rows selected.

sys_refcursor 做为参数传递结果集:
create or replace procedure pr_sys_refcursor(v_sys out sys_refcursor)
as
BEGIN
open v_sys for 'select * from emp';
end;
/

declare
type emp_type is table of emp%rowtype;
emp_tab emp_type;
v sys_refcursor;
BEGIN
pr_sys_refcursor(v);
fetch v bulk collect into emp_tab;
for i in 1..emp_tab.count LOOP
dbms_output.put_line(emp_tab(i).ename||','||emp_tab(i).empno);
end loop;
end;

sys_refcursor 函数返回表
create or replace function f_get_emp return sys_refcursor
is
v_emp sys_refcursor;
BEGIN
open v_emp for 'select * from emp';
return v_emp;
end;
/

SQL> select f_get_emp from dual;

F_GET_EMP
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

15 rows selected.

ref cursor 做为参数传递结果集
create or replace package pkg_ref_cursor
as
type ref_type is ref cursor;
function f_ref return ref_type;
end;
/

create or replace package body pkg_ref_cursor
is
function f_ref return ref_type
is
cursor_ref ref_type;
BEGIN
open cursor_ref for 'select * from emp';
return cursor_ref;
end;
end;
/

SQL> select pkg_ref_cursor.f_ref from dual;

F_REF
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

15 rows selected.

REF 参照类型(ref cursor 程序间传递结果集)
create or replace package ref_package as
TYPE emp_record_type IS RECORD(
ename VARCHAR2(25),
job VARCHAR2(10),
sal NUMBER(7, 2));
TYPE weak_ref_cursor IS REF CURSOR; --弱类型，不规定返回值
TYPE strong_ref_cursor IS REF CURSOR return emp%rowtype; --强类型，规定返回值
TYPE strong_ref2_cursor IS REF CURSOR return emp_record_type; --强类型，规定返回值
end ref_package;
/

弱类型ref测试:
create or replace procedure test_ref_weak(p_deptno emp.deptno%type,
p_cursor out ref_package.weak_ref_cursor) is
begin
case p_deptno
when 10 then
open p_cursor for
select empno, ename, sal, deptno from emp where deptno = p_deptno;
when 20 then
open p_cursor for
select * from emp where deptno = p_deptno;
end case;
end;
/

var c refcursor
exec test_ref_weak(10,:c); --传入不同形式参数，走不同分支，返回不同结果集！
print c
exec test_ref_weak(20,:c);
print c

oracle 9i 中定义了系统弱游标类型 sys_refcursor
create or replace procedure test_p(p_deptno number,
p_cursor out sys_refcursor) is
begin
open p_cursor for
select * from emp where deptno = p_deptno;
end test_p;
/

create or replace function getemp return sys_refcursor as
emp_cursor sys_refcursor;
begin
open emp_cursor for
select * from emp;
return emp_cursor;
end;
/
select getemp from dual;

强类型ref测试:查询结构必须符合游标返回值结构，否则报错：
PLS-00382: expression is of wrong type
create or replace procedure test_ref_strong(p_deptno emp.deptno%type,
p_cursor out ref_package.strong_ref_cursor) is
begin
open p_cursor for
select * from emp where deptno = p_deptno;
end test_ref_strong;
/

var c refcursor
exec test_ref_strong(10,:c);
print c;

create or replace procedure test_call is
c_cursor ref_package.strong_ref_cursor;
r_emp emp%rowtype;
begin
test_ref_strong(10, c_cursor);
loop
fetch c_cursor
into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.ename);
end loop;
close c_cursor;
end test_call;
/

exec test_call;

强类型ref测试:
create or replace procedure test_ref2_strong(p_deptno emp.deptno%type,
p_cursor out ref_package.strong_ref2_cursor) is
begin
open p_cursor for
select ename, job, sal from emp where deptno = p_deptno;
end test_ref2_strong;
/

var c refcursor
exec test_ref2_strong(10,:c);

11g dbms_sql可以解析出来或者传给外部程序解析

和table结合要写管道函数的

DECLARE
TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
index_by_table index_by_type;
v_num number := 1;
BEGIN
-- Populate index by table
FOR i IN 7..10 LOOP
index_by_table(v_num) := i;
v_num := v_num + 1;
END LOOP;

v_num := index_by_table.first();
dbms_output.put_line(v_num);
v_num := index_by_table.first;
dbms_output.put_line(v_num);
v_num := index_by_table(3);
dbms_output.put_line(v_num);
end;

create or replace package PCKG_CSM_DATA_STL_SET is

TYPE RET_PATH_SET IS TABLE OF NUMBER;

TYPE CV_TYPE IS REF CURSOR;

FUNCTION F_RET_PATH_SET(I_PRTN_ID NUMBER) RETURN RET_PATH_SET PIPELINED;
end;
/

create or replace package body PCKG_CSM_DATA_STL_SET is
FUNCTION F_RET_PATH_SET(I_PRTN_ID NUMBER) RETURN RET_PATH_SET PIPELINED is
CV SYS_REFCURSOR;
V_PRTN_ID PCKG_CSM_DATA_STL_SET.RET_PATH_SET;
RS NUMBER;
BEGIN
OPEN CV FOR select empno from emp where deptno=I_PRTN_ID;
--LOOP
FETCH CV BULK COLLECT
INTO V_PRTN_ID;
EXIT WHEN V_PRTN_ID.COUNT = 0;
RS := V_PRTN_ID.FIRST(); --取第一个元素的下标，和V_PRTN_ID.first;是一样的
WHILE RS IS NOT NULL LOOP
PIPE ROW(V_PRTN_ID(RS)); --第一个下标的值
RS := V_PRTN_ID.NEXT(RS); --取下一个下标
END LOOP;
--END LOOP;
RETURN;
END F_RET_PATH_SET;
end;
/

实际上他写错了，fetch bulk collect了都，就不用loop了
create or replace package body PCKG_CSM_DATA_STL_SET is
FUNCTION F_RET_PATH_SET(I_PRTN_ID NUMBER) RETURN RET_PATH_SET PIPELINED is
CV SYS_REFCURSOR;
V_PRTN_ID PCKG_CSM_DATA_STL_SET.RET_PATH_SET;
RS NUMBER;
BEGIN
OPEN CV FOR select empno from emp where deptno=I_PRTN_ID;
FETCH CV BULK COLLECT
INTO V_PRTN_ID;
RS := V_PRTN_ID.FIRST();
WHILE RS IS NOT NULL LOOP
PIPE ROW(V_PRTN_ID(RS));
RS := V_PRTN_ID.NEXT(RS);
END LOOP;
RETURN;
END F_RET_PATH_SET;
end;
/