8,127
===================================
Expand|Select|Wrap|Line Numbers
- 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
============================
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION out_func (outparm OUT VARCHAR2)
- RETURN VARCHAR2 IS
- BEGIN
- outparm := 'out param';
- RETURN 'return param';
- END out_func;
---------------------------
Expand|Select|Wrap|Line Numbers
- DECLARE
- retval VARCHAR2(50);
- outval VARCHAR2(50);
- BEGIN
- retval := out_func(outval);
- dbms_output.put_line(outval);
- dbms_output.put_line(retval);
- END;
==========================
Expand|Select|Wrap|Line Numbers
- --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;
------------------------
Expand|Select|Wrap|Line Numbers
- --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
