Defining and declaring collections, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Defining and declaring collections

Goto statement - sequential control, GOTO Statement The GOTO statement b...

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.

Example of group by and collect operator, Example of GROUP BY and COLLECT O...

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF...

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Aggregate assignment-declarations in sql, Aggregate Assignment The %ROWT...

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Using host arrays - bulk bind performance improvement, Using Host Arrays ...

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Testing triggers, Demonstrate your knowledge of PL/SQL programming by writi...

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

Recursion versus iteration, Recursion versus Iteration Dissimilar the i...

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

Negation - sql, Negation (NOT, ¬) - SQL There are three rows instead o...

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

Pascal programming and mysql programming, I have a Pascal Source file that ...

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

Python function, This task involves developing some functions that extract ...

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd