Define a nested table type for each column : Table of Type « PL SQL « Oracle PL / SQL






Define a nested table type for each column

    
SQL>
SQL> create table department
  2  ( dept_id       number(2),
  3    dept_name     varchar2(14),
  4    no_of_emps    varchar2(13)
  5  )
  6  /

Table created.

SQL>
SQL> INSERT INTO department VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO department VALUES (20, 'RESEARCH',   'DALLAS');

1 row created.

SQL> INSERT INTO department VALUES (30, 'SALES',      'CHICAGO');

1 row created.

SQL> INSERT INTO department VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2      CURSOR all_depts IS
  3          SELECT dept_id, dept_name FROM department ORDER BY dept_name;
  4
  5      TYPE dept_id IS TABLE OF department.dept_id%TYPE;
  6      TYPE dept_name IS TABLE OF department.dept_name%TYPE;
  7
  8      dept_ids dept_id;
  9      dept_names dept_name;
 10      inx1 PLS_INTEGER;
 11  BEGIN
 12      OPEN all_depts;
 13      FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;
 14      CLOSE all_depts;
 15
 16      FOR inx1 IN 1..dept_ids.count LOOP
 17          dept_names(inx1) := UPPER(dept_names(inx1));
 18          DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) ||' ' || dept_names(inx1));
 19      END LOOP;
 20
 21      FORALL x IN dept_ids.first..dept_ids.last
 22      UPDATE department
 23      SET dept_name = dept_names(x)
 24      WHERE dept_id = dept_ids(x);
 25  END;
 26  /
10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALES

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table department;

Table dropped.

SQL> --

   
    
    
  








Related examples in the same category

1.Create a function to convert string type variable to date type variable
2.Fetch a bulk into a table structure
3.Extend once, outside the loop for better performance
4.A package to manage a list of employees
5.Fill table of custom type and use it in for loop to insert
6.Table of custome type indexed by BINARY_INTEGER
7.Reference type attribute through index
8.Use for loop to fill a table collection
9.Select bulk collect into
10.Use for loop to insert value to table collection and then use table collection in another insert statement
11.The EXISTS Table Attribute
12.FIRST & LAST Table Attributes
13.NEXT & PRIOR Table Attributes
14.Uses the COUNT method to display the number of rows contained in a table collection
15.How to do a bulk collect into a nested table.
16.How to do a bulk collect into an associative array
17.Table collection indexed by column type