Oracle PL/SQL – Create Function Example

This article will help you to understand “Oracle PL/SQL – Create Function” with examples and description.

Oracle PL/SQL – CREATE FUNCTION statement is used to create 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.

Examples

1. Function to get person name and address from person number.

In this example, we have two tables person_info and person_address_details. The table person_info contains values of basic person info, While person_address_details contains values of person address details.

Now we want to get formatted address with person name using function. So we will create function get_complete_address. In this function we need to pass person_id as an input and it will return the person name with address.

Code

1.1 Create tables and function.

Output

table PERSON_INFO created.

Output

table PERSON_ADDRESS_DETAILS created.

Output

FUNCTION GET_COMPLETE_ADDRESS compiled

1.2 Insert data for testing.

1.3 Display the data.

PERSON_ID FIRST_NAME LAST_NAME
10 Luis Thomas
20 Wang Moris
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

Output

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

Output

Name-Wang Moris, City-Carson, State-Nevada, Country-US, ZIP Code-90220
2. Check if input string is Palindrome or not.

In this example we will create a function checkForPalindrome to check whether given string is palindrome or not. It requires Character String as input and will return Character String with result.

Code

2.1 Creating the function.

Output

FUNCTION CHECKFORPALINDROME compiled

2.2 Calling the function.

Output

Palindrome Check
COMPUTER IS NOT a palindrome.

Output

Palindrome Check
MAdam IS a palindrome.

Output

KANAK IS a palindrome.
3. Calculate income tax of given person.

In this example, we have two tables person and person_salary_details. person table contains person_id and fullname, While person_salary_details contains person salary details.

Now we want to calculate income tax using function. So we will create function calculate_tax. We assumed that the tax rate is 30% on all annual income from salary. We need to pass person_id as input and this function will return calculated tax amount.

Code

3.1 Create tables and function.

Output

table PERSON created.

Output

table PERSON_SALARY_DETAILS created.

Output

FUNCTION CALCULATE_TAX compiled

3.2 Insert data for testing.

3.3 Display the data.

PERSON_ID FULLNAME
101 Mark Phile
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.

Output

FULLNAME ANNUALSALARY YEAR TAX
Mark Phile 207000 2016 62100
References
  1. User Defined Functions :- Oracle official docs
  2. Create Function :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *