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
Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

Use Serially Reusable Packages To help you to manage the use of memory, the PL/SQL gives the pragma SERIALLY_ REUSABLE that mark some packages as serially reusable . So mark

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever