Oracle PLSQL function is a named block which can return a value.
Oracle PLSQL allows us to create functions to meet various business needs.
PLSQL functions are also called as subroutines or subprograms.
Oracle PLSQL syntax to create a function is:
CREATE [OR REPLACE] FUNCTION function_name
[( parameter_1 [IN] [OUT] parameter_data_type_1,
parameter_2 [IN] [OUT] parameter_data_type_2,…
parameter_N [IN] [OUT] parameter_data_type_N )]
RETURN return_datatype
IS | AS
— declaration_statements
BEGIN
— executable_statements
return {return_data_type};
[EXCEPTION
— the exception-handling statements]
END [function_name];
Let’s understand the above function creation syntax in detail:
1. The function_name is the name given to the PLSQL function. Preferably it should begin with a verb e.g. convert_to_date.
2. The parameter_name is the name of the parameter that we are passing to the function.
3. The parameter_data_type is the data type of the parameter that we are passing to the PLSQL function.
4. Every Oracle PLSQL function must have a RETURN statement in the code execution part.
The RETURN specified in the header part of the Oracle PLSQL function specifies the data-type of the value returned by the PLSQL function.
Ways to pass parameters to an Oracle PLSQL Function.
There are 3 ways of passing parameters to PLSQL Function:
a. IN
b. OUT and
c. IN OUT
- IN: This is the default style of parameter in PLSQL function. We use the IN mode whenever we want the parameter to be read only i.e. we cannot change the value of the parameter in the PLSQL function.The parameter defined as IN will behave as constant type inside the PLSQL function.
We can assign a default value to the IN type of parameter. Oracle PLSQL also allows up to make the IN as optional.
- OUT: The parameters labeled as OUT returns the values to the calling subprogram or subroutines.A default value cannot be assigned to OUT parameter hence we cannot make it optional.
We have to assign a value to OUT parameter before we exit the function or the value of the OUT parameter will be NULL.
- IN OUT: In this mode the actual parameter is passed to the PLSQL function with initial values and then within the PLSQL function the value of the parameter may get changed or reassigned.The IN OUT parameter is finally returned to the calling subroutine.
While calling a function with OUT parameters, we have to make sure than we pass variables for the corresponding OUT parameters.
The block structure of a PLSQL function is same as that of an PLSQL Anonymous Block except for the addition of CREATE [OR REPLACE FUNCTION, the parameters section of code and the RETURN Clause.
Example to create an Oracle PLSQL Function:
Suppose we have a table named ‘employee’ as shown below.
| Employee_ID | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
| 106 | Emp F | 40000 | Sales | 10 |
| 107 | Emp G | 12000 | Sales | 10 |
| 108 | Emp H | 12000 | Sales |
Suppose we want to create a function that shows us the name of an employee whenever we pass employee_id as parameter.
We can create an Oracle PLSQL Function as:
1 | CREATE OR REPLACE FUNCTION get_employee_name |
2 | (emp_id_in IN NUMBER) |
3 | RETURN VARCHAR2 |
4 | IS |
5 | emp_name VARCHAR2(100); |
6 | BEGIN |
7 | SELECT employee_name into emp_name |
8 | FROM employee |
9 | WHERE employee_id = emp_id_in; |
10 | RETURN emp_name; |
11 | END get_employee_name; |
Calling an Oracle PLSQL Function:
1. Calling PLSQL Function using Oracle SQL SELECT statement
Now, if we call the above PLSQL function using an SQL SELECT statement as:
1 | SELECT get_employee_name (101) |
2 | FROM dual; |
We will get ‘Emp A’ as result.
Also, if we change the employee_id passed to the function then we will get the name of another employee e.g.
1 | SELECT get_employee_name (105) |
2 | FROM dual; |
Will return ‘ Emp E’ as the employee_name.
2. Calling PLSQL Function using Oracle anonymous block
Let’s create an anonymous block to call the get_employee_name PLSQL function.
1 | DECLARE |
2 | n_x VARCHAR2(1000); |
3 | n_y VARCHAR2(1000); |
4 | n_z VARCHAR2(1000); |
5 | BEGIN |
6 | n_x := get_employee_name(101); |
7 | n_y := get_employee_name(102); |
8 | n_z := get_employee_name(103); |
9 |
10 | DBMS_OUTPUT.PUT_LINE(n_x); |
11 | DBMS_OUTPUT.PUT_LINE(n_y); |
12 | DBMS_OUTPUT.PUT_LINE(n_z); |
13 | END; |
Once we run the above Oracle SQL code we will get the following output:
Emp A
Emp B
Emp C
Here we have called the Oracle PLSQL Function using the Oracle anonymous block.