ref cursor sys_refcursor传递结果集

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 790217-DEC-8080020 RESEARCH

7499 ALLEN SALESMAN 769820-FEB-81160030030 noname

7521 WARD SALESMAN 769822-FEB-81125050030 noname

7566 JONES MANAGER 783902-APR-81297520 RESEARCH

7654 MARTIN SALESMAN 769828-SEP-811250140030 noname

7698 BLAKE MANAGER 783901-MAY-81285030 noname

7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING

7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH

7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING

7844 TURNER SALESMAN 769808-SEP-811500030 noname

7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH

7900 JAMES CLERK 769803-DEC-8195030 noname

7902 FORD ANALYST 756603-DEC-81300020 RESEARCH

7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING

1111 YODA JEDI 17-NOV-815000 noname

15 rows selected.

sys_refcursor 做为参数传递结果集:

create or replace procedure pr_sys_refcursor(v_sys out sys_refcursor)

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 in1..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

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME

--------------------------------------------------------------------------------------------------------------------------------

7369 SMITH CLERK 790217-DEC-8080020 RESEARCH

7499 ALLEN SALESMAN 769820-FEB-81160030030 noname

7521 WARD SALESMAN 769822-FEB-81125050030 noname

7566 JONES MANAGER 783902-APR-81297520 RESEARCH

7654 MARTIN SALESMAN 769828-SEP-811250140030 noname

7698 BLAKE MANAGER 783901-MAY-81285030 noname

7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING

7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH

7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING

7844 TURNER SALESMAN 769808-SEP-811500030 noname

7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH

7900 JAMES CLERK 769803-DEC-8195030 noname

7902 FORD ANALYST 756603-DEC-81300020 RESEARCH

7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING

1111 YODA JEDI 17-NOV-815000 noname

15 rows selected.

ref cursor 做为参数传递结果集

create or replace package pkg_ref_cursor

type ref_type isref cursor;

function f_ref return ref_type;

end;

create or replace package body pkg_ref_cursor

function f_ref return ref_type

cursor_ref ref_type;

BEGIN

open cursor_ref for'select * from emp';

return cursor_ref;

end;

SQL>select pkg_ref_cursor.f_ref from dual;

F_REF

--------------------

CURSOR STATEMENT :1

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME

--------------------------------------------------------------------------------------------------------------------------------

7369 SMITH CLERK 790217-DEC-8080020 RESEARCH

7499 ALLEN SALESMAN 769820-FEB-81160030030 noname

7521 WARD SALESMAN 769822-FEB-81125050030 noname

7566 JONES MANAGER 783902-APR-81297520 RESEARCH

7654 MARTIN SALESMAN 769828-SEP-811250140030 noname

7698 BLAKE MANAGER 783901-MAY-81285030 noname

7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING

7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH

7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING

7844 TURNER SALESMAN 769808-SEP-811500030 noname

7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH

7900 JAMES CLERK 769803-DEC-8195030 noname

7902 FORD ANALYST 756603-DEC-81300020 RESEARCH

7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING

1111 YODA JEDI 17-NOV-815000 noname

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

when10then

open p_cursor for

select empno, ename, sal, deptno from emp where deptno = p_deptno;

when20then

open p_cursor for

select*from emp where deptno = p_deptno;

endcase;

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;

exitwhen 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;

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;

如有错误，欢迎指正

ref cursor sys_refcursor传递结果集

继续阅读

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述