1. Basic VARRAY Declaration and Initialization
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
v_numbers varray_type := varray_type(1, 2, 3, 4, 5);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/
2. VARRAY Initialization with Default Values
DECLARE
TYPE varray_type IS VARRAY(3) OF VARCHAR2(10);
v_names varray_type := varray_type('John', 'Doe', 'Smith');
BEGIN
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
/
3. Appending Elements to a VARRAY
DECLARE
TYPE varray_type IS VARRAY(10) OF NUMBER;
v_numbers varray_type := varray_type(1, 2, 3);
BEGIN
v_numbers.EXTEND;
v_numbers(4) := 4;
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/
4. Removing an Element from a VARRAY
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
v_numbers varray_type := varray_type(1, 2, 3, 4, 5);
BEGIN
v_numbers.TRIM;
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_numbers(i));
END LOOP;
END;
/
5. Using Nested Tables
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3, 4, 5);
BEGIN
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
/
6. Appending Elements to a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3);
BEGIN
nt_numbers.EXTEND;
nt_numbers(4) := 4;
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
/
7. Deleting an Element from a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3, 4, 5);
BEGIN
nt_numbers.DELETE(3);
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
/
8. Trimming a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3, 4, 5);
BEGIN
nt_numbers.TRIM(2);
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
/
9. Declaring and Using an Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
aa_names associative_array_type;
BEGIN
aa_names(1) := 'John';
aa_names(2) := 'Doe';
aa_names(3) := 'Smith';
FOR i IN 1..aa_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_names(i));
END LOOP;
END;
/
10. Iterating Over an Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
aa_names associative_array_type;
i PLS_INTEGER;
BEGIN
aa_names(1) := 'John';
aa_names(2) := 'Doe';
aa_names(3) := 'Smith';
i := aa_names.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(aa_names(i));
i := aa_names.NEXT(i);
END LOOP;
END;
/
11. Deleting an Element from an Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
aa_names associative_array_type;
BEGIN
aa_names(1) := 'John';
aa_names(2) := 'Doe';
aa_names(3) := 'Smith';
aa_names.DELETE(2);
FOR i IN 1..aa_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_names(i));
END LOOP;
END;
/
12. Checking If an Element Exists in an Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
aa_names associative_array_type;
BEGIN
aa_names(1) := 'John';
aa_names(2) := 'Doe';
IF aa_names.EXISTS(3) THEN
DBMS_OUTPUT.PUT_LINE('Element exists');
ELSE
DBMS_OUTPUT.PUT_LINE('Element does not exist');
END IF;
END;
/
13. Using Nested Table as a Function Return Type
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
FUNCTION get_numbers RETURN nested_table_type IS
nt_numbers nested_table_type;
BEGIN
nt_numbers := nested_table_type(1, 2, 3, 4, 5);
RETURN nt_numbers;
END;
BEGIN
FOR i IN 1..get_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(get_numbers(i));
END LOOP;
END;
/
14. Using VARRAY as a Procedure Parameter
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(10);
PROCEDURE print_names(v_names varray_type) IS
BEGIN
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
BEGIN
print_names(varray_type('John', 'Doe', 'Smith'));
END;
/
15. Merging Nested Tables
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt1 nested_table_type := nested_table_type(1, 2, 3);
nt2 nested_table_type := nested_table_type(4, 5, 6);
merged_nt nested_table_type;
BEGIN
merged_nt := nt1 MULTISET UNION nt2;
FOR i IN 1..merged_nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(merged_nt(i));
END LOOP;
END;
/
16. Counting the Elements in a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3, 4, 5);
BEGIN
DBMS_OUTPUT.PUT_LINE('Number of elements: ' || nt_numbers.COUNT);
END;
/
17. Combining Nested Tables with `MULTISET UNION`
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt1 nested_table_type := nested_table_type(1, 2, 3);
nt2 nested_table_type := nested_table_type(4, 5, 6);
nt_combined nested_table_type;
BEGIN
nt_combined := nt1 MULTISET UNION nt2;
FOR i IN 1..nt_combined.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_combined(i));
END LOOP;
END;
/
18. Finding the Difference Between Nested Tables
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt1 nested_table_type := nested_table_type(1, 2, 3);
nt2 nested_table_type := nested_table_type(2, 3, 4);
nt_diff nested_table_type;
BEGIN
nt_diff := nt1 MULTISET EXCEPT nt2;
FOR i IN 1..nt_diff.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_diff(i));
END LOOP;
END;
/
19. Intersecting Nested Tables
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt1 nested_table_type := nested_table_type(1, 2, 3);
nt2 nested_table_type := nested_table_type(2, 3, 4);
nt_intersection nested_table_type;
BEGIN
nt_intersection := nt1 MULTISET INTERSECT nt2;
FOR i IN 1..nt_intersection.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_intersection(i));
END LOOP;
END;
/
20. Sorting Elements in a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(5, 1, 4, 2, 3);
BEGIN
SELECT COLUMN_VALUE
BULK COLLECT INTO nt_numbers
FROM TABLE(nt_numbers)
ORDER BY COLUMN_VALUE;
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
/
21. Using Nested Table in a FORALL Statement
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_numbers nested_table_type := nested_table_type(1, 2, 3, 4, 5);
BEGIN
FORALL i IN 1..nt_numbers.COUNT
INSERT INTO test_table (col1) VALUES (nt_numbers(i));
END;
/
22. Fetching Data into a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF employees%ROWTYPE;
nt_employees nested_table_type;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees;
FOR i IN 1..nt_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_employees(i).first_name || ' ' || nt_employees(i).last_name);
END LOOP;
END;
/
23. Storing Query Result in a VARRAY
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
v_names varray_type;
BEGIN
SELECT first_name
BULK COLLECT INTO v_names
FROM employees
WHERE ROWNUM <= 5;
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
/
24. Using Nested Table as IN Parameter in a Procedure
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
PROCEDURE display_numbers(nt_numbers nested_table_type) IS
BEGIN
FOR i IN 1..nt_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_numbers(i));
END LOOP;
END;
BEGIN
display_numbers(nested_table_type(1, 2, 3, 4, 5));
END;
/
25. Using Associative Array in a Function
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
FUNCTION get_name_by_id(aa_names associative_array_type, id PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN aa_names(id);
END;
BEGIN
DECLARE
aa associative_array_type;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
DBMS_OUTPUT.PUT_LINE(get_name_by_id(aa, 1));
END;
END;
/
26. Concatenating Two VARRAYs
DECLARE
TYPE varray_type IS VARRAY(10) OF VARCHAR2(100);
v1 varray_type := varray_type('John', 'Doe');
v2 varray_type := varray_type('Smith', 'Jane');
v_combined varray_type := varray_type();
BEGIN
v_combined.EXTEND(v1.COUNT + v2.COUNT);
FOR i IN 1..v1.COUNT LOOP
v_combined(i) := v1(i);
END LOOP;
FOR i IN 1..v2.COUNT LOOP
v_combined(v1.COUNT + i) := v2(i);
END LOOP;
FOR i IN 1..v_combined.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_combined(i));
END LOOP;
END;
/
27. Storing Table Data in a Nested Table
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees;
FOR i IN 1..nt_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_employees(i).first_name || ' ' || nt_employees(i).last_name);
END LOOP;
END;
/
28. Passing Associative Array to a Procedure
DECLARE
TYPE associative_array_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
PROCEDURE print_scores(scores associative_array_type) IS
key VARCHAR2(100);
BEGIN
key := scores.FIRST;
WHILE key IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(key || ': ' || scores(key));
key := scores.NEXT(key);
END LOOP;
END;
BEGIN
DECLARE
scores associative_array_type;
BEGIN
scores('John') := 90;
scores('Doe') := 85;
print_scores(scores);
END;
END;
/
29. Copying One Nested Table to Another
DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
nt1 nested_table_type := nested_table_type('John', 'Doe');
nt2 nested_table_type;
BEGIN
nt2 := nt1;
FOR i IN 1..nt2.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt2(i));
END LOOP;
END;
/
30. Converting a Nested Table to VARRAY
DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
TYPE varray_type IS VARRAY(10) OF VARCHAR2(100);
nt nested_table_type := nested_table_type('John', 'Doe', 'Smith');
v varray_type := varray_type();
BEGIN
v.EXTEND(nt.COUNT);
FOR i IN 1..nt.COUNT LOOP
v(i) := nt(i);
END LOOP;
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v(i));
END LOOP;
END;
/
31. Removing Duplicates from a Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
nt nested_table_type := nested_table_type('John', 'Doe', 'John');
nt_distinct nested_table_type := nested_table_type();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt_distinct.EXISTS(nt(i)) = FALSE THEN
nt_distinct.EXTEND;
nt_distinct(nt_distinct.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_distinct.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_distinct(i));
END LOOP;
END;
/
32. Using Nested Table with Nested Records
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
FOR i IN 1..nt_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_emp(i).first_name || ' ' || nt_emp(i).last_name || ' - ' || nt_emp(i).salary);
END LOOP;
END;
/
33. Using BULK COLLECT to Fill a Nested Table
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees;
FOR i IN 1..nt_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_employees(i).first_name || ' ' || nt_employees(i).last_name);
END LOOP;
END;
/
34. Converting a VARRAY to a Nested Table
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
v varray_type := varray_type('John', 'Doe', 'Smith');
nt nested_table_type := nested_table_type();
BEGIN
nt := nested_table_type();
FOR i IN 1..v.COUNT LOOP
nt.EXTEND;
nt(nt.COUNT) := v(i);
END LOOP;
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt(i));
END LOOP;
END;
/
35. Using Nested Table with Nested PL/SQL Table
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
nt_copy emp_nt := emp_nt();
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees;
nt_copy := nt_employees;
FOR i IN 1..nt_copy.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_copy(i).first_name || ' ' || nt_copy(i).last_name);
END LOOP;
END;
/
36. Using
FORALL with Nested Tables
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees WHERE ROWNUM <= 5;
FORALL i IN 1..nt_employees.COUNT
INSERT INTO employees_archive VALUES nt_employees(i);
END;
/
37. Using Nested Table to Store Row IDs
DECLARE
TYPE rid_nt IS TABLE OF ROWID;
nt_rids rid_nt;
BEGIN
SELECT ROWID BULK COLLECT INTO nt_rids FROM employees WHERE department_id = 10;
FOR i IN 1..nt_rids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_rids(i));
END LOOP;
END;
/
38. Updating Table Data Using Nested Table
DECLARE
TYPE nested_table_type IS TABLE OF NUMBER;
nt_ids nested_table_type := nested_table_type(100, 101, 102);
BEGIN
FORALL i IN 1..nt_ids.COUNT
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = nt_ids(i);
END;
/
39. Fetching Data into an Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
aa_employees associative_array_type;
BEGIN
SELECT * BULK COLLECT INTO aa_employees FROM employees WHERE department_id = 10;
FOR i IN 1..aa_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_employees(i).first_name || ' ' || aa_employees(i).last_name);
END LOOP;
END;
/
40. Combining Associative Arrays
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa1 associative_array_type;
aa2 associative_array_type;
i PLS_INTEGER;
BEGIN
aa1(1) := 'John';
aa1(2) := 'Doe';
aa2(1) := 'Jane';
aa2(2) := 'Smith';
i := aa1.COUNT + 1;
FOR j IN 1..aa2.COUNT LOOP
aa1(i) := aa2(j);
i := i + 1;
END LOOP;
FOR j IN 1..aa1.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa1(j));
END LOOP;
END;
/
41. Using Nested Table to Store Dates
DECLARE
TYPE date_nt IS TABLE OF DATE;
nt_dates date_nt := date_nt(SYSDATE, SYSDATE+1, SYSDATE+2);
BEGIN
FOR i IN 1..nt_dates.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_dates(i));
END LOOP;
END;
/
42. Passing VARRAY as a Function Parameter
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
FUNCTION sum_varray(v varray_type) RETURN NUMBER IS
sum NUMBER := 0;
BEGIN
FOR i IN 1..v.COUNT LOOP
sum := sum + v(i);
END LOOP;
RETURN sum;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(sum_varray(varray_type(1, 2, 3, 4, 5)));
END;
/
43. Using Nested Table for String Concatenation
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt_strings string_nt := string_nt('John', 'Doe', 'Smith');
combined_string VARCHAR2(1000);
BEGIN
FOR i IN 1..nt_strings.COUNT LOOP
combined_string := combined_string || nt_strings(i) || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(combined_string);
END;
/
44. Iterating Over Associative Array with String Index
DECLARE
TYPE associative_array_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
aa_scores associative_array_type;
BEGIN
aa_scores('John') := 90;
aa_scores('Doe') := 85;
FOR name IN (SELECT * FROM TABLE(aa_scores)) LOOP
DBMS_OUTPUT.PUT_LINE(name.COLUMN_VALUE);
END LOOP;
END;
/
45. Returning Nested Table from a Function
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
FUNCTION get_names RETURN string_nt IS
nt string_nt := string_nt();
BEGIN
nt.EXTEND(3);
nt(1) := 'John';
nt(2) := 'Doe';
nt(3) := 'Smith';
RETURN nt;
END;
BEGIN
FOR i IN 1..get_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(get_names(i));
END LOOP;
END;
/
46. Counting Non-Null Elements in Nested Table
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', NULL, 'Smith');
non_null_count NUMBER := 0;
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt(i) IS NOT NULL THEN
non_null_count := non_null_count + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Non-null elements: ' || non_null_count);
END;
/
47. Using Nested Table in Dynamic SQL
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
sql_query VARCHAR2(1000);
BEGIN
sql_query := 'SELECT * FROM employees WHERE last_name IN (';
FOR i IN 1..nt.COUNT LOOP
sql_query := sql_query || '''' || nt(i) || '''';
IF i < nt.COUNT THEN
sql_query := sql_query || ', ';
END IF;
END LOOP;
sql_query := sql_query || ')';
DBMS_OUTPUT.PUT_LINE(sql_query);
EXECUTE IMMEDIATE sql_query;
END;
/
48. Filtering Nested Table with Condition
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt(i) != 'Doe' THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
49. Removing Duplicates from Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa associative_array_type;
i PLS_INTEGER;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
aa(3) := 'John';
FOR i IN 1..aa.COUNT LOOP
FOR j IN i+1..aa.COUNT LOOP
IF aa(i) = aa(j) THEN
aa.DELETE(j);
END IF;
END LOOP;
END LOOP;
FOR i IN 1..aa.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa(i));
END LOOP;
END;
/
50. Nested Tables with Different Data Types
DECLARE
TYPE mixed_nt IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
nt mixed_nt;
BEGIN
nt(1) := 'John';
nt(2) := '100';
nt(3) := '50000';
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt(i));
END LOOP;
END;
/
51. Combining Multiple VARRAYs
DECLARE
TYPE varray_type IS VARRAY(10) OF VARCHAR2(100);
v1 varray_type := varray_type('John', 'Doe');
v2 varray_type := varray_type('Smith', 'Jane');
v_combined varray_type := varray_type();
BEGIN
v_combined.EXTEND(v1.COUNT + v2.COUNT);
FOR i IN 1..v1.COUNT LOOP
v_combined(i) := v1(i);
END LOOP;
FOR i IN 1..v2.COUNT LOOP
v_combined(v1.COUNT + i) := v2(i);
END LOOP;
FOR i IN 1..v_combined.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_combined(i));
END LOOP;
END;
/
52. Inserting Nested Table into Database Table
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
BEGIN
FOR i IN 1..nt.COUNT LOOP
INSERT INTO test_table (col1) VALUES (nt(i));
END LOOP;
END;
/
53. Using VARRAY in
a Procedure
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
PROCEDURE display_varray(v varray_type) IS
BEGIN
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v(i));
END LOOP;
END;
BEGIN
display_varray(varray_type(1, 2, 3, 4, 5));
END;
/
54. Using VARRAY for Date Storage
DECLARE
TYPE varray_type IS VARRAY(5) OF DATE;
v varray_type := varray_type(SYSDATE, SYSDATE+1, SYSDATE+2);
BEGIN
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v(i));
END LOOP;
END;
/
55. Updating Elements in Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa associative_array_type;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
aa(2) := 'Smith';
FOR i IN 1..aa.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa(i));
END LOOP;
END;
/
56. Filtering Associative Array Elements
DECLARE
TYPE associative_array_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
aa_scores associative_array_type;
BEGIN
aa_scores('John') := 90;
aa_scores('Doe') := 85;
FOR name IN aa_scores.FIRST..aa_scores.LAST LOOP
IF aa_scores(name) > 85 THEN
DBMS_OUTPUT.PUT_LINE(name || ': ' || aa_scores(name));
END IF;
END LOOP;
END;
/
57. Combining Two Nested Tables
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt1 string_nt := string_nt('John', 'Doe');
nt2 string_nt := string_nt('Smith', 'Jane');
nt_combined string_nt := string_nt();
BEGIN
nt_combined := nt1 MULTISET UNION nt2;
FOR i IN 1..nt_combined.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_combined(i));
END LOOP;
END;
/
58. Nested Table with PL/SQL Record
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
FOR i IN 1..nt_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_emp(i).first_name || ' ' || nt_emp(i).last_name || ' - ' || nt_emp(i).salary);
END LOOP;
END;
/
59. Combining Associative Arrays with PL/SQL Table
DECLARE
TYPE associative_array_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
aa_employees associative_array_type;
nt_employees employees%ROWTYPE;
BEGIN
SELECT * BULK COLLECT INTO aa_employees FROM employees WHERE department_id = 10;
nt_employees := aa_employees(1);
DBMS_OUTPUT.PUT_LINE(nt_employees.first_name || ' ' || nt_employees.last_name);
END;
/
60. Using BULK COLLECT with Associative Array
DECLARE
TYPE associative_array_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
aa_employees associative_array_type;
BEGIN
SELECT * BULK COLLECT INTO aa_employees FROM employees WHERE department_id = 10;
FOR i IN 1..aa_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_employees(i).first_name || ' ' || aa_employees(i).last_name);
END LOOP;
END;
/
61. Using Nested Table with BULK COLLECT
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees WHERE ROWNUM <= 5;
FOR i IN 1..nt_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_employees(i).first_name || ' ' || nt_employees(i).last_name);
END LOOP;
END;
/
62. Fetching Data into Nested Table with FORALL
DECLARE
TYPE emp_nt IS TABLE OF employees%ROWTYPE;
nt_employees emp_nt;
BEGIN
SELECT * BULK COLLECT INTO nt_employees FROM employees WHERE department_id = 10;
FORALL i IN 1..nt_employees.COUNT
INSERT INTO employees_archive VALUES nt_employees(i);
END;
/
63. Sorting Nested Table Elements
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
CURSOR c IS SELECT COLUMN_VALUE FROM TABLE(nt) ORDER BY COLUMN_VALUE;
BEGIN
FOR r IN c LOOP
DBMS_OUTPUT.PUT_LINE(r.COLUMN_VALUE);
END LOOP;
END;
/
64. Passing Nested Table as OUT Parameter
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
PROCEDURE fill_names(OUT nt string_nt) IS
BEGIN
nt := string_nt('John', 'Doe', 'Smith');
END;
nt_names string_nt;
BEGIN
fill_names(nt_names);
FOR i IN 1..nt_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_names(i));
END LOOP;
END;
/
65. Using Nested Table for String Manipulation
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
reversed_nt string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
reversed_nt.EXTEND;
reversed_nt(reversed_nt.COUNT) := REVERSE(nt(i));
END LOOP;
FOR i IN 1..reversed_nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(reversed_nt(i));
END LOOP;
END;
/
66. Using VARRAY in a Function Return
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
FUNCTION get_numbers RETURN varray_type IS
BEGIN
RETURN varray_type(1, 2, 3, 4, 5);
END;
BEGIN
FOR i IN 1..get_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(get_numbers(i));
END LOOP;
END;
/
67. Iterating Over Associative Array with PL/SQL Record
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE emp_aa IS TABLE OF emp_record INDEX BY PLS_INTEGER;
aa_emp emp_aa;
BEGIN
aa_emp(1).first_name := 'John';
aa_emp(1).last_name := 'Doe';
aa_emp(2).first_name := 'Jane';
aa_emp(2).last_name := 'Smith';
FOR i IN 1..aa_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_emp(i).first_name || ' ' || aa_emp(i).last_name);
END LOOP;
END;
/
68. Using VARRAY with PL/SQL Table
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
TYPE string_nt IS TABLE OF VARCHAR2(100);
v varray_type := varray_type('John', 'Doe', 'Smith');
nt string_nt;
BEGIN
nt := string_nt();
FOR i IN 1..v.COUNT LOOP
nt.EXTEND;
nt(nt.COUNT) := v(i);
END LOOP;
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt(i));
END LOOP;
END;
/
69. Combining Associative Arrays with FORALL
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa1 associative_array_type;
aa2 associative_array_type;
BEGIN
aa1(1) := 'John';
aa1(2) := 'Doe';
FORALL i IN 1..aa1.COUNT
aa2(i) := aa1(i);
FOR i IN 1..aa2.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa2(i));
END LOOP;
END;
/
70. Filtering Nested Table with LIKE Operator
DECLARE
TYPE string_nt IS
TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt(i) LIKE 'J%' THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
71. Using Nested Table for Employee Data
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
FOR i IN 1..nt_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_emp(i).first_name || ' ' || nt_emp(i).last_name || ' - ' || nt_emp(i).salary);
END LOOP;
END;
/
72. Using Associative Array with PL/SQL Record
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE emp_aa IS TABLE OF emp_record INDEX BY PLS_INTEGER;
aa_emp emp_aa;
BEGIN
aa_emp(1).first_name := 'John';
aa_emp(1).last_name := 'Doe';
aa_emp(2).first_name := 'Jane';
aa_emp(2).last_name := 'Smith';
FOR i IN 1..aa_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa_emp(i).first_name || ' ' || aa_emp(i).last_name);
END LOOP;
END;
/
73. Using Nested Table for Salary Calculation
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
total_salary NUMBER := 0;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
FOR i IN 1..nt_emp.COUNT LOOP
total_salary := total_salary + nt_emp(i).salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || total_salary);
END;
/
74. Using Associative Array for String Manipulation
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa associative_array_type;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
FOR i IN 1..aa.COUNT LOOP
aa(i) := UPPER(aa(i));
END LOOP;
FOR i IN 1..aa.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa(i));
END LOOP;
END;
/
75. Using Nested Table for Department Data
DECLARE
TYPE dept_record IS RECORD (
dept_name VARCHAR2(100),
location VARCHAR2(100)
);
TYPE dept_nt IS TABLE OF dept_record;
nt_dept dept_nt;
BEGIN
nt_dept := dept_nt();
nt_dept.EXTEND;
nt_dept(1).dept_name := 'HR';
nt_dept(1).location := 'New York';
nt_dept.EXTEND;
nt_dept(2).dept_name := 'Finance';
nt_dept(2).location := 'San Francisco';
FOR i IN 1..nt_dept.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_dept(i).dept_name || ' - ' || nt_dept(i).location);
END LOOP;
END;
/
76. Using VARRAY for Salary Storage
DECLARE
TYPE salary_varray IS VARRAY(5) OF NUMBER;
v salary_varray := salary_varray(50000, 55000, 60000);
BEGIN
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Salary: ' || v(i));
END LOOP;
END;
/
77. Using Nested Table with Different Data Types
DECLARE
TYPE mixed_nt IS TABLE OF VARCHAR2(100);
nt mixed_nt := mixed_nt('John', 'Doe', '100');
BEGIN
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt(i));
END LOOP;
END;
/
78. Using Nested Table for Bonus Calculation
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
bonus NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
total_bonus NUMBER := 0;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp(1).bonus := 5000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
nt_emp(2).bonus := 5500;
FOR i IN 1..nt_emp.COUNT LOOP
total_bonus := total_bonus + nt_emp(i).bonus;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Bonus: ' || total_bonus);
END;
/
79. Combining Nested Tables with PL/SQL Record
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
FOR i IN 1..nt_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_emp(i).first_name || ' ' || nt_emp(i).last_name || ' - ' || nt_emp(i).salary);
END LOOP;
END;
/
80. Using VARRAY for Storing Phone Numbers
DECLARE
TYPE phone_varray IS VARRAY(5) OF VARCHAR2(15);
v phone_varray := phone_varray('123-456-7890', '234-567-8901');
BEGIN
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Phone: ' || v(i));
END LOOP;
END;
/
81. Filtering Nested Table with Substring
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF SUBSTR(nt(i), 1, 1) = 'J' THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
82. Using Associative Array for Dynamic SQL
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
aa associative_array_type;
sql_query VARCHAR2(1000);
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
sql_query := 'SELECT * FROM employees WHERE first_name IN (';
FOR i IN 1..aa.COUNT LOOP
sql_query := sql_query || '''' || aa(i) || '''';
IF i < aa.COUNT THEN
sql_query := sql_query || ', ';
END IF;
END LOOP;
sql_query := sql_query || ')';
DBMS_OUTPUT.PUT_LINE(sql_query);
EXECUTE IMMEDIATE sql_query
;
END;
/
83. Iterating Over Nested Table with Different Data Types
DECLARE
TYPE mixed_nt IS TABLE OF VARCHAR2(100);
nt mixed_nt := mixed_nt('John', 'Doe', '100');
BEGIN
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt(i));
END LOOP;
END;
/
84. Using Nested Table for Employee Salary Calculation
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
bonus NUMBER
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
total_salary NUMBER := 0;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp(1).salary := 50000;
nt_emp(1).bonus := 5000;
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
nt_emp(2).salary := 55000;
nt_emp(2).bonus := 5500;
FOR i IN 1..nt_emp.COUNT LOOP
total_salary := total_salary + nt_emp(i).salary + nt_emp(i).bonus;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Salary with Bonus: ' || total_salary);
END;
/
85. Filtering Nested Table with IN Operator
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt(i) IN ('John', 'Smith') THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
86. Using Nested Table with FORALL Statement
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
BEGIN
FORALL i IN 1..nt.COUNT
INSERT INTO employees_archive (first_name) VALUES (nt(i));
END;
/
87. Combining Nested Table with Dynamic SQL
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
sql_query VARCHAR2(1000);
BEGIN
sql_query := 'SELECT * FROM employees WHERE last_name IN (';
FOR i IN 1..nt.COUNT LOOP
sql_query := sql_query || '''' || nt(i) || '''';
IF i < nt.COUNT THEN
sql_query := sql_query || ', ';
END IF;
END LOOP;
sql_query := sql_query || ')';
DBMS_OUTPUT.PUT_LINE(sql_query);
EXECUTE IMMEDIATE sql_query;
END;
/
88. Using Associative Array with PL/SQL Function
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
FUNCTION get_name_by_id(aa associative_array_type, id PLS_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN aa(id);
END;
aa associative_array_type;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
DBMS_OUTPUT.PUT_LINE(get_name_by_id(aa, 1));
END;
/
89. Removing Null Elements from Nested Table
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', NULL, 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF nt(i) IS NOT NULL THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
90. Using VARRAY for Storing Employee IDs
DECLARE
TYPE id_varray IS VARRAY(5) OF NUMBER;
v id_varray := id_varray(100, 101, 102);
BEGIN
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v(i));
END LOOP;
END;
/
91. Using Nested Table for String Manipulation
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
reversed_nt string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
reversed_nt.EXTEND;
reversed_nt(reversed_nt.COUNT) := REVERSE(nt(i));
END LOOP;
FOR i IN 1..reversed_nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(reversed_nt(i));
END LOOP;
END;
/
92. Combining Nested Table with Associative Array
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
nt string_nt := string_nt('John', 'Doe', 'Smith');
aa associative_array_type;
BEGIN
FOR i IN 1..nt.COUNT LOOP
aa(i) := nt(i);
END LOOP;
FOR i IN 1..aa.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa(i));
END LOOP;
END;
/
93. Using Nested Table for Employee Data with Dynamic SQL
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
sql_query VARCHAR2(1000);
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
sql_query := 'SELECT * FROM employees WHERE first_name IN (';
FOR i IN 1..nt_emp.COUNT LOOP
sql_query := sql_query || '''' || nt_emp(i).first_name || '''';
IF i < nt_emp.COUNT THEN
sql_query := sql_query || ', ';
END IF;
END LOOP;
sql_query := sql_query || ')';
DBMS_OUTPUT.PUT_LINE(sql_query);
EXECUTE IMMEDIATE sql_query;
END;
/
94. Filtering Nested Table with UPPER Function
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF UPPER(nt(i)) LIKE 'J%' THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
95. Iterating Over Nested Table with PL/SQL Record
DECLARE
TYPE emp_record IS RECORD (
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE emp_nt IS TABLE OF emp_record;
nt_emp emp_nt;
BEGIN
nt_emp := emp_nt();
nt_emp.EXTEND;
nt_emp(1).first_name := 'John';
nt_emp(1).last_name := 'Doe';
nt_emp.EXTEND;
nt_emp(2).first_name := 'Jane';
nt_emp(2).last_name := 'Smith';
FOR i IN 1..nt_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_emp(i).first_name || ' ' || nt_emp(i).last_name);
END LOOP;
END;
/
96. Using Associative Array with VARRAY
DECLARE
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
TYPE associative_array_type IS TABLE OF varray_type INDEX BY PLS_INTEGER;
aa associative_array_type;
v varray_type := varray_type('John', 'Doe', 'Smith');
BEGIN
aa(1) := v;
FOR i IN 1..aa(1).COUNT LOOP
DBMS_OUTPUT.PUT_LINE(aa(1)(i));
END LOOP;
END;
/
97. Using Nested Table for Storing IDs
DECLARE
TYPE id_nt IS TABLE OF NUMBER;
nt id_nt := id_nt(100, 101, 102);
BEGIN
FOR i IN 1..nt.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || nt(i));
END LOOP;
END;
/
98. Filtering Nested Table with LENGTH Function
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
nt_filtered string_nt := string_nt();
BEGIN
FOR i IN 1..nt.COUNT LOOP
IF LENGTH(nt(i)) > 3 THEN
nt_filtered.EXTEND;
nt_filtered(nt_filtered.COUNT) := nt(i);
END IF;
END LOOP;
FOR i IN 1..nt_filtered.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(nt_filtered(i));
END LOOP;
END;
/
99. Combining Nested Table with VARRAY
DECLARE
TYPE string_nt IS TABLE OF VARCHAR2(100);
TYPE varray_type IS VARRAY(5) OF VARCHAR2(100);
nt string_nt := string_nt('John', 'Doe', 'Smith');
v varray_type := varray_type();
BEGIN
v.EXTEND(nt.COUNT);
FOR i IN 1..nt.COUNT LOOP
v(i) := nt(i);
END LOOP;
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v(i));
END LOOP;
END;
/
100. Returning Associative Array from Function
DECLARE
TYPE associative_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
FUNCTION get_names RETURN associative_array_type IS
aa associative_array_type;
BEGIN
aa(1) := 'John';
aa(2) := 'Doe';
RETURN aa;
END;
BEGIN
FOR i IN 1..get_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(get_names(i));
END LOOP;
END;
/
No comments:
Post a Comment