CREATEFUNCTIONSPLSQL

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.

While calling a function with OUT parameters, we have to make sure than we pass variables for the corresponding OUT parameters.

• 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.

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:

CREATE OR REPLACE FUNCTION get_employee_name
       (emp_id_in IN NUMBER)
RETURN VARCHAR2
  IS
  emp_name VARCHAR2(100);
BEGIN
    SELECT employee_name into emp_name
    FROM employee
    WHERE employee_id = emp_id_in;
RETURN emp_name;
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:

SELECT get_employee_name (101)
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.

SELECT get_employee_name (105)
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.

DECLARE
  n_x VARCHAR2(1000);
  n_y VARCHAR2(1000);
   n_z VARCHAR2(1000);
BEGIN
   n_x := get_employee_name(101);
   n_y := get_employee_name(102);
   n_z := get_employee_name(103);

   DBMS_OUTPUT.PUT_LINE(n_x);
   DBMS_OUTPUT.PUT_LINE(n_y);
   DBMS_OUTPUT.PUT_LINE(n_z);
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.

---