--存储
CREATE OR REPLACE Procedure zed(
dh in varchar2,
n_Result OUT SYS_REFCURSOR
)
ASBEGIN
open n_Result for
select oeb01,oeb03,oeb04,oeb06,oeb12 from ds01.oeb_file a,(select rownum rn from (select max(oeb12) max_rec from ds01.oeb_file where oeb01=dh)
connect by level <= max_rec) b where a.oeb12>=rn and oeb01=dh order by oeb03;
END;我在PL/SQL里的调用代码DECLARE
dh varchar2(255) :='SMSXX-2211070130';
n varchar2(255);
BEGIN
n := ZED (dh);
DBMS_OUTPUT.PUT_LINE(n);
END;
现在始终是报wrong number or types of arguments in call to 'ZED'的错误。
CREATE OR REPLACE Procedure zed(
dh in varchar2,
n_Result OUT SYS_REFCURSOR
)
ASBEGIN
open n_Result for
select oeb01,oeb03,oeb04,oeb06,oeb12 from ds01.oeb_file a,(select rownum rn from (select max(oeb12) max_rec from ds01.oeb_file where oeb01=dh)
connect by level <= max_rec) b where a.oeb12>=rn and oeb01=dh order by oeb03;
END;我在PL/SQL里的调用代码DECLARE
dh varchar2(255) :='SMSXX-2211070130';
n varchar2(255);
BEGIN
n := ZED (dh);
DBMS_OUTPUT.PUT_LINE(n);
END;
现在始终是报wrong number or types of arguments in call to 'ZED'的错误。
CREATE OR REPLACE Procedure zed(
dh in varchar2,
n_Result OUT SYS_REFCURSOR
)
2 个参数
dh varchar2(255) :='SMSXX-2211070130';
n varchar2(255);
BEGIN
n := ZED (dh,'');
DBMS_OUTPUT.PUT_LINE(n);
END;
这样调用行吗?
dh varchar2(255) :='SMSXX-2211070130';
type r_cursor is ref cursor;
row_r_cursor r_cursor;
BEGIN
ZED (dh,row_r_cursor);
END;
PL/SQL中
1、IN模式:只读。在模块里面,实参的数值只能被引用,而这个参数不能被改变。
2、OUT模式:只写。模块能够给这个参数进行赋值,但是这个参数的数值不能被引用。
3、IN OUT模式:可读写。这个模块的数值在模块内可被引用或改变。
错误提示:不正确的参数个数。
DECLARE
dh varchar2(255) :='SMSXX-2211070130';
n varchar2(255);
m varchar2(255)
BEGIN
:m := ZED (dh,:n);
DBMS_OUTPUT.PUT_LINE(n);
END;
看这样行不?
DECLARE
dh varchar2(255) :='SMSXX-2211070130';
n varchar2(255);
BEGIN
exec ZED (dh,:n);
DBMS_OUTPUT.PUT_LINE(n);
END;
(
oeb01 varchar(20) not null,
oeb02 varchar(20) not null,
oeb03 varchar(20) not null,
oeb04 varchar(20) not null,
oeb06 varchar(20) not null,
oeb12 varchar(20) not null
)插入测试数据insert into oeb_file
values('1','12','123','1234','12345','12');
insert into oeb_file
values('2','13','124','1235','12346','13');
insert into oeb_file
values('3','14','125','1236','12347','14');修改后的存储过程CREATE OR REPLACE Procedure zed(dh in varchar2) AS
v_oeb01 varchar(20);
v_oeb03 varchar(20);
v_oeb04 varchar(20);
v_oeb06 varchar(20);
v_oeb12 varchar(20);--游标定义
type t_cur is ref cursor;
v_cur01 t_cur;
BEGIN open v_cur01 for
select oeb01, oeb03, oeb04, oeb06, oeb12
from oeb_file a,
(select rownum rn
from (select max(oeb12) max_rec
from oeb_file
where oeb01 = dh)
connect by level <= max_rec) b
where a.oeb12 >= rn
and oeb01 = dh
order by oeb03;
loop
fetch v_cur01 into v_oeb01,v_oeb03,v_oeb04,v_oeb06,v_oeb12;
exit when v_cur01%notfound;
DBMS_OUTPUT.PUT_LINE('oeb01='||v_oeb01|| 'oeb03='||v_oeb03||'oeb04='||v_oeb04||'oeb06='||v_oeb06||'oeb12='||v_oeb12);
end loop; -- DBMS_OUTPUT.PUT_LINE(n_Result);
END;调用
DECLARE
dh varchar2(255) :='1';
BEGIN
ZED (dh);
END;
显示结果在PL/SQL 的OUTPUT窗口显示
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
oeb01=1oeb03=123oeb04=1234oeb06=12345oeb12=12
不知道是否符合楼主的要求!
CREATE OR REPLACE Procedure zed(dh in varchar2) AS
v_oeb01 varchar(20);
v_oeb03 varchar(20);
v_oeb04 varchar(20);
v_oeb06 varchar(20);
v_oeb12 varchar(20);--游标定义
type t_cur is ref cursor;
v_cur01 t_cur;
BEGIN open v_cur01 for
select oeb01, oeb03, oeb04, oeb06, oeb12
from oeb_file a,
(select rownum rn
from (select max(oeb12) max_rec
from oeb_file
where oeb01 = dh)
connect by level <= max_rec) b
where a.oeb12 >= rn
and oeb01 = dh
order by oeb03;
loop
fetch v_cur01 into v_oeb01,v_oeb03,v_oeb04,v_oeb06,v_oeb12;
exit when v_cur01%notfound;
DBMS_OUTPUT.PUT_LINE('oeb01='||v_oeb01|| 'oeb03='||v_oeb03||'oeb04='||v_oeb04||'oeb06='||v_oeb06||'oeb12='||v_oeb12);
end loop;
close v_cur01;--关闭游标 -- DBMS_OUTPUT.PUT_LINE(n_Result);
END;需要关闭游标
我这的数据库是oracle 10g