PL/SQL-FUNCTIONS - 2

[debasisdas]

FUNCTION WITH DEFAULT PARAMETER
=============== =============== =====
[CODE=oracle]
CREATE OR REPLACE function weekdaysinmonth (mdate date default
sysdate-(to_char(sysdat e,'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(mdat e,'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;
[/CODE]


FUNCTION WITH OUT PARAMETER
=============== =============
[CODE=oracle]
CREATE OR REPLACE FUNCTION out_func (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
outparm := 'out param';
RETURN 'return param';
END out_func;
[/CODE]

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

[CODE=oracle]

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

[/CODE]

FUNCTION WITH IN OUT MODE
=============== ===========
[CODE=oracle]
--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;
[/code]

TO EXECUTE
------------------------
[code=oracle]
--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(N O);
DBMS_OUTPUT.PUT _LINE(SALARY);
END;
[/code]


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