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
Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

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

Overview of control structures According to the structure theorem, any computer program can be written by using the basic control structures as shown in figure below. They can b

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

which operation is used if we are interested in only certain columns of a table?

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci