PL-SQL
PL/SQL Variable Scope & Inner Outer Block: Nested Structure
What is Nested Blocks Oracle? In PL/SQL, each block can be nested into another block. They are...
A Collection is an ordered group of elements of particular data types. It can be a collection of simple data type or complex data type (like user-defined or record types).
In the collection, each element is identified by a term called "subscript." Each item in the collection is assigned with a unique subscript. The data in that collection can be manipulated or fetched by referring to that unique subscript.
Collections are most useful things when a large data of the same type need to be processed or manipulated. Collections can be populated and manipulated as whole using 'BULK' option in Oracle.
In this tutorial, you will learn-
Collections are classified based on the structure, subscript, and storage as shown below.
At any point, data in the collection can be referred by three terms Collection name, Subscript, Field/Column name as "<collection_name>(<subscript>).<column_name>". You are going to learn about these above-mentioned collection categories further in the below section.
Varray is a collection method in which the size of the array is fixed. The array size cannot be exceeded than its fixed value. The subscript of the Varray is of a numeric value. Following are the attributes of Varrays.
The below figure will explain the memory allocation of Varray (dense) diagrammatically.
Subscript | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Value | Xyz | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
Syntax for VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
A Nested table is a collection in which the size of the array is not fixed. It has the numeric subscript type. Below are more descriptions about nested table type.
The below figure will explain the memory allocation of Nested Table (dense and sparse) diagrammatically. The black colored element space denotes the empty element in a collection i.e. sparse.
Subscript | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Value (dense) | Xyz | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
Value(sparse) | Qwe | Asd | Afg | Asd | Wer |
Syntax for Nested Table:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
Index-by-table is a collection in which the array size is not fixed. Unlike the other collection types, in the index-by-table collection the subscript can consist be defined by the user. Following are the attributes of index-by-table.
The below figure will explain the memory allocation of Nested Table (sparse) diagrammatically. The black colored element space denotes the empty element in a collection i.e. sparse.
Subscript (varchar) | FIRST | SECOND | THIRD | FOURTH | FIFTH | SIXTH | SEVENTH |
Value(sparse) | Qwe | Asd | Afg | Asd | Wer |
Syntax for Index-by-Table
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
Constructors are the in-built function provided by the oracle that has the same name as of the object or collections. They are executed first whenever object or collections are getting referred for the first time in a session. Below are the important details of constructor in collection context:
Oracle provides many functions to manipulate and to work with the collections. These functions are very much useful in the program to determine and to modify the different attribute of the collections. The Following table will give the different functions and their description.
Method | Description | SYNTAX |
EXISTS (n) | This method will return Boolean results. It will return 'TRUE' if the nth element exists in that collection, else it will return FALSE. Only EXISTS functions can be used in uninitialized collection | <collection_name>.EXISTS(element_position) |
COUNT | Gives the total count of the elements present in a collection | <collection_name>.COUNT |
LIMIT | It returns the maximum size of the collection. For Varray, it will return the fixed size that has been defined. For Nested table and Index-by-table, it gives NULL | <collection_name>.LIMIT |
FIRST | Returns the value of the first index variable(subscript) of the collections | <collection_name>.FIRST |
LAST | Returns the value of the last index variable(subscript) of the collections | <collection_name>.LAST |
PRIOR (n) | Returns precedes index variable in a collection of the nth element. If there is no precedes index value NULL is returned | <collection_name>.PRIOR(n) |
NEXT (n) | Returns succeeds index variable in a collection of the nth element. If there is no succeeds index value NULL is returned | <collection_name>.NEXT(n) |
EXTEND | Extends one element in a collection at the end | <collection_name>.EXTEND |
EXTEND (n) | Extends n elements at the end of a collection | <collection_name>.EXTEND(n) |
EXTEND (n,i) | Extends n copies of the ith element at the end of the collection | <collection_name>.EXTEND(n,i) |
TRIM | Removes one element from the end of the collection | <collection_name>.TRIM |
TRIM (n) | Removes n elements from the end of collection | <collection_name>.TRIM (n) |
DELETE | Deletes all the elements from the collection. Makes the collection empty | <collection_name>.DELETE |
DELETE (n) | Deletes the nth element from the collection. If the nth element is NULL, then this will do nothing | <collection_name>.DELETE(n) |
DELETE (m,n) | Deletes the element in the range mth to nth in the collection | <collection_name>.DELETE(m,n) |
Example1: Record Type at Subprogram level
In this example, we are going to see how to populate the collection using 'BULK COLLECT' and how to refer the collection data.
DECLARE TYPE emp_det IS RECORD ( EMP_NO NUMBER, EMP_NAME VARCHAR2(150), MANAGER NUMBER, SALARY NUMBER ); TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl(); BEGIN INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000); INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000); INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000); INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000); COMMIT: SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec FROM emp; dbms_output.put_line (‘Employee Detail'); FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST LOOP dbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no); dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name); dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary); dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager); dbms_output.put_line('--------------------------------'); END LOOP; END; /
Code Explanation:
Output: As you can see in the above screenshot when the above code is executed you will get the following output
Employee Detail Employee Number: 1000 Employee Name: AAA Employee Salary: 25000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1001 Employee Name: XXX Employee Salary: 10000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1002 Employee Name: YYY Employee Salary: 15000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1003 Employee Name: ZZZ Employee Salary: 7500 Employee Manager Number: 1000 ----------------------------------------------
What is Nested Blocks Oracle? In PL/SQL, each block can be nested into another block. They are...
What is For Loop? "FOR LOOP" statement is best suitable when you want to execute a code for a...
$20.20 $9.99 for today 4.5 (108 ratings) Key Highlights of PL/SQL Tutorial PDF 188+ pages eBook...
SQL is the standard language to query a database. PL SQL basically stands for "Procedural Language...
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...