Basic Cursor Examples
1. Simple Explicit Cursor
CREATE OR REPLACE PROCEDURE simple_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
emp_rec emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
CLOSE emp_cursor;
END;
/
2. Cursor with Parameters
CREATE OR REPLACE PROCEDURE employees_by_dept(p_dept_id IN NUMBER) IS
CURSOR dept_cursor IS
SELECT employee_id, employee_name FROM employees WHERE department_id = p_dept_id;
emp_rec dept_cursor%ROWTYPE;
BEGIN
OPEN dept_cursor;
LOOP
FETCH dept_cursor INTO emp_rec;
EXIT WHEN dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
CLOSE dept_cursor;
END;
/
3. Cursor with FOR Loop
CREATE OR REPLACE PROCEDURE cursor_for_loop IS
BEGIN
FOR emp_rec IN (SELECT employee_id, employee_name FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
END;
/
4. Cursor with Record Type
CREATE OR REPLACE PROCEDURE cursor_record_type IS
TYPE emp_record IS RECORD (
employee_id employees.employee_id%TYPE,
employee_name employees.employee_name%TYPE
);
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
emp_rec emp_record;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
CLOSE emp_cursor;
END;
/
5. Cursor for Bulk Collect
CREATE OR REPLACE PROCEDURE bulk_collect_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
Advanced Cursor Examples
6. Cursor with Ref Cursor
CREATE OR REPLACE PROCEDURE get_employee_data(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR
SELECT employee_id, employee_name FROM employees;
END;
/
7. Using Cursor Variables
CREATE OR REPLACE PROCEDURE process_employee_cursor(p_cursor IN SYS_REFCURSOR) IS
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
LOOP
FETCH p_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN p_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ' Name: ' || v_employee_name);
END LOOP;
CLOSE p_cursor;
END;
/
8. Cursor with Dynamic SQL
CREATE OR REPLACE PROCEDURE dynamic_cursor_example(p_table_name IN VARCHAR2) IS
v_query VARCHAR2(1000);
CURSOR dyn_cursor IS
SELECT * FROM employees; -- Example query
BEGIN
v_query := 'SELECT * FROM ' || p_table_name;
OPEN dyn_cursor FOR v_query;
-- Process cursor as needed
CLOSE dyn_cursor;
END;
/
9. Cursor with a Nested Cursor
CREATE OR REPLACE PROCEDURE nested_cursor_example IS
CURSOR dept_cursor IS
SELECT department_id, department_name FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, employee_name FROM employees WHERE department_id = p_dept_id;
v_dept_id departments.department_id%TYPE;
v_dept_name departments.department_name%TYPE;
BEGIN
FOR dept_rec IN dept_cursor LOOP
v_dept_id := dept_rec.department_id;
v_dept_name := dept_rec.department_name;
DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
FOR emp_rec IN emp_cursor(v_dept_id) LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
END LOOP;
END;
/
10. Cursor with Aggregated Data
CREATE OR REPLACE PROCEDURE aggregated_data_cursor IS
CURSOR avg_salary_cursor IS
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
BEGIN
FOR rec IN avg_salary_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || rec.department_id || ' Avg Salary: ' || rec.avg_salary);
END LOOP;
END;
/
More Complex Cursor Scenarios
11. Cursor with Exception Handling
CREATE OR REPLACE PROCEDURE cursor_with_exception IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
emp_rec emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
CLOSE emp_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/
12. Cursor with an Update
CREATE OR REPLACE PROCEDURE update_employee_salary IS
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees;
BEGIN
FOR emp_rec IN emp_cursor LOOP
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = emp_rec.employee_id;
END LOOP;
END;
/
13. Cursor with INSERT Operation
CREATE OR REPLACE PROCEDURE insert_from_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees WHERE salary < 30000;
BEGIN
FOR emp_rec IN emp_cursor LOOP
INSERT INTO low_salary_employees (employee_id, employee_name)
VALUES (emp_rec.employee_id, emp_rec.employee_name);
END LOOP;
END;
/
14. Cursor to Fetch Data into a Collection
CREATE OR REPLACE PROCEDURE fetch_into_collection IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
15. Cursor to Manage Pagination
CREATE OR REPLACE PROCEDURE paginate_cursor(p_page_number IN NUMBER, p_page_size IN NUMBER) IS
CURSOR paginated_cursor IS
SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT employee_id, employee_name FROM employees ORDER BY employee_id
) a WHERE ROWNUM <= p_page_number * p_page_size
) WHERE rnum > (p_page_number - 1) * p_page_size;
BEGIN
FOR emp_rec IN paginated_cursor LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ' Name: ' || emp_rec.employee_name);
END LOOP;
END;
/
Using Cursors with PL/SQL Tables and Nested Tables
16. Cursor with PL/SQL Table
CREATE OR REPLACE PROCEDURE cursor_with_table IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
17. Cursor with Nested Table Type
CREATE OR REPLACE PROCEDURE nested_table_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
18. Cursor with REF Cursor in PL/SQL Table
CREATE OR REPLACE PROCEDURE ref_cursor_with_table(p_cursor OUT SYS_REFCURSOR) IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
OPEN p_cursor FOR SELECT * FROM employees;
FETCH p_cursor BULK COLLECT INTO v_employees;
CLOSE p_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
19. Cursor with Nested Cursor and PL/SQL Table
CREATE OR REPLACE PROCEDURE nested_cursor_with_table IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT * FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR dept_rec IN dept_cursor LOOP
OPEN emp_cursor(dept_rec.department_id);
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || dept_rec.department_id || ' Employee ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END LOOP;
END;
/
20. Cursor with Nested Tables and REF Cursors
CREATE OR REPLACE PROCEDURE nested_table_ref_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR dept_cursor IS
SELECT department_id FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT * FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR dept_rec IN dept_cursor LOOP
OPEN emp_cursor(dept_rec.department_id);
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || dept_rec.department_id || ' Employee ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END LOOP;
END;
/
Cursor Management Examples
21. Cursor with Dynamic SQL and Bind Variables
CREATE OR REPLACE PROCEDURE dynamic_cursor_with_bind(p_dept_id IN NUMBER) IS
v_query VARCHAR2(500);
CURSOR dyn_cursor IS
SELECT employee_id, employee_name FROM employees WHERE department_id = p_dept_id;
BEGIN
v_query := 'SELECT employee_id, employee_name FROM employees WHERE department_id = :1';
OPEN dyn_cursor FOR v_query USING p_dept_id;
-- Process cursor as needed
CLOSE dyn_cursor;
END;
/
22. Cursor to Handle Multiple Cursors
CREATE OR REPLACE PROCEDURE multi_cursor_handling IS
CURSOR dept_cursor IS
SELECT department_id FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, employee_name FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR dept_rec IN dept_cursor LOOP
OPEN emp_cursor(dept_rec.department_id);
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || dept_rec.department_id || ' Employee ID: ' || v_employee_id || ' Name: ' || v_employee_name);
END LOOP;
CLOSE emp_cursor;
END LOOP;
END;
/
23. Cursor with Prior Data Validation
CREATE OR REPLACE PROCEDURE validate_data_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees WHERE salary > 0;
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_employee_id IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Invalid ID for Employee: ' || v_employee_name);
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/
24. Cursor to Use REF Cursor for Client Application
CREATE OR REPLACE PROCEDURE client_app_ref_cursor(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR
SELECT employee_id, employee_name FROM employees;
END;
/
25. Cursor with SQL%ROWCOUNT
CREATE OR REPLACE PROCEDURE cursor_with_rowcount IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
v_rowcount NUMBER;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor;
v_rowcount := emp_cursor%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Number of rows fetched: ' || v_rowcount);
CLOSE emp_cursor;
END;
/
More Advanced Cursor Techniques
26. Cursor with REF Cursor and PL/SQL Table
CREATE OR REPLACE PROCEDURE ref_cursor_with_table(p_cursor OUT SYS_REFCURSOR) IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
OPEN p_cursor FOR SELECT * FROM employees;
FETCH p_cursor BULK COLLECT INTO v_employees;
CLOSE p_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
27. Cursor to Fetch Data in Batches
CREATE OR REPLACE PROCEDURE batch_fetch_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULK COLLECT INTO v_employees LIMIT 100;
EXIT WHEN v_employees.COUNT = 0;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END LOOP;
CLOSE emp_cursor;
END;
/
28. Cursor to Handle Data Inconsistencies
CREATE OR REPLACE PROCEDURE handle_data_inconsistencies IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name, salary FROM employees;
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_salary < 0 THEN
DBMS_OUTPUT.PUT_LINE('Inconsistent data: ID: ' || v_employee_id || ' Name: ' || v_employee_name || ' Salary: ' || v_salary);
END IF;
END LOOP;
CLOSE emp_cursor;
END;
/
29. Cursor with Recursive Query
CREATE OR REPLACE PROCEDURE recursive_cursor IS
CURSOR dept_cursor IS
WITH RECURSIVE org_chart AS (
SELECT department_id, department_name, manager_id FROM departments WHERE manager_id IS NULL
UNION ALL
SELECT d.department_id, d.department_name, d.manager_id
FROM departments d
INNER JOIN org_chart o ON d.manager_id = o.department_id
)
SELECT department_id, department_name FROM org_chart;
BEGIN
FOR dept_rec IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || dept_rec.department_id || ' Name: ' || dept_rec.department_name);
END LOOP;
END;
/
30. Cursor to Fetch and Update Data
CREATE OR REPLACE PROCEDURE fetch_and_update_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE salary < 50000;
v_employee_id employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = v_employee_id;
END LOOP;
CLOSE emp_cursor;
END;
/
Cursor with PL/SQL Collections
31. Cursor with Nested Table Collection
CREATE OR REPLACE PROCEDURE nested_table_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
32. Cursor with Associative Array
CREATE OR REPLACE PROCEDURE associative_array_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
v_index PLS_INTEGER := 1;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor BULK COLLECT INTO v_employees LIMIT 100;
EXIT WHEN v_employees.COUNT = 0;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
v_index := v_index + 1;
END LOOP;
CLOSE emp_cursor;
END;
/
33. Cursor with Cursor Variables and Collections
CREATE OR REPLACE PROCEDURE cursor_variable_with_collection IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
34. Cursor with Global Temporary Tables
CREATE OR REPLACE PROCEDURE cursor_with_temp_table IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
BEGIN
CREATE GLOBAL TEMPORARY TABLE temp_employees (
employee_id NUMBER,
employee_name VARCHAR2(100)
) ON COMMIT DELETE ROWS;
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
INSERT INTO temp_employees (employee_id, employee_name) VALUES (v_employee_id, v_employee_name);
END LOOP;
CLOSE emp_cursor;
-- Process data in temp_employees table
END;
/
35. Cursor with Complex Aggregation
CREATE OR REPLACE PROCEDURE complex_aggregation_cursor IS
CURSOR avg_salary_cursor IS
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
BEGIN
FOR rec IN avg_salary_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || rec.department_id || ' Avg Salary: ' || rec.avg_salary);
END LOOP;
END;
/
36. Cursor with XML Data Handling
CREATE OR REPLACE PROCEDURE xml_cursor_example IS
CURSOR xml_cursor IS
SELECT XMLTYPE('<root><employee><id>1</id><name>John</name></employee></root>') AS xml_data FROM dual;
v_xml XMLTYPE;
BEGIN
OPEN xml_cursor;
FETCH xml_cursor INTO v_xml;
CLOSE xml_cursor;
DBMS_OUTPUT.PUT_LINE('XML Data: ' || v_xml.getCLOBVal());
END;
/
37. Cursor with JSON Data Handling
CREATE OR REPLACE PROCEDURE json_cursor_example IS
CURSOR json_cursor IS
SELECT JSON_OBJECT('id' VALUE employee_id, 'name' VALUE employee_name) AS json_data
FROM employees;
v_json VARCHAR2(4000);
BEGIN
OPEN json_cursor;
LOOP
FETCH json_cursor INTO v_json;
EXIT WHEN json_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('JSON Data: ' || v_json);
END LOOP;
CLOSE json_cursor;
END;
/
38. Cursor with Transaction Control
CREATE OR REPLACE PROCEDURE cursor_with_transaction IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_employee_name;
EXIT WHEN emp_cursor%NOTFOUND;
-- Perform transaction-related operations
-- e.g., INSERT, UPDATE, DELETE operations
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
39. Cursor with DBMS_SQL Package
CREATE OR REPLACE PROCEDURE dynamic_cursor_dbms_sql IS
v_cursor INTEGER;
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'SELECT employee_id, employee_name FROM employees', DBMS_SQL.STATIC);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_employee_id);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_employee_name);
DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_employee_id);
DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_employee_name);
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ' Name: ' || v_employee_name);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/
40. Cursor with Recursive Hierarchical Query
CREATE OR REPLACE PROCEDURE hierarchical_cursor IS
CURSOR hierarchy_cursor IS
WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, employee_name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name
FROM employees e
INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;
BEGIN
FOR rec IN hierarchy_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || ' Name: ' || rec.employee_name || ' Manager ID: ' || rec.manager_id);
END LOOP;
END;
/
41. Cursor with PL/SQL Table Operations
CREATE OR REPLACE PROCEDURE plsql_table_cursor IS
TYPE emp_table IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
v_employees emp_table;
CURSOR emp_cursor IS
SELECT * FROM employees;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor BULK COLLECT INTO v_employees;
CLOSE emp_cursor;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employees(i).employee_id || ' Name: ' || v_employees(i).employee_name);
END LOOP;
END;
/
42. Cursor with Output Parameters
CREATE OR REPLACE PROCEDURE cursor_with_output(p_output OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_output FOR
SELECT employee_id, employee_name FROM employees;
END;
/
43. Cursor with SQL%FOUND and SQL%NOTFOUND
CREATE OR REPLACE PROCEDURE cursor_with_found_notfound IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee_id, v_employee_name;
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('First Row - ID: ' || v_employee_id || ' Name: ' || v_employee_name);
END IF;
FETCH emp_cursor INTO v_employee_id, v_employee_name;
IF emp_cursor%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No more rows.');
END IF;
CLOSE emp_cursor;
END;
/
44. Cursor with Multiple Fetches
CREATE OR REPLACE PROCEDURE multiple_fetches_cursor IS
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees;
v_employee_id employees.employee_id%TYPE;
v_employee_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_employee_id, v_employee_name;
DBMS_OUTPUT.PUT_LINE('First Fetch - ID: ' || v_employee_id || ' Name: ' || v_employee_name);
FETCH emp_cursor INTO v_employee_id, v_employee_name;
DBMS_OUTPUT.PUT_LINE('Second Fetch - ID: ' || v_employee_id || ' Name: ' || v_employee_name);
CLOSE emp_cursor;
END;
/
No comments:
Post a Comment