Adsense Ad

Thursday 16 March 2017

Oracle Cursor with basic loop and for loop

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as scott

SQL> SET SERVEROUTPUT ON;
SQL> --Basic Loop
SQL> DECLARE
  2    CURSOR C1 IS
  3      SELECT EMPNO, NAME FROM EMPLOYEE;
  4  
  5    V_EMPID VARCHAR2(100);
  6    V_NAME  VARCHAR2(300);
  7  
  8  BEGIN
  9  
 10    OPEN C1;
 11  
 12    LOOP
 13  
 14      FETCH C1
 15        INTO V_EMPID, V_NAME;
 16      EXIT WHEN C1%NOTFOUND;
 17      DBMS_OUTPUT.PUT_LINE(V_EMPID || ' ' || V_NAME);
 18  
 19    END LOOP;
 20  
 21  END;
 22  /

7839 KING
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
7369 SMITH
7698 BLAKE
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7900 JAMES
7782 CLARK
7934 MILLER

PL/SQL procedure successfully completed

SQL> --For Loop
SQL> DECLARE
  2    CURSOR C1 IS
  3      SELECT EMPNO, NAME FROM EMPLOYEE;
  4  
  5    V_EMPID VARCHAR2(100);
  6    V_NAME  VARCHAR2(300);
  7  
  8  BEGIN
  9  
 10    FOR I IN C1 LOOP
 11  
 12    V_EMPID := I.EMPNO;
 13    V_NAME := I.NAME;
 14      DBMS_OUTPUT.PUT_LINE(V_EMPID || ' ' || V_NAME);
 15    END LOOP;
 16  
 17  END;
 18  /

7839 KING
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
7369 SMITH
7698 BLAKE
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7900 JAMES
7782 CLARK
7934 MILLER

PL/SQL procedure successfully completed

SQL> 

No comments: