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
Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security l

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d