Functions in SQL

Functions are very similar to procedures in databases. In this article, I will try to break down functions in SQL and also mention the differences between functions and procedures in a database.

Remember! The RDBMS used in this article is the Oracle database. Let's dive in!!

The common differences between functions and procedures are:

• A function is a block of code called to perform a task and must return one or more values while a procedure is a block of code called to perform a task. Procedures do not have to return a value.
• A function can be called in a procedure but a procedure cannot be called in a function.
• A function can be called in a select statement but it must not contain IN or IN OUT parameters in the case while procedures cannot be executed in a select statement.

In PL SQL (Oracle), there are two (2) types of functions, we have the:

1. In-built functions: these are functions that come with the database at installation. An example of an in-built function is the NVL (used to handle null values) function

SELECT FIRST_NAME, NVL(HAS_SIBLINGS, 0) FROM STUDENTS;

The sample code above returns zero (0) where the HAS_SIBLINGS column has a null value.

1. User-defined functions: As the name implies, these are functions created by developers and users of the database.

Just like procedures, functions have two (2) main parts, the header and the body. The header has the name of the function and a RETURN clause specifying the data type to be returned by the function. The header looks something like

-- header

CREATE OR REPLACE FUNCTION get_emp_email(v_id IN NUMBER)
RETURN VARCHAR2

The function body has three (3) main parts which are the declarative section, the executable section and the exception handling section. The exception handling section is optional and does not have to be included. The syntax follows the format below

IS

[declarative section]

BEGIN

[executable section]

[EXCEPTION]

[exception-handling section]

END;

Variables, custom data types, cursors etc. are declared in the declarative part. The executable part contains the actual piece of code to be executed. It is between the BEGIN and END clauses and must contain at least one RETURN statement. Exceptions are handled in the exception handling block.

Let's create a function that will return an employee's email in the format firstname.lastname@company.com.

CREATE OR REPLACE FUNCTION get_emp_email(v_id IN NUMBER)
RETURN VARCHAR2
IS 
v_email VARCHAR2(150);
BEGIN
    SELECT LOWER(first_name  || '.' || last_name || '@learnplsql.com')
    INTO v_email
    FROM hr.employees
    WHERE employee_id = v_id;


    RETURN v_email;
END get_emp_email;

Because the function created above does not have OUT or IN OUT parameters, it can be used in a select statement. Let's get the email of employees with employee ID between 130 and 150.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, GET_EMP_EMAIL(EMPLOYEE_ID) EMAIL 
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 130 AND 150;

The output looks like this:

I find functions convenient for inexpensive joins or operations like the example above. Functions generally make my code neater when used this way. Another use case for functions is to perform computations.

BONUS

You can practice what you have learnt in Oracle's live SQL platform. The link is here.