Cursor attributes provide information about the cursor.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
The syntax for retrieving cursor attribute values is cursor_name%attribute, such as c1%NOTFOUND. The types of cursor attributes are as follows:
%FOUNDis a Boolean attribute. It returnsTRUEif the most recent fetch from the cursor returned a record.%NOTFOUNDis a Boolean attribute. It returnsTRUEif the most recent fetch from the cursor did not return a record.%ISOPENis a Boolean attribute. It returnsTRUEif the cursor is open.%ROWCOUNTis a numeric attribute. It returns the number of records fetched from the cursor.
Common cursor attributes and their return values
| Attribute | Declared cursor value | Implicit cursor value |
|---|---|---|
%FOUND |
If the cursor is open but no FETCH operation has been performed, it returns NULL. If the most recent FETCH operation returned a record, it returns TRUE. If the most recent FETCH operation did not return a record, it returns FALSE. |
If no DML or SELECT INTO statement has been executed, it returns NULL. If the most recent DML or SELECT INTO statement returned a record, it returns TRUE. If the most recent DML or SELECT INTO statement did not return a record, it returns FALSE. |
%NOTFOUND |
If the cursor is open but no FETCH operation has been performed, it returns NULL. If the most recent FETCH operation returned a record, it returns FALSE. If the most recent FETCH operation did not return a record, it returns TRUE. |
If no DML or SELECT INTO statement has been executed, it returns NULL. If the most recent DML or SELECT INTO statement returned a record, it returns FALSE. If the most recent DML or SELECT INTO statement did not return a record, it returns TRUE. |
%ROWCOUNT |
If the cursor is open, it returns a number greater than or equal to 0. | If no DML or SELECT INTO statement has been executed, it returns NULL; otherwise, it returns a number greater than or equal to 0. |
%ISOPEN |
If the cursor is open, it returns TRUE; otherwise, it returns FALSE. |
Always FALSE. |
Examples
Example: Use a simple cursor to retrieve data.
obclient> CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES(111, 'DEL', 'FA BEN', 1, 1500);
Query OK, 1 rows affected
obclient> INSERT INTO employees VALUES(112, 'AXEL', 'BELL', 1, 1000);
Query OK, 1 rows affected
obclient> INSERT INTO employees VALUES(113, 'CRIS', 'RACHAR', 1, 1000);
Query OK, 1 rows affected
obclient> DECLARE
CURSOR c_emp IS SELECT first_name, salary
FROM employees where rownum<5;
v_ename employees.first_name%TYPE;
v_sal employees.salary%TYPE;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename, v_sal;
WHILE c_emp%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||'''s salary is '||to_char(v_sal) );
FETCH c_emp INTO v_ename, v_sal;
END LOOP;
CLOSE c_emp;
END;
/
Query OK, 0 rows affected
DEL's salary is 1500
AXEL's salary is 1000
CRIS's salary is 1000
Example: Pass parameters.
obclient> DECLARE
CURSOR c_emp (in_job_id varchar default 'AC_MGR' ) IS
SELECT first_name, salary
FROM employees where job_id = in_job_id;
v_jobid employees.job_id%TYPE;
v_emp_name employees.first_name%TYPE;
v_sal employees.salary%TYPE;
BEGIN
v_jobid := 'IT_PROG';
OPEN c_emp(in_job_id => v_jobid);
LOOP
FETCH c_emp INTO v_emp_name, v_sal;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('DEPT '||v_jobid||': '||v_emp_name||'''s salary is '||to_char(v_sal) );
END LOOP;
CLOSE c_emp;
END;
/
Query OK, 0 rows affected
DEPT IT_PROG: Alexander's salary is 9000
DEPT IT_PROG: Bruce's salary is 6000
DEPT IT_PROG: David's salary is 4800
DEPT IT_PROG: Valli's salary is 4800
DEPT IT_PROG: Diana's salary is 4200
Differences between NO_DATA_FOUND and %NOTFOUND
The following table describes the differences between NO_DATA_FOUND and %NOTFOUND.
| Differences | NO_DATA_FOUND | %NOTFOUND |
|---|---|---|
| Definition | NO_DATA_FOUND is an exception. |
%NOTFOUND is a cursor attribute. |
| Trigger conditions | The SELECT ... INTO statement triggers NO_DATA_FOUND. |
|