Overview
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.
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. |