Main Tutorials

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

About Author

author image
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Subscribe
Notify of
8 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
pkumar
6 years ago

Great example of function. anyone can easily learn about function and how to write function code and how to call function in Oracle.
thanks

khaleel
4 years ago
Reply to  pkumar

great tutorial

mohankumar
2 years ago

well Explained

Khristine
3 years ago

Very good PL/SQL code, I understand very well. Thank you so much!

nodirbek
3 years ago

$result = 0;
$argument = ‘mmm’;
$sql = “begin :result := package.function_name(:argument); end;”;
$request = Yii::$app->db2
->createCommand($sql)
->bindParam(“:argument”, $argument)
->bindParam(‘:result’, $result, \PDO::PARAM_INPUT_OUTPUT,255)
->execute();
echo $result;

yisau adeola
5 years ago

good day it seems hard to understand for me please kindly help to learn programming cos i really love this amazing world .

Madan Neelapu
4 years ago

Great tutorial. Can we write a PL/SQL one function take either 3 or 5 arguments and return the value accordingly?

dan
6 years ago

sir , u are just great person to contrinute all this code stuff and how to things. I appreaciate your effort. Salute~