Using delete - collection method, PL-SQL Programming

Using DELETE

This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then DELETE(n) does nothing. DELETE(m,n) removes all the elements in the range m to n from an index-by table or a nested table. If the m is bigger than n or if m or n is null, then DELETE(m,n) does nothing. Some of the examples are shown below:

BEGIN

...

courses.DELETE(2); -- deletes element 2

courses.DELETE(7,7); -- deletes element 7

courses.DELETE(6,3); -- does nothing

courses.DELETE(3,6); -- deletes elements 3 through 6

projects.DELETE; -- deletes all elements

END;

Varrays are dense; therefore you cannot delete their individual elements. When an element to be deleted does not exist, then DELETE simply skips it; no exception is raised. The PL/SQL keeps placeholders for the deleted elements. Therefore, you can replace a deleted element simply by assigning it a new value.

The DELETE allows you to sustain a sparse nested table. In the example below, you retrieve nested table prospects into a temporary table, prune it, and then store it back in the database:

DECLARE

my_prospects ProspectList;

revenue NUMBER;

BEGIN

SELECT prospects INTO my_prospects FROM customers WHERE ...

FOR i IN my_prospects.FIRST..my_prospects.LAST LOOP

estimate_revenue(my_prospects(i), revenue); -- call procedure

IF revenue < 25000 THEN

my_prospects.DELETE(i);

END IF;

END LOOP;

UPDATE customers SET prospects = my_prospects WHERE...

The amount of memory allocated to the nested table can increase or decrease dynamically. As you delete the elements, then the memory is freed page by page. If you delete the whole table, then all the memory is freed.

Posted Date: 10/4/2012 3:20:01 AM | Location : United States







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

Write discussion on Using delete - collection method
Your posts are moderated
Related Questions
MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

What are 3 good practices of modeling and/or implementing data warehouses?

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

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

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding