Oracle PL/SQL - DELETE Method with Associative Array Indexed by String

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. DELETE Collection Method

Introduction

The following code populates an associative array indexed by string and deletes all elements.

Demo

SQL>
SQL> DECLARE-- w  ww .j  ava  2  s  .  c o m
  2    TYPE MyMap IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  3    MyMapVariable  MyMap;
  4
  5    PROCEDURE print_MyMapVariable IS
  6      i  VARCHAR2(10);
  7    BEGIN
  8      i := MyMapVariable.FIRST;
  9
 10      IF i IS NULL THEN
 11        DBMS_OUTPUT.PUT_LINE('MyMapVariable is empty');
 12      ELSE
 13        WHILE i IS NOT NULL LOOP
 14          DBMS_OUTPUT.PUT('MyMapVariable.(' || i || ') = '); print(MyMapVariable(i));
 15          i := MyMapVariable.NEXT(i);
 16        END LOOP;
 17      END IF;
 18
 19      DBMS_OUTPUT.PUT_LINE('---');
 20    END print_MyMapVariable;
 21
 22  BEGIN
 23    MyMapVariable('A') := 13;
 24    MyMapVariable('B') := 26;
 25    MyMapVariable('C') := 3;
 26    print_MyMapVariable;
 27
 28    MyMapVariable.DELETE; 
 29    print_MyMapVariable;
 30
 31    MyMapVariable('A') := 13;   
 32    MyMapVariable('B') := 260;  
 33    MyMapVariable('C') := 30;   
 34    MyMapVariable('D') := 21;   
 35    MyMapVariable('E') := 11;   
 36    MyMapVariable('F') := 11;   
 37    MyMapVariable('G') := 11;   
 38    MyMapVariable('H') := 21;   
 39    MyMapVariable('I') := 11;   
 40    print_MyMapVariable;
 41
 42    MyMapVariable.DELETE('C');      -- Delete one element
 43    print_MyMapVariable;
 44
 45    MyMapVariable.DELETE('N','W');  -- Delete range of elements
 46    print_MyMapVariable;
 47
 48    MyMapVariable.DELETE('Z','M');  -- Does nothing
 49    print_MyMapVariable;
 50  END;
 51  /
MyMapVariable.(A) = 13
MyMapVariable.(B) = 26
MyMapVariable.(C) = 3
---
MyMapVariable is empty
---
MyMapVariable.(A) = 13
MyMapVariable.(B) = 260
MyMapVariable.(C) = 30
MyMapVariable.(D) = 21
MyMapVariable.(E) = 11
MyMapVariable.(F) = 11
MyMapVariable.(G) = 11
MyMapVariable.(H) = 21
MyMapVariable.(I) = 11
---
MyMapVariable.(A) = 13
MyMapVariable.(B) = 260
MyMapVariable.(D) = 21
MyMapVariable.(E) = 11
MyMapVariable.(F) = 11
MyMapVariable.(G) = 11
MyMapVariable.(H) = 21
MyMapVariable.(I) = 11
---
MyMapVariable.(A) = 13
MyMapVariable.(B) = 260
MyMapVariable.(D) = 21
MyMapVariable.(E) = 11
MyMapVariable.(F) = 11
MyMapVariable.(G) = 11
MyMapVariable.(H) = 21
MyMapVariable.(I) = 11
---
MyMapVariable.(A) = 13
MyMapVariable.(B) = 260
MyMapVariable.(D) = 21
MyMapVariable.(E) = 11
MyMapVariable.(F) = 11
MyMapVariable.(G) = 11
MyMapVariable.(H) = 21
MyMapVariable.(I) = 11
---

PL/SQL procedure successfully completed.

SQL>

Related Topic