Update using the PL/SQL variables : Update « Object Oriented Database « Oracle PL / SQL

1. Oracle PL / SQL
2. Object Oriented Database
3. Update

Update using the PL/SQL variables

  
SQL>
SQL> CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);
  2  /

Type created.

SQL>
SQL>
SQL> CREATE TABLE library_catalog (
  2    catalog_number NUMBER(4),
  3    num_copies     NUMBER,
  4    num_out        NUMBER,
  5    checked_out    StudentList)
  6    NESTED TABLE checked_out STORE AS co_tab;

Table created.

SQL>
SQL>
SQL> DECLARE
  2    v_StudentList1 StudentList := StudentList(10000, 10002, 10003);
  3    v_StudentList2 StudentList := StudentList(10000, 10002, 10003);
  4    v_StudentList3 StudentList := StudentList(10000, 10002, 10003);
  5  BEGIN
  6    -- First insert rows with NULL nested tables.
  7    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
  8      VALUES (1000, 20, 3);
  9    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
 10      VALUES (1001, 20, 3);
 11    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
 12      VALUES (1002, 10, 3);
 13    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
 14      VALUES (2001, 50, 0);
 15    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
 16      VALUES (3001, 5, 0);
 17    INSERT INTO library_catalog (catalog_number, num_copies, num_out)
 18      VALUES (3002, 5, 1);
 19
 20    UPDATE library_catalog
 21      SET checked_out = v_StudentList1
 22      WHERE catalog_number = 1000;
 23    UPDATE library_catalog
 24      SET checked_out = v_StudentList2
 25      WHERE catalog_number = 1001;
 26    UPDATE library_catalog
 27      SET checked_out = v_StudentList3
 28      WHERE catalog_number = 1002;
 29
 30    -- And update the last row using a new variable.
 31    UPDATE library_catalog
 32      SET checked_out = StudentList(10009)
 33      WHERE catalog_number = 3002;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL>
SQL> DELETE FROM library_catalog
  2    WHERE catalog_number = 3001;

1 row deleted.

SQL>
SQL>
SQL>
SQL> select * from library_catalog;

CATALOG_NUMBER NUM_COPIES    NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
          1000         20          3
STUDENTLIST(10000, 10002, 10003)

          1001         20          3
STUDENTLIST(10000, 10002, 10003)

          1002         10          3
STUDENTLIST(10000, 10002, 10003)


CATALOG_NUMBER NUM_COPIES    NUM_OUT
-------------- ---------- ----------
CHECKED_OUT
--------------------------------------------------------------------------------
          2001         50          0


          3002          5          1
STUDENTLIST(10009)


SQL>
SQL> drop table library_catalog;

Table dropped.

SQL>
SQL>

   
  

Related examples in the same category