Tuesday, September 8, 2015

Oracle example of function, cursor, define variable cursor%rowtype

 Dear my readers,
Here i put example of how to create function and how to create cursor and define variable as cursor rowtype.
hope it will be useful and hope to receive your comments


CREATE OR REPLACE FUNCTION get_yovrtim
                           (wemp in number,wd1 in date, wd2 in date)
RETURN NUMBER IS
   CURSOR C IS
     SELECT EMP_NO    ,
       sum(HOURS_NO)  HOURS_NO  ,
       sum(PURE_HOURS)  PURE_HOURS
     FROM emp_overtime
     WHERE o_code in (select ot_code from ovr_t_typs)
          and emp_no = wemp
          and o_date between wd1 and wd2
     GROUP BY EMP_NO
 ;
 v c%rowtype;
 v_ovrtim number(15,3);
BEGIN
  OPEN C; FETCH C INTO v; CLOSE C;
   v_ovrtim := v.hours_no;
 RETURN v_ovrtim;
END;

------

Yasser

No comments:

Post a Comment