Basic Bulk Collect Programs
1. Basic Bulk Collect Example:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
2. Bulk Collect with Specific Columns:
DECLARE
TYPE emp_table IS TABLE OF employees.first_name%TYPE;
l_emps emp_table;
BEGIN
SELECT first_name BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i));
END LOOP;
END;
3. Bulk Collect with WHERE Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 10;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
4. Bulk Collect with JOIN:
DECLARE
TYPE emp_dept_table IS TABLE OF employees%ROWTYPE;
l_emps emp_dept_table;
BEGIN
SELECT e.* BULK COLLECT INTO l_emps
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_name = 'Sales';
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
5. Bulk Collect with LIMIT Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
CURSOR c1 IS SELECT * FROM employees;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO l_emps LIMIT 100;
EXIT WHEN l_emps.COUNT = 0;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END LOOP;
CLOSE c1;
END;
6. Bulk Collect with Dynamic SQL:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO l_emps;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
7. Bulk Collect with FORALL for Insert:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 20;
FORALL i IN l_emps.FIRST..l_emps.LAST
INSERT INTO employees_archive VALUES l_emps(i);
END;
8. Bulk Collect into Associative Array:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
9. Bulk Collect with Record Type:
DECLARE
TYPE emp_rec IS RECORD (first_name employees.first_name%TYPE, last_name employees.last_name%TYPE);
TYPE emp_table IS TABLE OF emp_rec;
l_emps emp_table;
BEGIN
SELECT first_name, last_name BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
10. Bulk Collect with ORDER BY:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees ORDER BY hire_date DESC;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).hire_date);
END LOOP;
END;
Intermediate Bulk Collect Programs
11. Bulk Collect with CASE Statement:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, first_name, last_name,
CASE WHEN salary > 10000 THEN 'High' ELSE 'Low' END AS salary_range
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).salary_range);
END LOOP;
END;
12. Bulk Collect with Returning Clause:
DECLARE
TYPE id_table IS TABLE OF employees.employee_id%TYPE;
l_ids id_table;
BEGIN
DELETE FROM employees WHERE department_id = 30
RETURNING employee_id BULK COLLECT INTO l_ids;
FOR i IN 1..l_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Deleted Employee ID: ' || l_ids(i));
END LOOP;
END;
13. Bulk Collect with Multiple Collections:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
TYPE emp_name_table IS TABLE OF employees.first_name%TYPE;
l_ids emp_id_table;
l_names emp_name_table;
BEGIN
SELECT employee_id, first_name BULK COLLECT INTO l_ids, l_names FROM employees;
FOR i IN 1..l_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_ids(i) || ' ' || l_names(i));
END LOOP;
END;
14. Bulk Collect with FETCH in Cursor:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
CURSOR c IS SELECT * FROM employees;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_emps LIMIT 100;
EXIT WHEN l_emps.COUNT = 0;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END LOOP;
CLOSE c;
END;
15. Bulk Collect with Exception Handling:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
END;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
16. Bulk Collect with FORALL and DELETE:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
l_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO l_ids FROM employees WHERE department_id = 20;
FORALL i IN l_ids.FIRST..l_ids.LAST
DELETE FROM employees WHERE employee_id = l_ids(i);
END;
17. Bulk Collect with Associative Array Indexed by VARCHAR2:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY VARCHAR2(50);
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
18. Bulk Collect with UNION:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM (
SELECT * FROM employees WHERE department_id = 10
UNION
SELECT * FROM employees WHERE department_id = 20
);
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
19. Bulk Collect with NVL Function:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, NVL(commission_pct, 0) AS commission_pct
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).employee_id || ' ' || l_emps(i).commission_pct);
END LOOP;
END;
20. Bulk Collect with Aggregation:
DECLARE
TYPE dept_salary_table IS TABLE OF NUMBER;
l_salaries dept_salary_table;
BEGIN
SELECT SUM(salary) BULK COLLECT INTO l_salaries
FROM employees GROUP BY department_id;
FOR i IN 1..l_salaries.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || l_salaries(i));
END LOOP;
END;
Advanced Bulk Collect Programs
21. Bulk Collect with Hierarchical Query:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
22. Bulk Collect with User-Defined Object Type:
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
DECLARE
TYPE emp_table IS TABLE OF emp_obj;
l_emps emp_table;
BEGIN
SELECT emp_obj(employee_id, first_name, last_name) BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
23. Bulk Collect with Subquery:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps
FROM employees WHERE employee_id IN (SELECT employee_id FROM employees WHERE department_id = 10);
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
24. Bulk Collect with DECODE Function:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, DECODE(department_id, 10, 'Sales', 20, 'HR', 'Other') AS dept_name
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).employee_id || ' ' || l_emps(i).dept_name);
END LOOP;
END;
25. Bulk Collect with Nested Collections:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
TYPE dept_emp_table IS TABLE OF emp_id_table;
l_dept_emps dept_emp_table;
BEGIN
SELECT CAST(MULTISET(SELECT employee_id FROM employees WHERE department_id = d.department_id) AS emp_id_table)
BULK COLLECT INTO l_dept_emps FROM departments d;
FOR i IN 1..l_dept_emps.COUNT LOOP
FOR j IN 1..l_dept_emps(i).COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || l_dept_emps(i)(j));
END LOOP;
END LOOP;
END;
26. Bulk Collect with Pipelined Table Functions:
CREATE OR REPLACE FUNCTION get_employees RETURN emp_table PIPELINED IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
PIPE ROW(l_emps(i));
END LOOP;
RETURN;
END get_employees;
-- Use in SQL query
SELECT * FROM TABLE(get_employees);
27. Bulk Collect with PL/SQL Table Functions:
CREATE OR REPLACE FUNCTION get_high_earners RETURN emp_table IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE salary > 10000;
RETURN l_emps;
END get_high_earners;
-- Use in PL/SQL block
DECLARE
l_emps emp_table;
BEGIN
l_emps := get_high_earners;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
28. Bulk Collect with Autonomous Transaction:
CREATE OR REPLACE PROCEDURE log_employees AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
INSERT INTO employee_log (employee_id, log_date)
VALUES (l_emps(i).employee_id, SYSDATE);
END LOOP;
COMMIT;
END;
END log_employees;
29. Bulk Collect with Compound Trigger:
CREATE OR REPLACE TRIGGER emp_compound_trigger
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEFORE EACH ROW IS
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting Employee ID: ' || :NEW.employee_id);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Updating Employee ID: ' || :NEW.employee_id);
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting Employee ID: ' || :OLD.employee_id);
END IF;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Logged Employee ID: ' || l_emps(i).employee_id);
END LOOP;
END AFTER EACH ROW;
END emp_compound_trigger;
30. Bulk Collect with SQL%ROWCOUNT:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
DBMS_OUTPUT.PUT_LINE('Rows Fetched: ' || SQL%ROWCOUNT);
END;
31. Bulk Collect with REPLACE INTO Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
MERGE INTO employees e
USING (SELECT l_emps(i).employee_id AS employee_id FROM dual) src
ON (e.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET e.salary = l_emps(i).salary
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name, salary)
VALUES (l_emps(i).employee_id, l_emps(i).first_name, l_emps(i).last_name, l_emps(i).salary);
END LOOP;
END;
32. Bulk Collect with Dynamic Cursors:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
c SYS_REFCURSOR;
BEGIN
OPEN c FOR 'SELECT * FROM employees WHERE department_id = :dept
_id' USING 10;
FETCH c BULK COLLECT INTO l_emps;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
CLOSE c;
END;
33. Bulk Collect with PARTITION BY:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, first_name, last_name, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).rank);
END LOOP;
END;
34. Bulk Collect with Analytical Functions:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, first_name, last_name,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).rank);
END LOOP;
END;
35. Bulk Collect with LEFT JOIN:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT e.*, d.department_name
BULK COLLECT INTO l_emps
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).department_name);
END LOOP;
END;
36. Bulk Collect with INNER JOIN:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT e.*, d.department_name
BULK COLLECT INTO l_emps
FROM employees e JOIN departments d ON e.department_id = d.department_id;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).department_name);
END LOOP;
END;
37. Bulk Collect with RIGHT JOIN:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT e.*, d.department_name
BULK COLLECT INTO l_emps
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).department_name);
END LOOP;
END;
38. Bulk Collect with CROSS JOIN:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT e.*, d.department_name
BULK COLLECT INTO l_emps
FROM employees e CROSS JOIN departments d;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).department_name);
END LOOP;
END;
39. Bulk Collect with Full Outer Join:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT e.*, d.department_name
BULK COLLECT INTO l_emps
FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).department_name);
END LOOP;
END;
40. Bulk Collect with ROW_NUMBER():
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT employee_id, first_name, last_name,
ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name || ' ' || l_emps(i).row_num);
END LOOP;
END;
41. Bulk Collect with GROUP BY:
DECLARE
TYPE dept_salary_table IS TABLE OF NUMBER;
l_salaries dept_salary_table;
BEGIN
SELECT department_id, SUM(salary)
BULK COLLECT INTO l_salaries FROM employees GROUP BY department_id;
FOR i IN 1..l_salaries.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || l_salaries(i));
END LOOP;
END;
42. Bulk Collect with HAVING:
DECLARE
TYPE dept_salary_table IS TABLE OF NUMBER;
l_salaries dept_salary_table;
BEGIN
SELECT department_id, SUM(salary)
BULK COLLECT INTO l_salaries FROM employees GROUP BY department_id HAVING SUM(salary) > 50000;
FOR i IN 1..l_salaries.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || l_salaries(i));
END LOOP;
END;
43. Bulk Collect with Union All:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 10
UNION ALL
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 20;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
44. Bulk Collect with MINUS:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 10
MINUS
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 20;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
45. Bulk Collect with Intersect:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 10
INTERSECT
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id = 20;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
46. Bulk Collect with DISTINCT:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT DISTINCT first_name, last_name
BULK COLLECT INTO l_emps FROM employees;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
47. Bulk Collect with IN Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE department_id IN (10, 20, 30);
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
48. Bulk Collect with BETWEEN Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp
_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE salary BETWEEN 5000 AND 10000;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
49. Bulk Collect with LIKE Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
BEGIN
SELECT * BULK COLLECT INTO l_emps FROM employees WHERE first_name LIKE 'J%';
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END;
50. Bulk Collect with FETCH Clause:
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_emps emp_table;
CURSOR c IS SELECT * FROM employees ORDER BY employee_id;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_emps LIMIT 50;
EXIT WHEN l_emps.COUNT = 0;
FOR i IN 1..l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emps(i).first_name || ' ' || l_emps(i).last_name);
END LOOP;
END LOOP;
CLOSE c;
END;
No comments:
Post a Comment