CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create procedure pro(p_date in date,p_rc out pkg_test.myrctype) as str varchar2(100); num number; begin num:=to_number(to_char(last_day(p_date),'mm')); str:='select code,rq,'; for i in 1..num loop if num>=10 then str:=str||'je_'||num||'+'; else str:=str||',je_0'||num||'+'; end if; end loop; str:=substr(str,1,length(str)-1)||'/'||num||' from tbl_test'; open p_rc for str; end; /
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create procedure pro(p_date in date,p_rc out pkg_test.myrctype)
as
str varchar2(100);
num number;
begin
num:=to_number(to_char(last_day(p_date),'mm'));
str:='select code,rq,';
for i in 1..num loop
if num>=10 then
str:=str||'je_'||num||'+';
else
str:=str||',je_0'||num||'+';
end if;
end loop;
str:=substr(str,1,length(str)-1)||'/'||num||' from tbl_test';
open p_rc for str;
end;
/