Fetch cursor till cursorVariable%notfound : Cursor Fetch « Cursor « Oracle PL / SQL






Fetch cursor till cursorVariable%notfound

 
SQL> CREATE TABLE emp(
  2      emp_ID NUMBER (6),
  3      START_DATE  DATE,
  4      END_DATE    DATE,
  5      JOB_ID  VARCHAR2 (10),
  6      DEPARTMENT_ID   NUMBER (4)
  7  );

Table created.

SQL> CREATE OR REPLACE PROCEDURE promotion_review_1
  2  IS
  3     nempid   NUMBER;
  4     dstartdate    DATE;
  5     denddate      DATE;
  6     sjobid        VARCHAR2 (20);
  7     CURSOR empCursor IS SELECT emp_id, start_date, end_date, job_id FROM emp;
  8  BEGIN
  9     OPEN empCursor;
 10     LOOP
 11        FETCH empCursor INTO nempid, dstartdate, denddate, sjobid;
 12        EXIT WHEN empCursor%NOTFOUND;
 13        DBMS_OUTPUT.put_line ('emp '||nempid||' had job '||sjobid||' for '||(denddate - dstartdate)||' days.');
 14     END LOOP;
 15     CLOSE empCursor;
 16  END;
 17  /

Procedure created.

SQL> show errors
No errors.
SQL> drop table emp;

Table dropped.

   
  








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.Check Cursor%FOUND after fetching
10.Fetch value in cursor until NOTFOUND
11.ORA-01002: fetch out of sequence
12.Fetch out cursor value and insert to another table
13.PLS-00394: wrong number of values in the INTO list of a FETCH statement
14.FETCH cursor BULK COLLECT
15.Fetch cursor data to number variable
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