Using extend - collection method, PL-SQL Programming

Using EXTEND

To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to the  collection. For e.g. the statement below appends 5 copies of element 1 to nested table courses:

courses.EXTEND(5,1);

You cannot use the EXTEND to initialize an automatically null collection. In addition if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that kind.

The EXTEND operates on the internal size of the collection, that includes any deleted elements. Therefore, if EXTEND encounters deleted the elements, then it includes them in its tally. The PL/SQL keeps placeholders for the deleted elements so that you can swap them whenever you wish. Consider the example shown below:

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(10);

courses CourseList;

BEGIN

courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');

courses.DELETE(3); -- delete element 3

/* PL/SQL keeps a placeholder for element 3. So, the

next statement appends element 4, not element 3. */

courses.EXTEND; -- append one null element

/* Now element 4 exists, so the next statement does

not raise SUBSCRIPT_BEYOND_COUNT. */

courses(4) := 'Engl 2005';

Whenever it includes deleted elements, then the internal size of a nested table differs from the values returned by the COUNT and LAST. For illustration, when you initialize a nested table with five elements, delete the elements 2 & 5, then the internal size is 5, and the COUNT returns 3, &  the LAST returns 4. All the deleted elements are treated similarly.

Posted Date: 10/4/2012 3:17:44 AM | Location : United States







Related Discussions:- Using extend - collection method, Assignment Help, Ask Question on Using extend - collection method, Get Answer, Expert's Help, Using extend - collection method Discussions

Write discussion on Using extend - collection method
Your posts are moderated
Related Questions
Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Order of Evaluation When you do not use the parentheses to specify the order of evaluation, the operator precedence determine the order. Now compare the expressions below: NOT

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

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.

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by