User Defined PL/SQL Function

You might be new to PL/SQL Functions but you are not new to Functions if you have used SQL before. Function is a subprogram that computes a value. Functions are similar to Stored Procedures except that a Function returns a value to the environment in which it is called. Function in PL/SQL is also known as user Function or user-defined Function.  

Most Database Management Systems come with built in Functions like COUNT, AVG, MAX, MIN etc. These are examples of system built in Function. It is impossible to include all the Functions under built in Functions list, therefore most DBMS engines let you create a custom Function to your need. The custom Functions you create with PL/SQL are called user-defined functions. This article talks about Function Syntax, Execution, Limitations and the difference between Function and Procedure. You will also walkway with when to create Function or procedure.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name
[(parameter name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatyp
{IS | AS}
BEGIN
   < function_body >
END [function_name];
/

Problem:

The employee table has grown big as the company expanded over the few years. Now, Human Resources can’t keep track of number of employee on each department, therefore they requested a look up tool. The lookup tool takes the department number and outputs the number of employee for the requested department.

Solutions:

You will need a function that takes department number as input and spits out the count for your input. before we implementing a function, let's write a test query that counts the number of employee for each department. This query  will be helpful to validate the result of  the Functions result. The query below display dept number along with the count for all the department on employee table.

The purpose of the Function is to return the number of employee for a given department. It takes department number as input parameter and returns the employee count for the department number you pass in.

CREATE OR REPLACE FUNCTION baniya.get_dept_count(deptno_in IN NUMBER)
     RETURN NUMBER
AS
     employee_count NUMBER := 0;
BEGIN
     SELECT COUNT( *)
     INTO employee_count
     FROM baniya.employee
     WHERE deptno = deptno_in;
    
     RETURN employee_count;
END;
/

Executing Function:  There are more than one ways to execute a function. I would show you the most common ways to execute using SQL and inside PL/SQL

SQL: We will call a functions inside a SQL.

SELECT baniya.get_dept_count(10) as "Total Employee" FROM dual;

Output:

PL/SQL: We will call a Function inside a PL/SQL block.

SET serveroutput ON
DECLARE
     deptno_in       NUMBER := 10;
     function_output NUMBER;
BEGIN
     function_output := baniya.get_dept_count(deptno_in) ;
     dbms_output.put_line('There are '||function_output || ' employees for dept ' || deptno_in ||'.');
END;
/

Output:

When learning PL/SQL user defined Function, the Stored Procedure becomes confusing after learning Function or vice versa. To fully comprehend Function and when to use it, you will need to know PL/SQL Stored Procedure if you don’t already. Why is there a need to create a Function when we already have stored procedures? I frequently get asked: “what are the differences between Stored Procedure and Function? They both can return values, they both take parameters then what are the differences? Yes, this is confusing until you start writing the Functions and Stored Procedure.

1. Function returns only one value, procedure can return multiple values. 
2. Function can be called from SQL statements not Procedure.
3.  Procedure can only be called from another Procedure or PL/SQL blocks.
4. DML and DDL statements cannot be used inside Function, therefore you use stored procedure for DDL, DML and TLC.
5. Functions are mostly used for computations where are procedures are for executing business rules. 

Limitations: You cannot perform DDL or DML inside a function. The function will compile but does not execute it. Let’s see in example how the limitation behaves.

DML Example:This Function takes department number and deletes the record that matches the input from employee table.

CREATE OR REPLACE FUNCTION baniya.delete_emp_table(deptno_in IN NUMBER)
     RETURN NUMBER
AS
     employee_count NUMBER := 0;
BEGIN
     delete from BANIYA.EMPLOYEE where DEPTNO = deptno_in;
     employee_count := sql%rowcount;
     commit;
     RETURN  employee_count;
END;
/

The Function compiled without errors. We will now execute to see if it works using SQL:

SELECT baniya.delete_emp_table(30) FROM dual;

Output:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "BANIYA.DELETE_EMP_TABLE", line 6
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

DDL Example: Similarly, the function with DDL command inside a Function may compile but will not execute, therefore a PL/SQL stored procedure is there for DDL and DML statement to execute not Function.

I hope you are now comfortable using Function and not mistaken with procedure.