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;
怎么在commit之前判斷執行了多少行？
uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
sql%rowcount轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/70818.html
標籤：開發