SQL Cursors
Mon, 03/09/2009 - 21:59 | 
hdanzl
Cursors in SQL allow the developer/user to iterate row by row through SQL result sets.
Example
Here's a small example of cursors in action:
DROP TABLE IF EXISTS table2;
CREATE TABLE table1 (f1 INT, f2 VARCHAR(10));
CREATE TABLE table2 (f1 INT, f2 VARCHAR(10));
INSERT INTO table1 VALUES (1,'r1'), (2,'r2'), (3,'r3');
DECLARE myvar1 INT;
DECLARE myvar2 VARCHAR(10);
DECLARE acursor CURSOR FOR SELECT f1, f2 FROM table1;
OPEN acursor;
FETCH FIRST FROM acursor INTO myvar1, myvar2;
WHILE @@FETCH_STATUS = 0 DO
INSERT INTO table2 VALUES (myvar1, myvar2);
FETCH NEXT FROM acursor INTO myvar1, myvar2;
END WHILE;
CLOSE acursor;
So the first thing we need to do is to declare a variable of type cursor and associate it with a sql result set. Then we need to open the cursor, which will execute the associated query. After that the cursor will be positioned before the first row. To get the first record we need to do FETCH NEXT, which can optionally read the row values into local variables. Now naturally we can already be at the end of a result set (it would be empty in that case), so we need to check the result of the FETCH operation. NexusDB defines the system variable @@FETCH_STATUS which has statement scope and returns 0 for no error or a value <> 0 if EOF, BOF or any other error condition. If @@FETCH_STATUS<>0 then the variables that were passed as target for the values are undefined. Finally we also need to close the cursor to release the resources.
In the example above after the first FETCH will set FETCH_STATUS to 0, myvar1 will be 1 and myvar2 will be 'r1'.
BNF
Of course there's not only support for FETCH NEXT. Please look at the full syntax here:
OPEN cursorname;
CLOSE cursorname;
FETCH [ [ <fetch orientation> ] FROM ] cursorname INTO <fetch target list>
<fetch orientation> ::= NEXT | PRIOR | FIRST | LAST | { ABSOLUTE | RELATIVE} <offset>;
The future
The initial release will see read-only cursors, but we're looking into extending that down the road with updateable cursors. Immediately I want to state the obvious, that this will have similar restrictions to the RequestLive property in TnxQuery: not all sql statements can be updateable, but we will endeavor to support as many cases as possible.
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
Site Updates
- Jul 24 2024 - 1:53pm
- Jul 24 2024 - 1:22pm
- Apr 10 2024 - 10:19am
- Jan 28 2024 - 7:30pm
- Jan 25 2024 - 9:10am
First FETCH should be "FIRST"
When using the cursor several times (in a procedure for example) doing FETCH NEXT after then OPEN cursor will work only the first time. This (seems to) work always :
OPEN acursor;
FETCH FIRST FROM acursor INTO myvar1, myvar2;
WHILE @@FETCH_STATUS = 0 DO
INSERT INTO table2 VALUES (myvar1, myvar2);
FETCH NEXT FROM acursor INTO myvar1, myvar2;
END WHILE;
CLOSE acursor;
(this is/was for 3.0800)