This article will help you to understand how to create a user defined function. It’s also known as stored function or user function.
- User defined functions are similar to procedures. The only difference is that function always returns a value.
- 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.
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 |
well Explained
Very good PL/SQL code, I understand very well. Thank you so much!
$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;
Great tutorial. Can we write a PL/SQL one function take either 3 or 5 arguments and return the value accordingly?
good day it seems hard to understand for me please kindly help to learn programming cos i really love this amazing world .
sir , u are just great person to contrinute all this code stuff and how to things. I appreaciate your effort. Salute~
Great example of function. anyone can easily learn about function and how to write function code and how to call function in Oracle.
thanks
great tutorial