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.
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.
c1不是int型的吗?那么“ fetch c1 into v”为什么会报错呢?
declare
v_errm varchar2(256);
c1 sys_refcursor; //是这个c1吧,这个cursor是什么样的?
v int := 0;
这是我执行后的结果。SP_caller run!
SP_callee run!
100