1. What is an explicit cursor in Oracle?
An explicit cursor is a cursor that is declared and controlled by the programmer to retrieve and process multiple rows returned by a query.
Unlike implicit cursors, explicit cursors require you to:
1. Declare
2. Open
3. Fetch
4. Close
2. When should you use an explicit cursor?
Use explicit cursors when:
- A query returns multiple rows
- You need row-by-row processing
- You want full control over cursor operations
- You need to pass parameters to a query
3. What is the basic syntax of an explicit cursor?
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;
v_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
4. What are the steps in working with explicit cursors?
1. DECLARE – Define the cursor with a SELECT statement
2. OPEN – Execute the query and allocate memory
3. FETCH – Retrieve rows one by one
4. CLOSE – Release memory
5. What are explicit cursor attributes?
|
Attribute |
Description |
|
cursor_name%FOUND |
TRUE if last fetch returned a row |
|
cursor_name%NOTFOUND |
TRUE if last fetch did not return a row |
|
cursor_name%ROWCOUNT |
Number of rows fetched so far |
|
cursor_name%ISOPEN |
TRUE if cursor is open |
Example:
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;
6. What is a cursor FOR loop?
A cursor FOR loop automatically handles:
- OPEN
- FETCH
- CLOSE
Example:
BEGIN
FOR rec IN (SELECT employee_id, first_name
FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);
END LOOP;
END;
✔ Recommended because it is cleaner and safer.
7. What is a parameterized cursor?
A cursor that accepts parameters.
Example:
DECLARE
CURSOR emp_cursor (p_dept_id NUMBER) IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = p_dept_id;
v_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor(20);
LOOP
FETCH emp_cursor INTO v_id, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
8. What happens if you forget to close a cursor?
- Memory remains allocated
- Can cause resource leaks
- May lead to "maximum open cursors exceeded" error
Always close explicit cursors after use.
9. What are common errors with explicit cursors?
- Fetching without opening
- Forgetting EXIT condition → infinite loop
- Closing an already closed cursor
- Mismatched FETCH variables and SELECT columns
10. What is the difference between implicit and explicit cursors?
|
Feature |
Implicit Cursor |
Explicit Cursor |
|
Created by |
Oracle |
Developer |
|
Used for |
Single-row & DML |
Multi-row queries |
|
Manual control |
No |
Yes |
|
OPEN/FETCH/CLOSE |
Not required |
Required |
|
Cursor name |
SQL |
User-defined |
11. Can explicit cursors be used in procedures and triggers?
Yes. They can be used in:
- Procedures
- Functions
- Packages
- Triggers
But avoid heavy row-by-row logic in triggers for performance reasons.
12. What are best practices for explicit cursors?
- Prefer cursor FOR loops when possible
- Always close cursors
- Use parameterized cursors for flexibility
- Avoid row-by-row processing when bulk operations are possible
- Use BULK COLLECT for performance when handling large datasets
13. What is BULK COLLECT with explicit cursor?
Used to fetch multiple rows at once into collections for better performance.
Example:
DECLARE
TYPE emp_table IS TABLE OF employees.employee_id%TYPE;
v_emp_ids emp_table;
BEGIN
SELECT employee_id
BULK COLLECT INTO v_emp_ids
FROM employees
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || v_emp_ids.COUNT);
END;
No comments:
Post a Comment