How to write Database Procedures

What are Database Procedures

Procedures are commonplace across several different occupations and processes. Simply put, the term 'procedure' is a series of actions conducted in a certain order or manner to achieve a particular result. More popularly, a surgical procedure or an 'Operation' are steps a surgeon would take to carry out a surgical operation on a person. In this series, the focus is on procedures as it relates to database operations. I am assuming we all know what a database is, but for those who don't, you can read it up here.

Procedures also commonly known as stored procedures can be defined, in a simple way, as a reusable block of code that represents a specific business logic stored in a schema. Just a quick example, as an HR manager that is responsible for staff remuneration and payroll; you would have periods where you might want to increase staff salary, change their pay grade, and different other related sub-processes. Achieving this manually (updating) would be very time consuming, cumbersome, and error-prone. With a simple procedure, this can be achieved quite easily at any time the need to perform that particular operation arises.

Let's get right into it.

As we all know, we have different types of databases, but this article would focus on procedures as it relates to Oracle RDBMS. No need to worry, across all databases running on standard SQL, you would only experience little syntactical differences. The examples in this article use the common HR schema and you can practice here. We are going to start by writing a procedure that takes in an employee ID and returns the employee's name.

Syntax

Generally, procedures have 2 parts which are the specification (spec for short) and its body.

Procedure Specification

The specification of the procedure serves as the entry point into the procedure. It contains, the procedure name, the parameters related to that procedure (Inputs and Outputs), as well as other optional clauses used to describe the procedure. The specification always starts with the keyword PROCEDURE and ends with a parameter list which is optional. The parameter list contains the input and output variables for that procedure. Procedures that do not require any parameter list have their specifications written without parenthesis.

As opposed to functions, procedures can have multiple outputs or none at all. Most times, procedures contain parameters because we need to do similar things but with different data. The procedure with the parameter list sample below has 2 parameters; an input parameter (employee ID with a number datatype) and an output parameter (employee name with a varchar2 datatype). The IN and OUT indicators specify if that parameter is an input into the procedure or an output from the procedure.

-- procedure specification sample

procedure get_emp_name(v_id in number, v_emp_name out varchar2)

The code snippet above shows a procedure specification with a parameter list. Each parameter is separated with a comma while the snippet below shows a procedure specification without a parameter list.

-- procedure specification without a parameter list

procedure get_all_emp_names

NOTE: General syntax is (parameter_name parameter mode parameter datatype) where the parameter is defined by the developer, the parameter mode is either 'IN' or 'OUT' followed by the corresponding data type of the parameter

Procedure Body

The procedure body starts after the specification with the keyword 'IS' or 'AS'. The body usually has 3 (three) parts namely:

1. The Declarative part (optional)
2. The Executable part
3. Exception handling part (optional) The declarative part: All the variables, data types, and cursors to be used throughout the procedure are declared in this section of the procedure body following the keyword 'IS' or 'AS'.

The executable part contains the program to be executed. This is where the logic of the procedure is written. It is the part that uses all the parameters specified, as well as the variables declared, in other to achieve the purpose of the procedure. Each valid SQL statement must be ended by ';' identifying the statement and a single statement and syntactically separating it from the following valid SQL statements in the procedure body.

Exceptions are handled in the exception-handling part of the procedure body. Exception handling is very important in general programming. as this prevents our code from crashing when it runs into an unexpected error. A very simple exception handler code snippet will be used in this article as an example.

-- declarative part

IS
/* declaring one variable called l_name */ 

l_name VARCHAR2(20);

-- executable part (the real logic of the procedure)
begin

    select first_name  || ' ' || last_name 
    into l_name                            -- Variable to hold the result of the query
    from employees
    where employee_id = v_id;   -- Input specified in the specification of the procedure

    v_emp_name := l_name;       -- Output from the procedure.

end get_emp_name;

Note: The exception handler will be inside the 'BEGIN' and 'END' code block. The exception block code snippet will return 'exception block, employee does not exist' on any exception encountered during the execution of the code between the BEGIN and END block.

BEGIN

    /* insert executable code here */

    -- exception handler
    exception 
        when others then
        v_emp_name := 'exception block, employee does not exist';

END get_emp_name;

Putting it all together

Putting it all together, we have

CREATE OR REPLACE PROCEDURE get_emp_name(v_id IN NUMBER, v_emp_name OUT VARCHAR2)
IS  
    -- declarative part
    l_name VARCHAR2(20);

    -- executable part
BEGIN
    SELECT first_name  || ' ' || last_name 
    INTO l_name
    FROM employees
    WHERE employee_id = v_id;

    v_emp_name := l_name;

    -- exception handler
    EXCEPTION 
        WHEN OTHERS THEN
    v_emp_name := 'exception block, employee does not exist';

END get_emp_name;

The above procedure takes in one parameter (v_id)and returns the employee's full name where the employee ID is the same as the parameter v_id. In the case where the employee ID does not exist, the exception block is triggered and the procedure returns 'exception block, employee does not exist' as the output from the procedure.

To conclude, procedures are perfect for writing certain business logic that requires multiple statements that must all be executed. A simple and common example is a banking transaction in a banking system where one account is debited and another account is credited and the transaction is recorded in a journal. This operation in SQL, requires 2 update statements and an insert statement before we can say the transaction is successful. If any step is not done, we would want the other executed statements to be rolled back. Procedures are also good for preventing SQL injection and usually have a performance improvement when compared to individually connecting and executing each statement on the database.