Functions in SQL

Functions in SQL are the executable blocks that are created and stored in the database. The functions can be invoked from the SQL statements whenever required. Though SQL has some inbuilt functions but the developers can write their own user-defined functions.

The functions in SQL are similar to the procedures in SQL that we have discussed earlier. But both of them share differences too. In the section ahead, we will discuss the structure of function in SQL, more about the parameters used in a function, the difference between procedure and function. We will wind up the content discussion on how you can remove a function in SQL.

Content: Functions in SQL

1. What is Function in SQL?
2. Execution of Functions
3. Parameters of Functions
4. Functions Vs Procedures
5. Removing Functions

What is Function in SQL?

Function in SQL can be defined as a set of SQL statements that defines a ‘business logic’. SQL function returns only one value to the calling program and can even be used as part of the expression. The syntax of the SQL function is as follow:

CREATE OR REPLACE FUNCTION [schema.] function_name

[(argument IN datatype, . . . .)] RETURN datatype {IS, AS}

[local variable declarations];

BEGIN

executable statements;

EXCEPTION

exception handlers;

END [function_name];

The entire structure of function can be divided into two parts function specification and function body. The function specification starts from the keyword FUNCTION and ends at the RETURN clause. The RETURN clause describes the type of data that will be returned by this function. The function body specifies the working of the function, it starts with the keyword IS or AS. The function body is terminated with the keyword END. The END keyword may or may not be followed by the function name as it is optional.

Let us discuss the set of rules that a SQL function must satisfy for being called in a SQL statement.

1. When the SQL function is being called from the SELECT statement or from a parallelized INSERT, UPDATE or DELETE statement then the corresponding function cannot alter any of the database tables.
2. INSERT, UPDATE or DELETE are the states that are used to modify the database tables and if the SQL function is being called from these statements the function is not allowed to query or modify the database table modified.
3. When the SQL function is being called by the SQL statements SELECT, INSERT, UPDATE, DELETE then the function is not allowed to execute any transaction control statements like COMMIT, session control statement like SET ROLE, or system control statement like ALTER SYSTEM.
   Even the function is not allowed to execute the data definition language (DDL) statement as these statements are automatically followed by an automatic commit.

If the SQL function does not follow the above-specified rule then the program calling such function will receive a run time error.

Execution of Functions

Consider that we have a database table EMP(EMP_ID, EMP_NAME, SAL). Now, to determine the salary of a particular employee we will create a function:

CREATE OR REPLACE FUNCTION [schema.] function_name

[(argument IN datatype, . . . .)] RETURN datatype {IS, AS}

[local variable declarations];

BEGIN

executable statements;

EXCEPTION

exception handlers;

END [function_name];

The function created above returns the salary of the employee whose employee_id it has received in the parameter.

Once the function is created, the next step is to execute the created function. The syntax to call the get_sal:

SELECT get_sal(‘E108’) FROM DUAL;

This SQL statement will return the salary of the employee with the employee id 108.

Parameters of Functions

Parameters are initialized to the values of the argument that are passed to the function while calling. The outcome of the function execution depends on how the parameters have been passed to the function. Further, we will discuss the parameter modes that describe the behaviour of parameters in the function.

There are three types of parameter modes IN, OUT, IN/OUT.

a. IN Mode

Whenever the parameters are passed in the IN mode, they act as a constant within the function. The function cannot assign new values to the parameters with IN mode. The IN parameter can be a constant, a literal initialized variable or an expression.

The IN parameter can be initialized to the default values and IN mode is the default mode of the parameters. If no mode is specified with the parameter it is by default treated as the IN parameter.

b. OUT Mode

A function can assign a new value to the parameter with the OUT mode. The OUT parameter doesn’t act like a constant or a literal instead it acts as a variable. The value of the OUT parameter can be changed or referenced in the expression.

By default, the value of the formal OUT parameter is NULL. The parameter corresponding to the OUT parameter may have some value before it has been sent as an out parameter to the function but that value is lost as soon as the function is called.

c. IN/OUT Mode 

An IN/OUT parameter acts as both IN parameter and OUT parameter. As an IN parameter, it provides input value to the function and inside the function, this parameter value can be changed and returned.

Functions Vs Procedures

Though a procedure and function are created to perform a specified task they differ in many senses. Let us discuss the difference between functions and procedures.

1. A function always returns only one value to the calling program however the procedure does not return any value to the calling program.
2. A function can be used as an element in the SQL expression whereas the procedure can not be used as an element of SQL expression.
3. The function must have at least one RETURN statement whereas the procedure may or may not have the RETURN statement. The RETURN statement in procedure transfers the control of execution back to the calling program.

Removing Functions

Once you create and save function in the database you can use it as many times as you require. But, if you feel you can even delete or remove the functions from the database. The syntax for removing the function from the database is as follow.

DROP FUNCTION <FUNCTION NAME>;

However, to drop or remove a function from the database you must have DROP ANY FUNCTION privilege.

So, this is all about the functions is SQL. We have learned to create functions, execute functions and even remove the created function from the database. We have discussed different modes of parameters in function and we have learned about the differences between SQL function and SQL procedure.