Oracle PL/SQL/PL SQL/RETURNING Into
RETURNING BULK COLLECT INTO
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>
RETURNING INTO clause.
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>