Oracle stored procedure how to obtain the SQL influence how much?-CodePudding

 
The CREATE OR REPLACE PROCEDURE P_TEST (
I_var VARCHAR2, 
Out_var out sys_refcursor 
) 
AS 
Dynamic_drop_sql varchar2 (8000); 
The begin 
Dynamic_drop_sql:='
Insert into ts_tab1 select * from ts_tab2 t where t.v ar2=' ' '| | i_var | |' ' 'and t.v ar3 & gt; Sysdate - 15 
'; 
Dbms_output. Put_line (dynamic_drop_sql); 
The open out_var for dynamic_drop_sql; 
commit; 
end;

How to determine performed before you commit how many rows?

CodePudding user response:

 
Dclare 
I_var VARCHAR2 (200) :=', - myself to a test values 
TYPE t_tab IS TABLE OF ts_tab2 % rowtype; 
T_tab1 t_tab; 
Out_var out sys_refcursor 
Dynamic_drop_sql varchar2 (8000); 
The begin 
Dynamic_drop_sql:='
Insert into ts_tab1 
Select * from ts_tab2 t where t.v ar2=: L1 and t.v ar3 & gt; Sysdate - 15 RETURNING field into: l2 '; 
The 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). The field... ); 
END LOOP; 
The open out_var for dynamic_drop_sql; 
end;

CodePudding user response:

SQL % rowcount