Cursor attributes can provide information about a cursor.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The syntax for obtaining the attribute values of a cursor is cursor_name%attribute, for example, c1%NOTFOUND. The following describes the types of cursor attributes:
%FOUND: a BOOLEAN attribute, which returnsTRUEif the last cursor read operation returns a record.%NOTFOUND: a BOOLEAN attribute, which is opposite to%FOUND.%ISOPEN: a BOOLEAN attribute, which returnsTRUEif the cursor has been opened.%ROWCOUNT: a numeric attribute, which returns the number of records read from the cursor.
Common cursor attributes and their return values
| Attribute | Value for a declared cursor | Value for an implicit cursor |
|---|---|---|
%FOUND |
Returns NULL if the cursor is opened but no FETCH operations are performed. Returns TRUE if the last FETCH operation returns one row of record.Returns FALSE if the last FETCH operation does not return a record. |
Returns NULL if no DML or SELECT INTO statement is executed.Returns TRUE if the last DML or SELECT INTO statement executed returns one row of record.Returns FALSE if the last DML or SELECT INTO statement executed does not return a record. |
%NOTFOUND |
Returns NULL if the cursor is opened but no FETCH operations are performed.Returns FALSE if the last FETCH operation returns one row of record.Returns TRUE if the last FETCH operation does not return a record. |
Returns NULL if no DML or SELECT INTO statement is executed.Returns FALSE if the last DML or SELECT INTO statement executed returns one row of record.Returns TRUE if the last DML or SELECT INTO statement executed does not return a record. |
%ROWCOUNT |
Returns a number greater than or equal to 0 if the cursor is opened. | Returns NULL if no DML or SELECT INTO statement is executed, and returns a number greater than or equal to 0 if otherwise. |
%ISOPEN |
Returns TRUE if the cursor is opened, and returns FALSE if otherwise. |
Always returns FALSE. |
Examples
Example: Fetch data by using a simple cursor
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 by using a cursor
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.
| Difference | NO_DATA_FOUND | %NOTFOUND |
|---|---|---|
| Definition | NO_DATA_FOUND is a definition of exceptions. |
%NOTFOUND is a cursor attribute. |
| Trigger condition | SELECT ... INTO will trigger NO_DATA_FOUND. |
|