Using delete - collection method, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Using delete - collection method

Short-circuit evaluation-pl/sql expressions , Short-Circuit Evaluation ...

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

I want customer management program, This is a Customer Management project. ...

This is a Customer Management project. Customer data is presented in a text file. The program will load this text data into its DB columns. The data mapping is user definable. User

I need a ppd-cpa or filesharing site, PPD , CPA, Filesharing Site Projec...

PPD , CPA, Filesharing Site Project Description: This is very easy I need a PPD , CPA, Filesharing Site. [PPD] stands for (pay per download) example hotsharecash [Files

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF...

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Using commit, Using COMMIT The COMMIT statements end the present trans...

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Other monadic - sql, Other monadic - SQL In 2VL there are just 4 (2 2 ...

Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

Write sql code that will create the writes table, Question: Consider th...

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Truth tables , Truth Tables: However in propositional logic - here we ...

Truth Tables: However in propositional logic - here we are restricted to expressing sentences and where the propositions are true or false - so we can check where a particular

Comparison operators- pl/sql, Comparison Operators The Comparison operat...

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Keyword & parameter description - exceptions, Keyword & Parameter Descripti...

Keyword & Parameter Description: WHEN: This keyword introduces the exception handler. You can have many exceptions execute the similar sequence of the statements by follo

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