17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE P_TEST(
i_var VARCHAR2,
out_var out sys_refcursor
)
AS
dynamic_drop_sql varchar2(8000);
begin
dynamic_drop_sql :='
insert into ts_tab1 select * from ts_tab2 t where t.var2='''||i_var||''' and t.var3 > sysdate-15
';
dbms_output.put_line(dynamic_drop_sql);
open out_var for dynamic_drop_sql;
commit;
end;
dclare
i_var VARCHAR2(200) :='',--自己给个测试值
TYPE t_tab IS TABLE OF ts_tab2%rowtype;
t_tab1 t_tab;
out_var out sys_refcursor
dynamic_drop_sql varchar2(8000);
begin
dynamic_drop_sql :='
insert into ts_tab1
select * from ts_tab2 t where t.var2= :L1 and t.var3 > sysdate-15 RETURNING 字段 into :l2';
EXECUTE IMMEDIATE dynamic_drop_sql
USING i_var RETURNING BULK COLLECT INTO t_tab1;
dbms_output.put_line(dynamic_drop_sql);
FOR I IN 1..t_tab1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(t_tab1(I).字段....);
END LOOP;
open out_var for dynamic_drop_sql;
end;