Stored Procedures

Almost all DBMS provide ways to group queries together in order to do a group of things in a sequence or every time something happens. Among the most famous of these are Stored Procedures, Functions, and Triggers.

Stored Procedures are essentially programs that are stored in DBMS that carry out a series of steps. Let us look at The ORACLE DBMS implementation of a stored procedure. Here is an example of a PL/SQL (ORACLE's variant of SQL) procedure ‘addrow’ that takes an integer i as input and inserts a row containing the integer into a sample table. First, a table t2 is created with two columns

CREATE TABLE T2 (
column1 INTEGER,
column2 CHAR(10)
);

CREATE PROCEDURE addrow(i IN NUMBER) AS
BEGIN
INSERT INTO T2 VALUES(i, 'sampledata');
END addrow;

run;

A procedure is introduced by the keywords, CREATE PROCEDURE, followed by the procedure name and its parameters. A parameter, for its part, is a value that is sent into the procedure. There can be any number of parameters, each followed by a mode and a data type. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). That is, IN parameters cannot be changed while OUT parameters can only be set. In the type area, the size of the field should not be specified the way it is done in the CREATE TABLE statement. The actual length of a parameter depends on the value that is passed in when the procedure is called.

Following the arguments is the keyword AS (IS may also be used). Then, the body which contains the series of statements to be executed comes next. We have repeated the name of the procedure after 'END', but this is not necessary. This procedure will be called from somewhere else; perhaps even the web, in the following way

addrow(10)

The following row will be inserted into t2 as a result:

Column1	Column2
10	samplecode

Figure 8b: Example of a simple Oracle stored procedure

Triggers are a special construct similar to Procedures. Triggers became famous due to their use in PL/SQL - the programming language that comes with the ORACLE DBMS. Procedures are executed when they are invoked from the outside by a program or another procedure while a Trigger is executed whenever the 'triggering' event set to start it takes place. In the ORACLE DBMS, The triggering event could be an INSERT, DELETE, or UPDATE SQL command. The timing of the trigger can either be BEFORE or AFTER one of these commands. Row level triggers take place once for each row affected by the triggering statement while statement level triggers fire just once for per UPDATE query.

Figure 8b: Example of a simple Oracle row level trigger