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

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

Related examples in the same category