Saltar al contenido
SQL

Procedures and Functions in PL SQL3 Minutos de Lectura




Functions in PL SQL  procedures are subprograms composed of a set of SQL statements. Functions and PL/SQL procedures are not very different. A procedure or function is made up of a set of SQL and PL/SQL statements logically grouped together to perform a specific task. Stored procedures and functions constitute a block of PLSQL code that has been compiled and stored in the Oracle database system tables.

PLSQL procedures or functions are dynamic in that they support parameters that can be passed to them prior to execution. Therefore, a procedure or function can perform different tasks depending on the parameters passed to it.

Oracle procedures and functions are composed of a part in which variables and cursors are defined, another executable part composed of SQL and PLSQL statements, and another optional part focused on handling exceptions and errors that occur during execution.

The steps that Oracle follows to execute a procedure or function are the following:

Verify if the user has permission to execute.
Verify the validity of the procedure or function.
And finally execute it.
The advantages of using procedures and functions instead of SQL scripts are:

Ease of security management.

Better performance as they are compiled and stored in the database.
Better memory management.
Higher productivity and integrity.
The most important difference between procedures and functions is that a function, at the end of its execution, returns a value to the PL/SQL block that called it. However, in procedures this is not possible, although we can define multiple output parameters that would be returned to the PL/SQL block from which the procedure was executed (the latter is also possible in functions).

Syntax of a PLSQL procedure

CREATE OR REPLACE PROCEDURE [schema].procedure-name
(name-parameter {IN, OUT, IN OUT} data type, ..) {IS, AS}
Variable declaration;
Declaration of constants;
Cursor declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTION
PL/SQL exception block;
END;

Syntax of a Functions in PL SQL

CREATE OR REPLACE FUNCTION [schema].function-name
(parameter-name {IN, OUT, IN OUT} data-type, …)
RETURN datatype {IS, AS}
Variable declaration;
Declaration of constants;
Cursor declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTION
PL/SQL exception block;
END;

Clarifications on the syntax

Parameter-name: is the name we want to give to the parameter. We can use multiple parameters. If we do not need them we can omit the parenthesis.

IN: specifies that the parameter is an input parameter and therefore it must have a value at the time of calling the function or procedure. If nothing is specified, the parameters are input by default.

OUT: specifies that it is an output parameter. These are parameters whose value is returned after the execution of the procedure to the PL/SQL block that called it. PLSQL functions do not support output parameters.

IN OUT: These are both input and output parameters.

Data-type: Indicates the PLSQL data type that corresponds to the parameter (NUMBER, VARCHAR2, etc).

Practical examples

Example of creating a PL/SQL procedure:

CREATE OR REPLACE PROCEDURE
procedure1 (a IN NUMBER, b IN OUT NUMBER) IS
vmax NUMBER;
BEGIN
SELECT salary, maximum
INTO b, vmax
FROM employees
WHERE employee_id=a;
IF b < vmax THEN
b:=b+100;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b:=-1;
RETURN;
WHEN OTHERS THEN
RAISE;
END;

Configuración