8,127
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
=============
Expand|Select|Wrap|Line Numbers
- 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];
====================================
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION HELLO RETURN VARCHAR2 IS
- BEGIN
- RETURN 'Hello welcome to TSDN';
- END HELLO;
To call
--------------
Expand|Select|Wrap|Line Numbers
- select hello from dual;
SAMPLE FUNCTION WITH IN PARAMETER
==================================
Expand|Select|Wrap|Line Numbers
- 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;
------------------------------------------------
Expand|Select|Wrap|Line Numbers
- SELECT date_diff('15-JUL-1980', '19-MAR-1979') FROM dual;
=============================
Expand|Select|Wrap|Line Numbers
- 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
------------------------
Expand|Select|Wrap|Line Numbers
- select evenodd(1234) from dual;
IN MODE FUNCTION EXAMPLE #3
=============================
Expand|Select|Wrap|Line Numbers
- 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;
------------------------
Expand|Select|Wrap|Line Numbers
- SELECT shoname (7788) FROM DUAL;
=============================
Expand|Select|Wrap|Line Numbers
- 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
