Oracle PLSQL Tutorials For Beginners 5 : PLSQL Cursors , Implicit Cursor...

/*****************************************************************************
             PLSQL  Cursors & Types.
             ------------------------
              1) Implicit Cursors
              2) Explicit Cursors
             
              Cursor Atributes.
              --------------------
              1) %FOUND
              2) %NOTFOUND
              3) %ISOPEN
              4) %ROWCOUNT
             
              Explicit Cursors Process:
              -----------------
              1) DECLARE - Cursor
              2) OPEN  - Cursor
              3) FETCH - Cursor
              4) CLOSE - Cursor
              Declaring the cursor for initializing the memory
              Opening the cursor for allocating the memory
              Fetching the cursor for retrieving the data
              Closing the cursor to release the allocated memory

******************************************************************************/
-- Implecit Cursor Attributes....

select * from emp;

-- SQL%ROWCOUNT

DECLARE
  l_vc_name emp.ename%type;
  l_vc_sal emp.sal%type;
BEGIN
  SELECT ENAME, SAL
  INTO l_vc_name, l_vc_SAL
  FROM EMP
  WHERE empno=7566;
  dbms_output.put_line('Number of rows processed: '||sql%rowcount);
END;
/

---  SQL%FOUND, SQL%NOTFOUND & SQL%ROWCOUNT

DECLARE 
   v_total_Updated_rows number(2);
BEGIN
   UPDATE emp
   SET sal = sal + 500;    
   --- Checking Implicit Cursor  Data %NOTFOUND Attribute..
   IF sql%notfound THEN
      dbms_output.put_line('None of the Employee Salaries Updated');
   -- Checking Implicit Cursor Data using %FOUND Attribute..
   ELSIF sql%found THEN
   -- Getting Row Count using %ROWCOUNT Attribute..
      v_total_Updated_rows := sql%rowcount;
      dbms_output.put_line( v_total_Updated_rows || ' Total No.Of Employee Salaries Updated ');
   END IF; 
   commit;
END;
/  

/*****************************************************************************
                 Explicit Cursor Example....
*****************************************************************************/
CREATE OR REPLACE PROCEDURE Get_emp_names_Proc (Dept_num IN NUMBER) IS
   Emp_name       VARCHAR2(10);
   V_Total_Employees NUMBER(10);
   CURSOR         c1 (Depno NUMBER) IS
                     SELECT Ename FROM Emp
                        WHERE deptno = Depno;
BEGIN
   OPEN c1(Dept_num);
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN C1%NOTFOUND;     
      DBMS_OUTPUT.PUT_LINE(Emp_name);     
   END LOOP;
   V_Total_employees:=c1%rowcount;
   DBMS_OUTPUT.PUT_LINE('Total Employees for Dept 10 :: '||V_Total_employees);
   CLOSE c1;
END;
/

select * from emp;


exec Get_emp_names_Proc(10);