PL/SQL: control statements and cursors
PL/SQL control statements (IF, CASE, LOOP, FOR, WHILE, EXIT) and cursors (implicit, explicit, cursor FOR loop, REF CURSOR) for processing query results and controlling flow in stored programs.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
PL/SQL control statements and cursors let an engineer write procedural logic inside the database: make decisions, iterate, and process query result rows one by one. They are essential for writing stored procedures, functions, and triggers that implement business rules, batch processing, reports, and data transformations efficiently inside the RDBMS.
Key ideas
Control statement: a language construct that changes the order of statement execution. Examples: IF statement, CASE statement, LOOP, WHILE loop, FOR loop, and EXIT/
EXIT WHEN, plusCONTINUE/CONTINUE WHENto skip to the next iteration. Define each before use.IF statement: choose one of several alternatives based on boolean expressions. Exact form: IF condition THEN statements [ELSIF condition THEN statements]... [ELSE statements] END IF; Assumes conditions evaluate to TRUE/FALSE/NULL (NULL treated as UNKNOWN: IF uses only TRUE to enter branch).
CASE statement: two variants: simple CASE (CASE expr WHEN val1 THEN ... WHEN val2 THEN ... ELSE ... END CASE) and searched CASE (CASE WHEN cond1 THEN ... WHEN cond2 THEN ... ELSE ... END CASE). Use when you compare one expression to many values or evaluate multiple conditions.
LOOP: infinite loop with explicit EXIT (LOOP statements; EXIT WHEN condition; END LOOP;). Use to repeat until a runtime condition stops it.
WHILE loop: WHILE condition LOOP statements END LOOP; condition evaluated before each iteration (zero iterations possible).
FOR loop: numeric FOR: FOR i IN low..high LOOP ... END LOOP; loop variable i is read-only and iterates over the integer range. Use REVERSE (FOR i IN REVERSE low..high LOOP) to iterate downward; bounds are inclusive; zero iterations occur if low > high; i is implicitly declared and goes out of scope after the loop. Also cursor FOR loop (see cursors) which iterates rows returned by a query.
Cursor: a handle to process rows returned by a query one at a time.
Implicit cursor: created automatically for every SQL DML (INSERT/UPDATE/DELETE/SELECT INTO) executed in PL/SQL. Its attributes are referenced by
SQL%ROWCOUNT,SQL%FOUND,SQL%NOTFOUND. Use for quick feedback like number of rows affected. Also applies to MERGE. Note:SQL%ISOPENexists but always returns FALSE for the implicit cursor.Explicit cursor: user-declared cursor with a query. Lifecycle and steps: declare, open, fetch, process, close. Exact steps required for an explicit cursor:
- Declaration: CURSOR c_name IS SELECT ...; (declare in declaration section)
- Open: OPEN c_name; (allocates resources and positions before first row)
- Fetch: FETCH c_name INTO variables; (copies current row into variables and advances cursor)
- Check: test cursor attributes
c_name%FOUND,c_name%NOTFOUND,c_name%ROWCOUNTif needed - Close: CLOSE c_name; (release resources)
The cursor attributes for explicit cursors are: %ROWCOUNT (number of rows fetched so far), %FOUND (TRUE if last fetch returned a row), %NOTFOUND (TRUE if last fetch returned no row), %ISOPEN (TRUE if cursor is open). Parameterized cursors are supported (e.g., CURSOR c_emp(p_dept employees.dept%TYPE) IS SELECT ... WHERE dept = p_dept;). For positioned updates/deletes, use CURSOR ... FOR UPDATE and WHERE CURRENT OF cursor_name.
Cursor FOR loop: shorthand that declares an implicit loop record, implicitly OPENs the cursor, FETCHes each row into the record, runs the loop body, and implicitly CLOSEs the cursor at the end. Syntax: FOR rec IN (SELECT ...) LOOP ... END LOOP; or FOR rec IN cursor_name LOOP ... END LOOP; This is exception-safe and simpler when you only need to read rows.
REF CURSOR (cursor variable): a pointer to a result set whose query can be assigned at runtime. Useful for returning query results from procedures or for dynamic queries. Two kinds: strongly typed (RETURN type) and weakly typed (no return type declared).
Error handling: exceptions in PL/SQL are handled with EXCEPTION blocks. If a cursor is left open when an exception occurs, it should be CLOSED in an exception handler to avoid resource leaks.
Diagram to draw (for exam): draw a vertical timeline showing cursor lifecycle. Label four boxes in order: DECLARE -> OPEN -> FETCH (repeat) -> CLOSE. Add arrows: from FETCH back to FETCH for repeated iteration until %NOTFOUND TRUE, then arrow to CLOSE. Next to FETCH draw sample variables receiving column values. Also show implicit cursor box attached to every DML with label SQL%ROWCOUNT, SQL%FOUND.
Formulas
-- explicit cursor declaration CURSOR cursor_name IS SELECT col1, col2 FROM table WHERE ...;
-- open, fetch, close OPEN cursor_name; FETCH cursor_name INTO var1, var2; CLOSE cursor_name;
-- explicit cursor attributes cursor_name%ROWCOUNT cursor_name%FOUND cursor_name%NOTFOUND cursor_name%ISOPEN
-- cursor FOR loop FOR rec IN cursor_name LOOP ... END LOOP; or FOR rec IN (SELECT ...) LOOP ... END LOOP;
-- implicit cursor attributes SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND
-- IF statement IF cond THEN ... ELSIF cond2 THEN ... ELSE ... END IF;
-- CASE (searched) CASE WHEN cond1 THEN ... WHEN cond2 THEN ... ELSE ... END CASE;
-- simple FOR loop (numeric) FOR i IN 1..10 LOOP ... END LOOP;
-- WHILE loop WHILE cond LOOP ... END LOOP;
-- EXIT WHEN LOOP ... EXIT WHEN cond; END LOOP;
-- REF CURSOR declaration (weak) TYPE ref_cur IS REF CURSOR; rc ref_cur; OPEN rc FOR dynamic_query; FETCH rc INTO ...; CLOSE rc;
Worked examples
Note: to view DBMS_OUTPUT output in SQL*Plus/SQL Developer, enable server output: SET SERVEROUTPUT ON.
Example 1 — explicit cursor and control statements: print IT employees with salary above department average
-- sample input: create table and rows
CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), dept VARCHAR2(20), salary NUMBER);
INSERT INTO employees VALUES (1,'Ravi','IT',50000);
INSERT INTO employees VALUES (2,'Sita','IT',60000);
INSERT INTO employees VALUES (3,'Mohan','HR',40000);
INSERT INTO employees VALUES (4,'Anita','IT',55000);
COMMIT;
-- PL/SQL block
SET SERVEROUTPUT ON;
DECLARE
avg_sal NUMBER;
CURSOR c_it IS SELECT emp_id, emp_name, salary FROM employees WHERE dept = 'IT' ORDER BY emp_id;
v_emp_id employees.emp_id%TYPE;
v_name employees.emp_name%TYPE;
v_sal employees.salary%TYPE;
BEGIN
SELECT ROUND(AVG(salary)) INTO avg_sal FROM employees WHERE dept = 'IT';
DBMS_OUTPUT.PUT_LINE('Average salary for IT = '||TO_CHAR(avg_sal));
OPEN c_it;
LOOP
FETCH c_it INTO v_emp_id, v_name, v_sal;
EXIT WHEN c_it%NOTFOUND;
IF v_sal > avg_sal THEN
DBMS_OUTPUT.PUT_LINE('Above avg: '||v_name||' ('||v_emp_id||') Salary='||v_sal);
END IF;
END LOOP;
CLOSE c_it;
END;
/
Exact output (DBMS_OUTPUT lines): Average salary for IT = 55000 Above avg: Sita (2) Salary=60000
Example 2 — cursor FOR loop (simpler) and WHERE condition
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees with salary > 45000:');
FOR r IN (SELECT emp_id, emp_name, salary FROM employees WHERE salary > 45000 ORDER BY emp_id) LOOP
DBMS_OUTPUT.PUT_LINE(r.emp_id||' - '||r.emp_name||' - '||r.salary);
END LOOP;
END;
/
Exact output: Employees with salary > 45000: 1 - Ravi - 50000 2 - Sita - 60000 4 - Anita - 55000
Example 3 — implicit cursor attributes after DML
SET SERVEROUTPUT ON;
DECLARE
rows_updated NUMBER;
BEGIN
UPDATE employees SET salary = salary * 1.10 WHERE dept = 'HR';
rows_updated := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Rows updated: '||rows_updated);
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('SQL%FOUND is TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('SQL%FOUND is FALSE');
END IF;
END;
/
Exact output: Rows updated: 1 SQL%FOUND is TRUE
(If HR has no rows, SQL%ROWCOUNT would be 0 and SQL%FOUND FALSE.)
Common mistakes
- Confusing implicit cursor attributes (
SQL%ROWCOUNT) with explicit cursor attributes (c%ROWCOUNT). They are different: SQL%ROWCOUNT refers to last SQL statement executed, not a named cursor. - Using SELECT ... INTO expecting multiple rows (raises TOO_MANY_ROWS). Use a cursor or BULK COLLECT for multiple rows.
- Not closing explicit cursors in exception handlers (resource leak). Use cursor FOR loop to avoid this when only reading.
- Relying on
cursor%ROWCOUNTbefore any fetch — it is zero until fetches happen. - Trying to OPEN a cursor FOR loop cursor again (FOR loop already opens and closes automatically).
For your exam
Kinds of questions this topic produces:
- Definitions: define cursor, cursor attributes, implicit vs explicit cursor, REF CURSOR.
- Short notes: explain cursor FOR loop, lifecycle of explicit cursor, difference between SQL%ROWCOUNT and c%ROWCOUNT.
- Programs: write a PL/SQL block using explicit cursor to process rows and print results; write an UPDATE and show use of SQL%ROWCOUNT; convert explicit cursor code into cursor FOR loop.
- Diagrams: draw cursor lifecycle with labels DECLARE, OPEN, FETCH (loop), CLOSE and show where attributes are valid.
- Derivations/Explanations: step-by-step how PL/SQL fetch loop processes rows and when %FOUND/%NOTFOUND change.
What a full-marks answer includes:
- Precise definitions of terms used (e.g., explicit cursor = "user-declared query handle"), correct syntax, and mention of attributes. For program questions: correct PL/SQL block that compiles, includes SET SERVEROUTPUT ON to show output, and demonstrates correct OPEN/FETCH/CLOSE or cursor FOR loop. For explanation questions: show the exact sequence of operations and explain behavior on boundary cases (zero rows, one row, many rows).
Quick check
- What are the four attributes available for an explicit cursor?
- What does
SQL%ROWCOUNTreport after an UPDATE statement? - Does a cursor FOR loop require explicit OPEN and CLOSE? Answers: %ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN; number of rows affected by the last SQL statement; No, the FOR loop opens and closes the cursor automatically.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?