Simple Loop and fetch cursor : CURSOR FOR Loop « Cursor « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Cursor
  3. CURSOR FOR Loop

Simple Loop and fetch cursor

  

SQL>
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'E', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2     v_emp emp%ROWTYPE;
  3
  4     CURSOR auth_cur IS SELECT * FROM emp;
  5  BEGIN
  6     OPEN auth_cur;
  7     LOOP
  8        FETCH auth_cur INTO v_emp;
  9        EXIT WHEN auth_cur%NOTFOUND;
 10
 11        DBMS_OUTPUT.PUT_LINE(v_emp.lname);
 12     END LOOP;
 13
 14     CLOSE auth_cur;
 15  END;
 16  /
B
D
F
H
Z

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.








Related examples in the same category

1.Create a cursor in for statement
2.The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to guide a loop
3.A function that uses a CURSOR FOR Loop
4.Use 'for loop' to loop through the cursor
5.A subquery in the FROM clause of a cursor within a cursor FOR loop
6.Assign value from cursor to a table collection by index
7.Calculation based on cursor value
8.For each row in a cursor
9.Output value in cursor one by one
10.Use pl/sql, cursor and for loop to count table