Oracle PL/SQL/PL SQL/RETURNING Into
RETURNING BULK COLLECT INTO
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Numbers t_Numbers; 7 v_Strings t_Strings; 8 BEGIN 9 DELETE FROM MyTable; 10 FOR v_Outer IN 1..10 LOOP 11 FOR v_Inner IN 1..v_Outer LOOP 12 INSERT INTO MyTable (num_col, char_col) 13 VALUES (v_Outer, "Element #" || v_Inner); 14 END LOOP; 15 v_Numbers(v_Outer) := v_Outer; 16 END LOOP; 17 18 FORALL v_Count IN 1..5 19 DELETE FROM MyTable 20 WHERE num_col = v_Numbers(v_Count) 21 RETURNING char_col BULK COLLECT INTO v_Strings; 22 23 DBMS_OUTPUT.PUT_LINE("After delete:"); 24 FOR v_Count IN 1..v_Strings.COUNT LOOP 25 DBMS_OUTPUT.PUT_LINE(" v_Strings(" || v_Count || ") = " || v_Strings(v_Count)); 26 END LOOP; 27 END; 28 /
After delete:
v_Strings(1) = Element #1 v_Strings(2) = Element #1 v_Strings(3) = Element #2 v_Strings(4) = Element #1 v_Strings(5) = Element #2 v_Strings(6) = Element #3 v_Strings(7) = Element #1 v_Strings(8) = Element #2 v_Strings(9) = Element #3 v_Strings(10) = Element #4 v_Strings(11) = Element #1 v_Strings(12) = Element #2 v_Strings(13) = Element #3 v_Strings(14) = Element #4 v_Strings(15) = Element #5
PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
RETURNING INTO clause.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 4.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 4.00
- Tim Viper History 4.00
- Barbara Blues Economics 7.00
- David Large Music 4.00
- Chris Elegant Nutrition 8.00
- Rose Bond Music 7.00
- Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Sharon Clear Computer Science 3.00
12 rows selected. SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 v_NewRowid ROWID; 3 myFirstName lecturer.first_name%TYPE; 4 v_LastName lecturer.last_name%TYPE; 5 v_ID lecturer.ID%TYPE; 6 BEGIN 7 INSERT INTO lecturer(ID, first_name, last_name, major, current_credits) 8 VALUES (20001, "Xavier", "Xemes", "Nutrition", 0) 9 RETURNING rowid INTO v_NewRowid; 10 11 DBMS_OUTPUT.PUT_LINE("Newly inserted rowid is " || v_NewRowid); 12 13 UPDATE lecturer 14 SET current_credits = current_credits + 3 15 WHERE rowid = v_NewRowid 16 RETURNING first_name, last_name INTO myFirstName, v_LastName; 17 18 DBMS_OUTPUT.PUT_LINE("Name: " || myFirstName || " " || v_LastName); 19 20 DELETE FROM lecturer 21 WHERE rowid = v_NewRowid 22 RETURNING ID INTO v_ID; 23 24 DBMS_OUTPUT.PUT_LINE("ID of new row was " || v_ID); 25 END; 26 /
Newly inserted rowid is AAAEWNAABAAAKlyAAM Name: Xavier Xemes ID of new row was 20001 PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 4.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 4.00
- Tim Viper History 4.00
- Barbara Blues Economics 7.00
- David Large Music 4.00
- Chris Elegant Nutrition 8.00
- Rose Bond Music 7.00
- Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Sharon Clear Computer Science 3.00
12 rows selected. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>
</source>