1. Create an Associative Array Type
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
END;
2. Populate Associative Array with Employee Names
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
emp_list(3) := 'Charlie';
END;
3. Loop Through an Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_list(i));
END LOOP;
END;
4. Access an Element of Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE('First Employee: ' || emp_list(1));
END;
5. Count Elements in Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
emp_count NUMBER;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
emp_count := emp_list.COUNT;
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || emp_count);
END;
6. Check if an Element Exists in Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
IF emp_list.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('Employee exists: ' || emp_list(1));
ELSE
DBMS_OUTPUT.PUT_LINE('Employee does not exist');
END IF;
END;
7. Delete an Element from an Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
emp_list.DELETE(1); -- Delete Alice
DBMS_OUTPUT.PUT_LINE('After deletion:');
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_list(i));
END LOOP;
END;
8. Initialize Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
emp_list.DELETE; -- Clears all elements
DBMS_OUTPUT.PUT_LINE('Array initialized.');
END;
9. Use Associative Array with PL/SQL Records
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(50)
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1).emp_id := 1;
emp_list(1).emp_name := 'Alice';
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_list(1).emp_id || ', Name: ' || emp_list(1).emp_name);
END;
10. Create an Associative Array of Numbers
DECLARE
TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
num_list num_array;
BEGIN
num_list(1) := 10;
num_list(2) := 20;
END;
11. Sum Values in an Associative Array
DECLARE
TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
num_list num_array;
total_sum NUMBER := 0;
BEGIN
num_list(1) := 10;
num_list(2) := 20;
FOR i IN num_list.FIRST .. num_list.LAST LOOP
total_sum := total_sum + num_list(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Sum: ' || total_sum);
END;
12. Use Associative Array to Store Product Prices
DECLARE
TYPE price_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
price_list price_array;
BEGIN
price_list('Laptop') := 1000;
price_list('Smartphone') := 500;
DBMS_OUTPUT.PUT_LINE('Price of Laptop: ' || price_list('Laptop'));
END;
13. Loop Through Associative Array with String Index
DECLARE
TYPE price_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
price_list price_array;
BEGIN
price_list('Laptop') := 1000;
price_list('Smartphone') := 500;
FOR i IN price_list.FIRST .. price_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Product: ' || i || ', Price: ' || price_list(i));
END LOOP;
END;
14. Initialize Associative Array with Data
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_list(i));
END LOOP;
END;
15. Count Employees in Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || emp_list.COUNT);
END;
16. Associative Array with Nested Records
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(50)
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1).emp_id := 1;
emp_list(1).emp_name := 'Alice';
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_list(1).emp_id || ', Name: ' || emp_list(1).emp_name);
END;
17. Nested Loop Through Associative Array
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(50)
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1).emp_id := 1;
emp_list(1).emp_name := 'Alice';
emp_list(2).emp_id := 2;
emp_list(2).emp_name := 'Bob';
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_list(i).emp_id || ', Name: ' || emp_list(i).emp_name);
END LOOP;
END;
18. Associative Array with VARCHAR2 Key
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
emp_list('E002') := 'Bob';
DBMS_OUTPUT.PUT_LINE('Employee E001: ' || emp_list('E001'));
END;
19. Check for Existence of Key in Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
IF emp_list.EXISTS('E001') THEN
DBMS_OUTPUT.PUT_LINE('Employee exists: ' || emp_list('E001'));
ELSE
DBMS_OUTPUT.PUT_LINE('Employee does not exist');
END IF;
END;
20. Delete Element by Key
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
emp_list('E002') := 'Bob';
emp_list.DELETE('E001'); -- Delete Alice
DBMS_OUTPUT.PUT_LINE('After deletion:');
FOR key IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('
Employee: ' || emp_list(key));
END LOOP;
END;
21. Retrieve Keys from Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
emp_list('E002') := 'Bob';
DBMS_OUTPUT.PUT_LINE('Employee Keys:');
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Key: ' || i);
END LOOP;
END;
22. Sort Associative Array by Key
-- Oracle PL/SQL does not support sorting associative arrays directly
-- You may need to use a collection type or a temporary table for sorted results.
23. Use Associative Array with PL/SQL Collections
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE('Employees: ' || emp_list(1) || ', ' || emp_list(2));
END;
24. Associative Array for Storing Grades
DECLARE
TYPE grade_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
grade_list grade_array;
BEGIN
grade_list('Alice') := 90;
grade_list('Bob') := 85;
DBMS_OUTPUT.PUT_LINE('Grade of Alice: ' || grade_list('Alice'));
END;
25. Get Average Grade from Associative Array
DECLARE
TYPE grade_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
grade_list grade_array;
total NUMBER := 0;
count NUMBER := 0;
BEGIN
grade_list('Alice') := 90;
grade_list('Bob') := 85;
FOR i IN grade_list.FIRST .. grade_list.LAST LOOP
total := total + grade_list(i);
count := count + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Average Grade: ' || (total / count));
END;
26. Use Associative Array to Store Employee IDs
DECLARE
TYPE emp_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 101;
emp_list(2) := 102;
DBMS_OUTPUT.PUT_LINE('Employee ID 1: ' || emp_list(1));
END;
27. Update an Employee Name
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
emp_list(2) := 'Robert'; -- Update Bob to Robert
DBMS_OUTPUT.PUT_LINE('Updated Employee: ' || emp_list(2));
END;
28. Use Associative Array with Dates
DECLARE
TYPE date_array IS TABLE OF DATE INDEX BY VARCHAR2(50);
date_list date_array;
BEGIN
date_list('Alice') := SYSDATE; -- Current date
DBMS_OUTPUT.PUT_LINE('Date for Alice: ' || date_list('Alice'));
END;
29. Get Maximum Value from Associative Array
DECLARE
TYPE num_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
num_list num_array;
max_val NUMBER := -1;
BEGIN
num_list('A') := 10;
num_list('B') := 20;
FOR i IN num_list.FIRST .. num_list.LAST LOOP
IF num_list(i) > max_val THEN
max_val := num_list(i);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Maximum Value: ' || max_val);
END;
30. Reverse Elements in Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
temp_list emp_array;
idx PLS_INTEGER := 1;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
-- Reverse the array
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
temp_list(idx) := emp_list(emp_list.LAST - (i - 1));
idx := idx + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reversed Employees:');
FOR i IN temp_list.FIRST .. temp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(temp_list(i));
END LOOP;
END;
31. Find Minimum Value from Associative Array
DECLARE
TYPE num_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
num_list num_array;
min_val NUMBER := 99999; -- Assume a large value
BEGIN
num_list('A') := 10;
num_list('B') := 5;
FOR i IN num_list.FIRST .. num_list.LAST LOOP
IF num_list(i) < min_val THEN
min_val := num_list(i);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Minimum Value: ' || min_val);
END;
32. Use Associative Array in Functions
CREATE OR REPLACE FUNCTION get_employee_name(emp_id IN NUMBER) RETURN VARCHAR2 IS
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
RETURN emp_list(emp_id);
END;
33. Use Associative Array in Procedures
CREATE OR REPLACE PROCEDURE display_employee(emp_id IN NUMBER) IS
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_list(emp_id));
END;
34. Return Associative Array from Function
CREATE OR REPLACE FUNCTION get_all_employees RETURN emp_array IS
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
RETURN emp_list;
END;
35. Use Associative Array for Dynamic SQL
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1) := 'Alice';
emp_list(2) := 'Bob';
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
EXECUTE IMMEDIATE 'INSERT INTO employees (name) VALUES (' || emp_list(i) || ')';
END LOOP;
END;
36. Associative Array with Multiple Data Types
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(50)
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1).emp_id := 1;
emp_list(1).emp_name := 'Alice';
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_list(1).emp_id || ', Name: ' || emp_list(1).emp_name);
END;
37. Load Data into Associative Array from a Table
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
v_emp_name VARCHAR2(50);
v_index PLS_INTEGER := 0;
BEGIN
FOR rec IN (SELECT name FROM employees) LOOP
v_index := v_index + 1;
emp_list(v_index) := rec.name;
END LOOP;
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_list(i));
END LOOP;
END;
38. Use Associative Array with OUT Parameter
CREATE OR REPLACE PROCEDURE get_employee_names(emp_names OUT emp_array) IS
BEGIN
emp_names(1) := 'Alice';
emp_names(2) := 'Bob';
END;
39. Fetch Employee Names from Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_names emp_array;
BEGIN
get_employee_names(emp_names);
FOR i IN emp_names.FIRST .. emp_names.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_names(i));
END LOOP;
END;
40. Use Associative Array with Cursor FOR LOOP
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
FOR rec IN (SELECT name FROM employees) LOOP
emp_list(emp_list.COUNT + 1) := rec.name;
END LOOP
;
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_list(i));
END LOOP;
END;
41. Use Associative Array for Fast Lookup
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
emp_list('E002') := 'Bob';
DBMS_OUTPUT.PUT_LINE('Employee E001: ' || emp_list('E001'));
END;
42. Dynamic Array Indexing
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
emp_list emp_array;
index PLS_INTEGER := 1;
BEGIN
emp_list(index) := 'Alice';
index := index + 1;
emp_list(index) := 'Bob';
DBMS_OUTPUT.PUT_LINE('Last Employee: ' || emp_list(index));
END;
43. Return Multiple Values from Associative Array
CREATE OR REPLACE FUNCTION get_employee_info(emp_id IN NUMBER) RETURN emp_rec IS
TYPE emp_rec IS RECORD (
emp_name VARCHAR2(50),
emp_salary NUMBER
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
BEGIN
emp_list(1).emp_name := 'Alice';
emp_list(1).emp_salary := 50000;
RETURN emp_list(emp_id);
END;
44. Calculate Total Salary from Associative Array
DECLARE
TYPE emp_rec IS RECORD (
emp_name VARCHAR2(50),
emp_salary NUMBER
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
total_salary NUMBER := 0;
BEGIN
emp_list(1).emp_name := 'Alice';
emp_list(1).emp_salary := 50000;
emp_list(2).emp_name := 'Bob';
emp_list(2).emp_salary := 60000;
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
total_salary := total_salary + emp_list(i).emp_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || total_salary);
END;
45. Count Unique Values in Associative Array
DECLARE
TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
num_list num_array;
unique_count NUMBER := 0;
BEGIN
num_list(1) := 10;
num_list(2) := 20;
unique_count := num_list.COUNT;
DBMS_OUTPUT.PUT_LINE('Unique Count: ' || unique_count);
END;
46. Use Associative Array for Caching Data
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_cache emp_array;
BEGIN
emp_cache('E001') := 'Alice';
IF emp_cache.EXISTS('E001') THEN
DBMS_OUTPUT.PUT_LINE('Cached Employee: ' || emp_cache('E001'));
END IF;
END;
47. Find Average Salary from Associative Array
DECLARE
TYPE emp_rec IS RECORD (
emp_name VARCHAR2(50),
emp_salary NUMBER
);
TYPE emp_array IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
emp_list emp_array;
total_salary NUMBER := 0;
count NUMBER := 0;
BEGIN
emp_list(1).emp_name := 'Alice';
emp_list(1).emp_salary := 50000;
emp_list(2).emp_name := 'Bob';
emp_list(2).emp_salary := 60000;
FOR i IN emp_list.FIRST .. emp_list.LAST LOOP
total_salary := total_salary + emp_list(i).emp_salary;
count := count + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Average Salary: ' || (total_salary / count));
END;
48. Find Employee by Name Using Associative Array
DECLARE
TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(50);
emp_list emp_array;
BEGIN
emp_list('E001') := 'Alice';
emp_list('E002') := 'Bob';
IF emp_list.EXISTS('E001') THEN
DBMS_OUTPUT.PUT_LINE('Found Employee: ' || emp_list('E001'));
END IF;
END;
49. Implementing Stacks Using Associative Arrays
DECLARE
TYPE stack_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
stack stack_array;
top PLS_INTEGER := 0;
BEGIN
-- Push
top := top + 1;
stack(top) := 10;
-- Pop
DBMS_OUTPUT.PUT_LINE('Popped Value: ' || stack(top));
stack.DELETE(top);
top := top - 1;
END;
50. Implementing Queues Using Associative Arrays
DECLARE
TYPE queue_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
queue queue_array;
front PLS_INTEGER := 1;
rear PLS_INTEGER := 0;
BEGIN
-- Enqueue
rear := rear + 1;
queue(rear) := 10;
-- Dequeue
DBMS_OUTPUT.PUT_LINE('Dequeued Value: ' || queue(front));
queue.DELETE(front);
front := front + 1;
END;
No comments:
Post a Comment