SQL Procedure

Introduction

A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. There are many useful applications of SQL procedures within a database or database application architecture. SQL procedures can be used to create simple scripts for quickly querying transforming, updating data, generating basic reports, improve application performance, modularizing applications, and improve overall database design, and database security.

Features of SQL procedures

• Can contain SQL Procedural Language statements and features which support the implementation of control-flow logic around traditional static and dynamic SQL statements.
• Easy to implement, because they use a simple high-level, strongly-typed language.
• SQL procedures are more reliable than equivalent external procedures.
• Support input, output, and input-output parameter passing modes.
• Support a simple, but powerful condition and error-handling model.
• Return multiple results sets to the caller or to a client application.
• Allow you to easily access the SQLSTATE and SQLCODE values as special variables.
• Reside in the database and are automatically backed up and restored.
• Can be invoked wherever the CALL statement is supported.
• Support nested procedure calls to other SQL procedures or procedures implemented in other languages.

Disadvantages

• Stored procedure languages are vendor specific. Therefore if you switch to another vendor's database, it requires to rewriting the existing stored procedures.
• Stored procedure languages from different vendors have different levels of sophistication. For example, Oracle's PL/SQL has more language features and built-in features than Microsoft's T-SQL.
• Tool support for writing and debugging stored procedures is often not as good as for other programming languages, though it depends on vendors and languages.

Defining an SQL procedure

The CREATE PROCEDURE statement for SQL procedures :

• Names the procedure
• Creates the stored procedure
• Defines the parameters and their attributes
• Provides other information about the procedure which will be used when the procedure is called
• Defines the procedure body

Here is the complete syntax of CREATE PROCEDURE (the syntax is based on SQL:2003 standard).

Syntax:

CREATE PROCEDURE proc_name
( [{[IN | OUT | INOUT] [parameter_name] 
datatype [AS LOCATOR] [RESULT]}
[, ...]] )
[ RETURNS datatype [AS LOCATOR]
LANGUAGE {ADA | C | FORTRAN | MUMPS | PASCAL | PLI | SQL}
[RETURN NULL ON NULL INPUT | CALL ON NULL INPUT]
[DYNAMIC RESULT SETS int]
code_block

Explanation:

CREATE PROCEDURE proc_name: Creates a new stored procedure with the name proc_ name.

( [{[IN | OUT | INOUT] [parameter_name] datatype [AS LOCATOR] [RESULT]} [, ...]] ) : Specifies the number of parameters of the procedure and the data type of each parameter. A parameter for a procedure can be used only for input, output, or both input and output. The parameters name must be unique within the procedure.

You can declare the paramete(s) in the following way:

SQL Code:

[{IN | OUT | INOUT}] parameter_name_1 datatype,
[{IN | OUT | INOUT}] parameter_name_2 datatype,
[{IN | OUT | INOUT}] parameter_name_3 datatype,[...]

IN : Identifies the parameter as an input parameter to the procedure.

OUT : Identifies the parameter as an output parameter that is returned by the procedure.

INOUT : Identifies the parameter as both an input and output parameter for the procedure.

datatype : Specifies the data type of the parameter(s).

The AS LOCATOR (optional) subclause is used to validate an external routine with a RETURNS parameter that is a BLOB (A collection of binary data stored as a single entity in a database.), CLOB (Store character data encoded in the database character set. ), NCLOB (Stores character data encoded in the national character set), ARRAY, or user-defined type.

[RETURN NULL ON NULL INPUT | CALL ON NULL INPUT]: When RETURNS NULL ON NULL INPUT (the option is used with a host language which cannot support NULLs) sets, the function immediately return a NULL value if it is passed a NULL value. CALL ON NULL INPUT specifies that the function is to be invoked if any, or all, argument values are null, making the function responsible for testing for null argument values. The function can return a null or non null value.

LANGUAGE {ADA | C | FORTRAN | MUMPS | PASCAL | PLI | SQL}: Most database platforms do not support all of these languages and may support several not mentioned, such as Java. The default is SQL.

[DYNAMIC RESULT SETS int] : Declaring dynamic result sets a stored procedure can open a certain number of cursors (int) and that those cursors are visible after returning from the procedure. The default is 0.

code_block: Declares the procedural statements that handle all processing within the stored procedure. The content of the code_block depends on the rules and procedural language used by the database.

Implementation

The exact and correct implementation of stored procedures depends upon database system and varies from one to another. Major database vendors support them in some form. Stored procedures can be implemented in a variety of programming languages (depends on the database system), for example, SQL, Java, C, or C++. See the following database system and implementation language :

Example: SQL Procedure

Here is a simple example that takes as input student registration number, total marks and number of subjects and updates the percentage of marks :

SQL Code:

CREATE PROCEDURE STUDENT_MARKS
(IN STUDENT_REG_NO CHAR(15),IN TOTAL_MARKS DECIMAL(7,2), NO_SUBJECTS INT(3))
 LANGUAGE SQL MODIFIES SQL DATA 
 UPDATE STUDENTMAST.MARKS
 SET PERCENTAGE = TOTAL_MARKS/NO_SUBJECT
 WHERE REG_NO = STUDENT_REG_NO

Explanation:

• Names of the procedure are STUDENT_MARKS
• Defines parameter STUDENT_REG_NO ( character data type of length 15), TOTAL_MARKS (decimal data type) and NO_SUBJECTS (integer type) which all are input parameters.
• Indicates the procedure is an SQL procedure that modifies SQL data.
• Defines the procedure body as a single UPDATE statement. When the procedure is called, the UPDATE statement is executed using the values passed for STUDENT_REG_NO, TOTAL_MARKS, and NO_SUBJECTS
• Defines the procedure body

Call a procedure

The CALL statement is used to invoke a procedure that is stored in a DATABASE. Here is the syntax:

CALL sp_name([parameter[,...]]) 
CALL sp_name[()]

sp_name : Name of the procedure.

parameter, ... : List of parameters enclosed in parentheses and separated by commas.

Alter a procedure

Following command alter an existing procedure :

SQL Code:

ALTER {PROCEDURE | FUNCTION} object_name
[( {parameter_name datatype }[, ...] )]
[NAME new_object_name]
[LANGUAGE {ADA | C | FORTRAN | MUMPS | PASCAL | PLI | SQL}]
[PARAMETER STYLE {SQL | GENERAL}]
[NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA]
[RETURN NULL ON NULL INPUT | CALL ON NULL INPUT]
[DYNAMIC RESULT SETS int]
[CASCADE | RESTRICT]

Drop a procedure

DROP PROCEDURE proc_name

proc_name: Name of the procedure.

See our MySQL Procedure Tutorial

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Drop
Next: Controlling Transactions