This article will help you to understand how to create a user defined function. It’s also known as stored function or user function.

  1. User defined functions are similar to procedures. The only difference is that function always returns a value.
  2. User defined functions can be used as a part of an SQL expression.
Note
Oracle SQL does not support calling of functions with Boolean parameters or returns.

1. Function – Get formatted address

In this example, we will create a function to get formatted address by giving the person’s name.

1.1 Create tables and function.


-- creating table person_info

CREATE TABLE person_info
(
  PERSON_ID number(5) primary key,
  FIRST_NAME varchar2(20),
  LAST_NAME varchar2(20)
);

--creating table person_address_details

CREATE TABLE person_address_details
(
  PERSON_ADDRESS_ID number(5) primary key,
  PERSON_ID number(5) references person_info(person_id),
  CITY varchar2(15),
  STATE varchar2(15),
  COUNTRY varchar2(20),
  ZIP_CODE varchar2(10)
);

--creating function get_complete_address

create or replace FUNCTION get_complete_address(in_person_id IN NUMBER) 
   RETURN VARCHAR2
   IS person_details VARCHAR2(130);

   BEGIN 

	  SELECT 'Name-'||person.first_name||' '|| person.last_name||', 
        City-'|| address.city ||', State-'||address.state||', 
        Country-'||address.country||', ZIP Code-'||address.zip_code 
      INTO person_details
      FROM person_info person, person_address_details address
      WHERE person.person_id = in_person_id 
      AND address.person_id = person.person_id;

      RETURN(person_details); 

    END get_complete_address;

1.2 Insert data for testing.


INSERT INTO person_info VALUES (10,'Luis','Thomas');
INSERT INTO person_info VALUES (20,'Wang','Moris');

INSERT INTO person_address_details VALUES (101,10,'Vegas','Nevada','US','88901');
INSERT INTO person_address_details  VALUES (102,20,'Carson','Nevada','US','90220');

1.3 Display the data.


select * from PERSON_INFO;
PERSON_ID FIRST_NAME LAST_NAME
10 Luis Thomas
20 Wang Moris

select * from PERSON_ADDRESS_DETAILS;
PERSON_ADDRESS_ID PERSON_ID CITY STATE COUNTRY ZIP_CODE
101 10 Vegas Nevada US 88901
102 20 Carson Nevada US 90220

1.4 Calling the function. We can call function many ways. Here first we will call it in SELECT statement. And then we will call it from dbms_output.put_line


SELECT get_complete_address(10) AS "Person Address" FROM DUAL;

-- output
-- Name-Luis Thomas, City-Vegas, State-Nevada, Country-US, ZIP Code-88901

SET SERVEROUTPUT ON;
EXECUTE dbms_output.put_line(get_complete_address(20));

-- output
-- Name-Wang Moris, City-Carson, State-Nevada, Country-US, ZIP Code-90220

2. Function – Check Palindrome String

In this example, we will create a function to check whether a given string is palindrome or not.

A palindrome is a word, phrase, number, or other sequence of characters which reads the same backward as forward, such as madam or racecar.
https://en.wikipedia.org/wiki/Palindrome

2.1 Creating the function.


CREATE OR REPLACE FUNCTION checkForPalindrome(inputString VARCHAR2)
   RETURN VARCHAR2 
   IS result VARCHAR2(75);
   
   reversedString VARCHAR2(50); 
   BEGIN 
      SELECT REVERSE(inputString) INTO reversedString FROM DUAL;
            
      -- Using UPPER to ignore case sensitivity.
      IF UPPER(inputString) = UPPER(reversedString)
      THEN
      RETURN(inputString||' IS a palindrome.');
      END IF;
      RETURN (inputString||' IS NOT a palindrome.');
      
    END checkForPalindrome;
/

2.2 Calling the function.


SELECT checkForPalindrome('COMPUTER') FROM DUAL;
--	Output
-- 	COMPUTER IS NOT a palindrome.


SELECT checkForPalindrome('MAdam') FROM DUAL;
--	Output
-- 	MAdam IS a palindrome.


SELECT checkForPalindrome('KANAK') FROM DUAL;

--	Output
-- 	KANAK IS a palindrome.

3. Function – Calculate income tax

In this example, we will create a function to calculate income tax, assumed tax rate is 30% of all annual income from salary.

3.1 Create tables and function.


--creating table person

CREATE TABLE person
(
  PERSON_ID number(5) primary key,
  FULLNAME varchar2(20)
);

--creating table person_salary_details

CREATE TABLE person_salary_details
(
  SALARY_ID number(5) primary key,
  PERSON_ID number(5) references person(person_id),
  SALARY number(8),
  MONTH varchar2(9),
  YEAR number(4)
);

--creating function

CREATE OR REPLACE FUNCTION calculate_tax(personId NUMBER)
   RETURN NUMBER
   IS tax NUMBER(10,2);

BEGIN 
   tax := 0;
   
      SELECT (sum(salary)*30)/100 INTO tax FROM person_salary_details WHERE person_id = personId;
            
      RETURN tax;
      
END calculate_tax;

3.2 Insert data for testing.


INSERT INTO person VALUES (101,'Mark Phile');

INSERT INTO person_salary_details VALUES (1,101,15000,'JANUARY',2016);
INSERT INTO person_salary_details VALUES (2,101,15000,'FEBRUARY',2016);
INSERT INTO person_salary_details VALUES (3,101,15000,'MARCH',2016);
INSERT INTO person_salary_details VALUES (4,101,18000,'APRIL',2016);
INSERT INTO person_salary_details VALUES (5,101,18000,'MAY',2016);
INSERT INTO person_salary_details VALUES (6,101,18000,'JUNE',2016);
INSERT INTO person_salary_details VALUES (7,101,18000,'JULY',2016);
INSERT INTO person_salary_details VALUES (8,101,18000,'AUGUST',2016);
INSERT INTO person_salary_details VALUES (9,101,18000,'SEPTEMBER',2016);
INSERT INTO person_salary_details VALUES (10,101,18000,'OCTOBER',2016);
INSERT INTO person_salary_details VALUES (11,101,18000,'NOVEMBER',2016);
INSERT INTO person_salary_details VALUES (12,101,18000,'DECEMBER',2016);

3.3 Display the data.


select * from PERSON;
PERSON_ID FULLNAME
101 Mark Phile

select * from PERSON_SALARY_DETAILS;
SALARY_ID PERSON_ID SALARY MONTH YEAR
1 101 15000 JANUARY 2016
2 101 15000 FEBRUARY 2016
3 101 15000 MARCH 2016
4 101 18000 APRIL 2016
5 101 18000 MAY 2016
6 101 18000 JUNE 2016
7 101 18000 JULY 2016
8 101 18000 AUGUST 2016
9 101 18000 SEPTEMBER 2016
10 101 18000 OCTOBER 2016
11 101 18000 NOVEMBER 2016
12 101 18000 DECEMBER 2016

3.4 Calling the function.


SELECT person.fullname, sum(sal.salary) AS AnnualSalary, sal.year,calculate_tax(101) AS tax 
FROM person,person_salary_details sal
WHERE person.person_id = 101 and sal.year = 2016
GROUP BY person.fullname, sal.year;

Output

FULLNAME ANNUALSALARY YEAR TAX
Mark Phile 207000 2016 62100

References

  1. User Defined Functions :- Oracle official docs
  2. Create Function :- Oracle official docs