Cursors
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Different cursor options. |
SQL Server Usage
A set is a fundamental concept of the relation data model from which SQL is derived. SQL is a declarative language that operates on whole sets, unlike most procedural languages that operate on individual data elements. A single invocations of an SQL statements can return a whole set or modify millions of rows.
Many developers are accustomed to using procedural or imperative approaches to develop solutions that are difficult to implement using set-based querying techniques. Also, operating on row data sequentially may be a more appropriate approach in certain situations.
Cursors provide an alternative mechanism for operating on result sets. Instead of receiving a table object containing rows of data, applications can use cursors to access the data sequentially, row-by-row. Cursors provide the following capabilities:
-
Positioning the cursor at specific rows of the result set using absolute or relative offsets.
-
Retrieving a row, or a block of rows, from the current cursor position.
-
Modifying data at the current cursor position.
-
Isolating data modifications by concurrent transactions that affect the cursor’s result.
-
T-SQL statements can use cursors in scripts, stored procedures, and triggers.
Syntax
DECLARE <Cursor Name> CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR <SELECT statement> [ FOR UPDATE [ OF <Column List>]][;]
FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE <Value> | RELATIVE <Value>] FROM <Cursor Name> INTO <Variable List>;
Examples
Process data in a cursor.
DECLARE MyCursor CURSOR FOR SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Col1 = T2.Col1; OPEN MyCursor; DECLARE @VarCursor1 VARCHAR(20); FETCH NEXT FROM MyCursor INTO @VarCursor1; WHILE @@FETCH_STATUS = 0 BEGIN EXEC MyPRocessingProcedure @InputParameter = @VarCursor1; FETCH NEXT FROM product_cursor INTO @VarCursor1; END CLOSE MyCursor; DEALLOCATE MyCursor ;
For more information, see SQL Server Cursors
PostgreSQL Usage
Similar to T-SQL Cursors in SQL Server, PostgreSQL has PL/pgSQL cursors that you can use 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.
Examples
Declare a Cursor
The following table includes the DECLARE..CURSOR
options that are Transact-SQL extended syntax have no equivalent in PostgreSQL.
SQL Server option | Use | Comments |
---|---|---|
|
Defining that |
Using |
|
Cursor will make a temporary copy of the data. |
For small data sets temporary tables can be created and declare a cursor that will select these tables. |
|
Determining that membership and order of rows in the cursor are fixed. |
N/A |
|
Cursor will reflect all data changes made on the selected rows. |
Default for PostgreSQL. |
|
Will use |
N/A |
|
Determine that positioned updates or deletes made by the cursor are guaranteed to succeed. |
N/A |
|
Determine that positioned updates or deletes made by the cursor will not succeed if the rows has been updated. |
N/A |
|
Will send warning messages to the client if the cursor is implicitly converted from the requested type. |
N/A |
Declare a Cursor in PL/pgSQL to be used with any query. The variable c1 is unbounded because it isn’t bound to any particular query.
DECLARE c1 refcursor;
Declare a Cursor in PL/pgSQL with a bounded query.
DECLARE c2 CURSOR FOR SELECT * FROM employees;
Declare a Cursor with a parametrized bound query:
-
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 c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;
Declare a backward-scrolling compatible Cursor using the SCROLL
option.
-
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
orFOR SHARE
.
DECLARE c3 SCROLL CURSOR FOR SELECT id, name FROM employees;
Open a Cursor
The OPEN
command is fully compatible between SQL Server and PostgreSQL.
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;
You can insert parameter values into the dynamic command with format()
and USING
. For example, the table name is inserted into the query with format()
. The comparison value for col1
is inserted with a USING
parameter.
Open a Cursor that was bound to a query when the cursor was declared and 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 wasn’t declared to take arguments, you can specify the arguments outside the cursor.
DO $$ DECLARE var1 integer; c3 CURSOR FOR SELECT * FROM employees where id = var1; BEGIN var1 := 1; OPEN c3; END$$;
Fetch a Cursor
Use the following syntax to fetch a cursor.
FETCH [ direction [ FROM | IN ] ] cursor_name
The following table shows additional PostgreSQL options as a direction for the FETCH command.
PostgreSQL option | Use |
---|---|
ALL |
Get all remaining rows |
FORWARD |
Same as NEXT |
FORWARD |
(n) Fetch the next n rows |
FORWARD |
ALL Same as ALL |
BACKWARD |
Same as PRIOR |
BACKWARD |
(n) Fetch the prior n rows |
BACKWARD |
ALL Fetch all prior rows |
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 data types.
DO $$ DECLARE c3 CURSOR FOR SELECT id, name FROM employees; emp_id integer; emp_name varchar; BEGIN OPEN c3; FETCH FROM 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 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$$;
Summary
Feature | SQL Server | Aurora PostgreSQL |
---|---|---|
Cursor options |
[FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] |
[ BINARY ] [ INSENSITIVE ] [ [ NO ]
SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ]
|
Updateable cursors |
DECLARE CURSOR... FOR UPDATE |
DECLARE cur_name CURSOR... FOR UPDATE |
Cursor declaration |
DECLARE CURSOR |
DECLARE cur_name CURSOR |
Cursor open |
OPEN |
OPEN |
Cursor fetch |
FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE | RELATIVE |
FETCH [ direction [ FROM | IN ] ] cursor_name The direction can be empty or one of the following: |
Cursor close |
CLOSE |
CLOSE |
Cursor deallocate |
DEALLOCATE |
Same effect as CLOSE (not required) |
Cursor end condition |
|
Not supported |
For more information, see FETCH