Cursors
SQL Server UsageA 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:
SyntaxDECLARE <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>; ExamplesProcess 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 and Cursors (Transact-SQL) in the SQL Server documentation. PostgreSQL UsageSimilar 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 ExamplesDeclare a Cursor The following table includes the
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:
DECLARE c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1; Declare a backward-scrolling compatible Cursor using the
DECLARE c3 SCROLL CURSOR FOR SELECT id, name FROM employees; Open a Cursor The 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
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 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.
The PL/pgSQL Fetch the values returned from the 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 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
For more information, see FETCH in the PostgreSQL documentation. What is for update clause in cursors?The FOR UPDATE clause is an optional part of a SELECT statement. Cursors are read-only by default. The FOR UPDATE clause specifies that the cursor should be updatable, and enforces a check during compilation that the SELECT statement meets the requirements for an updatable cursor.
Which of the following cursor statements when executed populates the rows in the cursor?The OPEN statement populates the result set, and FETCH returns a row from the result set.
Which attribute of cursor is used to display the number of rows in the cursor?%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far.
Which option can be used with the query of the cursor to obtain a lock on the rows opened by the cursor?You can use the FOR UPDATE clause in a SELECT against multiple tables. In this case, rows in a table are locked only if the FOR UPDATE clause references a column in that table.
|
