With the exception of ROWCOUNT, which returns an integer,
cursor attributes are Boolean type expressions that provide information about the state of a cursor.
Some attributes only return TRUE or FALSE in case of %FOUND, %NOTFOUND, %ISOPEN attributes.
Cursor Attributes
The use of cursor attributes is almost same between Oracle and Altibase except two attributes listed in the below.
Oracle
Altibase
Remarks
%FOUND
%FOUND
Before the first fetch from an open cursor, it returns null.
It returns TRUE if the last fetch returned a row,
or FALSE if the last fetch failed to return a row.
%NOTFOUND
%NOTFOUND
Before the first fetch from an open cursor returns null.
it returns FALSE if the last fetch returned a row.
TRUE if the last fetch failed to return a row.
%ISOPEN
%ISOPEN
whether a cursor is open or not.
%ROWCOUNT
%ROWCOUNT
how many rows have been
fetched so far.
%BULK_ROWCOUNT
This is useful with forall statement.
This attribute acts like an index-by table. Its element stores the number of rows processed by the ith
execution of an UPDATE or DELETE statement.
%BULK_EXCEPTIONS
An associative array that stores information about any exceptions encountered by a
forall statement that uses the SAVE EXCEPTIONS clause.
Example
Oracle
Altibase
Comments
DECLARE
TYPE empcurtyp IS REF CURSOR
RETURN employees%ROWTYPE;
emp_cv empcurtyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN -- open cursor variable
OPEN emp_cv FOR SELECT * FROM employees;
END IF;
CLOSE emp_cv;
END;
/
CREATE TYPESET empcurtypeset
AS
TYPE empcurtyp IS REF CURSOR;
END;
/
CREATE OR REPLACE PROCEDURE get_employee
AS
TYPE MY_CUR IS REF CURSOR;
emp_cv empcurtypeset.empcurtyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN -- open cursor variable
OPEN emp_cv FOR 'SELECT * FROM employees';
END IF;
CLOSE emp_cv;
END;
/
Fetching from a Cursor Variable into a Record using %ISOPEN attributes
In Altibase, Between the creation of typeset and type and the creation of procedure using ref cursor should be separated unlikely in Oracle.
DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv empcurtyp;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv FOR SELECT * FROM employees WHERE employee_id < 120;
LOOP
FETCH emp_cv INTO emp_rec; -- fetch from cursor variable
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
DBMS_OUTPUT.PUT_LINE('Name = ' || emp_rec.first_name || ' ' ||
emp_rec.last_name);
END LOOP;
CLOSE emp_cv;
END;
/
CREATE OR REPLACE TYPESET empcurtypeset
AS
TYPE empcurtyp IS REF CURSOR;
END;
/
CREATE OR REPLACE PROCEDURE fetch_employee
AS
TYPE empcurtyp IS REF CURSOR;
emp_cv empcurtypeset.empcurtyp;
emp_rec employees%ROWTYPE;
stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'webmaster';
BEGIN
OPEN emp_cv FOR 'SELECT * FROM employees WHERE eno < 120';
LOOP
FETCH emp_cv INTO emp_rec; -- fetch from cursor variable
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
SYSTEM_.PRINTLN('Name = ' || emp_rec.e_firstname || ' ' || emp_rec.e_lastname);
END LOOP;
CLOSE emp_cv;
END;
/
Fetching from a Cursor Variable into a Record %ROWTYPE attributes
CREATE TYPE emp_ary AS VARRAY(50) OF varchar2(4000);
CREATE TYPESET typeset_1
AS
TYPE emp_rec_type IS RECORD(
Name VARCHAR(20),
Job_id VARCHAR(10),
Salary NUMBER(8));
TYPE emp_arr_type is TABLE OF emp_rec_type INDEX BY INTEGER;
END;/
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;
/
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;
/
%ROWCOUNT attribute indicates how many rows are fetched using cursor.
