游标作参数时,传入的深度有限制吗?为什么下面的代码为运行时会出异常呢?
create or replace procedure SP_callee(c1 in out sys_refcursor) as
v_errm varchar2(256);
v int := 100; begin
dbms_output.put_line('SP_callee run!'); open c1 for
select v from sys.dual; exception
when others then
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
dbms_output.put_line('SP_callee error!');
end SP_callee;
/------------------------------------------------------------- create or replace procedure SP_caller(c1 in out sys_refcursor) as
v_errm varchar2(256); begin
dbms_output.put_line('SP_caller run!'); sp_callee(c1); exception
when others then
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
dbms_output.put_line('SP_caller error!'); end SP_caller;
/------------------------------------------------------------------- set serverout on;
declare
v_errm varchar2(256);
c1 sys_refcursor;
v int := 0; begin sp_caller(c1); fetch c1 into v;
dbms_output.put_line(v); exception
when others then
DBMS_OUTPUT.PUT_LINE( 'error !');
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
end;
为什么在SQLPLUS中运行时,得到下面的输出?SP_caller run!
SP_callee run!
error !
error code=[]:ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
PL/SQL procedure successfully completed.
create or replace procedure SP_callee(c1 in out sys_refcursor) as
v_errm varchar2(256);
v int := 100; begin
dbms_output.put_line('SP_callee run!'); open c1 for
select v from sys.dual; exception
when others then
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
dbms_output.put_line('SP_callee error!');
end SP_callee;
/------------------------------------------------------------- create or replace procedure SP_caller(c1 in out sys_refcursor) as
v_errm varchar2(256); begin
dbms_output.put_line('SP_caller run!'); sp_callee(c1); exception
when others then
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
dbms_output.put_line('SP_caller error!'); end SP_caller;
/------------------------------------------------------------------- set serverout on;
declare
v_errm varchar2(256);
c1 sys_refcursor;
v int := 0; begin sp_caller(c1); fetch c1 into v;
dbms_output.put_line(v); exception
when others then
DBMS_OUTPUT.PUT_LINE( 'error !');
v_errm := SUBSTR(SQLERRM, 1, 256);
DBMS_OUTPUT.PUT_LINE( 'error code=[]:' || v_errm);
end;
为什么在SQLPLUS中运行时,得到下面的输出?SP_caller run!
SP_callee run!
error !
error code=[]:ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
PL/SQL procedure successfully completed.
Action: Change the program statement or declaration. Verify what query the variable actually refers to during execution.
我在最外层用FETCH INTO获取返回值时,ORACLE抛ORA-06504异常。说类型不匹配。 可是游标返回的变量是INT型的,我的FETCH INTO语句中的变量也是INT型的啊!为什么会抛出这个异常呢?
这是我执行后的结果。SP_caller run!
SP_callee run!
100