Creating Functions in Oracle PLSQL

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:

[( 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
— declaration_statements
— executable_statements
return {return_data_type};
— 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

    • 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)
      emp_name VARCHAR2(100);
        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.

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

    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.

Tagged , , , . Bookmark the permalink.