Oracle PL/SQL/PL SQL/RETURNING Into

Материал из SQL эксперт
Перейти к: навигация, поиск

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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. 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

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. Sharon Clear Computer Science 3.00

12 rows selected. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>

</source>