Cursors

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		Cursors	`TYPE … IS REF CURSOR` isn’t supported by PostgreSQL. Minor differences in syntax may require some code rewrite. PostgreSQL doesn’t support `%ISOPEN`, `%BULK_EXCEPTIONS`, and `%BULK_ROWCOUNT`.

Four star feature compatibility

Three star automation level

TYPE … IS REF CURSOR isn’t supported by PostgreSQL.

Minor differences in syntax may require some code rewrite.

PostgreSQL doesn’t support %ISOPEN, %BULK_EXCEPTIONS, and %BULK_ROWCOUNT.

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:

Implicit cursors are session cursors constructed and managed by PL/SQL automatically without being created or defined by a user. PL/SQL opens an implicit cursor each time you run a SELECT or DML statement. Implicit cursors are also called SQL cursors.
Explicit cursors are session cursors created, constructed, and managed by a user. Cursors are declared and defined by naming it and associating it with a query. Unlike an implicit cursor, you can reference an explicit cursor using its name. An explicit cursor is called a named cursor.

Examples

The following examples demonstrate cursor usage:

Define an explicit PL/SQL cursor named c1.
The cursor runs an SQL statement to return rows from the database.
The PL/SQL loop reads data from the cursor, row by row, and stores the values into two variables: v_lastname and v_jobid.
The loop uses the %NOTFOUND attribute to terminate when the last row is read from the database.

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;

Define an implicit PL/SQL cursor using a FOR Loop.
The cursor runs a query and stores values returned into a record.
A loop iterates over the cursor data set and prints the result.

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.

PostgreSQL Usage

Similar to Oracle PL/SQL cursors, PostgreSQL has PL/pgSQL cursors that enable you to iterate business logic on rows read from the database. They can encapsulate the query and read the query results a few rows at a time. All access to cursors in PL/pgSQL is performed through cursor variables, which are always of the refcursor data type.

Create a PL/pgSQL cursor by declaring it as a variable of type refcursor.

Examples of Declare a Cursor

Declare a cursor in PL/pgSQL to be used with any query.

DECLARE c1 refcursor;

The variable c1 is unbound since it isn’t bound to any particular query.

Declare a cursor in PL/pgSQL with a bound query.

DECLARE c2 CURSOR FOR SELECT * FROM employees;

In the following example, you can replace FOR with IS for Oracle compatibility. Declare a cursor in PL/pgSQL to be used with any query.

DECLARE c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;

The id variable is replaced by an integer parameter value when the cursor is opened.
When declaring a cursor with SCROLL specified, the cursor can scroll backwards.
If NO SCROLL is specified, backward fetches are rejected.

Declare a backward-scrolling compatible cursor using the SCROLL option.

DECLARE c3 SCROLL CURSOR FOR SELECT id, name FROM employees;

SCROLL specifies that rows can be retrieved backwards. NO SCROLL specifies that rows can’t be retrieved backwards.
Depending upon the complexity of the run plan for the query, SCROLL might create performance issues.
Backward fetches aren’t allowed when the query includes FOR UPDATE or FOR SHARE.

Examples of Open a Cursor

Open a Cursor variable that was declared as Unbound and specify the query to run.

OPEN c1 FOR SELECT * FROM employees WHERE id = emp_id;

Open a cursor variable that was declared as Unbound and specify the query to run as a string expression. This approach provides greater flexibility.

OPEN c1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

Parameter values can be inserted into the dynamic command using format() and USING. For example, the table name is inserted into the query using format(). The comparison value for col1 is inserted using a USING parameter.

Open a cursor that was bound to a query when the cursor was declared and that was declared to take arguments.

DO $$
DECLARE
  c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;
BEGIN
  OPEN c3(var1 := 42);
END$$;

For the c3 cursor, supply the argument value expressions. If the cursor was not declared to take arguments, the arguments can be specified outside the cursor.

DO $$
DECLARE
  var1 integer;
  c3 CURSOR FOR SELECT * FROM employees where id = var1;
BEGIN
  var1 := 1;
  OPEN c3;
END$$;

Examples of Fetch a Cursor

The PL/pgSQL FETCH command retrieves the next row from the cursor into a variable. Fetch the values returned from the c3 cursor into a row variable.

DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
  OPEN c3;
  FETCH c3 INTO rowvar;
END$$;

Fetch the values returned from the c3 cursor into two scalar datatypes.

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH c3 INTO emp_id, emp_name;
END$$;

PL/pgSQL supports a special direction clause when fetching data from a cursor using the NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD arguments. Omitting direction is equivalent to as specifying NEXT. For example, fetch the last row from the cursor into the declared variables.

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
END$$;

For more information, see FETCH in the PostgreSQL documentation.

Example of Close a Cursor

Close a PL/pgSQL cursor using the CLOSE command.

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
  CLOSE c3;
END$$;

Example of Iterating Through a Cursor

PL/pgSQL supports detecting when a cursor has no more data to return and can be combined with loops to iterate over all rows of a cursor reference.

The following PL/pgSQL code uses a loop to fetch all rows from the cursor and then exit after the last record is fetched (using EXIT WHEN NOT FOUND).

PL/pgSQL supports detecting when a cursor has no more data to return and can be combined with loops to iterate over all rows of a cursor reference.

The following PL/pgSQL code uses a loop to fetch all rows from the cursor and then exit after the last record is fetched (using EXIT WHEN NOT FOUND).

DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
OPEN c3;
  LOOP
    FETCH FROM c3 INTO rowvar;
    EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE c3;
END$$;

Example of Move a Cursor Without Fetching Data

MOVE repositions a cursor without retrieving any data and works such as the FETCH command, except it only repositions the cursor in the dataset and doesn’t return the row to which the cursor is moved. The special variable FOUND can be checked to determine if there is a next row.

Move to the last row (null or no data found) for cursor c3.

MOVE LAST FROM c3;

Move the cursor two records back.

MOVE RELATIVE -2 FROM c3;

Move the c3 cursor two records forward.

MOVE FORWARD 2 FROM c3;

Example of Update or Delete Current

When a cursor is positioned on a table row, that row can be updated or deleted. There are restrictions on what the cursor’s query can select for this type of DML to succeed.

For example, the current row to which the C3 cursor is pointed to is updated.

UPDATE employee SET salary = salary*1.2 WHERE CURRENT OF c3;

Example of Use an Implicit Cursor (FOR Loop Over Queries)

DO $$
DECLARE
  item RECORD;
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
    RAISE NOTICE 'Name = %, Job=%', item.last_name, item.job_id;
  END LOOP;
END $$;

Summary

Action	Oracle PL/SQL	PostgreSQL PL/pgSQL
Declare a bound explicit cursor	CURSOR c1 IS SELECT * FROM employees;	c2 CURSOR FOR SELECT * FROM employees;
Open a cursor	OPEN c1;	OPEN c2;
Move Cursor to next row and fetch into a record variable (rowvar was declared in the DECLARE section)	FETCH c1 INTO rowvar;	FETCH c2 INTO rowvar;
Move Cursor to next row and fetch into multiple scalar data types (emp_id, emp_name, salary was declared in the DECLARE section)	FETCH c1 INTO emp_id, emp_name, salary;	FETCH c2 INTO emp_id, emp_name, salary;
Iterate through an implicit cursor using a loop	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;	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;
Declare a cursor with variables	CURSOR c1 (key NUMBER) IS SELECT * FROM employees WHERE id = key;	C2 CURSOR (key integer) FOR SELECT * FROM employees WHERE id = key;
Open a cursor with variables	OPEN c1(2);	OPEN c2(2); or OPEN c2(key := 2);
Exit a loop after no data found	EXIT WHEN c1%NOTFOUND;	EXIT WHEN NOT FOUND;
Detect if a cursor has rows remaining in its dataset	%FOUND	FOUND
Determine how many rows were affected from any DML statement	%BULK_ROWCOUNT	Not Supported but you can run with every DML `GET DIAGNOSTICS integer_var = ROW_COUNT`; and save the results in an array
Determine which DML run failed with the relevant error code	%BULK_EXCEPTIONS	N/A
Detect if the Cursor is open	%ISOPEN	N/A
Detect if a Cursor has no rows remaining in its dataset	%NOTFOUND	NOT FOUND
Returns the number of rows affected by a cursor	%ROWCOUNT	GET DIAGNOSTICS integer_var = ROW_COUNT;

For more information, see Cursors and Basic Statements in the PostgreSQL documentation.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Oracle Anonymous Block and PostgreSQL DO

Oracle DBMS_OUTPUT and PostgreSQL RAISE