Lernmaterialien

Cursor

A cursor in Oracle PL/SQL is a handle (a pointer to a private memory area) that lets you work with the rows returned by a SQL query—especially when a query returns more than one row.

When Oracle runs a SELECT, it creates a result set in memory. A cursor is how PL/SQL can fetch those rows one at a time (or in bulk), so you can process them procedurally.

Explicit cursors (you declare them)

You use these when a SELECT may return multiple rows and you want to loop through them.

Lifecycle:

1. DECLARE the cursor (the query)

2. OPEN it

3. FETCH rows into variables

4. CLOSE it

Example:

DECLARE
  CURSOR c_emp IS
    SELECT employee_id, last_name
    FROM employees
    WHERE department_id = 10;

  v_id   employees.employee_id%TYPE;
  v_name employees.last_name%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_id, v_name;
    DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name);
  END LOOP;
  CLOSE c_emp;
END;
/

Cursor FOR LOOP (common and cleaner)

PL/SQL handles OPEN/FETCH/CLOSE for you:

BEGIN
  FOR r IN (SELECT employee_id, last_name
            FROM employees
            WHERE department_id = 10)
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ' ' || r.last_name);
  END LOOP;
END;
/

Why cursors matter

• SQL is set-based (works on many rows at once).

• PL/SQL is procedural (often processes row-by-row).

• Cursors bridge that gap.

If you tell me whether you want the explanation focused on teaching (conceptual) or coding patterns (FOR loop vs explicit cursor vs bulk collect), I can tailor examples.

Sample

PLSQL

CREATE OR REPLACE PROCEDURE GRIESMAYER_TRANSFER(
    p_ACCOUNT_ID_FROM IN NUMBER,
    p_ACCOUNT_ID_TO   IN NUMBER,
    p_BALANCE         IN NUMBER,
    p_TRANSFER_ID     OUT NUMBER)
AS
    ...
BEGIN
    ...
END;

This procedure will handle a transfer from one account (p_ACCOUNT_ID_FROM) t another account (p_ACCOUNT_ID_TO).

Table

Cursor

SET SERVEROUTPUT ON;
SET AUTOCOMMIT OFF;
       
       
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
   FOR todo_record IN (
            SELECT *
            FROM   TRANSFERS_TODO
            WHERE  TODO_STATUS = 'OPEN')
   LOOP

      GRIESMAYER_TRANSFER(todo_record.ACCOUNT_ID_FROM,
                          todo_record.ACCOUNT_ID_TO,
                          todo_record.BALANCE,
                          p_TRANSFER_ID));
      
      UPDATE GRIESMAYER_TRANSFERS_TODO
      SET    TODO_STATUS = 'DONE'
      WHERE  TRANSFERS_TODO_ID = todo_record.TRANSFERS_TODO_ID;
      
   END LOOP;
   
   COMMIT;
END;