PL/SQL-FUNCTIONS - 1

[debasisdas]

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
--------------

select hello from dual;

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