Cursors

Oracle Usage

PL/SQL cursors are pointers to data sets on which application logic can iterate. The data sets hold rows returned by SQL statements. You can refer to the active data set in named cursors from within a program.

There are two types of PL/SQL cursors:

Examples

The following examples demonstrate cursor usage:

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
    ORDER BY last_name;
    v_lastname employees.last_name%TYPE; -- variable to store last_name
    v_jobid employees.job_id%TYPE; -- variable to store job_id
  BEGIN
    OPEN c1;
    LOOP -- Fetches 2 columns into variables
      FETCH c1 INTO v_lastname, v_jobid;
      EXIT WHEN c1%NOTFOUND;
    END LOOP;
  CLOSE c1;
END;

BEGIN
FOR item IN
  (SELECT last_name, job_id FROM employees WHERE job_id LIKE '%MANAGER%'
    AND manager_id > 400 ORDER BY last_name) LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

For more information, see Explicit Cursor Declaration and Definition and Implicit Cursor Attribute in the Oracle documentation.

MySQL Usage

Aurora MySQL supports cursors only within stored routines, functions and stored procedures.

Unlike Oracle, which offers an array of cursor types, Aurora MySQL Cursors have the following characteristics:

Cursor declarations must appear before handler declarations and after variable and condition declarations.

Similar to Oracle, cursors are declared with the DECLARE CURSOR, opened with OPEN, fetched with FETCH, and closed with CLOSE.

Declare Cursor

DECLARE <Cursor Name> CURSOR FOR <Cursor SELECT Statement>

The DECLARE CURSOR statement instantiates a cursor object and associates it with a SELECT statement. This SELECT is then used to retrieve the cursor rows.

To fetch the rows, use the FETCH statement. As mentioned before, only FETCH NEXT is supported. The number of output variables specified in the FETCH statement must match the number of columns retrieved by the cursor.

Aurora MySQL cursors have additional characteristics:

Open Cursor

OPEN <Cursor Name>;

The OPEN command populates the cursor with the data, either dynamically or in a temporary table, and readies the first row for consumption by the FETCH statement.

Fetch Cursor

FETCH [[NEXT] FROM] <Cursor Name> INTO <Variable 1> [,<Variable n>]

The FETCH statement retrieves the current pointer row, assigns the column values to the variables listed in the FETCH statement, and advances the cursor pointer by one row. If the row is not available, meaning the cursor has been exhausted, a No Data condition is raised with an SQLSTATE value of '0200000'. To catch this condition, or the alternative NOT FOUND condition, you must create a condition handler.

Close Cursor

CLOSE <Cursor Name>;

The CLOSE statement closes an open cursor. If the cursor with the specified name does not exist, an error is raised. If a cursor is not explicitly closed, Aurora MySQL closes it automatically at the end of the BEGIN …​ END block in which it was declared.

Examples

The following example uses a cursor to iterate over source rows and merges into an OrderItems table.

Create an OrderItems table.

CREATE TABLE OrderItems(OrderID INT NOT NULL,
  Item VARCHAR(20) NOT NULL,
  Quantity SMALLINT NOT NULL,
  PRIMARY KEY(OrderID, Item));

Create and populate the SourceTable.

CREATE TABLE SourceTable (
  OrderID INT,
  Item VARCHAR(20),
  Quantity SMALLINT,
  PRIMARY KEY (OrderID, Item));

INSERT INTO SourceTable (
  OrderID, Item, Quantity)
VALUES (1, 'M8 Bolt', 100),
  (2, 'M8 Nut', 100),
  (3, 'M8 Washer', 200);

Create a procedure to loop through SourceTable and insert rows.

CREATE PROCEDURE LoopItems()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE var_OrderID INT;
  DECLARE var_Item VARCHAR(20);
  DECLARE var_Quantity SMALLINT;
  DECLARE ItemCursor CURSOR FOR SELECT OrderID, Item, Quantity FROM SourceTable;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN ItemCursor;
  CursorStart: LOOP
  FETCH NEXT FROM ItemCursor INTO var_OrderID, var_Item, var_Quantity;
  IF Done THEN LEAVE CursorStart;
  END IF;
    INSERT INTO OrderItems (OrderID, Item, Quantity)
      VALUES (var_OrderID, var_Item, var_Quantity);
  END LOOP;
  CLOSE ItemCursor;
END;

Run the stored procedure.

CALL LoopItems();

Select all rows from the OrderItems table.

SELECT * FROM OrderItems;

OrderID  Item       Quantity
1        M8 Bolt    100
2        M8 Nut     100
3        M8 Washer  200

Summary

CURSOR c1 IS
SELECT * FROM employees;

DECLARE c1 CURSOR
FOR SELECT *
  FROM employees;

OPEN c1;

OPEN c1;

FETCH c1 INTO rowvar;

FETCH NEXT FROM c1 INTO rowvar;

FETCH c1
INTO emp_id, emp_name, salary;

FETCH NEXT FROM c1 INTO emp_id,
emp_name, salary;

FOR item IN (
  SELECT last_name, job_id FROM employees
  WHERE job_id LIKE '%CLERK%'
  AND manager_id > 120 ORDER BY last_name )
  LOOP
    << do something
    >>
  END LOOP;

CURSOR c1 (key NUMBER)
IS SELECT * FROM employees
WHERE id = key;

SET @sqltext1 := CONCAT
('DECLARE c1 CURSOR FOR
SELECT * FROM employees WHERE
id =',key);
PREPARE stmt1 FROM @sqltext1;
EXECUTE stmt1;

OPEN c1(2);

EXIT WHEN c1%NOTFOUND;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = TRUE;
And in the fetching loop insert:
IF done THEN LEAVE;

%FOUND

%BULK_ROWCOUNT

%BULK_EXCEPTIONS

%ISOPEN

%NOTFOUND

%ROWCOUNT

For more information, see Cursors in the MySQL documentation.