1. Basic declaration and simple insertion
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := 'Apple';
arr(2) := 'Banana';
arr(3) := 'Cherry';
DBMS_OUTPUT.PUT_LINE(arr(1));
DBMS_OUTPUT.PUT_LINE(arr(2));
DBMS_OUTPUT.PUT_LINE(arr(3));
END;
Output:
Apple
Banana
Cherry
2. Associative array indexed by VARCHAR2
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
arr arr_type;
BEGIN
arr('one') := 100;
arr('two') := 200;
arr('three') := 300;
DBMS_OUTPUT.PUT_LINE(arr('two')); -- 200
END;
Output:
200
3. Loop through associative array using FIRST and NEXT
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
idx BINARY_INTEGER;
BEGIN
arr(10) := 'Ten';
arr(20) := 'Twenty';
arr(30) := 'Thirty';
idx := arr.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Index: ' || idx || ', Value: ' || arr(idx));
idx := arr.NEXT(idx);
END LOOP;
END;
Output:
Index: 10, Value: Ten
Index: 20, Value: Twenty
Index: 30, Value: Thirty
4. Delete a key from associative array
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := 'A';
arr(2) := 'B';
arr(3) := 'C';
arr.DELETE(2);
IF arr.EXISTS(2) THEN
DBMS_OUTPUT.PUT_LINE('Key 2 exists');
ELSE
DBMS_OUTPUT.PUT_LINE('Key 2 deleted');
END IF;
END;
Output:
Key 2 deleted
5. Delete entire associative array
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := 10;
arr(2) := 20;
arr.DELETE;
IF arr.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Array is empty');
END IF;
END;
Output:
Array is empty
6. Check if key exists
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
arr arr_type;
BEGIN
arr('x') := 'ex';
IF arr.EXISTS('x') THEN
DBMS_OUTPUT.PUT_LINE('x exists');
ELSE
DBMS_OUTPUT.PUT_LINE('x does not exist');
END IF;
IF arr.EXISTS('y') THEN
DBMS_OUTPUT.PUT_LINE('y exists');
ELSE
DBMS_OUTPUT.PUT_LINE('y does not exist');
END IF;
END;
Output:
x exists
y does not exist
7. Use associative array to sum values
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
arr arr_type;
total NUMBER := 0;
idx BINARY_INTEGER;
BEGIN
arr(1) := 100;
arr(2) := 200;
arr(3) := 300;
idx := arr.FIRST;
WHILE idx IS NOT NULL LOOP
total := total + arr(idx);
idx := arr.NEXT(idx);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total sum: ' || total);
END;
Output:
Total sum: 600
8. Associative array with record type
DECLARE
TYPE rec_type IS RECORD (id NUMBER, name VARCHAR2(20));
TYPE arr_type IS TABLE OF rec_type INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1).id := 101;
arr(1).name := 'John';
arr(2).id := 102;
arr(2).name := 'Jane';
DBMS_OUTPUT.PUT_LINE('ID: ' || arr(2).id || ', Name: ' || arr(2).name);
END;
Output:
ID: 102, Name: Jane
9. Find max key in associative array
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
arr arr_type;
max_key BINARY_INTEGER;
BEGIN
arr(5) := 50;
arr(2) := 20;
arr(8) := 80;
max_key := arr.LAST;
DBMS_OUTPUT.PUT_LINE('Max key is ' || max_key);
END;
Output:
Max key is 8
10. Using associative array inside a function to return data
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
FUNCTION get_fruits RETURN arr_type IS
fruits arr_type;
BEGIN
fruits(1) := 'Mango';
fruits(2) := 'Orange';
RETURN fruits;
END;
fruits arr_type;
BEGIN
fruits := get_fruits;
DBMS_OUTPUT.PUT_LINE(fruits(2));
END;
Output:
Orange
11. Bulk collect into associative array
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
SELECT department_name BULK COLLECT INTO arr FROM departments WHERE ROWNUM <= 3;
FOR i IN arr.FIRST .. arr.LAST LOOP
DBMS_OUTPUT.PUT_LINE(arr(i));
END LOOP;
END;
Output (example):
Administration
Marketing
Purchasing
12. Associative array as parameter to procedure
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE print_arr(arr IN arr_type) IS
idx BINARY_INTEGER;
BEGIN
idx := arr.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(arr(idx));
idx := arr.NEXT(idx);
END LOOP;
END;
my_arr arr_type;
BEGIN
my_arr(1) := 'One';
my_arr(2) := 'Two';
print_arr(my_arr);
END;
Output:
One
Two
13. Associative array keys with gaps
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
idx BINARY_INTEGER;
BEGIN
arr(10) := 'Ten';
arr(30) := 'Thirty';
idx := arr.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Key: ' || idx || ' Value: ' || arr(idx));
idx := arr.NEXT(idx);
END LOOP;
END;
Output:
Key: 10 Value: Ten
Key: 30 Value: Thirty
14. Update value in associative array
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := 'Old';
DBMS_OUTPUT.PUT_LINE('Before update: ' || arr(1));
arr(1) := 'New';
DBMS_OUTPUT.PUT_LINE('After update: ' || arr(1));
END;
Output:
Before update: Old
After update: New
15. Associative array of Boolean values
DECLARE
TYPE arr_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := TRUE;
arr(2) := FALSE;
IF arr(1) THEN
DBMS_OUTPUT.PUT_LINE('Index 1 is TRUE');
END IF;
IF NOT arr(2) THEN
DBMS_OUTPUT.PUT_LINE('Index 2 is FALSE');
END IF;
END;
Output:
Index 1 is TRUE
Index 2 is FALSE
16. Associative array with negative integer keys
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(-1) := 'Minus One';
arr(-5) := 'Minus Five';
DBMS_OUTPUT.PUT_LINE(arr(-1));
DBMS_OUTPUT.PUT_LINE(arr(-5));
END;
Output:
Minus One
Minus Five
17. Associative array with string keys (case sensitive)
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);
arr arr_type;
BEGIN
arr('Key') := 'Value1';
arr('key') := 'Value2';
DBMS_OUTPUT.PUT_LINE(arr('Key'));
DBMS_OUTPUT.PUT_LINE(arr('key'));
END;
Output:
Value1
Value2
18. Associative array to reverse a string
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
arr arr_type;
str VARCHAR2(20) := 'HELLO';
result VARCHAR2(20) := '';
i BINARY_INTEGER;
BEGIN
FOR i IN 1 .. LENGTH(str) LOOP
arr(i) := SUBSTR(str, i, 1);
END LOOP;
FOR i IN REVERSE 1 .. LENGTH(str) LOOP
result := result || arr(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reversed: ' || result);
END;
Output:
Reversed: OLLEH
19. Associative array using user-defined type as key — Not supported
Oracle associative arrays only support BINARY_INTEGER and VARCHAR2 as key types. You cannot use other types as keys.
20. Using associative array for frequency count of characters
DECLARE
TYPE freq_arr IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1);
freq freq_arr;
str VARCHAR2(100) := 'MISSISSIPPI';
i PLS_INTEGER;
ch VARCHAR2(1);
BEGIN
FOR i IN 1 .. LENGTH(str) LOOP
ch := SUBSTR(str, i, 1);
IF freq.EXISTS(ch) THEN
freq(ch) := freq(ch) + 1;
ELSE
freq(ch) := 1;
END IF;
END LOOP;
FOR ch IN freq.FIRST .. freq.LAST LOOP -- Won't work for VARCHAR2 keys
NULL; -- Skip this, iterate differently below
END LOOP;
-- Iterating with string keys:
ch := freq.FIRST;
WHILE ch IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(ch || ': ' || freq(ch));
ch := freq.NEXT(ch);
END LOOP;
END;
Output:
M: 1
I: 4
S: 4
P: 2
21. Associative array in a package
CREATE OR REPLACE PACKAGE my_pkg IS
TYPE arr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
arr arr_type;
PROCEDURE init_arr;
PROCEDURE print_arr;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
PROCEDURE init_arr IS
BEGIN
arr(1) := 10;
arr(2) := 20;
END;
PROCEDURE print_arr IS
idx BINARY_INTEGER;
BEGIN
idx := arr.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(arr(idx));
idx := arr.NEXT(idx);
END LOOP;
END;
END;
/
BEGIN
my_pkg.init_arr;
my_pkg.print_arr;
END;
Output:
10
20
22. Nested associative arrays (Associative array of associative arrays)
DECLARE
TYPE inner_arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE outer_arr_type IS TABLE OF inner_arr_type INDEX BY VARCHAR2(10);
outer_arr outer_arr_type;
BEGIN
outer_arr('fruits')(1) := 'Apple';
outer_arr('fruits')(2) := 'Banana';
outer_arr('vegetables')(1) := 'Carrot';
outer_arr('vegetables')(2) := 'Peas';
DBMS_OUTPUT.PUT_LINE(outer_arr('fruits')(2)); -- Banana
DBMS_OUTPUT.PUT_LINE(outer_arr('vegetables')(1)); -- Carrot
END;
Output:
Banana
Carrot
23. Use associative array to store employee names by employee_id
DECLARE
TYPE emp_arr_type IS TABLE OF VARCHAR2(50) INDEX BY NUMBER;
employees emp_arr_type;
BEGIN
employees(1001) := 'Alice';
employees(1002) := 'Bob';
DBMS_OUTPUT.PUT_LINE(employees(1001));
DBMS_OUTPUT.PUT_LINE(employees(1002));
END;
Output:
Alice
Bob
24. Associative array with NULL keys (Not allowed)
Attempting to use NULL as a key raises an exception:
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
arr arr_type;
BEGIN
arr(NULL) := 1; -- Raises error
END;
Output:
ORA-06502: PL/SQL: numeric or value error: NULL index value
25. Associative array with large sparse keys
DECLARE
TYPE arr_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1000000) := 'Million';
DBMS_OUTPUT.PUT_LINE(arr(1000000));
END;
Output:
Million
26. Associative array COUNT method
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
arr arr_type;
BEGIN
arr(1) := 10;
arr(2) := 20;
arr(5) := 50;
DBMS_OUTPUT.PUT_LINE('Count = ' || arr.COUNT);
END;
Output:
Count = 3
27. Using associative array as temporary cache
DECLARE
TYPE cache_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(20);
cache cache_type;
FUNCTION get_data(key VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF cache.EXISTS(key) THEN
RETURN cache(key);
ELSE
cache(key) := 'Data for ' || key; -- Simulate fetching
RETURN cache(key);
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(get_data('A'));
DBMS_OUTPUT.PUT_LINE(get_data('B'));
DBMS_OUTPUT.PUT_LINE(get_data('A')); -- Cached result
END;
Output:
Data for A
Data for B
Data for A
28. Associative array with composite keys (string concatenation)
DECLARE
TYPE arr_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
arr arr_type;
key VARCHAR2(100);
BEGIN
key := 'Dept1' || '-' || 'Emp1';
arr(key) := 12345;
DBMS_OUTPUT.PUT_LINE(arr('Dept1-Emp1'));
END;
Output:
12345
29. Associative array using %ROWTYPE (for record collection)
DECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
emps emp_tab;
BEGIN
SELECT * INTO emps(1) FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(emps(1).first_name || ' ' || emps(1).last_name);
END;
Assuming an employees table exists and employee 100 exists.
No comments:
Post a Comment