Go Back

PLSQL 3

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL:

PLSQL Basics 2:

CURSORS:

In order to process a SQL statement oracle will allocate an area of memory known as the context area. A CURSOR is a handle or pointer to the context area. There are two types of CURSORS.

1. IMPLICIT CURSORS. 2. EXPLICIT CURSORS.

The ORACLE server uses implicit cirsors to parse and execute your SQL statements. EXPLICIT CURSORS are explicitly declared by the programmer.

IMPLICIT CURSOR ATTRIBUTES:

USING IMPLICIT CURSOR ATTRIBUTES, you can test the outcome of your SQL statements. SQL %ISOPEN the ORACLE engine automatically opens and closes. The SQL CURSOR AFTER EXECUTING its associated SELECT,INSERT,UPDATE or DELETE SQL STATEMENT has been processed in case of IMPLICIT CURSORS. Thus the SQL%IS OPEN ATTRIBUTE of an IMPLICIT CURSOR cannot be references outside of its SQL STATEMENT. As a result, SQL%ISOPEN always evaluates to FALSE.

SQL%FOUND BOOLEAN ATTRIBUTE THAT EVALUATES TO TRUE, if an INSERT,UPDATE or DELETE AFFECTED one or more ROWS or SELECT STATEMENT RETURNS one or more ROWS. otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND. SQL%NOTFOUND BOOLEAN ATTRIBUTE that evaluates to FALSE, If an INSERT,UPDATE or DELETE AFFECTED ONE or MORE ROWS or SELECT STATEMENT RETURNS one or more ROWS. otherwise it evaluates to TRUE. The syntax for accessing this attribute is SQL%NOTFOUND. SQL%ROWCOUNT RETURNS THE NUMBER OF ROWS affected by an INSERT,UPDATE or DELETE or SELECT INTO STATEMENT. The syntax for accessing this attribute is SQL%ROWCOUNT.
  
SQL> VARIABLE ROW_DELETED  VARCHAR2(30)

SQL>  DECLARE
         v_deptno   EMP.DEPTNO%TYPE :=10;
      BEGIN
         DELETE 
           FROM emp
          WHERE DEPTNO = v_deptno;
         :ROW_DELETED :=(SQL%ROWCOUNT || 'ROWS DELETED');
      END;
      /

PL/SQL procedure successfully completed.

SQL> PRINT ROW_DELETED
ROW_DELETED                                                                                         
--------------------------------                                                                    
3ROWS DELETED              


SQL>  BEGIN
         DELETE 
           FROM dept
          WHERE deptno=10;
         IF SQL%FOUND 
         THEN
            dbms_output.put_line('DEPARTMENT SUCCESSFULLY DELTED');
         END IF;
         IF SQL%NOTFOUND 
         THEN
            dbms_output.put_line('DEPARTMENT NO DOES NOT EXSITS');
         END IF;
      END;
      /
DEPARTMENT SUCCESSFULLY DELTED                                                  
PL/SQL procedure successfully completed.


  BEGIN
   DELETE 
     FROM dept1
    WHERE deptno = 10;
      IF SQL%FOUND THEN
         dbms_output.put_line('DEPARTMENT SUCCESSFULLY DELETED');
      END IF;
      IF SQL%NOTFOUND THEN
         dbms_output.put_line('DEPARTMENT NO DOES NOT EXSITS');
      END IF;
  END;
 /
DEPARTMENT NO DOES NOT EXSITS                                                   
PL/SQL procedure successfully completed.

SQL %ROWCOUNT:
   DECLARE
      rows_deleted NUMBER(10);
      BEGIN
         DELETE 
           FROM emp
          WHERE DEPTNO=10;
         rows_deleted:=SQL%ROWCOUNT;
         dbms_output.put_line('NO OF ROWS DELETED:  ' ||rows_deleted);
   END;
SQL> /
NO OF ROWS DELETED:  3                                                          
PL/SQL procedure successfully completed.
PROCESSING THE EXPLICIT CURSORS: The four PL/SQL steps necessary for EXPLICIT CURSOR processing are as follows: 1. DECLARE THE CURSOR. 2. OPEN THE CURSOR FOR QUERY. 3. FETCH THE RESULTS INTO PL/SQL VARIBALES. 4. CLOSE THE CURSOR. EXPLICIT CURSOR ATTRIBUTES:

%ROWCOUNT returns the number of ROWS fetched from the active set. It is set to zero when the CURSOR is opened. The syntax for accessing this attribute is CURSORNAME%FOUND.

%FOUND BOOLEAN ATTRIBUTE that evaluates to TRUE If the last fetch succeeded because a ROW was avaliable or to false if the last fetch failed because no more rows were avaliable. The syntax for accessing this attributeis CURSORNAME%FOUND.

%NOTFOUND BOOLEAN attribute that evaluates to false if the last fetch succeeded because a row was avaliable or to true if the last fetch failed because no more rows were avaliable. The syntax for accessing this attribute is CURSORNAME%NOTFOUND.

%IS OPEN evaluates to TRUE, if an EXPLICIT CURSOR IS OPEN OR to FALSE, if it is closed. The syntax for accessing this attribute is CURSORNAME%ISOPEN.

IN THE BELOW PL/SQL BLOCK ONLY ONE ROW RETURNS FROM SELECT STATEMENT.

  
   DECLARE
      v_ename EMP.ENAME%TYPE;
      v_job   EMP.JOB%TYPE;
      v_sal   EMP.SAL%TYPE;
   BEGIN
      SELECT ename,
             job,
             sal 
        INTO v_ename,
             v_job,
             v_sal 
        FROM emp
       WHERE empno = &EMPLOYEE_NO;
   	dbms_output.put_line(v_ename||'  '||v_job||'  '||v_sal);
   END;
SQL> /
Enter value for employee_no: 7369
SMITH  CLERK  800                                                               
PL/SQL procedure successfully completed.

THE BELOW PL/SQL BLOCK RETURNS MORE THAN ONE ROW.

   DECLARE
      v_ename EMP.ENAME%TYPE;
      v_job   EMP.JOB%TYPE;
      v_sal   EMP.SAL%TYPE;
   BEGIN
      SELECT ename,
             job,
             sal 
        INTO v_ename,
             v_job,
             v_sal 
        FROM emp
       WHERE deptno = &DEPARTMENT_NO;
   	dbms_output.put_line(v_ename||'  '||v_job||'  '||v_sal);
   END;

SQL> /
Enter value for department_no: 10

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 6 


SLOVING THE ABOVE PROBLEM WITH CURSORS:

 DECLARE
   CURSOR C 
       IS 
      SELECT ename,
             job,
             sal 
        FROM emp 
       WHERE deptno = &DEPARTMENT_NO;
    v_ename EMP.ENAME%TYPE;
    v_job   EMP.JOB%TYPE;
    v_sal   EMP.SAL%TYPE;
BEGIN
   OPEN C;
   LOOP
      FETCH C 
       INTO v_ename,
            v_job,
            v_sal;
      EXIT WHEN C%NOTFOUND;
      dbms_output.put_line(v_ename||'  '||v_job||'  '||v_sal);
   END LOOP;
END;

SQL> /
Enter value for department_no: 10
CLARK  MANAGER  2450                                                            
KING  PRESIDENT  5000                                                           
MILLER  CLERK  1300                                                             
PL/SQL procedure successfully completed.


1  SELECT empno,ename,sal FROM emp
  2* WHERE deptno=10
SQL> /

    EMPNO 	ENAME          	SAL                                                                      
--------- ---------- ---------                                                                      
     7782 	CLARK           	2450                                                                      
     7839 	KING            	5000                                                                      
     7934 	MILLER          	1300                                                                      



   DECLARE
      v_empno    EMP.EMPNO%TYPE;
      v_ename    EMP.ENAME%TYPE;
      v_sal      EMP.SAL%TYPE;
      v_deptno   EMP.DEPTNO%TYPE:=10;
      v_no_rows  NUMBER(5);
      CURSOR EMP_CURSOR 
          IS 
      SELECT empno,
             ename,
             sal 
        FROM emp 
       WHERE deptno = v_deptno;
   BEGIN
      OPEN EMP_CURSOR;
      LOOP
         FETCH EMP_CURSOR 
          INTO v_empno,
               v_ename,
               v_sal;
         EXIT WHEN EMP_CURSOR%NOTFOUND;
            dbms_output.put_line(V_EMPNO || '         ' || V_ENAME || '        '||V_SAL);
      END LOOP;
      v_no_rows  := EMP_CURSOR%ROWCOUNT;
      dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || v_no_rows);
      CLOSE EMP_CURSOR;
   END;
SQL> /
7782         CLARK        	2450                                                                      
7839         KING        	5000                                                                       
7934         MILLER        	1300                                                                     
NO OF ROWS AFFECTED       3                                                                         
PL/SQL procedure successfully completed.

Working with %ISOPEN CURSOR ATTRIBUTE:

   DECLARE
      v_empno    EMP.EMPNO%TYPE;
      v_ename    EMP.ENAME%TYPE;
      v_sal      EMP.SAL%TYPE;
      v_deptno   EMP.DEPTNO%TYPE:=10;
      v_no_rows  NUMBER(5);
   CURSOR EMP_CURSOR 
       IS 
   SELECT empno,
          ename,
          sal 
     FROM emp1 
    WHERE deptno = v_deptno;

   BEGIN
      OPEN EMP_CURSOR;
         IF EMP_CURSOR%ISOPEN 
         THEN
            LOOP
            FETCH EMP_CURSOR 
             INTO v_empno,
                  v_ename,
                  v_sal;
            EXIT WHEN EMP_CURSOR%NOTFOUND;
            dbms_output.put_line(v_empno || '         ' || v_ename || '        '||v_sal);
            END LOOP;
            v_no_rows  := EMP_CURSOR%ROWCOUNT;
            dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || v_no_rows);
         END IF;
      CLOSE EMP_CURSOR;
   END;
SQL> /

7782         CLARK        2450                                                                      
7839         KING         5000                                                                       
7934         MILLER       1300                                                                     
NO OF ROWS AFFECTED       3                                                                         

PL/SQL procedure successfully completed.

Working with %FOUND CURSOR ATTRIBUTE:


SQL>     DECLARE
            v_empno    EMP.EMPNO%TYPE;
            v_ename    EMP.ENAME%TYPE;
            v_sal      EMP.SAL%TYPE;
            v_deptno   EMP.DEPTNO%TYPE := 10;
            v_no_rows  NUMBER(5);
         CURSOR EMP_CURSOR 
             IS SELECT empno,
                       ename,
                       sal 
                  FROM emp1 
                 WHERE deptno = v_deptno; 
         BEGIN  
            OPEN EMP_CURSOR;
         IF EMP_CURSOR%ISOPEN THEN 
            LOOP
            FETCH EMP_CURSOR 
             INTO v_empno,
                  v_ename,
                  v_sal;
               IF EMP_CURSOR%FOUND THEN
                  dbms_output.put_line(v_empno || '         ' || v_ename || '        '||v_sal);
               ELSE
                  EXIT;
               END IF;
            END LOOP;
            v_no_rows  := EMP_CURSOR%ROWCOUNT;
            dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || V_NO_ROWS);
         END IF;
            CLOSE EMP_CURSOR;
         END;
      /

7782         CLARK        2450                                                                      
7839         KING         5000                                                                       
7934         MILLER       1300                                                                     
NO OF ROWS AFFECTED       3                                                                         

PL/SQL procedure successfully completed.



Working with %NOTFOUNDCURSOR ATTRIBUTE:

SQL>  DECLARE
         v_empno    EMP.EMPNO%TYPE;
         v_ename    EMP.ENAME%TYPE;
         v_sal      EMP.SAL%TYPE;
         v_deptno   EMP.DEPTNO%TYPE := 10;
         v_no_rows  NUMBER(5);
      CURSOR EMP_CURSOR 
          IS SELECT empno,
                    ename,
                    sal 
               FROM emp 
              WHERE deptno = v_deptno;  
         BEGIN
            OPEN EMP_CURSOR;
               IF EMP_CURSOR%ISOPEN 
               THEN
                  LOOP
                  FETCH EMP_CURSOR 
                   INTO v_empno,
                        v_ename,
                        v_sal;
                     IF EMP_CURSOR%NOTFOUND 
                     THEN
                        EXIT;
                     ELSE
                        dbms_output.put_line(v_empno || '         ' || v_ename || '        '||v_sal);
                     END IF;
                  END LOOP;
                  v_no_rows  := EMP_CURSOR%ROWCOUNT;
                  dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || v_no_rows);
               END IF;
            CLOSE EMP_CURSOR;
         END;
      /

7782         CLARK       2450                                                                      
7839         KING        5000                                                                       
7934         MILLER      1300                                                                     

NO OF ROWS AFFECTED       3                                                                         

PL/SQL procedure successfully completed.



Working with WHILE LOOP IN CURSORS:


     DECLARE
        v_empno    EMP.EMPNO%TYPE;
        v_ename    EMP.ENAME%TYPE;
        v_sal      EMP.SAL%TYPE;
        v_deptno   EMP.DEPTNO%TYPE := 10;
        v_no_rows  NUMBER(5);
        CURSOR EMP_CURSOR 
            IS SELECT empno,
                      ename,
                      sal 
                 FROM EMP 
                WHERE deptno=v_deptno;  
      BEGIN
         OPEN EMP_CURSOR;
            IF EMP_CURSOR%ISOPEN 
            THEN
               FETCH EMP_CURSOR 
                INTO v_empno,
                     v_ename,
                     v_sal;
               WHILE EMP_CURSOR%FOUND  
               LOOP
                  dbms_output.put_line(V_EMPNO || '         ' || V_ENAME || '        '||V_SAL); 
                  FETCH EMP_CURSOR 
                   INTO v_empno,
                        v_ename,
                        v_sal;
               END LOOP;
               v_no_rows  := EMP_CURSOR%ROWCOUNT;
               dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || v_no_rows);
            END IF;
         CLOSE EMP_CURSOR;
      END;
 /

7782         CLARK        2450                                                                      
7839         KING         5000                                                                       
7934         MILLER       1300                                                                     

NO OF ROWS AFFECTED       3                                                                         

PL/SQL procedure successfully completed.

Working With FOR LOOP IN CURSORS:

      DECLARE
         v_empno    EMP.EMPNO%TYPE;
         v_ename    EMP.ENAME%TYPE;
         v_sal      EMP.SAL%TYPE;
         v_deptno   EMP.DEPTNO%TYPE:=10;
         v_no_rows  NUMBER(5);
         CURSOR EMP_CURSOR 
             IS SELECT EMPNO,
                       ENAME,
                       SAL 
                  FROM EMP 
                 WHERE DEPTNO = V_DEPTNO;  
      BEGIN
         FOR rcEMP_CURSOR IN EMP_CURSOR 
         LOOP
            dbms_output.put_line(rcEMP_CURSOR.EMPNO || '         ' || rcEMP_CURSOR.ENAME || '        '||rcEMP_CURSOR.SAL); 
            v_no_rows  :=EMP_CURSOR%ROWCOUNT;
         END LOOP;
         dbms_output.put_line('NO OF ROWS AFFECTED' || '       ' || V_NO_ROWS);
      END;
  /

7782         CLARK       2450                                                                      
7839         KING        5000                                                                       
7934         MILLER      1300                                                                     

NO OF ROWS AFFECTED       3                                                                         

PL/SQL procedure successfully completed.



Go Back

Go Back