User Defined Function

Example of Using User Defined Function


Article Revision Date	24-OCT-2003
Web	www.SamOraTech.com
Author	Sameer Wadhwa
Code Verified and Tested against Database Version 8.x,9.x

Let us consider the following user defined function

STEP 1:-

CREATE FUNCTION bt_gt(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS

BEGIN

IF a > b then

RETURN 1;

ELSE

RETURN 0;

END IF;

END;

STEP 2:-

Let us consider the following select

SamSQL :> select empno,sal from emp;

EMPNO SAL

---------- ----------

7369 800

7499 1600

7521 1250

7566 2975

7654 1250

7698 2850

7782 2450

7788 3000

7839 5000

7844 1500

7876 1100

7900 950

7902 3000

7934 1300

14 rows selected.

Step 3:-

Use of user defined function

In the following example , I am trying to display all the records of emp table having salary greater than 3000;

SamSQL :> select empno,sal from emp

where bt_gt(SAL,3000) = 1;

EMPNO SAL

---------- ----------

7369 800

7839 5000

7900 950

In the following example , I am trying to display all the records of emp table having salary less than 3000;

SamSQL :> select empno,sal from emp

where bt_gt(sal,3000) = 0;

EMPNO SAL

---------- ----------

7499 1600

7521 1250

7566 2975

7654 1250

7698 2850

7782 2450

7788 3000

7844 1500

7876 1100

7902 3000

7934 1300

I think it is clear now how do we use the user defined function. You can generalize these function, create a public synonyms on it and can use anywhere in the database according to your requirement.

Hope the above information will help …

-- Author

Sameer Wadhwa

Please send your feedback at SamOracle@Yahoo.com

Disclaimer:

The author does not guarantee that this information is error-free.

If any errors are found, please report them to author at SamOracle@Yahoo.com

Ask and Solve Database Problems at OraTechSupportGroup

What do you think about Oracle Techniques?

Code Verified and Tested against Database Version 8.x,9.x

STEP 1:-

Step 3:-

Use of user defined function