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 
