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:

-- Creating a stored procedure named STUDENT_MARKS
CREATE PROCEDURE STUDENT_MARKS
(
  -- Defining an input parameter STUDENT_REG_NO of type CHAR(15)
  IN STUDENT_REG_NO CHAR(15),
  -- Defining an input parameter TOTAL_MARKS of type DECIMAL(7,2)
  IN TOTAL_MARKS DECIMAL(7,2),
  -- Defining an input parameter NO_SUBJECTS of type INT(3)
  IN NO_SUBJECTS INT(3)
)
-- Specifying the language of the procedure as SQL and indicating it modifies SQL data
LANGUAGE SQL MODIFIES SQL DATA
-- Defining the SQL statement to update the MARKS table in the STUDENTMAST schema
UPDATE STUDENTMAST.MARKS
-- Setting the PERCENTAGE column to the result of dividing TOTAL_MARKS by NO_SUBJECTS
SET PERCENTAGE = TOTAL_MARKS / NO_SUBJECTS
-- Specifying the condition for the update operation
WHERE REG_NO = STUDENT_REG_NO;

Explanation:

• This SQL code creates a stored procedure named STUDENT_MARKS.


• Stored procedures are precompiled SQL code that can be stored and executed on demand.


• The CREATE PROCEDURE statement is used to define a new stored procedure.


• The procedure takes three input parameters: STUDENT_REG_NO, TOTAL_MARKS, and NO_SUBJECTS.


• STUDENT_REG_NO is expected to be a character string of length 15, TOTAL_MARKS is a decimal number with a precision of 7 and a scale of 2, and NO_SUBJECTS is an integer.


• The LANGUAGE SQL statement specifies that the procedure is written in SQL and MODIFIES SQL DATA indicates that it will modify data in the database.


• The UPDATE statement inside the procedure updates the PERCENTAGE column in the MARKS table of the STUDENTMAST schema.


• The PERCENTAGE column is updated by dividing the TOTAL_MARKS by the NO_SUBJECTS for the specified student registration number (REG_NO).


• Once created, the procedure can be called with appropriate values for its parameters to update the percentage marks of a student in the database.

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