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

Parameter modes, Parameter Modes   To define the behavior of formal pa... Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

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)

Cursors - syntax, Cursors   To execute the multi-row query, the Oracle... Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Logical operators-pl/sql expressions , Logical Operators The logical op... Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Cursor attributes in pl sql, Cursor Attributes   The Cursors and curso... Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Raise_application_error, Raise_application_error -  procedure of package D... Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Union and or - sql, UNION and OR - SQL SQL supports UNION explicitly b... UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

Use external routines - improve performance of application, Use External Ro... Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Cursor variables in pl sql, Cursor Variables:   To execute the multi-... Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Parameter and keyword description - object types, Parameter and Keyword Des... Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause: