PL/SQL-FUNCTIONS - 2

[debasisdas]

FUNCTION WITH DEFAULT PARAMETER
===================================

CREATE OR REPLACE function weekdaysinmonth(mdate date default
 sysdate-(to_char(sysdate,'dd')-1),weekday integer default 1)  return varchar2 
 as
     myexp exception;
     cursor c1(dt date) is
     with dtsun as (select dt + level-1 dm,
     to_char(dt+ level-1,'d') dy
     from dual connect by level <=to_char(last_day(dt),'dd'))
     select count(dm) from dtsun where dy=weekday;
     mcount number(4);
     wkd varchar2(10);
 begin
     if weekday>7 or weekday<=0 then
         raise myexp;
     end if;
     if to_char(mdate,'dd')<>1 then
         raise myexp;
     end if;
     open c1(mdate);
     fetch c1 into mcount;
     close c1;
     case weekday
     when 2 then
     wkd:='Mon';
     when 3 then
     wkd:='Tue';
     when 4 then
     wkd:='Wed';
     when 5 then
     wkd:='Thu';
     when 6 then
     wkd:='Fri';
     when 7 then
     wkd:='Sat';
     else
     wkd:='Sun';
     end case;
     return ('Number of '|| wkd || ' in '||to_char(mdate,'Month') || ' is ' || mcount);
exception
    when myexp then
         return ('supplied date should be first of the month and weekday should be between 1-7');
    when others then
         return null;
end;
 

FUNCTION WITH OUT PARAMETER
============================

CREATE OR REPLACE FUNCTION out_func (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS
 
BEGIN
  outparm := 'out param';
  RETURN 'return param';
END out_func;
 

TO EXECUTE
---------------------------

 
DECLARE
  retval VARCHAR2(50);
  outval VARCHAR2(50);
BEGIN
  retval := out_func(outval);
  dbms_output.put_line(outval);
  dbms_output.put_line(retval);
END;
 
 

FUNCTION WITH IN OUT MODE
==========================

--through the same variable the function accepts the value and returns the value.
CREATE FUNCTION ANSAL(ENO IN OUT NUMBER)
RETURN NUMBER
IS BEGIN
SELECT (SAL+NVL(COMM,0))*12 INTO ENO FROM EMP WHERE EMPNO=ENO;
RETURN ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO MATCHING DATA FOUND');
RETURN 0;
END;
 

TO EXECUTE
------------------------

--Since the procedure contains out mode it can't be called directly at SQL Prompt.
DECLARE
NO NUMBER(5):=&NO;
SALARY NUMBER(5);
BEGIN
SALARY:=ANSAL(NO);
DBMS_OUTPUT.PUT_LINE(SALARY);
END;
 

Also check Oracle Tips And Tricks -PL/SQL-FUNCTIONS - 1