.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Post New Resource Bookmark and Share   

Collections in PLSQL

Posted By:Ashutosh Jha       Posted Date: May 27, 2014    Points: 200    Category:    URL: http://www.dotnetspark.com  

Here i will explain what is collections and where/how to use it.
 

PLSQL Collections-an overview:

A collection is a homogeneous single dimensional structure, which constitutes an ordered set of elements of a similar type.
 Being a homogeneous structure, all elements are of the same data type. The structure of the element contains cells with a
 subscript. The elements reside in these cells to make the index as their location information. The subscript or cell index
 becomes identification of an element and is used for its access.

A collection element can be of any valid SQL data type or a user-defined type. An element of the SQL primitive data type
 is a scalar value while an element of the user-defined type is an object type instance. A collection can be used within 
a PL/SQL program by declaring a PL/SQL variable of collection type. The local PL/SQL variable can hold the instances of 
its collection type. Besides, a database column in a table can also be of the schema collection type.

Collections provide an efficient way to organize the data in an array or set format while making the use of 
object-oriented features. An instance of a nested table or varray collection type is accessed as an object while 
the data is still stored in database columns. Collections can be used to avail data caching in programs and boost 
up the performance of SQL operations. On dedicated server connections, a session always uses User Global Area (UGA),
 a component of PGA, for collection operations. On the other hand, for shared server mode, the collection operations
 are still carried out in UGA; but UGA is now a part of System Global Area (SGA), thus indirectly in SGA. This is because 
in shared server connections, multiple server processes can affect a session, thus UGA must be allocated out of the SGA.

Categorization

Collections are of two types-persistent and non-persistent. A collection is persistent if it stores the collection 
structure and elements physically in the database. Contrarily, a non-persistent collection is active for a program only 
that is, maximum up to a session.

Associative arrays follow the following syntax for declaration in a PL/SQL declare block:

TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL 
  INDEX BY [INDEX DATA TYPE]

For illustration, the following are the valid conditions of the associative array in a PL/SQL block:

/*Array of CLOB data*/
TYPE clob_t IS TABLE OF CLOB 
INDEX BY PLS_INTEGER;
/*Array of employee ids indexed by the employee names*/
TYPE empno_t IS TABLE OF employees.empno%TYPE NOT NULL
INDEX BY employees.ename%type;

The following PL/SQL program declares an associative array type in a PL/ SQL block

/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a collection type associative array and its variable*/
  TYPE string_asc_arr_t IS TABLE OF NUMBER 
  INDEX BY VARCHAR2(10);
  l_str string_asc_arr_t;
  l_idx VARCHAR2(50);
BEGIN
/*Assign the total count of days in each quarter against each cell*/
  l_str ('JAN-MAR') := 90;
  l_str ('APR-JUN') := 91;
  l_str ('JUL-SEP') := 92;
  l_str ('OCT-DEC') := 93; 
  l_idx := l_str.FIRST;
  WHILE (l_idx IS NOT NULL)
  LOOP
  DBMS_OUTPUT.PUT_LINE('Value at index '||l_idx||' is '||l_str(l_
idx));
   l_idx := l_str.NEXT(l_idx);
  END LOOP;
END;
/

Value at index APR-JUN is 91
Value at index JAN-MAR is 90
Value at index JUL-SEP is 92
Value at index OCT-DEC is 93

PL/SQL procedure successfully completed.

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend