Collections

PL/SQL Collections

---

1. Collections

---

Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all the SQL is handled by the SQL statement executor or SQL engine.
Due to this, there is an overhead associated with each context switch between the two engines. Too many context switches between PL/SQL and SQL engines can harm performance.

---

---

2. VARRAYs

---

A VARRAY allows you to store repeating attributes of a record in a single row.

Example to illustrate VARRAY Concept in PL/SQL

---

1.
— Create a PL/SQL Block to display the contents of a VARRAY Elements —

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc; — Variable v2 holds the Datatype of VARRAY —
BEGIN
v2(1):=78;
DBMS_OUTPUT.PUT_LINE(v2(1));
END;
/

This PL/SQL Block will error out since v2 holds a NULL value and without the constructor method and EXTEND function,we cannot directly initialize element values to a VARRAY.

---

2.
— Rectified PL/SQL Block —
— Using the Basic Version of initializing values a VARRAY —

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc; — Variable v2 holds the Datatype of VARRAY —
BEGIN
v2:=abc(78);
DBMS_OUTPUT.PUT_LINE(v2(1));
END;
/

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc; — Variable v2 holds the Datatype of VARRAY —
BEGIN
v2:=abc(78,33,33);
DBMS_OUTPUT.PUT_LINE(v2(1)||’-‘||V2(2)||’-‘||V2(3));
END;
/

— Using Constructor Method and EXTEND Function —
DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc; — Variable v2 holds the Datatype of VARRAY —
BEGIN
v2:=abc(); — Using Constructor Method —
v2.EXTEND; — This EXTEND initializes with a NULL Value in the VARRAY —
v2(1):=78;
v2(1):=79;
DBMS_OUTPUT.PUT_LINE(v2(1));
END;
/

— Extending to hold 2 Values to the VARRAY —
DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc; — Variable v2 holds the Datatype of VARRAY —
BEGIN
v2:=abc(); — Using Constructor Method —
v2.EXTEND(2); — This EXTEND can hold a maximum of 2 Elements in the VARRAY —
v2(1):=78;
v2(2):=79;
DBMS_OUTPUT.PUT_LINE(v2(1));
DBMS_OUTPUT.PUT_LINE(v2(2));
END;
/

3.
— Another version of the same PL/SQL Block (Without EXTEND Function) —

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc:=abc(1,2,3,4,5,6,7,8);
v_count NUMBER:=v2.COUNT;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_count);
DBMS_OUTPUT.PUT_LINE(v2(1));
DBMS_OUTPUT.PUT_LINE(v2(2));
DBMS_OUTPUT.PUT_LINE(v2(3));
DBMS_OUTPUT.PUT_LINE(v2(4));
DBMS_OUTPUT.PUT_LINE(v2(5));
DBMS_OUTPUT.PUT_LINE(v2(6));
DBMS_OUTPUT.PUT_LINE(v2(7));
DBMS_OUTPUT.PUT_LINE(v2(8));
END;
/

4.
— Another version of the same PL/SQL Block (Using LOOPING Concept) —

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc;
BEGIN
v2:=abc(); — Using Constructor Method —
FOR i IN 1..10
LOOP
v2.EXTEND;
v2(i):=i;
DBMS_OUTPUT.PUT_LINE(v2(i));
END LOOP;
END;
/

5.
— Another version of the same PL/SQL Block (Using LOOPING Concept by selecting records from EMP Table) —

DECLARE
TYPE abc IS VARRAY(10) OF NUMBER; — Holds a Maximum of 10 Elements of Number Datatype —
v2 abc;
CURSOR c3 IS SELECT empno FROM emp WHERE rownum <=10; — Select 10 Records from EMP Table —
v_emp abc;
v_count NUMBER;
BEGIN
v_emp:=abc();
FOR rec IN c3
LOOP
v_emp.EXTEND;
DBMS_OUTPUT.PUT_LINE(rec.empno);
v_count:=v_emp.COUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records retrieved ‘||v_count);
END;
/

Example to illustrate VARRAY Concept in PL/SQL to identify which of your tools were borrowed by which of your neighbours.

1.
— Create BORROWERS Table —
CREATE TABLE borrowers
(name VARCHAR2(25),
tool VARCHAR2(25),
CONSTRAINT BORROWER_PK primary key(name,tool))
/

Even though the borrower’s name value does not change it is repeated in each record because it is part of the primary key.
Using COLLECTIONS VArrays we can repeat only those column values that change potentially saving storage space.

2.
— Create a TYPE named TOOL_TY as VARRAY having one column —
CREATE TYPE tools_va as varray(5) of CHAR(25) — This VARRAY contains CHAR —
/

When this CREATE TYPE is executed a VARRAY is created which will hold a maximum of 5 entries per record.
Now I can use this type as the basis for a column definition while a table is created.

3.
— Drop Table to recreate the same having VARRAY as Datatype —
DROP TABLE borrowers PURGE
/

CREATE TABLE borrower
(name VARCHAR2(25) PRIMARY KEY,
tools TOOLS_VA)
/

4.
— Describe the VARRAY Contents —

DESC borrower;

5.
— Display the Column Contents of a VARRAY Table —
COLUMN DATA_TYPE FORMAT A12

SELECT column_name,data_type
FROM user_tab_columns
WHERE table_name = ‘BORROWER’
/

For more details on TOOLS_VA query the USER_COLL_TYPES View
SELECT
coll_type,
elem_type_name,
upper_bound,
length
FROM user_coll_types
WHERE type_name = ‘TOOLS_VA’
/

6.

— Inserting Records into VARRAYs —

Following example will insert a single record into BORROWER’s Table. The record will have a single name column value and three tools values.

INSERT INTO borrower VALUES
(‘JED HOPKINS’,TOOLS_VA(‘HAMMER’,’SLEDGE’,’AX’))
/

1.This INSERT command first specifies the value for the NAME column. Because the NAME column is not part of any abstract datatype it is populated in the same way you would insert into any relational column.
2.The next part of the INSERT command inserts records into TOOLS Column.Since the TOOLS column uses the TOOLS_VA varying array we need to use the TOOLS_VA constructor method.
3.You can even set the uninitialized values to NULL with the INSERT
INSERT INTO borrower VALUES
(‘BRUCE HOPKINS’,TOOLS_VA(‘HAMMER’,’SLEDGE’,’AX’,NULL,NULL))
/

7.
— Selecting Data from VARRAYs —

SELECT tools FROM borrower
/

SELECT
b.name,
n.*
FROM borrower b,
TABLE(b.tools) n
/

You can use the TABLE Function in the FROM clause to display the data from VARRAYs on seperate lines

The TABLE Function took as its input the name of the VARRAY and its output is given the alias N.
The values within N are selected generating the second column of the output.

DROP TABLE borrower PURGE
/

DROP TYPE tools_va
/

— Example to demonstrate a VARRAY which initially holds two values —
— In this VARRAY we cannot delete an element —

DECLARE
TYPE table_type IS VARRAY(5) OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
— Initialise the collection with two values.
v_tab := table_type(1, 2);

FOR i IN 1 .. 2 LOOP
v_tab(v_tab.last) := i;
END LOOP;
— Traverse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

— Example to demonstrate a VARRAY which initially holds two values and using EXTEND to extend to 5 —
— In this VARRAY we cannot delete an element —

DECLARE
TYPE table_type IS VARRAY(5) OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
— Initialise the collection with two values.
v_tab := table_type(1, 2);

— Extend the collection with extra values. —
<< load_loop >>
FOR i IN 3 .. 5 LOOP — While attempting to extend from 5 to 6 will result in error (Maximum 5) —
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP load_loop;

— Can’t delete from a VARRAY —
— v_tab.DELETE(3); —

— Traverse collection
v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/

---

---

3. Nested Tables in PL/SQL

---

Nested Tables are similiar to INDEX BY Tables but these can be stored in database columns but INDEX BY Tables cannot be stored in database columns.

A Nested Table can be considered as a single – column table that can be either reside in a memory,or as a column in a database table. A Nested Table is similiar to a VARRAY where VARRAY’s order of elements is static. Elements can be deleted or added anywhere in the Nested Table whereas a VARRAY can only add or delete elements from the end of the array.Nested Table can contain empty elements.

Nested Table can be considered in the following scenarios :

1. You need to delete or update some elements,but not all elements at once.
2. The Index Values are not consecutive
3. We don’t have any predefined upper bound for index values.

Example of a Nested Table

---

— Example to Create a Type of Table —
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/

— Example to create a Table holding that Table Type as a Nested Table —
CREATE TABLE custom_nested_tab(id number,col1 my_tab_t)
NESTED TABLE col1 STORE AS col1_tab
/

— Inserting Values into Nested Table —
INSERT INTO custom_nested_tab VALUES(1,my_tab_t(‘A’))
/

INSERT INTO custom_nested_tab VALUES(1,my_tab_t(‘AA’))
/

INSERT INTO custom_nested_tab VALUES(1,my_tab_t(‘AB’))
/

— Inserting Values into Nested Table —
INSERT INTO custom_nested_tab VALUES(2,my_tab_t(‘B’,’C’))
/

— Inserting Values into Nested Table —
INSERT INTO custom_nested_tab VALUES(3,my_tab_t(‘D’,’E’,’F’))
/

SELECT col1 FROM custom_nested_tab
/

— Unnesting the Sub Table having a Nested Table —
SELECT id,column_value FROM custom_nested_tab t1, TABLE(t1.col1) t2
/

DROP TABLE custom_nested_tab PURGE
/

DROP TYPE my_tab_t
/

Example of a Nested Table with a Primary Key

---

— Example to Create a Type of Table —
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF NUMBER;
/

— Example to create a Table holding that Table Type as a Nested Table —
CREATE TABLE custom_nested_tab(id my_tab_t PRIMARY KEY,name VARCHAR2(10))
NESTED TABLE id STORE AS id_tab
/

Example of a Nested Table with Multiple Columns

---

1.
Create a Object Type.
— Example of a Object Type —
CREATE OR REPLACE TYPE my_rec IS OBJECT(r_id NUMBER,r_name_1 VARCHAR2(50))
/

2.
Create another Object which holds the Object Type.
— Example to Create a Type of Table —
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF my_rec;
/

3.
Create a Nested Table holding that Object (Holding the Object Type).
— Example to create a Table holding that Table Type as a Nested Table —
CREATE TABLE custom_nested_tab(id my_tab_t,name_2 VARCHAR2(10))
NESTED TABLE id STORE AS id_tab
/

— Inserting Values into Nested Table —
INSERT INTO custom_nested_tab VALUES(my_tab_t(my_rec(100,’A’)),’K1′)
/

— Using Nested Tables in PL/SQL —

---

— Example to demonstrate in printing element values from a Nested Table in PL/SQL —
DECLARE
— Declare a Local Nested Table Collection Type —
TYPE loc_num_nest_t IS TABLE OF NUMBER;
l_local_nt loc_num_nest_t:=loc_num_nest_t(10,20,30);
BEGIN

DBMS_OUTPUT.PUT_LINE(‘Printing First three elements’);

— Use FOR Loop to print the Nested Table Elements —
FOR i IN 1..l_local_nt.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(l_local_nt(i));
END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Printing next set of elements after using EXTEND’);

l_local_nt.EXTEND();
l_local_nt:=loc_num_nest_t(10,20,30,40);

— Use FOR Loop to print the Nested Table Elements —
FOR i IN 1..l_local_nt.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(l_local_nt(i));
END LOOP;
END;
/

— Define an Object Type —
CREATE OR REPLACE TYPE typ_item AS OBJECT
(prodid NUMBER(5),price NUMBER(7,2))
/

— Define a Nested Table Type —
CREATE TYPE typ_item_nst AS TABLE OF typ_item
/

— Create a Table by having the Column of the Collection Type —
CREATE TABLE porder
(ordid NUMBER(5),
supplier NUMBER(5),
requester NUMBER(4),
ordered DATE,
items typ_item_nst)
NESTED TABLE items STORE AS item_store_tab
/

— Insert some set of Records into the Nested Table —
INSERT INTO porder
VALUES(500,50,5000,SYSDATE,
typ_item_nst
(
typ_item(55,555),
typ_item(56,566),
typ_item(57,577)))
/

INSERT INTO porder
VALUES(800,80,8000,SYSDATE,
typ_item_nst(typ_item(88,888)))
/

— Query the set of records from the Table —
SELECT * FROM porder
/

— Query the Set of Records to bring along the Nested Table Values using TABLE Function —
SELECT p2.ordid,p1.*
FROM porder p2,TABLE(p2.items) p1
/

— Using Nested Table in PL/SQL —
CREATE OR REPLACE PROCEDURE add_items
(p_ordid NUMBER,p_new_items typ_item_nst)
IS
v_num_items NUMBER;
v_with_discount typ_item_nst;
BEGIN
v_num_items:=p_new_items.COUNT;
v_with_discount:=p_new_items;
IF v_num_items > 2 THEN
— Ordering More than 2 Items gives us a Discount of 5% —
FOR i IN 1..v_num_items
LOOP
v_with_discount(i):=
typ_item(
p_new_items(i).prodid,
p_new_items(i).price *.95);
END LOOP;
END IF;
UPDATE porder
SET items = v_with_discount
WHERE ordid = p_ordid;
END add_items;
/

— How to execute this PL/SQL Procedure —

DECLARE
v_form_items typ_item_nst:=typ_item_nst();
BEGIN
— Let us assume this form holds 4 items —
v_form_items.EXTEND(4);
v_form_items(1):=typ_item(1804,65);
v_form_items(2):=typ_item(3172,42);
v_form_items(3):=typ_item(3337,800);
v_form_items(4):=typ_item(2144,14);
add_items(800,v_form_items);
END;
/

— Query the set of records from the Table —
SELECT * FROM porder
/

— Query the Set of Records to bring along the Nested Table Values using TABLE Function —
SELECT p2.ordid,p1.*
FROM porder p2,TABLE(p2.items) p1
/

SELECT parent_table_column,table_name,return_type
FROM user_nested_tables
/

SELECT parent_table_column,table_name,return_type
FROM user_nested_tables
–WHERE parent_table_name = ‘TYP_ITEM_NST’
/

— Example to demonstrate Nested Table initially which holds 2 elements and extends to hold 5 elements —
— Using DELETE attribute to delete element from any of the nested table elements values —

DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN

— Initialise the collection with two values.
v_tab := table_type();

— Extend the collection with extra values.
FOR i IN 3 .. 5 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP;

— Delete the third item of the collection.
v_tab.DELETE(3);

— Traverse sparse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

— Example to demonstrate Nested Table initially which holds 2 elements and extends to hold 5 elements —
— Using DELETE attribute to delete 3rd and 4th element from the nested table —
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
— Initialise the collection with two values.
v_tab := table_type();
— Extend the collection with extra values.
FOR i IN 1 .. 10 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP;
— Delete the third item of the collection.
v_tab.DELETE(3,4);
— Traverse sparse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

— Example to demonstrate Nested Table initially which holds 2 elements and extends to hold 10 elements —
— Using DELETE attribute to delete staring from 7th and ending 9th element from the nested table —
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN

— Initialise the collection with two values.
v_tab := table_type();

— Extend the collection with extra values.
FOR i IN 1 .. 10 LOOP — Can keep extending element values to a Nested Table —
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP;
— Delete the third item of the collection.
v_tab.DELETE(7,9);

— Traverse sparse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

— Delete the 7th and 9th Element from a Nested Table —
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
— Initialise the collection with two values.
v_tab := table_type();
— Extend the collection with extra values.
FOR i IN 1 .. 10 LOOP — Can keep extending element values to a Nested Table —
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP;
v_tab.DELETE(7); — Deletes the 7th Element —
v_tab.DELETE(9); — Deletes the 9th Element —
— Traverse sparse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

— Example to demonstrate Nested Table —
— Without Looping till 20 elements and trying to delete the unexisting elements
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
— Initialise the collection with two values.
v_tab := table_type();
— Extend the collection with extra values.
FOR i IN 1 .. 20 LOOP — Looping performs only till 10 —
v_tab.extend();
v_tab(v_tab.last) := i;
END LOOP;
v_tab.DELETE(15,20);
— Traverse sparse collection
v_idx := v_tab.FIRST;
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(‘The number ‘ || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP;
END;
/

---

---

4.
INDEX BY Tables or Associate Arrays

---

PL/SQL Tables are PL/SQL’s ways of providing arrays.
Arrays are like temporary tables in memory and thus are processed very quickly.
These are not normal database tables and DML statements cannot be issued against them.
This type of table is indexed by a binary integer counter whose value can be referenced using the number of the index.
PL/SQL Tables exist in memory only and disappears after the session ends.
PL/SQL Table uses a primary key to give you array – like access to rows.
The no.of rows in a PL/SQL Table can increase dynamically.

Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection.
However, PL/SQL tables differ from arrays in two important ways.

1. Arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. 2.Arrays require consecutive index numbers, but PL/SQL tables do not. This characteristic, called sparsity, allows the use of meaningful index numbers. For example, you can use a series of employee numbers (such as 7369, 7499, 7521, 7566, …) to index a PL/SQL table of employee names.

PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms.

There are two steps in the declaration of a PL/SQL Table.

1. Define the Table structure using the TYPE statement
2. Once the Table Type is created then declare the actual table.

EXTEND cannot be used with INDEX BY Tables.

PL/SQL Table Attributes :

1. EXISTS – Returns TRUE if the specified entry exists in the table.
2. COUNT – Returns the no.of rows in the table.
3. FIRST – Returns the index of the first row in the table.
4. LAST – Returns the index of the last row in the table.
5. NEXT – Returns the index of the next row in the table after the specified row.
6. DELETE – Deletes the PL/SQL Table.

PL/SQL Table attributes are used with the following syntax :
.

If you declare a PL/SQL Table named t_emp then get the rowcount for the table using
v_count:=t_emp.count;

— Example to create an Indexed Table and access each element from that PL/SQL Table —
DECLARE
TYPE num_type IS TABLE OF number INDEX BY BINARY_INTEGER;
num num_type;
BEGIN
num(1):=100;
num(2):=200;
num(3):=300;
DBMS_OUTPUT.PUT_LINE(‘Each Element in PL/SQL Table’);
DBMS_OUTPUT.PUT_LINE(num(1));
DBMS_OUTPUT.PUT_LINE(num(2));
DBMS_OUTPUT.PUT_LINE(num(3));
END;
/

— PL/SQL Block to use the PL/SQL Table concept with FIRST and NEXT attribute —
DECLARE
TYPE my_dept_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
var_of_dept my_dept_table;
index_value NUMBER;
BEGIN
FOR i IN (SELECT * FROM dept)
LOOP
var_of_dept(i.deptno):=i.dname||’ ‘||i.loc;
END LOOP;
index_value:=var_of_dept.FIRST;
LOOP
EXIT WHEN index_value IS NULL;
DBMS_OUTPUT.PUT_LINE(index_value||’ ‘||var_of_dept(index_value));
index_value:=var_of_dept.next(index_value);
END LOOP;
END;
/

— PL/SQL Table which displays the last element from the PL/SQL Table using LAST Attribute —
DECLARE
TYPE my_dept_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
var_of_dept my_dept_table;
index_value NUMBER;
BEGIN
FOR i IN (SELECT * FROM dept)
LOOP
var_of_dept(i.deptno):=i.dname;
END LOOP;
index_value:=var_of_dept.LAST;
LOOP
EXIT WHEN index_value IS NULL;
DBMS_OUTPUT.PUT_LINE(index_value||’ ‘||var_of_dept(index_value));
index_value:=var_of_dept.next(index_value);
END LOOP;
END;
/

— PL/SQL Block to use the PL/SQL Table concept with FIRST and NEXT attribute using Cursor FOR Loop and —- Counter Variables —
DECLARE
— Declare the Cursor to select 3 records from DEPT Table —
CURSOR name_cur IS SELECT deptno,dname FROM dept WHERE rownum <= 3;
— Declare a PL/SQL Table Type having the contents of DEPT Table indexed — by DEPTNO Primary Key —
TYPE my_dept_table IS TABLE OF dept.deptno%type INDEX BY BINARY_INTEGER;
var_of_dept my_dept_table; — Declare a variable storing the contents —
index_value INTEGER;
BEGIN
FOR i IN name_cur
LOOP
var_of_dept(i.deptno):=i.deptno;
END LOOP;

    index_value:=var_of_dept.FIRST;

    LOOP
        EXIT WHEN index_value IS NULL;
        DBMS_OUTPUT.PUT_LINE(var_of_dept(index_value));
        index_value:=var_of_dept.next(index_value);
    END LOOP;

END;
/

— PL/SQL Block using Cursor FOR Loop to fetch entire columns of Oracle data into the PL/SQL tables ename_tab and sal_tab —

DECLARE
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;
sal_tab SalTabTyp;
n BINARY_INTEGER := 0;
BEGIN
/* Fetch entire columns into PL/SQL tables. */
FOR i IN (SELECT ename, sal FROM emp)
LOOP
n:= n + 1;
ename_tab(n) := i.ename;
sal_tab(n) := i.sal;
DBMS_OUTPUT.PUT_LINE(i.ename||’ ‘||i.sal);
END LOOP;
END;
/

— PL/SQL Table using the COUNT Attribute to display 1 to 10 looping through each record —
DECLARE
TYPE t_NumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_Numbers t_NumberTable;
v_total NUMBER;
BEGIN
FOR I IN 1..10 LOOP
v_numbers(i) := i;
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
v_total := v_numbers.COUNT;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records displayed :’||v_total);
END;
/

DECLARE
TYPE t_NumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_Numbers t_NumberTable;
v_total NUMBER;
BEGIN
FOR I IN 1..10 LOOP
v_numbers(i) := i;
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
v_total := v_numbers.COUNT;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records displayed :’||v_total);
END;
/

— PL/SQL Table using the EXISTS Attribute to return TRUE or FALSE —
DECLARE
Type t_FirstNameTable IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
FirstNames t_FirstNameTable;
BEGIN
— Insert rows into the table.
FirstNames(1) := ‘Scott’;
FirstNames(3) := ‘Joanne’;
— Check to see if rows exist.
IF FirstNames.EXISTS(1) THEN
DBMS_OUTPUT.put_line(‘Row 1 exists! ‘||firstnames(1));
ELSE
DBMS_OUTPUT.put_line(‘Row 1 doesn”t exist!’);
END IF;
IF FirstNames.EXISTS(2) THEN
DBMS_OUTPUT.put_line(‘Row 2 exists!’);
ELSE
DBMS_OUTPUT.put_line(‘Row 2 doesn”t exist!’);
END IF;
IF FirstNames.EXISTS(3) THEN
DBMS_OUTPUT.put_line(‘Row 3 exists! ‘||firstnames(3));
ELSE
DBMS_OUTPUT.put_line(‘Row 3 doesn”t exist!’);
END IF;
END;
/

— PL/SQL Table using the DELETE Attribute —
— Deletes the 1st Element —
DECLARE
TYPE num_type IS TABLE OF number INDEX BY BINARY_INTEGER;
num num_type;
BEGIN
num(1):=100;
num(2):=200;
num(3):=300;
num.DELETE(1);
DBMS_OUTPUT.PUT_LINE(‘Each Element in PL/SQL Table’);
DBMS_OUTPUT.PUT_LINE(num(2));
DBMS_OUTPUT.PUT_LINE(num(3));
END;
/

— PL/SQL Table using the DELETE Attribute —
— Deletes the 1st Element —
DECLARE
TYPE num_type IS TABLE OF number INDEX BY BINARY_INTEGER;
num num_type;
BEGIN
num(1):=100;
num(2):=200;
num(3):=300;
num.DELETE(1);
DBMS_OUTPUT.PUT_LINE(‘Each Element in PL/SQL Table’);
DBMS_OUTPUT.PUT_LINE(num(1)); — This errors out with no_data_found Exception
DBMS_OUTPUT.PUT_LINE(num(2));
DBMS_OUTPUT.PUT_LINE(num(3));
END;
/

— PL/SQL Table using the DELETE Attribute —
— Deletes the 5th Element and does not continue with the Loop —
DECLARE
TYPE t_NumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_Numbers t_NumberTable;
v_total NUMBER;
BEGIN
FOR I IN 1..10 LOOP
v_numbers(i) := i;
v_numbers.DELETE(5);
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/

— PL/SQL Table using INDEX BY BINARY INTEGER along with RECORD Type —
DECLARE
type dept_rec is record(deptno dept.deptno%type,dname dept.dname%type,loc dept.loc%type);
type dept_tab is table of dept_rec index by binary_integer;
v_dept dept_tab;
BEGIN
v_dept(10).deptno:=10;
v_dept(20).deptno:=20;
FOR i in v_dept.first..v_dept.last
LOOP
IF v_dept.exists(i) THEN
DBMS_OUTPUT.put_line(v_dept(i).deptno);
END IF;
END LOOP;
END;
/

— Example to Insert Records into a Table using PL/SQL Table —

---

1.
— Create the Destination Table —
CREATE TABLE employee_tab(id NUMBER,name VARCHAR2(150))
/

2.

DECLARE
TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp emp_tab;
CURSOR c1 IS SELECT empno,ename FROM emp;
i NUMBER;
BEGIN
OPEN c1;
LOOP
EXIT WHEN c1%NOTFOUND;
FETCH c1 INTO v_emp(i);
i:=i + 1;
END LOOP;
CLOSE c1;

— Display the entire PL/SQL Table on the Screen —
FOR i IN 1..v_emp.LAST
LOOP
DBMS_OUTPUT.put_line(‘Dept No. ‘||v_dept(i).deptno);
END LOOP;
END;
/

---

---