Batch Processing dynamic SQL

1.
DECLARE
TYPE ref_cursor_type IS ref CURSOR;
V_mycursor ref_cursor_type;
TYPE id_list is table of integer;
TYPE name_list is table of varchar2 (30 );
V_tabid id_list: = id_list ();
V_tabname name_list: = name_list ();
SQL _str varchar2 (200 );
BEGIN
-- Query the rows and put them in the Set
SQL _str: = 'select empno, ename from emp ';
SQL _str: = SQL _str | 'order by empno desc ';
Execute immediate SQL _str BULK COLLECT INTO v_tabid, v_tabname;
FOR c IN v_tabid.first... v_tabid.last LOOP
Dbms_output.put_line ('empno is '| v_tabid (c) | 'record NAME is' | v_tabname (c ));
End loop;
Dbms_output.put_line ('---------------------------------');
-- Update (return the updated value)
SQL _str: = 'Update emp set empno = 1 + empno, ename = ''a' where rownum = 1 RETURNING empno, ename into: 1,: 2 ';
Execute immediate SQL _str RETURNING BULK COLLECT INTO v_tabid, v_tabname;
FOR c IN v_tabid.first... v_tabid.last LOOP
Dbms_output.put_line ('empno is '| v_tabid (c) | 'record NAME is' | v_tabname (c ));
End loop;
Dbms_output.put_line ('---------------------------------');
-- Delete (return the row to be deleted)
SQL _str: = 'delete from emp where rownum <= 2 RETURNING empno, ename into: 1,: 2 ';
Execute immediate SQL _str RETURNING BULK COLLECT INTO v_tabid, v_tabname;
FOR c IN v_tabid.first... v_tabid.last LOOP
Dbms_output.put_line ('empno is '| v_tabid (c) | 'record NAME is' | v_tabname (c ));
End loop;
Dbms_output.put_line ('---------------------------------');
-- Insert (returns the inserted row)
SQL _str: = 'insert into emp (empno, ename) values (1, ''abc'') RETURNING empno, ename into: 1,: 2 ';
Execute immediate SQL _str RETURNING BULK COLLECT INTO v_tabid, v_tabname;
FOR c IN v_tabid.first... v_tabid.last LOOP
Dbms_output.put_line ('empno is '| v_tabid (c) | 'record NAME is' | v_tabname (c ));
End loop;
Dbms_output.put_line ('---------------------------------');
/* Batch fetch
Syntax:
Fetch dynamic_cursor
Bulk collect into define_variable [, define_variable...]
*/
SQL _str: = 'select empno, ename from emp ';
SQL _str: = SQL _str | 'order by empno desc ';
OPEN v_mycursor FOR SQL _str;
-- Fetch
FETCH v_mycursor bulk collect into v_tabid, v_tabname;
-- Off
CLOSE v_mycursor;
-- Lose
FOR c IN v_tabid.first... v_tabid.last LOOP
Dbms_output.put_line ('empno is '| v_tabid (c) | 'record NAME is' | v_tabname (c ));
End loop;
Dbms_output.put_line ('---------------------------------');
END;
2 .-------
Forall
DECLARE
/* Batch forall
Syntax: the dynamic string must be insert/update/delete, and cannot be select
Forall index in lower... upper
Execute immediate dynamic_string
Using bind | bind (index) [, bind | bind (index)...]
[{Returning | return} bulk collect into bind_argument [, bind_argument...];
*/
TYPE sal_list is table of number (8, 2 );
TYPE name_list is table of varchar2 (30 );
TYPE dept_list is varray (15) OF integer;
V_depts dept_list: = dept_list (10, 20, 30, 40, 50, 60, 70, 80 );
V_tabsal sal_list: = sal_list ();
V_tabname name_list: = name_list ();
SQL _str varchar2 (200 );
BEGIN
SQL _str: = 'Update emp set sal = sal *: arg1 where DEPTNO =: arg2 ';
SQL _str: = SQL _str | 'returning ename, sal into: arg3,: arg4 ';
-- Raise the salary of 10% for the first four departments and return the result to the collection.
FORALL j IN 1 .. 4
Execute immediate SQL _str
Using 1.10, v_depts (j)
Returning bulk collect into v_tabname, v_tabsal;
-- Display Results
FOR j IN v_tabname.first .. v_tabname.last LOOP
Dbms_output.put_line ('employee '| v_tabname (j)
| 'Salary mentioned '| v_tabsal (j ));
End loop;
Dbms_output.put_line ('---------------------------------');
-- Raise the salary of 20% for the next four departments and return the result to the collection.
FORALL j IN 5 .. 8
Execute immediate SQL _str
Using 1.20, v_depts (j)
Returning bulk collect into v_tabname, v_tabsal;
-- Display Results (use notfound to determine whether a result set exists)
If SQL % NOTFOUND THEN
Dbms_output.put_line ('no data Update ');
ELSE
FOR j IN v_tabname.first .. v_tabname.last LOOP
Dbms_output.put_line ('employee '| v_tabname (j)
| 'Salary mentioned '| v_tabsal (j ));
End loop;
End if;
END;
3. Bind a value with the same name.
You can use begin end to enclose SQL statements.
For example:
Execute immediate 'in in calc_stats (: x,: x,: y,: x,: y); end; 'using a, B;
Bind A to X. Bind B to A different name for the second time, and so on.