Batch Processing dynamic SQL

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.