1. What is a cursor in Oracle?
A cursor is a pointer that allows you to fetch and manipulate query results row by row. In Oracle, cursors are essential when you need to process query results in PL/SQL procedures, functions, or triggers.
- Implicit cursor: Automatically created by Oracle for SQL statements like SELECT INTO, INSERT, UPDATE, DELETE.
- Explicit cursor: Declared by the programmer for more control over multi-row queries.
2. What are the types of cursors in Oracle?
1. Implicit Cursor
o Automatically managed by Oracle.
o Used when a single-row query is executed.
o Accessible using %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN attributes.
2. Explicit Cursor
o Declared and controlled by the developer.
o Used for multi-row queries.
o Requires: DECLARE, OPEN, FETCH, CLOSE.
o Attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
3. Ref Cursor (Cursor Variable)
o A pointer to a query result set, can be passed between PL/SQL and client applications.
o Can be strongly typed (with a specific return type) or weakly typed (any structure).
3. How do you declare and use an explicit cursor?
Syntax Example:
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name FROM employees WHERE department_id = 10;
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id, v_first_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_first_name);
END LOOP;
CLOSE emp_cursor;
END;
Steps:
1. Declare the cursor.
2. Open the cursor.
3. Fetch rows into variables.
4. Close the cursor.
4. What are cursor attributes in Oracle?
Cursor attributes provide information about the cursor state:
|
Attribute |
Description |
|
%FOUND |
TRUE if the last fetch returned a row. |
|
%NOTFOUND |
TRUE if the last fetch did not return a row. |
|
%ROWCOUNT |
Number of rows fetched so far. |
|
%ISOPEN |
TRUE if the cursor is open, FALSE if closed. |
5. What is a REF CURSOR and when to use it?
- REF CURSOR is a cursor variable that points to a result set.
- Used when:
- Returning query results to client applications (like Java or PL/SQL programs).
- You need dynamic SQL with varying return structures.
Example:
DECLARE
TYPE ref_cursor_type IS REF CURSOR;
emp_ref_cursor ref_cursor_type;
v_emp_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
BEGIN
OPEN emp_ref_cursor FOR SELECT employee_id, first_name FROM employees WHERE department_id = 20;
LOOP
FETCH emp_ref_cursor INTO v_emp_id, v_name;
EXIT WHEN emp_ref_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_name);
END LOOP;
CLOSE emp_ref_cursor;
END;
6. Can cursors be used in triggers or functions?
Yes, cursors can be used in triggers, procedures, functions, and packages, but implicit cursors are more common in triggers due to their simplicity.
7. How do you fetch multiple rows efficiently?
- Use a cursor FOR loop instead of explicit 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;
This automatically opens, fetches, and closes the cursor.
8. What are the differences between implicit and explicit cursors?
|
Feature |
Implicit Cursor |
Explicit Cursor |
|
Defined by |
Oracle automatically |
Programmer |
|
Rows fetched |
Single-row queries |
Multi-row queries |
|
Needs OPEN/FETCH/CLOSE |
No |
Yes |
|
Cursor attributes |
%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN |
Same, plus can be used explicitly |
9. Common mistakes with cursors
1. Forgetting to CLOSE explicit cursors → memory leaks.
2. Using %FOUND before first fetch.
3. Not handling %NOTFOUND → infinite loops.
4. Fetching into variables with mismatched data types.
10. Best practices
- Use cursor FOR loops when possible to simplify code.
- Close cursors as soon as you finish using them.
- Prefer REF CURSOR for dynamic queries.
- Avoid unnecessary cursors for simple single-row queries—use implicit cursors instead.
No comments:
Post a Comment