Extend once, outside the loop for better performance

    
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>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2      TYPE dept_id IS TABLE OF department.dept_id%TYPE;
  3      TYPE dept_name IS TABLE OF department.dept_name%TYPE;
  4
  5      dept_ids dept_id;
  6      dept_names dept_name;
  7      inx1 PLS_INTEGER;
  8  BEGIN
  9      dept_ids := dept_id();
 10      dept_names := dept_name();
 11
 12      dept_ids.extend(10);
 13      dept_names.extend(10);
 14
 15      FOR inx1 IN 1..10 LOOP
 16          dept_ids(inx1) := inx1 + 10;
 17          dept_names(inx1) := 'Dept #' || TO_CHAR(inx1+10);
 18      END LOOP;
 19
 20      FORALL x IN dept_ids.first..dept_ids.last
 21      INSERT INTO department (dept_id, dept_name)
 22      VALUES (dept_ids(x), dept_names(x));
 23  END;
 24  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table department;

Table dropped.

SQL>
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.	Define a nested table type for each column
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

Extend once, outside the loop for better performance : Table of Type « PL SQL « Oracle PL / SQL

Related examples in the same category