Oracle PL/SQL – CREATE function example

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;

select * from PERSON_ADDRESS_DETAILS;

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;

select * from PERSON_SALARY_DETAILS;

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

References

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