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

Parameter & keyword description-execute immediate statement, Parameter and ...

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Sql queries-oracle , 1- You can check attribute names from each table in D...

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Enforce security in the database system, Question: (a) In the context o...

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Advantages of pl/sql, Advantages of PL/SQL The PL/SQL is a high-perform...

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

%type - cursors, %TYPE: This attribute gives the datatype of a formerly...

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

Oracle PL SQL, I need to write one function and one procedure to query a Or...

I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

Advantages of subprograms, Advantages of Subprograms The Subprograms g...

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Assignment source not a literal - variable, Assignment Source Not a Literal...

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

Object type in pl/sql, Object Type: The object type is a user-define...

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

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