Collections:

 The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subscript which determines its position in the collection. The PL/SQL offers 3 kinds of collections: the nested tables, index-by tables, and varrays . The Nested tables extend the functionality of the index-by tables (formerly known as the "PL/SQL tables").

The Collections works like the arrays found in mainly the third-generation programming languages. Though, collections can have only one dimension and should be indexed by integers. (In several languages like Ada and Pascal, arrays can have the multiple dimensions and can be indexed by inventory types.)

The Nested tables and varrays can store instances of an object type and, on the other hand, can be attributes of an object type. The collections can also be passed as the parameters. Therefore, you can use them to move the columns of data into and out of database tables or between the client-side applications and stored subprograms.

Syntax:

Keyword and Parameter Description

 

type_name:

These identify a user-defined type specifier that is used in the subsequent declarations of collections.

element_type:

This is any PL/SQL datatype except the BOOLEAN, BINARY_INTEGER, LONG, LONG RAW, NATURAL, NATURALN, NCLOB, NCHAR, NVARCHAR2, object types with TABLE or VARRAY attributes, PLS_INTEGER, POSITIVE, SIGNTYPE, POSITIVEN, REF CURSOR, STRING, TABLE, or VARRAY. Also, with the varrays, the element_type cannot be CLOB, BLOB, or an object type with BLOB or CLOB attributes. If the element_type is a record type, every field in the record should be a scalar type or an object type.

INDEX BY BINARY_INTEGER:

This optional clause defines the Version 2 PL/SQL tables that are called index-by tables in Version 8.

size_limit:

This is a positive integer literal which specifies the maximum size of a varray that is the maximum number of elements that the varray can contain.

Posted Date: 10/6/2012 9:05:54 AM | Location : United States

Ask an Expert

Related Discussions:- Collections in pl sql, Assignment Help, Ask Question on Collections in pl sql, Get Answer, Expert's Help, Collections in pl sql Discussions

Write discussion on Collections in pl sql
Your posts are moderated

Write your message here..

Related Questions

Loop statements, LOOP Statements The LOOP statements execute a series o... LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Row counterparts of table operators - sql, Row Counterparts of Table Operat... Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Select query, Query: SELECT * FROM EMPLOYEE1; Select 5 columns and ... Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Fetching from a cursor variable, Fetching from a Cursor Variable The F... Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Mixed notation, Mixed Notation The fourth procedure call shows that yo... Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ... OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Cursors, What is Cursors how to use it in Real time application ? What is Cursors how to use it in Real time application ?

Translate the given er schema into sql, Consider the schema for FreeCheckin... Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Example of check constraints - sql, Example of Check Constraints Examp... Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

Pits, PITS Depressions in secondary cell wall is called pit. A pi... PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -