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
Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

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.

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

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 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 e

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit