/* Formatted on 26-09-2011 11:20:21 (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE proc_emp_sal
IS
   v_comp_mnths      emp_new_sunita.comp_mnths%TYPE;
   v_compltd_mnths   emp_new_sunita.comp_mnths%TYPE;
   --num_total_rows    NUMBER;
   v_new_salary      emp_sunita.salary%TYPE;

   CURSOR emp_sal_cur
   IS
      SELECT empid, name, salary, deptno, joing_date
      FROM emp_sunita
      WHERE func_get_date (joing_date) >= 365;
BEGIN
   OPEN emp_sal_cur;


   FOR emp_rec IN emp_sal_cur
   LOOP
      BEGIN
         FETCH emp_sal_cur INTO emp_rec;

         v_compltd_mnths   := TRUNC (func_get_date  (emp_rec.joing_date) / 12);


         IF (func_get_date  (emp_rec.joing_date) >= 365
             AND func_get_date  (emp_rec.joing_date) <= 730)
         THEN
            BEGIN
               v_new_salary   := emp_rec.salary * 1.1;
               v_comp_mnths   := TRUNC (func_get_date  (emp_rec.joing_date) / 12);
               
               Insert into emp_new_sunita(comp_mnths,new_salary) values(V_COMP_MNTHS,V_NEW_SALARY);
            END;
         ELSIF (func_get_date  (emp_rec.joing_date) >= 731 AND func_get_date (emp_rec.joing_date) <= 1095)
         THEN
            BEGIN
               v_new_salary   := emp_rec.salary * 1.2;
               v_comp_mnths   := TRUNC (getdate (emp_rec.joing_date) / 12);
               Insert into emp_new_sunita(comp_mnths,new_salary) values(V_COMP_MNTHS,V_NEW_SALARY);
            END;
         ELSIF (getdate (emp_rec.joing_date) >= 1096)
         THEN
            BEGIN
               v_new_salary   := emp_rec.salary * 1.3;
               v_comp_mnths   := TRUNC (getdate (emp_rec.joing_date) / 12);
               Insert into emp_new_sunita(comp_mnths,new_salary) values(V_COMP_MNTHS,V_NEW_SALARY);
            END;
         END IF;
      END;
Exit when emp_sal_cur%NOTFOUND;

   END LOOP;

   CLOSE emp_sal_cur;
END;
/

OPEN emp_sal_cur;

CLOSE emp_sal_cur;

declare
  cursor emp_sal_cur is select ...
  cur_r emp_sal_cur%rowtype;
begin
  open emp_sal_cur;
  loop
    fetch emp_sal_cur into cur_r;
    exit when emp_sal_cur%notfound

    <do something>
  end loop;
  close emp_sal_cur;
end;

begin
  for cur_r in emp_sal_cur loop
  
    <do something>
  end loop;
end;