创建存储过程:
SQL>create or replace procedure test_p(out_var out sys_refcursor)
SQL>as
SQL>begin
SQL>open out_var for select * from test;
SQL>end;
SQL>/
调用存储过程:
SQL>declare
SQL>v_out sys_refcursor;
SQL>begin
SQL>test_p(v_out);
SQL>end;
SQL>print :v_out;
SQL>/出现错误提示:PLS-00103: 出现符号 "PRINT"
????
SQL>create or replace procedure test_p(out_var out sys_refcursor)
SQL>as
SQL>begin
SQL>open out_var for select * from test;
SQL>end;
SQL>/
调用存储过程:
SQL>declare
SQL>v_out sys_refcursor;
SQL>begin
SQL>test_p(v_out);
SQL>end;
SQL>print :v_out;
SQL>/出现错误提示:PLS-00103: 出现符号 "PRINT"
????
var v_out refcursor;
begin
test_p(:v_out);
end;
print :v_out;
SQL>v_out sys_refcursor;
SQL>begin
SQL>test_p(v_out);
SQL>end;
SQL>dbms_output.put_line(v_out);
SQL>/
SQL>v_out sys_refcursor;
SQL>begin
SQL>test_p(v_out);
SQL>dbms_output.put_line(v_out.xx);--因为是游标返回值。
SQL>end;
/
v_out sys_refcursor;
v_test test%rowtype;
begin
test_p(v_out);
loop
fetch v_out into v_test
dbms_output.put_line(v_test.xx);--因为是游标返回值。
end loop;
close v_out;
end;
/
再试试。
v_out sys_refcursor;
v_test test%rowtype;
begin
test_p(v_out);
loop
fetch v_out into v_test;
exit when v_out%notfound;
dbms_output.put_line(v_test.xx);
close v_out;
end;
/
xx要换成test的列名,刚测试过了。可以的
2 as
3 begin
4 open out_var for select * from test;
5 end;
6 /过程已创建。scott@STUDY> var v_out refcursor;
scott@STUDY> begin
2 test_p(:v_out);
3 end;
4 /PL/SQL 过程已成功完成。scott@STUDY> print :v_out; CITY_ID PROVINCE_ID
---------- -----------
1 2
100 1
101 4
102 1已用时间: 00: 00: 00.01
SQL> var v_out refcursor;
REFCURSOR not supported显示这个提示错误
v_out sys_refcursor;
v_test test%rowtype;
begin
test_p(v_out);
loop
fetch v_out into v_test;
exit when v_out%notfound;
dbms_output.put_line(v_test.xx);
end loop;
close v_out;
end;
/
不好意思少了个end loop;自己可以调试阿。