Check Cursor%FOUND after fetching

  
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL> DECLARE
  2     CURSOR cur_employee_salary IS
  3        SELECT sal
  4        FROM   emp
  5        WHERE  empno = 7369;
  6     lv_cur_employee_salary cur_employee_salary%ROWTYPE;
  7  BEGIN
  8     OPEN cur_employee_salary;
  9     FETCH cur_employee_salary INTO lv_cur_employee_salary;
 10     IF cur_employee_salary%FOUND THEN
 11        DBMS_OUTPUT.PUT_LINE('Record Found.');
 12     ELSE
 13        DBMS_OUTPUT.PUT_LINE('Record NOT Found.');
 14     END IF;
 15     CLOSE cur_employee_salary;
 16  END;
 17  /
Record Found.

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>
SQL>

Related examples in the same category

1.	Below is a function that demonstrates how to use the FETCH statement
2.	Fetch value from cursor
3.	Fetches the records from a cursor variable that returns employee information
4.	Fetch cursor to two variables
5.	legal and illegal FETCH statements.
6.	"ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop.
7.	A WHILE cursor fetch loop.
8.	Different BULK_COLLECT statements used for bulk binds
9.	Fetch value in cursor until NOTFOUND
10.	ORA-01002: fetch out of sequence
11.	Fetch out cursor value and insert to another table
12.	PLS-00394: wrong number of values in the INTO list of a FETCH statement
13.	FETCH cursor BULK COLLECT
14.	Fetch cursor data to number variable
15.	Fetch cursor till cursorVariable%notfound
16.	Fetch cursor to table collection of row type
17.	Fetch cursor value to three variables
18.	Fetch row by row
19.	Fetch single column cursor to varchar2 variable

Check Cursor%FOUND after fetching : Cursor Fetch « Cursor « Oracle PL / SQL

Related examples in the same category