Collection methods in pl sql, PL-SQL Programming

Assignment Help:

Collection Methods:

 The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the COUNT, EXISTS, LIMIT, FIRST, LAST, NEXT, EXTEND, PRIOR, TRIM, & DELETE helpful to normalize the code, make collections easier to use, and make your applications easier to sustain.

The COUNT, EXISTS, LIMIT, PRIOR, FIRST, LAST, and NEXT are the functions, that appear as part of an expression. The TRIM, EXTEND, and DELETE are the procedures, that appear as a statement. The EXISTS, NEXT, PRIOR, TRIM, EXTEND, and DELETE take integer parameters.

Syntax:

 

467_collaction function in pl sql.png

Keyword and Parameter Description

collection_name:

These identify an index-by table, varray, or nested table formerly declared within the present scope.

COUNT:

The COUNT returns to the number of elements which a collection presently contains, that is helpful as the present size of a collection is not always known. You can use the COUNT wherever an integer expression is allowed.

For varrays, the COUNT always equals to LAST. For nested tables, generally, COUNT equals to LAST. But, if you delete elements from the middle of the nested table, the COUNT is smaller than LAST.

DELETE:

This procedure has 3 forms. The DELETE eliminates all the elements from a collection. The DELETE (n) eliminates the nth element from a nested table. If n is null, the DELETE (n) does nothing. The DELETE (m,n) eliminates all the elements in the range of m..n from a nested table. If m is bigger than n or if m or n is null, then DELETE (m,n) does nothing.

Index:

This is an expression which should yield an integer.

EXISTS:

The EXISTS(n) returns TRUE when the nth element in a collection exists. Or else, EXISTS(n) returns FALSE. Primarily, you can use EXISTS with DELETE to sustain the sparse nested tables. You can also use the EXISTS to avoid raising an exception whenever you reference a nonexistent element. When passed an out-of-range subscript, the EXISTS returns FALSE rather than of raising the SUBSCRIPT_OUTSIDE_LIMIT.

EXTEND:

This procedure has 3 forms. The EXTEND appends one null element to the collection. The EXTEND(n) appends n null elements to the collection. The EXTEND(n,i) appends n copies of the ith element to the  collection. The EXTEND operates on the internal size of the collection. Therefore, if EXTEND encounters deleted elements, it involves them in its tally.

FIRST, LAST:

The FIRST & LAST return the first & last (lowest limit & upper limit) index numbers in a collection. If the collection is blank, the FIRST and LAST return NULL. When the collections contain only one element, the FIRST and LAST return similar index number. For varrays, the FIRST always returns 1 and the LAST always equals to COUNT. For the nested tables, generally, LAST equals to COUNT. But, when you delete elements from the middle of the nested table, the LAST is larger than COUNT.

LIMIT:

For the nested tables, which have no maximum size, the LIMIT returns NULL. For varrays, the LIMIT returns the maximum number of elements which a varray can contain (that you should specify in its type definition).

NEXT, PRIOR:

The PRIOR(n) returns the index number which precedes an index n in a collection. The NEXT(n) returns the index number which succeed index n. If n has no predecessor, then the PRIOR(n) returns NULL. Similarly, if n has no successor, the NEXT(n) returns the NULL.

TRIM:

This procedure has 2 forms. The TRIM eliminates one element from the end of the collection. The TRIM(n) eliuminates n elements from the end of the  collection. If n is bigger than COUNT, then the TRIM(n) raises the SUBSCRIPT_BEYOND_COUNT. The TRIM operates on the internal size of a collection. Therefore, if TRIM encounters deleted elements, it involves them in its tally.


Related Discussions:- Collection methods in pl sql

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Explicitly specifying the join condition - sql, Explicitly specifying the j...

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

3cx pbx sync windows service, 3CX PBX sync Windows Service Project Descr...

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Quantification in sql, Quantification in SQL To quantify something, as...

Quantification in SQL To quantify something, as the theory book has it, is to state its quantity, to say how many of it there are. For example, in Tutorial D the expression CO

Primary key - sql, Primary Key - SQL A PRIMARY KEY specification carri...

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

Need azure crm web application with authentication, Need Azure CRM Web Appl...

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Deriving predicates from predicates in sql, Deriving Predicates from Predic...

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

%found - explicit cursor attributes, %FOUND Subsequent to a cursor or ...

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

Effects of null in table literal, Effects of NULL in Table Literal Whe...

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Using %type-declarations in sql, Using %TYPE The %TYPE attribute gives th...

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

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