大家好!我是一个新手,定义了一个存储过程返回了一个sys_refcursor的结果集,想把它在dmbs_sql中处理.不知道该如果使用.
create or replace function af_test_return
(param1 in varchar2) return sys_refcursor as
ls_cur sys_refcursor;
begin
open ls_cur for select msg from test;
return ls_cur;
end af_test_return;create or replace function af_xtwh_run_function
(ar_sql varchar2; /*传入要执行的函数语句现在传入的是select AF_TEST_RETURN(:PARAM1) from dual*/
args_name myargs; /*myargs是一个字符型的数组,用以传入ar_sql语句要用到的变量名args_name和变值args*/
args myargs
)return integer;
as
l_cur integer;
l_ref_cur sys_refcursor;
i integer :=1;
l_ret integer;
ls_aa test.msg%type;
begin
l_cur:=dbms_sql.open_cursor();
dbms_sql.parse(l_cur,ar_sql,dbms_sql.native);
for i in 1..args_name.count() loop
dbms_sql.bind_variable(l_cur,args_name(i),args(i));
end loop
l_ret:=dbms_sql.execute(l_cur);
l_ref_cur:=dbms_sql.to_refcursor(l_cur);fetch l_ref_cur into ls_aa; /*这句出错*/
dbms_output.put_line(ls_aa);
return 1;
end af_xtwh_run_function;
调试时:
SET SERVEROUTPUT ON
DECLARE
ARGS_NAME MYARGS;
ARGS MYARGS;
LS_SQL VARCHAR2(100);
Li_i integer;
BEGIN
SELECT ARGUMENT_NAME BULK COLLECT INTO ARGS_NAME FROM SYS.USER_ARGUMENTS WHERE OBJECT_NAME ='AF_TEST_RETURN' AND IN_OUT ='IN' ;
ARGS :=MYARGS('AA');
LS_SQL := AF_XTWH_GET_FUNCTION_SQL('AF_TEST_RETURN'); /*返回af_test_return的调用语法.*/
DBMS_OUTPUT.PUT_LINE(LS_SQL);
SELECT AF_XTWH_RUN_FUNCTION(LS_SQL,ARGS_NAME,ARGS) INTO li_i FROM DUAL;
DBMS_OUTPUT.PUT_LINE('BB12222');
END;结果出来:
错误报告:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CURSER
ORA-06512: 在 "AF_XTWH_RUN_FUNCTION", line 33
create or replace function af_test_return
(param1 in varchar2) return sys_refcursor as
ls_cur sys_refcursor;
begin
open ls_cur for select msg from test;
return ls_cur;
end af_test_return;create or replace function af_xtwh_run_function
(ar_sql varchar2; /*传入要执行的函数语句现在传入的是select AF_TEST_RETURN(:PARAM1) from dual*/
args_name myargs; /*myargs是一个字符型的数组,用以传入ar_sql语句要用到的变量名args_name和变值args*/
args myargs
)return integer;
as
l_cur integer;
l_ref_cur sys_refcursor;
i integer :=1;
l_ret integer;
ls_aa test.msg%type;
begin
l_cur:=dbms_sql.open_cursor();
dbms_sql.parse(l_cur,ar_sql,dbms_sql.native);
for i in 1..args_name.count() loop
dbms_sql.bind_variable(l_cur,args_name(i),args(i));
end loop
l_ret:=dbms_sql.execute(l_cur);
l_ref_cur:=dbms_sql.to_refcursor(l_cur);fetch l_ref_cur into ls_aa; /*这句出错*/
dbms_output.put_line(ls_aa);
return 1;
end af_xtwh_run_function;
调试时:
SET SERVEROUTPUT ON
DECLARE
ARGS_NAME MYARGS;
ARGS MYARGS;
LS_SQL VARCHAR2(100);
Li_i integer;
BEGIN
SELECT ARGUMENT_NAME BULK COLLECT INTO ARGS_NAME FROM SYS.USER_ARGUMENTS WHERE OBJECT_NAME ='AF_TEST_RETURN' AND IN_OUT ='IN' ;
ARGS :=MYARGS('AA');
LS_SQL := AF_XTWH_GET_FUNCTION_SQL('AF_TEST_RETURN'); /*返回af_test_return的调用语法.*/
DBMS_OUTPUT.PUT_LINE(LS_SQL);
SELECT AF_XTWH_RUN_FUNCTION(LS_SQL,ARGS_NAME,ARGS) INTO li_i FROM DUAL;
DBMS_OUTPUT.PUT_LINE('BB12222');
END;结果出来:
错误报告:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CURSER
ORA-06512: 在 "AF_XTWH_RUN_FUNCTION", line 33
应该定义为行类型的,
ls_aa test%ROWTYPE;
游标返回的行,虽然你的行里只有一列,,,,
如果返回的结果集不用sys_refcursor用上嵌套表的话,嵌套表的列个数能不定吗?
显式游标的使用需要4步: 1. 声明游标
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
sys_refcursor也是声明了一个显示游标2. 打开游标
open mycur(000627)
注:000627是参数 3. 读取数据
fetch mycur into varno, varprice; 4. 关闭游标
close mycur;
注:open mycur(000627)中参数000627是什么意思?
fetch l_ref_cur into ls_aa; /*这句出错*/修改为:for cur in l_ref_cur loop
fetch cur into ls_aa;
end loop;
ls_ref_cur_new sys_refcursor;
fetch l_ref_cur into l_ref_cur_new;
fetch l_ref_cur_new into ls_aa;