This thread contains some useful tips/samples regarding FUNCTIONS in oracle, that the forum members may find useful.
FUNCTION:
==========
1.IT IS A COMPILED BLOCK OF CODE WHICH IS STORED AS AN OBJECT WITHIN THE DATABASE.
2.IT MUST RETURN A VALUE TO THE CALLING PROCEDURE.
3.ONCE A FUNCTION IS CREATED IT BECOMES PART OF THE DUAL TABLE.
4.IF A FUNCTION CONTAINS ANY OUT OR IN-OUT PARAMETER IT CAN'T BE EXECUTED AT STATMENT LEVEL.
BASIC SYNTAX
=============
-
CREATE [OR REPLACE] FUNCTION FUNCTION NAME[PARAMETER PARAMETER MODE],....])]
-
RETURN DATATYPE
-
{IS/AS}
-
[LOCAL DECLARATION]
-
BEGIN
-
EXECUTABLE STATMENT
-
RETURN VALUE;
-
[EXCEPTION
-
EXCEPTION HANDLER]
-
RETURN VALUE;
-
END [FUNCTIONNAME];
-
sample code to show a parameterless function
====================================
-
CREATE OR REPLACE FUNCTION HELLO RETURN VARCHAR2 IS
-
BEGIN
-
RETURN 'Hello welcome to TSDN';
-
END HELLO;
To call
--------------
SAMPLE FUNCTION WITH IN PARAMETER
==================================
-
-
CREATE OR REPLACE FUNCTION date_diff (max_date STRING, min_date STRING)
-
RETURN PLS_INTEGER IS
-
-
BEGIN
-
RETURN TO_DATE(max_date) - TO_DATE(min_date);
-
EXCEPTION
-
WHEN OTHERS THEN
-
RETURN NULL;
-
END date_diff;
-
TO EXECUTE THE FUNCTION
------------------------------------------------
-
SELECT date_diff('15-JUL-1980', '19-MAR-1979') FROM dual;
IN MODE FUNCTION EXAMPLE #2
=============================
-
CREATE OR REPLACE FUNCTION EVENODD(num_in NUMBER) RETURN STRING IS
-
BEGIN
-
IF MOD(num_in, 2) = 0 THEN
-
RETURN 'EVEN';
-
ELSE
-
RETURN 'ODD';
-
END IF;
-
END EVENODD;
TO EXECUTE
------------------------
- select evenodd(1234) from dual;
IN MODE FUNCTION EXAMPLE #3
=============================
-
CREATE OR REPLACE function shoname (num in number) return string is
-
name varchAr2(10);
-
begin
-
select ename into name from emp where empno=num;
-
return name;
-
end;
-
TO EXECUTE
------------------------
- SELECT shoname (7788) FROM DUAL;
IN MODE FUNCTION EXAMPLE #4
=============================
-
create or replace function spelldate(dt date) return varchar2 as
-
mdate varchar2(50);
-
begin
-
select to_char(dt,'ddspth month year') into mdate
-
from dual;
-
return mdate;
-
end;
Also check
PL/SQL-FUNCTIONS - 2 
