 |
|
Oracle Tips by Burleson |
PL/SQL
Collection Methods
DELETE and TRIM
DELETE and TRIM are covered together because they
both remove elements from a collection. Using the DELETE methodwithout an argument removes all the elements in a collection. The
DELETE method with arguments removes only the specified elements.
auth.delete;
-- removes all elements.
auth.delete(4); -- removes the fourth element
auth.delete(4,8); removes all elements from 4 to 8
- inclusive.
When you delete an element, the element is removed
from the collection, however the location in the collection still
exists and you can place a new element into the collection at that
location. If the element you delete does not exist, the DELETE methodwill return back to the calling code, having done nothing. Its also
important to note that no PL/SQL exception is raised when you delete a
non-existing element.
The TRIM methodremoves
elements only from the end of a collection. Without an argument, the
TRIM method removes the last element.
auth.trim;
-- removes the last elements.
auth.trim(4); -- removes the last four element
If you try to trim more elements than exist, the
TRIM that executes after the first element is removed will raise a
SUBSCRIPT_BEYOND_COUNTexception.
Now the warning! Do not mix the TRIM and DELETE
methods on the same collection. Remember that the DELETE methodremoves the element and keeps the location so you can reuse it
again. The TRIM method
removes the element and the
location. If you deleted the last element using the DELETE method,
the element location will remain. If you subsequently use the TRIM
method to remove the next element, the TRIM method will actually
remove the empty place left by the DELETE method and the actual last
element will remain intact.
EXIST
If I am going to manually work through a
collection that contains deleted elements, I must be able to determine
if the collection element is valid, this is the function of the EXIST
method. The EXIST method returns a true or false, but never a
null. It returns a true if the element location contains a valid
element, otherwise it returns a false.
You can not determine the last element of a
collection with the EXIST methodbecause if you
test an element past the last element, the EXIST method will simply
return false. You can not determine if you have passed the last
element or just found empty/invalid elements. You must use the LAST
methodto determine the end of the collection.
If
auth.exist(23) then
The code fragment in the above example, checks for
the existence of an element at position 23 before performing some
operation.
EXTEND
Upon creation, the nested tableand
VARRAYcollections start with no elements. To add an element,
you must first make a location to place that element. This is the
function of the EXTEND method. The EXTEND
method without arguments will add one element to the collection. Used
with arguments, the EXTEND method adds the defined number of elements
and may initialize them
auth.extend;
-- creates space for one new element.
auth.extend(10); -- creates space for 10 new elements.
auth.extend(10,0); - creates space for 10 new elements.
-- Initialized the elements to 0.
The third line in the above example is used when
the collection is defined as not null. In that case the extend method
must create space and initialize the element.
The collection methods allow us to find move
through a collection, test for valid elements and then process the
data at that locations. At this point we are ready to introduce and
use the three PL/SQL collections, starting with the VARRAY.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
