带sys_refcursor输出参数的存储过程
带sys_refcursor输出参数的存储过程Sql代码
CREATE OR REPLACE PROCEDURE sp_test (
p_outstrOUT VARCHAR2
,p_outintOUT NUMBER
,p_ref1 OUT SYS_REFCURSOR
,p_ref2 OUT SYS_REFCURSOR
)
AS
BEGIN
p_outstr := 'abc';
p_outint := '56789';
OPEN p_ref1 FOR SELECT ROWNUM*2 AS RN FROM DUAL CONNECT BY ROWNUM<=10;
OPEN p_ref2 FOR SELECT ROWNUM*2+1 AS RN FROM DUAL CONNECT BY ROWNUM<=10;
END sp_test;
/
过程已创建。
CREATE OR REPLACE PROCEDURE sp_test (
p_outstrOUT VARCHAR2
,p_outintOUT NUMBER
,p_ref1 OUT SYS_REFCURSOR
,p_ref2 OUT SYS_REFCURSOR
)
AS
BEGIN
p_outstr := 'abc';
p_outint := '56789';
OPEN p_ref1 FOR SELECT ROWNUM*2 AS RN FROM DUAL CONNECT BY ROWNUM<=10;
END sp_test;
/
过程已创建。
调用存储过程：
declare
p_outstr VARCHAR2(10);
p_outint NUMBER;
p_ref1 REFCURSOR;
Result_number;
begin
sp_test(p_outstr,p_outint,p_ref1);
loop
fetch p_ref1 into result_;
exit when p_ref1%notfound
dbms_output.put_line(result_);
end loop;
close p_ref1;
end;
利用print客户端打印，sqlplus下：
Sql代码
SET AUTOPRINT ON
VAR p_outstr VARCHAR2(10);
VAR p_outint NUMBER;
VAR p_ref1 REFCURSOR;
VAR p_ref2 REFCURSOR;
SET AUTOPRINT ON
VAR p_outstr VARCHAR2(10);
VAR p_outint NUMBER;
VAR p_ref1 REFCURSOR;
VAR p_ref2 REFCURSOR;
Sql代码
scott@ORCL>EXEC sp_test(:p_outstr,:p_outint,:p_ref1,:p_ref2);
PL/SQL 过程已成功完成。
RN
----------
3
5
7
9
11
13
15
17
19
21
23
已选择11行。
RN
----------
2
4
6
8
10
12
14
16
18
20
22
已选择11行。
P_OUTINT
----------
56789
P_OUTSTR
--------------------------------
abc
scott@ORCL>EXEC sp_test(:p_outstr,:p_outint,:p_ref1,:p_ref2);
PL/SQL 过程已成功完成。
RN
----------
3
5
7
9
11
13
15
17
19
21
23
已选择11行。
RN
----------
2
4
6
8
10
12
14
16
18
20
22
已选择11行。
P_OUTINT
----------
56789
P_OUTSTR
--------------------------------
abc
也可以在声明输入输出变量之后，依次print p_outstr,...........
2.使用对象类型，同样返回结果集.
Sql代码
scott@ORCL> CREATE TYPE t_test AS OBJECT (
2 id NUMBER
3 ,name VARCHAR2(20)
4 )
5;
6/
类型已创建。
scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;
2/
类型已创建。
scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test
2AS
3 v_ret tb_test:=tb_test();
4BEGIN
5 FOR i IN 1..10 LOOP
6 v_ret.EXTEND;
7 v_ret(i) := t_test(i,'name'||i);
8 END LOOP;
9 RETURN v_ret;
10END f_test;
11/
函数已创建。
scott@ORCL>select * from table(f_test);
ID NAME
---------- ------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
10 name10
已选择10行。
scott@ORCL> CREATE TYPE t_test AS OBJECT (
2 id NUMBER
3 ,name VARCHAR2(20)
4 )
5;
6/
类型已创建。
scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;
2/
类型已创建。
scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test
2AS
3 v_ret tb_test:=tb_test();
4BEGIN
5 FOR i IN 1..10 LOOP
6 v_ret.EXTEND;
7 v_ret(i) := t_test(i,'name'||i);
8 END LOOP;
9 RETURN v_ret;
10END f_test;
11/
函数已创建。
scott@ORCL>select * from table(f_test);
ID NAME
---------- ------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
10 name10
已选择10行。
3. 用SELECT BULK COLLECT INTO取得数据,返回嵌套表的例子。
Sql代码
scott@ORCL>create table test (id number, name varchar2(20));
表已创建。
scott@ORCL>CREATE TYPE t_test AS OBJECT (
2 id NUMBER
3 ,name VARCHAR2(20)
4 )
5/
类型已创建。
scott@ORCL>;
scott@ORCL>CREATE TYPE tb_test AS TABLE OF t_test;
2/
类型已创建。
scott@ORCL>CREATE OR REPLACE FUNCTION f_test RETURN tb_test
2AS
3 v_ret tb_test:=tb_test();
4BEGIN
5 SELECT t_test(id,name)
6 BULK COLLECT INTO v_ret
7 FROM TEST;
8
9 RETURN v_ret;
10END f_test;
11/
函数已创建。
scott@ORCL>select * from table(f_test);
未选定行
scott@ORCL>insert into test select rownum,'NAME'||rownum FROM DUAL CONNECT BY RO
WNUM<=10;
已创建11行。
scott@ORCL>select * from table(f_test);
ID NAME
---------- ------------------------------
1 NAME1
2 NAME2
3 NAME3
4 NAME4
5 NAME5
6 NAME6
7 NAME7
8 NAME8
9 NAME9
10 NAME10
11 NAME11
已选择11行。
页:
[1]