1. What is a Nested Cursor?
A nested cursor is a cursor operation performed inside another cursor operation. The outer cursor processes the parent records, while the inner cursor processes related child records.
2. Why do we use Nested Cursors?
Nested cursors are useful when one set of records is related to another. The source notes show examples such as departments with employees beneath them.
3. Basic Syntax
DECLARE
CURSOR outer_cursor IS
SELECT ...;
```
CURSOR inner_cursor(...) IS
SELECT ...;
```
BEGIN
FOR outer_rec IN outer_cursor
LOOP
FOR inner_rec IN inner_cursor(...)
LOOP
-- Process inner record
END LOOP;
END LOOP;
END;
/4. Simple Nested Cursor Example
The notes show a department-and-employee example using a department cursor and a parameterized employee cursor.
DECLARE
CURSOR dept_cursor IS
SELECT department_id, department_name
FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = p_dept_id;
BEGIN
FOR dept IN dept_cursor
LOOP
DBMS_OUTPUT.PUT_LINE(
'Department: ' || dept.department_name
);
FOR emp IN emp_cursor(dept.department_id)
LOOP
DBMS_OUTPUT.PUT_LINE(
' Employee: ' ||
emp.first_name ||
' - Salary: ' ||
emp.salary
);
END LOOP;
END LOOP;
END;
/5. How does a Nested Cursor work?
The processing happens like this: outer cursor, then one inner cursor run for each outer-row value. The inner cursor starts again for each outer row.
Outer Cursor
↓
Department 10
↓
Inner Cursor
↓
Employees of Department 10
↓
Finish employees
↓
Department 20
↓
Inner Cursor
↓
Employees of Department 20
↓
Finish employees
↓
Next department6. Outer Cursor and Inner Cursor
| Term | Meaning |
|---|---|
| Outer cursor | The cursor that controls the outer loop. |
| Inner cursor | The cursor inside the outer loop. |
7. Why is a Parameterized Cursor useful?
A parameterized cursor allows the inner cursor to use a value from the current outer record.
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = p_dept_id;8. Can we use an inline query instead of a named inner cursor?
Yes. The notes say you do not always need a named inner cursor, and for a simple one-time inner query the inline query is often easier to read.
BEGIN
FOR dept IN (
SELECT department_id, department_name
FROM departments
)
LOOP
DBMS_OUTPUT.PUT_LINE(
'Department: ' || dept.department_name
);
FOR emp IN (
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = dept.department_id
)
LOOP
DBMS_OUTPUT.PUT_LINE(
' Employee: ' || emp.first_name
);
END LOOP;
END LOOP;
END;
/9. Can a Nested Cursor have three levels?
Yes. The file gives a Department → Employee → Project example.
BEGIN
FOR dept IN (
SELECT department_id, department_name
FROM departments
)
LOOP
DBMS_OUTPUT.PUT_LINE(
'Department: ' || dept.department_name
);
FOR emp IN (
SELECT employee_id, first_name
FROM employees
WHERE department_id = dept.department_id
)
LOOP
DBMS_OUTPUT.PUT_LINE(
' Employee: ' || emp.first_name
);
FOR project IN (
SELECT project_id, project_name
FROM projects
WHERE employee_id = emp.employee_id
)
LOOP
DBMS_OUTPUT.PUT_LINE(
' Project: ' || project.project_name
);
END LOOP;
END LOOP;
END LOOP;
END;
/10. Can we use nested explicit cursors with OPEN/FETCH/CLOSE?
Yes. The notes show a full explicit example with OPEN, FETCH, EXIT WHEN, and CLOSE for both cursors, but also note that a Cursor FOR LOOP is much simpler.
DECLARE
CURSOR dept_cursor IS
SELECT department_id, department_name
FROM departments;
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = p_dept_id;
v_dept_id departments.department_id%TYPE;
v_dept_name departments.department_name%TYPE;
v_emp_id employees.employee_id%TYPE;
v_emp_name employees.first_name%TYPE;
BEGIN
OPEN dept_cursor;
LOOP
FETCH dept_cursor
INTO v_dept_id, v_dept_name;
EXIT WHEN dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
'Department: ' || v_dept_name
);
OPEN emp_cursor(v_dept_id);
LOOP
FETCH emp_cursor
INTO v_emp_id, v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
' Employee: ' || v_emp_name
);
END LOOP;
CLOSE emp_cursor;
END LOOP;
CLOSE dept_cursor;
END;
/11. Nested Cursor vs JOIN
The source recommends a JOIN for simple data retrieval and a single SQL statement for set-based work whenever possible.
| Nested Cursor | JOIN / Set-based SQL |
|---|---|
| Useful for parent-child processing | Often better for simple retrieval |
| Processes row by row | Processes data in a set |
| Can be more verbose | Usually shorter and clearer |
12. Can the inner cursor use values from the outer cursor?
Yes. This is one of the most important features of nested cursor processing. The inner query can use the current outer cursor value, such as dept.department_id.
13. What happens if the outer cursor returns no rows?
The entire nested processing is skipped. If the outer loop has zero iterations, the inner loop never runs.
14. What happens if the inner cursor has no rows?
The outer loop continues normally, and the inner loop simply executes zero times for that outer row.
15. Can we use IF, UPDATE, DELETE, EXIT, and CONTINUE inside nested cursors?
Yes. The source includes examples of IF logic, UPDATE, DELETE, EXIT, CONTINUE, FOR UPDATE, and WHERE CURRENT OF inside nested cursor processing.
16. Customer and Orders Example
The notes also show another hierarchical example using customers and orders.
BEGIN
FOR customer IN (
SELECT customer_id, customer_name
FROM customers
)
LOOP
DBMS_OUTPUT.PUT_LINE(
'Customer: ' || customer.customer_name
);
FOR ord IN (
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = customer.customer_id
)
LOOP
DBMS_OUTPUT.PUT_LINE(
' Order: ' || ord.order_id ||
' Amount: ' || ord.total_amount
);
END LOOP;
END LOOP;
END;
/17. Frequently Asked Questions
| Question | Answer |
|---|---|
| What is a nested cursor? | A cursor or cursor loop executed inside another cursor loop. |
| What is the outer cursor? | The cursor controlling the outer loop. |
| What is the inner cursor? | The cursor executing inside the outer loop. |
| Why use nested cursors? | To process related parent-child records. |
| Can the inner cursor use values from the outer cursor? | Yes. |
| Can a parameterized cursor be used as the inner cursor? | Yes. |
| Can we use an inline query for the inner cursor? | Yes. |
| Can we have three or more cursor levels? | Yes, although deep nesting can become difficult to maintain. |
| What happens if the outer cursor returns no rows? | The inner cursor is never executed. |
| What happens if the inner cursor returns no rows? | The inner loop executes zero times, and the outer loop continues. |
Important Exam Points
- Nested cursor = cursor inside another cursor.
- The outer cursor processes the parent records.
- The inner cursor processes related child records.
- The inner cursor can use values from the outer cursor.
- Parameterized cursors are commonly used for the inner cursor.
- Cursor FOR LOOP automatically handles OPEN, FETCH, and CLOSE.
- The inner loop executes once for each applicable outer row.
- Outer cursor returns zero rows → inner cursor does not execute.
- Inner cursor returns zero rows → outer loop continues.
- EXIT without a label exits the innermost loop.
- Labels can be used to exit an outer loop.
- CONTINUE can skip an iteration.
- FOR UPDATE can be used when row locking or update processing is required.
- WHERE CURRENT OF can be used with an appropriate FOR UPDATE cursor.
- Multiple nesting levels are possible.
- For simple parent-child retrieval, a JOIN may be better.
- For set-based DML, prefer a single SQL statement when possible.
Quick Revision Diagram
OUTER CURSOR
|
+-- Department
|
+-- INNER CURSOR
|
+-- Employees
|
+-- Another level if neededOne-line memory trick: Nested Cursor = Outer cursor finds the parent, and the inner cursor processes the related children.
No comments:
Post a Comment