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

Using prior and next - collection method, Using PRIOR and NEXT The PRI... Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Sql query for testing triggers and stored procedures , Description: Dem... Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Develop data business intelligence project, Develop Data Business Intellige... Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

Table comparison - sql, Table Comparison - SQL The following definitio... Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Procedures in pl/sql, Procedures   The procedure is a subprogram which... Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Transaction visibility, Transaction Visibility As the figure shows, th... Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

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:

In out mode - parameter modes, IN OUT Mode An IN OUT parameter passes ... IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

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 -

Example of foreign key constraint - sql, Example of Foreign Key Constraint ... Example of Foreign Key Constraint Example: Alternative formulation for 6.3 as a foreign key constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_take_exam