Fetch row by row : Cursor Fetch « Cursor « Oracle PL / SQL






Fetch row by row

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

Table created.

SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(3, 'Wil', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(4, 'Jane', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(5, 'Mary', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7, 'Chris', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(8, 'Smart', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(9, 'Peter', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(10, 'Take', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL>
SQL> variable x refcursor
SQL> declare
  2   r emp%rowtype;
  3   begin
  4       :x := emp_list;
  5   loop
  6       fetch :x into r;
  7       exit when :x%notfound;
  8       dbms_output.put_line(r.empno||','||r.hiredate);
  9   end loop;
 10   close :x;
 11  end;
 12  /
2,20-02-1981
3,22-02-1981
4,02-04-1981
5,28-09-1981
7,09-06-1981
8,09-12-1982
9,17-11-1981
10,08-09-1981
13,03-12-1981

PL/SQL procedure successfully completed.

SQL>
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 till cursorVariable%notfound
17.Fetch cursor to table collection of row type
18.Fetch cursor value to three variables
19.Fetch single column cursor to varchar2 variable