Associative Arrays (Index-by Tables)
1. Simple Associative Array Example
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
DBMS_OUTPUT.PUT_LINE(emp_names(1));
END;
2. Associative Array with Loops
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
FOR i IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
3. Associative Array with Dynamic Keys
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
emp_names emp_table;
BEGIN
emp_names('E01') := 'John Doe';
emp_names('E02') := 'Jane Smith';
DBMS_OUTPUT.PUT_LINE(emp_names('E01'));
END;
4. Associative Array in a Procedure
CREATE OR REPLACE PROCEDURE display_employees IS
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
FOR i IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
5. Associative Array with Error Handling
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_names(2));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Key not found');
END;
END;
Nested Tables
6. Simple Nested Table Example
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(2);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
DBMS_OUTPUT.PUT_LINE(emp_names(1));
END;
7. Nested Table with Dynamic Size
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(3);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names(3) := 'Mike Johnson';
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
8. Nested Table as Function Return Type
CREATE OR REPLACE FUNCTION get_employees RETURN SYS.ODCIVARCHAR2LIST IS
emp_names SYS.ODCIVARCHAR2LIST;
BEGIN
emp_names := SYS.ODCIVARCHAR2LIST();
emp_names.EXTEND(2);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
RETURN emp_names;
END;
9. Nested Table in a Procedure with Parameter
CREATE OR REPLACE PROCEDURE display_employees(emp_names IN SYS.ODCIVARCHAR2LIST) IS
BEGIN
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
10. Nested Table with DML Operations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(2);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_names(i));
END LOOP;
emp_names.DELETE(1);
DBMS_OUTPUT.PUT_LINE('After deletion:');
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_names(i));
END LOOP;
END;
Varrays
11. Simple Varray Example
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_names(1));
END;
12. Varray with Dynamic Size
DECLARE
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith', 'Mike Johnson');
BEGIN
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
13. Varray as Function Return Type
CREATE OR REPLACE FUNCTION get_employees RETURN emp_array IS
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
RETURN emp_names;
END;
14. Varray in a Procedure with Parameter
CREATE OR REPLACE PROCEDURE display_employees(emp_names IN emp_array) IS
BEGIN
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
15. Varray with Exception Handling
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_names(3));
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Index out of range');
END;
END;
Associative Arrays with Tables
16. Associative Array with Table Data
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
17. Associative Array for Aggregation
DECLARE
TYPE dept_count_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
dept_counts dept_count_table;
BEGIN
FOR rec IN (SELECT department_name, COUNT(*) AS emp_count FROM employees GROUP BY department_name) LOOP
dept_counts(rec.department_name) := rec.emp_count;
END LOOP;
FOR dept IN dept_counts.FIRST..dept_counts.LAST LOOP
DBMS_OUTPUT.PUT_LINE(dept || ': ' || dept_counts(dept));
END LOOP;
END;
18. Associative Array with Cursors
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO emp_data;
CLOSE emp_cursor;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
19. Associative Array in a Package
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
PROCEDURE init_employees;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN VARCHAR2;
END emp_pkg;
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
PROCEDURE init_employees IS
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
END;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN emp_names(p_index);
END;
END emp_pkg;
20. Associative Array with Exception Handling in a Package
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_table IS TABLE OF VARCHAR2(100
) INDEX BY PLS_INTEGER;
emp_names emp_table;
PROCEDURE init_employees;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN VARCHAR2;
END emp_pkg;
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
PROCEDURE init_employees IS
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
END;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN emp_names(p_index);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Employee not found';
END;
END emp_pkg;
Nested Tables with Procedures
21. Nested Table in a Procedure
CREATE OR REPLACE PROCEDURE process_employees IS
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(2);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
22. Nested Table with Dynamic Operations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(5);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names(3) := 'Mike Johnson';
emp_names.EXTEND;
emp_names(4) := 'Emily Davis';
emp_names(5) := 'William Brown';
emp_names.DELETE(2);
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
23. Nested Table with SQL Operations
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
24. Nested Table with Bulk Collect
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE hire_date > SYSDATE - 365;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
25. Nested Table with PL/SQL Table Functions
CREATE OR REPLACE FUNCTION get_employees(p_dept_id IN NUMBER) RETURN SYS.ODCIVARCHAR2LIST IS
emp_names SYS.ODCIVARCHAR2LIST;
BEGIN
SELECT employee_name BULK COLLECT INTO emp_names FROM employees WHERE department_id = p_dept_id;
RETURN emp_names;
END;
Varrays with Procedures
26. Varray in a Procedure
CREATE OR REPLACE PROCEDURE display_employees IS
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
27. Varray with Exception Handling
CREATE OR REPLACE PROCEDURE display_employees IS
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_names(3));
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Index out of range');
END;
END;
28. Varray as Function Parameter
CREATE OR REPLACE PROCEDURE process_employees(emp_names IN emp_array) IS
BEGIN
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
29. Varray with SQL Operations
DECLARE
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array;
BEGIN
SELECT employee_name BULK COLLECT INTO emp_names FROM employees WHERE hire_date > SYSDATE - 365;
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
30. Varray with DML Operations
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith', 'Mike Johnson');
BEGIN
emp_names.EXTEND(2);
emp_names(4) := 'Emily Davis';
emp_names(5) := 'William Brown';
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
Combining Collections
31. Associative Array with Nested Table
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
TYPE emp_table IS TABLE OF emp_array INDEX BY PLS_INTEGER;
emp_data emp_table;
BEGIN
emp_data(1) := emp_array('John Doe', 'Jane Smith');
emp_data(2) := emp_array('Mike Johnson', 'Emily Davis');
FOR i IN emp_data.FIRST..emp_data.LAST LOOP
FOR j IN 1..emp_data(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i)(j));
END LOOP;
END LOOP;
END;
32. Nested Table with Varray
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
TYPE emp_table IS TABLE OF emp_array;
emp_data emp_table;
BEGIN
emp_data := emp_table();
emp_data.EXTEND(2);
emp_data(1) := emp_array('John Doe', 'Jane Smith');
emp_data(2) := emp_array('Mike Johnson', 'Emily Davis');
FOR i IN 1..emp_data.COUNT LOOP
FOR j IN 1..emp_data(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i)(j));
END LOOP;
END LOOP;
END;
33. Associative Array with Nested Table Data
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
TYPE dept_table IS TABLE OF emp_table INDEX BY VARCHAR2(50);
dept_data dept_table;
BEGIN
SELECT * BULK COLLECT INTO dept_data('HR') FROM employees WHERE department_id = 10;
FOR i IN dept_data('HR').FIRST..dept_data('HR').LAST LOOP
DBMS_OUTPUT.PUT_LINE(dept_data('HR')(i).employee_name);
END LOOP;
END;
34. Nested Table with Associative Array as Column
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
TYPE emp_table IS TABLE OF emp_array;
emp_data emp_table;
BEGIN
emp_data := emp_table();
emp_data.EXTEND(2);
emp_data(1) := emp_array('John Doe', 'Jane Smith');
emp_data(2) := emp_array('Mike Johnson', 'Emily Davis');
FOR i IN 1..emp_data.COUNT LOOP
FOR j IN 1..emp_data(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i)(j));
END LOOP;
END LOOP;
END;
35. Nested Table with Associative Array in a Package
CREATE OR REPLACE PACKAGE emp_pkg IS
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
TYPE emp_table IS TABLE OF emp_array INDEX BY PLS_INTEGER;
emp_data emp_table;
PROCEDURE init_employees;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN emp_array;
END emp_pkg;
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
PROCEDURE init_employees IS
BEGIN
emp_data(1) := emp_array('John Doe', 'Jane Smith');
emp_data(2) := emp_array('Mike Johnson', 'Emily Davis');
END;
FUNCTION get_employee(p_index IN PLS_INTEGER) RETURN emp_array IS
BEGIN
RETURN emp_data(p_index);
END;
END emp_pkg;
Advanced Collection Operations
36. Associative Array with Aggregated Data
DECLARE
TYPE dept_count_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
dept_counts dept_count_table;
BEGIN
FOR rec IN (SELECT department_name, COUNT(*) AS emp_count FROM employees GROUP BY department_name)
LOOP
dept_counts(rec.department_name) := rec.emp_count;
END LOOP;
FOR dept IN dept_counts.FIRST..dept_counts.LAST LOOP
DBMS_OUTPUT.PUT_LINE(dept || ': ' || dept_counts(dept));
END LOOP;
END;
37. Nested Table with SQL Operations and Bulk Collect
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
38. Associative Array with PL/SQL Table Functions
CREATE OR REPLACE FUNCTION get_dept_counts RETURN SYS.ODCINUMBERLIST IS
dept_counts SYS.ODCINUMBERLIST;
BEGIN
FOR rec IN (SELECT COUNT(*) AS emp_count FROM employees GROUP BY department_id) LOOP
dept_counts.EXTEND;
dept_counts(dept_counts.COUNT) := rec.emp_count;
END LOOP;
RETURN dept_counts;
END;
39. Nested Table with Bulk Operations
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;
FORALL i IN emp_data.FIRST..emp_data.LAST
INSERT INTO employee_backup VALUES emp_data(i);
END;
40. Varray with Bulk Collect and DML Operations
DECLARE
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array;
BEGIN
SELECT employee_name BULK COLLECT INTO emp_names FROM employees WHERE hire_date > SYSDATE - 365;
FOR i IN 1..emp_names.COUNT LOOP
INSERT INTO employee_archive VALUES (emp_names(i));
END LOOP;
END;
Using Collections with Cursors
41. Associative Array with Cursor Data
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id = 10;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO emp_data;
CLOSE emp_cursor;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
42. Nested Table with Cursor
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id = 20;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO emp_data;
CLOSE emp_cursor;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
43. Varray with Cursor Data
DECLARE
TYPE emp_array IS VARRAY(10) OF VARCHAR2(100);
emp_names emp_array;
CURSOR emp_cursor IS SELECT employee_name FROM employees WHERE department_id = 30;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO emp_names;
CLOSE emp_cursor;
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
44. Associative Array with Cursor and Dynamic SQL
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id = 40;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO emp_data;
CLOSE emp_cursor;
FOR i IN 1..emp_data.COUNT LOOP
EXECUTE IMMEDIATE 'INSERT INTO employee_archive VALUES (:1, :2)' USING emp_data(i).employee_id, emp_data(i).employee_name;
END LOOP;
END;
45. Nested Table with Dynamic SQL
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE department_id = 50';
EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO emp_data;
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
Working with Collections and SQL
46. Associative Array with SQL Query Results
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 60;
FOR i IN emp_data.FIRST..emp_data.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_data(i).employee_name);
END LOOP;
END;
47. Nested Table with Aggregation
DECLARE
TYPE emp_table IS TABLE OF NUMBER;
dept_counts emp_table;
BEGIN
SELECT COUNT(*) BULK COLLECT INTO dept_counts FROM employees GROUP BY department_id;
FOR i IN dept_counts.FIRST..dept_counts.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee count: ' || dept_counts(i));
END LOOP;
END;
48. Varray with SQL Aggregation
DECLARE
TYPE emp_array IS VARRAY(10) OF NUMBER;
emp_counts emp_array;
BEGIN
SELECT COUNT(*) BULK COLLECT INTO emp_counts FROM employees WHERE hire_date > SYSDATE - 365;
FOR i IN 1..emp_counts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee count: ' || emp_counts(i));
END LOOP;
END;
49. Associative Array with SQL Updates
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 70;
FOR i IN emp_data.FIRST..emp_data.LAST LOOP
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_data(i).employee_id;
END LOOP;
END;
50. Nested Table with SQL Inserts
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emp_data emp_table;
BEGIN
SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 80;
FOR i IN 1..emp_data.COUNT LOOP
INSERT INTO employee_archive VALUES emp_data(i);
END LOOP;
END;
Collection Manipulations
51. Associative Array with Manipulations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names.EXTEND(1);
emp_names(3) := 'Mike Johnson';
emp_names.DELETE(2);
FOR i IN emp_names.FIRST..emp_names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
52. Nested Table with Manipulations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(3);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names(3) := 'Mike Johnson';
emp_names.DELETE(1);
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
53. Varray with Manipulations
DECLARE
TYPE emp_array IS VARRAY(5) OF VARCHAR2(100);
emp_names emp_array := emp_array('John Doe', 'Jane Smith');
BEGIN
emp_names.EXTEND(2);
emp_names(3) := 'Mike Johnson';
emp_names(4) := 'Emily Davis';
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
54. Nested Table with Complex Operations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100);
emp_names emp_table;
BEGIN
emp_names := emp_table();
emp_names.EXTEND(5);
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names(3) := 'Mike Johnson';
emp_names(4) := 'Emily Davis';
emp_names
(5) := 'William Brown';
emp_names.DELETE(2);
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
55. Associative Array with Dynamic Operations
DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_table;
BEGIN
emp_names(1) := 'John Doe';
emp_names(2) := 'Jane Smith';
emp_names.EXTEND(3);
emp_names(3) := 'Mike Johnson';
emp_names(4) := 'Emily Davis';
emp_names.DELETE(2);
FOR i IN emp_names.FIRST..emp_names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;
No comments:
Post a Comment