Defining and declaring collections, PL-SQL Programming

Defining and Declaring Collections

To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types and TABLE in the declarative part of any of the PL/SQL block, package, or subprogram. For the nested tables, you can use the syntax as shown below:


TYPE type_name IS TABLE OF element_type [NOT NULL];

and for varrays, use the syntax shown below:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];


Where type_name is a type specifier used later to declare the collections, size_limit is a positive integer literal, and an element_type is any PL/SQL datatype except

BINARY_INTEGER, PLS_INTEGER
BOOLEAN
BLOB, CLOB (restriction applies only to varrays)
LONG, LONG RAW
NATURAL, NATURALN
NCHAR, NCLOB, NVARCHAR2
object types with BLOB or CLOB attributes (restriction applies only to varrays)
object types with TABLE or VARRAY attributes
POSITIVE, POSITIVEN
REF CURSOR
SIGNTYPE
STRING
TABLE
VARRAY


If element_type is a record type, then every field in the record must be a scalar type or an object type.


For the index-by tables, use the following syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY BINARY_INTEGER;


Dissimilar nested tables and varrays, the index-by tables can have the following element types: BOOLEAN, LONG, BINARY_INTEGER, LONG RAW, NATURAL, NATURALN, PLS_ INTEGER, POSITIVE, POSITIVEN, STRING, and SIGNTYPE. That is because the nested tables and varrays are intended primarily for the database columns. As such, they cannot use the PL/SQL-specific types. If it is declared locally, they could theoretically use those types, and the restriction is preserved for consistency.

The Index-by tables are initially sparse. This enables you, for instance, to store the reference data in a temporary index-by table using a numeric primary key like the index. In the illustration below, you declare an index-by table of records. Every element of the table stores a row from the emp database table.


DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
BEGIN
/* Retrieve employee record. */
SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7788;


When defining a VARRAY type, you should specify its maximum size. In the following illustration, you define a type that stores up to 366 dates:

DECLARE
TYPE Calendar IS VARRAY(366) OF DATE;


To identify the element type, you can use %TYPE that provides the datatype of a variable or database column. You can also use %ROWTYPE that provides the rowtype of a cursor or database table. The Two illustrations are as follows:



DECLARE
TYPE EmpList IS TABLE OF emp.ename%TYPE; -- based on column
CURSOR c1 IS SELECT * FROM dept;
TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE; -- based on cursor


In the next illustration, you use a RECORD type to specify the element type:

DECLARE
TYPE AnEntry IS RECORD (
term VARCHAR2(20),
meaning VARCHAR2(200));
TYPE Glossary IS VARRAY(250) OF AnEntry;


In the final illustration, you impose a NOT NULL constraint on the element type:

DECLARE
TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;

An initialization clause is not needed (or allowed).




Declaring Collections

Once you define a collection type, you can declare the collections of that type, as the SQL Plus script below shows:

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE Student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList) -- declare nested table as attribute
/


The identifier courses represent the whole nested table. Each and every element of courses will store the code name of a college course like ’Math 1020’.
The script below creates a database column which stores varrays. Each and every element of the varrays will store a Project object.


CREATE TYPE Project AS OBJECT( --create object
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2))
/
CREATE TYPE ProjectList AS VARRAY(50) OF Project -- define VARRAY
type
/
CREATE TABLE department ( -- create database table
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList) -- declare varray as column
/


The illustration below shows that you can use %TYPE to provide the datatype of a earlier declared collection:

DECLARE
TYPE Platoon IS VARRAY(20) OF Soldier;
p1 Platoon;
p2 p1%TYPE;


You can declare collections as the proper parameters of the functions and procedures.
In that way, you can pass collections to the stored subprograms and from one subprogram to another. In the example below, you declare a nested table as the proper parameter of a packaged procedure:


CREATE PACKAGE personnel AS
TYPE Staff IS TABLE OF Employee;
...
PROCEDURE award_bonuses (members IN Staff);
END personnel;



You can also specify a collection type in the RETURN clause of a function specification, as the illustration below shows:


DECLARE
TYPE SalesForce IS VARRAY(25) OF Salesperson;
FUNCTION top_performers (n INTEGER) RETURN SalesForce IS...


The Collections follow the usual scoping and instantiation rules. In a block or subprogram, the collections are instantiated whenever you enter the block or subprogram and cease to exist when you exit. In a package, the collections are instantiated when you first reference the package and cease to exist whenever you end the database session.

Posted Date: 10/3/2012 6:36:20 AM | Location : United States







Related Discussions:- Defining and declaring collections, Assignment Help, Ask Question on Defining and declaring collections, Get Answer, Expert's Help, Defining and declaring collections Discussions

Write discussion on Defining and declaring collections
Your posts are moderated
Related Questions
Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

Datatypes Every constant and variable has a datatype that specifies the storage format, constraints, and the valid range of values. The PL/SQL gives a variety of predefined dat

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

Find the account numbers of all customers whose balance is more than 10,000 $

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

THEO R Y OF CATASTROPHISM OR CATALYSM (CUVIER 1769-1832) - The world has passed thorugh several stages and at the end of each stage there was a catastrophe killing all the