5. Using Cursors#
This chapter describes how to manage and use cursors.
Overview#
There are two ways of reading table records within a stored procedure: using the SELECT INTO statement.
The SELECT INTO statement can be used to read only a single record. If more than one record is returned by a SELECT INTO statement, an error will be raised. Therefore, in situations where it can be expected that more than one record will be returned, it is necessary to use a cursor
Declaring a Cursor#
A cursor must be explicitly declared in the declare section of a stored procedure block, along with the SELECT statement with which it is used. After it has been declared, a cursor can be managed in one of the following two ways:
-
Cursor Management Using OPEN, FETCH, CLOSE
-
Cursor management Using a Cursor FOR LOOP
Cursor Management Using OPEN, FETCH, and CLOSE#
A cursor can be controlled in the block body using the OPEN, FETCH, and CLOSE statements. The OPEN statement is used to initialize the cursor. The FETCH statement is then executed repeatedly to retrieve rows. Finally, the cursor is released using the CLOSE statement.
OPEN#
This statement is used to initialize all of the resources that are necessary in order to use a cursor. If user-defined parameters were specified when the cursor was defined, they are passed to the cursor using the OPEN statement.
FETCH#
The FETCH statement is used to retrieve one record at a time from the set of results that satisfy the cursor's SELECT statement and store it in one or more variables. Each column can be stored in a separate variable, or the entire row can be stored in a RECORD type variable, typically declared using %ROWTYPE, having the same number and type of fields as the retrieved record.
For an explanation of RECORD type variables, please refer to the Chapter 6: User-Defined Types.
CLOSE#
This is the step of releasing the resources allocated to a cursor that is no longer in use. A cursor must be closed before the procedure or function in which the cursor was declared is terminated.
Cursor Management Using a Cursor FOR LOOP#
This is the type of loop that executes all of the OPEN, FETCH, and CLOSE statements. Iteration of the loop continues until there are no more records left to process. This statement is convenient to use because it obviates the need to use explicit OPEN and CLOSE statements.
CURSOR#
Syntax#
Purpose#
The CURSOR statement is used to declare a cursor. It must specify the name of the cursor and the SELECT statement that the cursor uses to retrieve records.
cursor_name#
This is the name of the cursor, which is referenced in the OPEN, FETCH, CLOSE, and Cursor FOR LOOP statements.
cursor_parameter_declaration#
In cases where it is necessary to use parameters with a cursor's SELECT statement, they can be defined for the cursor in the same way that they are for stored procedures.
The following limitations apply to the use of parameters with cursors:
-
Cursor parameters can be used only within SELECT statements
-
The use of %ROWTYPE is not supported.
-
Cursor parameters cannot be OUT or IN/OUT parameters
A value is assigned to a cursor parameter using an OPEN CURSOR or CURSOR FOR statement. This value is used when the cursor's SELECT statement is executed.
DECLARE
CURSOR c1 IS
SELECT empno, ename, job, sal
FROM emp
WHERE sal > 2000;
CURSOR c2
(low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS
SELECT ......;
data_type#
Please refer to "Declaring Local Variables" "in Chapter 3 of this manual.
Example#
CREATE TABLE highsal
(eno INTEGER, e_firstname CHAR(20), e_lastname CHAR(20), salary NUMBER(10,2));
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DECLARE
CURSOR c1 IS
SELECT eno, e_firstname, e_lastname, salary FROM employees
WHERE salary IS not NULL
ORDER BY salary desc;
emp_first CHAR(20);
emp_last CHAR(20);
emp_no INTEGER;
emp_sal NUMBER(10,2);
BEGIN
OPEN c1;
FOR i IN 1 .. 5 LOOP
FETCH c1 INTO emp_no, emp_first, emp_last, emp_sal;
EXIT WHEN c1%NOTFOUND;
INSERT INTO highsal VALUES(emp_no, emp_first, emp_last, emp_sal);
END LOOP;
CLOSE c1;
END;
END;
/
iSQL> EXEC proc1;
EXECUTE success.
iSQL> SELECT * FROM highsal;
ENO E_FIRSTNAME E_LASTNAME SALARY
-------------------------------------------------------------------------
10 Elizabeth Bae 4000
11 Zhen Liu 2750
5 Farhad Ghorbani 2500
16 Wei-Wei Chen 2300
14 Yuu Miura 2003
5 rows selected.
OPEN#
Syntax#
Purpose#
This statement is used to initialize a cursor, execute the query, and determine the result set, so that data can be retrieved using the FETCH statement. When this statement is executed, the system will allocate all resources required to use the cursor. If an attempt is made to open a cursor that is already opened, a CURSOR_ALREADY_OPEN error will be raised.
cursor_name#
This is the name of the cursor to open.
A cursor having this name must have been declared in the declare section of the current block or an outer block.
cursor_parameter_name#
Parameters can be optionally specified for a cursor. These parameters can be used in the associated query in place of constants or local variables.
If the cursor has parameters, they are declared as shown below:
DECLARE
CURSOR c1(pname VARCHAR(40), pno INTEGER) IS
SELECT empno, ename, job, sal
FROM emp
WHERE eame = pname;
BEGIN
OPEN c1;
......
END;
The OPEN statement is used as follows when parameter values are passed to a cursor.
OPEN c1(emp_name, 100);
OPEN c1('mylee', 100);
OPEN c1(emp_name, dept_no);
Example#
Example 1#
CREATE TABLE mgr
(mgr_eno INTEGER, mgr_first CHAR(20), mgr_last CHAR(20), mgr_dno SMALLINT);
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DECLARE
CURSOR emp_cur IS
SELECT eno, e_firstname, e_lastname, dno FROM employees
WHERE emp_job = 'manager';
emp_no employees.eno%TYPE;
emp_first employees.e_firstname%TYPE;
emp_last employees.e_lastname%TYPE;
emp_dno employees.dno%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_no, emp_first, emp_last, emp_dno;
EXIT WHEN emp_cur%NOTFOUND;
INSERT INTO mgr VALUES(emp_no, emp_first, emp_last, emp_dno);
END LOOP;
CLOSE emp_cur;
END;
END;
/
iSQL> EXEC proc1;
Execute success.
iSQL> select * from mgr;
MGR.MGR_ENO MGR.MGR_FIRST MGR.MGR_LAST MGR.MGR_DNO
-------------------------------------------------------------------------
7 Gottlieb Fleischer 4002
8 Xiong Wang 4001
16 Wei-Wei Chen 1001
3 rows selected.
Example 2#
CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER);
CREATE TABLE t2(i1 INTEGER, i2 INTEGER, i3 INTEGER);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,2,2);
INSERT INTO t1 VALUES(30,30,30);
INSERT INTO t1 VALUES(50,50,50);
CREATE OR REPLACE PROCEDURE proc1
AS
CURSOR c1(k1 INTEGER, k2 INTEGER, k3 INTEGER) IS
SELECT * FROM t1
WHERE i1 <= k1 AND i2 <= k2 AND i3 <= k3;
BEGIN
FOR rec1 IN c1(2,2,2) LOOP
INSERT INTO t2 VALUES (rec1.i1, rec1.i2, rec1.i3);
END LOOP;
END;
/
iSQL> SELECT * FROM t2;
T2.I1 T2.I2 T2.I3
----------------------------------------
No rows selected.
iSQL> EXEC proc1;
EXECUTE success.
iSQL> SELECT * FROM t2;
T2.I1 T2.I2 T2.I3
----------------------------------------
1 1 1
2 2 2
2 rows selected.
FETCH#
Syntax#
Purpose#
This statement is used to obtain one row from an open cursor and store the value(s) in the variable(s) specified in the INTO clause of the SELECT statement.
A list of variables that match the column types specified in the cursor's SELECT statement is specified. Alternatively, the name of a RECORD type variable is specified, and the row retrieved from the cursor is saved in the RECORD type variable.
The use of RECORD type varialbe in FETCH statement has the following restrictions:
- Only one RECORD type variable can be used to store one retrieved row.
- It must be possible to save all of the columns retrieved by the SELECT statement into the RECORD type variable.
- RECORD type variables cannot be combined with regular variables.
If an attempt is made to fetch results from a cursor that is not open, an INVALID_CURSOR error will occur.
cursor_name#
This is the name of the cursor to use to fetch records. A cursor having this name must have been declared in the declare section of the current block or an outer block.
record_name#
This is used to specify the name of the RECORD type variable into which the cursor's SELECT statement retrieves records. The RECORD type variable that is used must have the same number of columns as the SELECT statement's select list, and the column types must be compatible and specified in the corresponding order.
When retrieving all columns from a table, it is convenient to declare a RECORD type variable using the %ROWTYPE attribute for the table from which the records are to be retrieved.
variable_name#
This is the name of the variable into which a value will be stored. The number of such variables must be the same as the number of columns specified in the cursor's SELECT statement. Furthermore, the order of the variables must be set such that their types correspond with the respective types of the columns in the select list.
LOOP
FETCH c1 INTO my_name, my_empno, my_deptno;
EXIT WHEN c1%NOTFOUND;
END LOOP;
fetch_bulk_collect_clause#
Using a LIMIT clause enables adjusting the amount of lines returned in the BULK COLLECTION. Refer to the BULKC OLLECTION clause of the SELECT INTO statement for further information on the BULK COLLECT clause.
Example#
Example 1#
CREATE TABLE emp_temp(eno INTEGER, e_firstname CHAR(20), e_lastname CHAR(20));
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DECLARE
CURSOR c1 IS SELECT eno, e_firstname, e_lastname FROM employees;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
INSERT INTO emp_temp
VALUES(emp_rec.eno, emp_rec.e_firstname, emp_rec.e_lastname);
END LOOP;
CLOSE c1;
END;
END;
/
iSQL> select eno, e_firstname, e_lastname from emp_temp;
ENO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
3 Ken Kobain
.
.
.
18 John Huxley
19 Alvar Marquez
20 William Blake
20 rows selected.
Example 2#
iSQL> create table emp_temp(eno integer, e_firstname char(20), e_lastname char(20));
Create success.iSQL> select * from emp_temp;
EMP_TEMP.ENO EMP_TEMP.E_FIRSTNAME EMP_TEMP.E_LASTNAME
-------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
3 Ken Kobain
4 John Huxley
5 Alvar Marquez
6 William Blake
6 rows selected.
iSQL> create or replace procedure proc1 as
type emp_rec is record(eno integer, e_firstname char(20), e_lastname char(20));
type emp_arr is table of emp_rec index by integer;
cursor c1 is select * from emp_temp;
arr1 emp_arr;
begin
open c1;
loop
fetch c1 bulk collect into arr1 limit 4;
exit when c1%NOTFOUND;
println('count : '|| arr1.count());
end loop;
close c1;
end;
/
iSQL>exec proc1;
count : 4
count : 2
Execute success.
CLOSE#
Syntax#
Purpose#
This statement is used to close an open cursor and free all associated resources.
A cursor that has already been closed can be reopened using the OPEN statement. If an attempt is made to close a cursor that is already closed, a INVALID_CURSOR error will be raised.
If the user doesn't expressly close a cursor using this statement, the cursor is automatically closed upon exiting the block in which the cursor was declared. However, it is recommended that the user use this statement to expressly close a cursor immediately after the user has finished using it in order to return all associated resources to the system as early as possible.
cursor_name#
This is the name of the cursor to close.
Example#
CLOSE c1;
Cursor FOR LOOP#
Syntax#
Purpose#
The Cursor FOR LOOP construct automatically opens a cursor, fetches results, and closes the cursor.
A cursor FOR LOOP uses a cursor declared in the declare section of the block, and returns one of the rows retrieved by the query every time the loop iterates. The current record is saved in a RECORD type variable that can be accessed from within the loop.
label_ name#
This is used to specify a label for the loop, which will be necessary in order to designate the loop in an EXIT or CONTINUE statement.
counter_name#
This is used to specify the name of the RECORD type variable in which one row that was fetched using the cursor will be stored. This variable does not need to be declared in the declare section of the block, because it is automatically created such that the number of columns and the types of the columns match those of the fetched rows.
A variable created in this way is referenced using the syntax "counter_name.column_name". When referencing a variable in this way, column_name is the name of a column in the select list of the cursor's SELECT statement. Therefore, when an expression is used in the select list, an alias must be specified for the expression in the select list in order to allow the expression to be referenced in this way.
cursor_name#
This is used to specify the name of the cursor to use in the loop. This cursor must have been declared in the declare section of the current block or an outer block.
cursor_parameter_name#
Please refer to "cursor_parameter_name" in this chapter.
Example#
CREATE TABLE emp_temp(eno INTEGER, e_firstname CHAR(20), e_lastname CHAR(20));
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DECLARE
CURSOR c1 IS SELECT eno, e_firstname, e_lastname FROM employees;
BEGIN
FOR emp_rec IN c1 LOOP
INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.e_firstname, emp_rec.e_lastname);
END LOOP;
END;
END;
/
iSQL> EXEC proc1;
Execute success.
iSQL> SELECT * FROM emp_temp;
ENO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
.
.
.
19 Alvar Marquez
20 William Blake
20 rows selected.
Cursor Attributes#
Refer to attribute values managed by Altibase to understand the state of cursors during the execution of the cursor.
Purpose#
Purpose#
Cursor attributes are user-accessible. With the exception of ROWCOUNT, which returns an integer, cursor attributes are Boolean type expressions that provide information about the state of a cursor.
Based on the current state of the cursor, the value of each attribute can be TRUE or FALSE.
The user can check the values of attributes of cursors declared using the DECLARE statement, and can additionally check those of implicit cursors declared within the system. Implicit cursors exist for DELETE, UPDATE, and INSERT statements, as well as for the SELECT INTO statement, which returns one record. They contain the attribute values for the cursor pertaining to the most recently executed SQL statement.
%FOUND#
This attribute indicates whether any rows satisfying the condition in the cursor's SELECT statement have been found. Note however that the value of %FOUND is always FALSE in the following cases, regardless of whether or not any rows that satisfy the condition actually exist:
- A cursor that has not been opened
- A cursor for which a FETCH statement has never been executed
- A cursor that has been closed
For implicit cursors, if one or more records are affected by the execution of a DELETE, UPDATE, or INSERT statement, or if a SELECT INTO statement returns at least one record, the value of %FOUND for the associated cursor is TRUE.
However, if a SELECT INTO statement returns two or more records, the TOO_MANY_ROWS exception will occur before it is possible to check the value of the %FOUND attribute. Such a case should be handled as an exception rather than by referring to the %FOUND cursor attribute.
The value of the %FOUND attribute can be checked as follows:
DELETE FROM emp;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO emp VALUES ( ...... );
......
END IF;
%NOTFOUND#
This attribute is also used to check whether any rows that satisfy the condition in the cursor's SELECT statement have been found. It always has the opposite value of %FOUND.
If no records are affected by the result of execution of a DELETE, UPDATE or INSERT statement, or if a SELECT INTO statement does not return any records, the value of the %NOTFOUND attribute for the associated implicit cursor is TRUE.
However, if a SELECT INTO statement returns no records, the NO_DATA_FOUND exception will occur before it is possible to check the value of the %NOTFOUND attribute. Such a case should be handled as an exception rather than by referring to the %NOTFOUND cursor attribute.
The value of the %NOTFOUND attribute can be checked as follows:
DELETE FROM emp;
IF SQL%NOTFOUND THEN
......
END IF;
%ISOPEN#
The %ISOPEN attribute is used to check whether the cursor is open. If the cursor is closed, this value will be FALSE.
The value of the %ISOPEN attribute can be checked as follows:
OPEN c1; -- CURSOR OPEN
IF c1%ISOPEN THEN
......
END IF;
%ROWCOUNT#
%ROWCOUNT indicates how many rows have been fetched by the cursor at the present point in time.
Note that %ROWCOUNT does not indicate the number of records that satisfy the conditions in the cursor's SELECT statement. Rather, it increases by 1 whenever one row is fetched. If not even one row has been fetched, the value of %ROWCOUNT will be zero.
If this attribute is checked before a cursor is opened, or after it has been closed, an INVALID_CURSOR error will be returned.
DELETE FROM emp;
IF SQL%ROWCOUNT > 10 THEN
......
END IF;
Example#
Example 1#
CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER);
CREATE TABLE t3(i1 INTEGER);
INSERT INTO t1 VALUES(2,2,2);
CREATE OR REPLACE PROCEDURE proc1
AS
v1 INTEGER;
BEGIN
SELECT i1 INTO v1 FROM t1 WHERE i1 = 2;
IF SQL%found THEN
INSERT INTO t1 SELECT * FROM t1;
v1 := SQL%ROWCOUNT;
INSERT INTO t3 VALUES(v1);
END IF;
END;
/
iSQL> EXEC proc1;
Execute success.
iSQL> SELECT * FROM t3;
T3.I1
--------------
1
1 row selected.
Example 2#
CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER);
CREATE TABLE t2(i1 INTEGER, i2 INTEGER, i3 INTEGER);
CREATE TABLE t3(i1 INTEGER);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(1,1,1);
CREATE OR REPLACE PROCEDURE proc1
AS
CURSOR c1 IS SELECT * FROM t1;
v1 INTEGER;
v2 INTEGER;
v3 INTEGER;
BEGIN
OPEN c1;
IF c1%ISOPEN THEN
LOOP
FETCH c1 INTO v1, v2, v3;
IF c1%FOUND THEN
INSERT INTO t2 VALUES (v1, v2, v3);
ELSIF c1%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
END IF;
v1 := c1%ROWCOUNT;
INSERT INTO t3 VALUES (v1);
CLOSE c1;
END;
/
iSQL> EXEC proc1;
Execute success.
iSQL> SELECT * FROM t1;
T1.I1 T1.I2 T1.I3
----------------------------------------
1 1 1
1 1 1
1 1 1
3 rows selected.
iSQL> SELECT * FROM t2;
T2.I1 T2.I2 T2.I3
----------------------------------------
1 1 1
1 1 1
1 1 1
3 rows selected.
iSQL> SELECT * FROM t3;
T3.I1
--------------
3
1 row selected.
Example 3#
CREATE TABLE emp_temp(eno INTEGER, e_firstname CHAR(20), e_lastname CHAR(20));
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DECLARE
CURSOR c1 IS SELECT eno, e_firstname, e_lastname FROM employees;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%ROWCOUNT > 10 OR c1%NOTFOUND;
INSERT INTO emp_temp
VALUES(emp_rec.eno, emp_rec.e_firstname, emp_rec.e_lastname);
END LOOP;
CLOSE c1;
END;
END;
/
iSQL> EXEC proc1;
EXECUTE success.
iSQL> SELECT * FROM emp_temp;
ENO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
3 Ken Kobain
4 Aaron Foster
5 Farhad Ghorbani
6 Ryu Momoi
7 Gottlieb Fleischer
8 Xiong Wang
9 Curtis Diaz
10 Elizabeth Bae
10 rows selected.