Exit When Cursor%NOTFOUND : Cursor Not Found « Cursor « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Cursor » Cursor Not Found 




Exit When Cursor%NOTFOUND
  
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(72),
  7                    COMM NUMBER(72),
  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);

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

SQL>
SQL>
SQL> DECLARE
  2       CURSOR csr_org IS
  3             SELECT empno, ename FROM   emp;
  4             v_org_rec csr_org%ROWTYPE;
  5     BEGIN
  6          OPEN csr_org;
  7          LOOP
  8               FETCH csr_org INTO v_org_rec;
  9               EXIT WHEN csr_org%NOTFOUND;
 10                 dbms_output.put_line(v_org_rec.empno||' '||v_org_rec.ename);
 11          END LOOP;
 12          CLOSE csr_org;
 13   END;
 14  /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

SQL> --

   
  














Related examples in the same category
1.If cursor not found, reset the value
2.Explicit Cursor and notfound
3.SQL%NOTFOUND and if statement
4.Incorrect location of the EXIT WHEN statement.
5.Behavior of the NO_DATA_FOUND exception.
6.Use 'EXIT WHEN studentCursor%NOTFOUND' just after fetch statement
7.NO_DATA_FOUND vs. %NOTFOUND
8.Loop till exit when cursorName%notfound
9.Output last row in a cursor
10.fetch cursor till notfound
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.