A SQL script consists of stored SQL statements that allow parameters and variables, and can be reused. They are stored either as separate files, or as part of the DBMS. Most SQL databases have the ability to create and store SQL scripts. MySQL and IBM's DB2, for example, allow SQL scripting, but their SQL code must be part of a procedure written in a programming language such as Java or C, and the scripts are stored in separate files. Sybase SQL Anywhere and Microsoft SQL Server store and execute SQL scripts directly as part of the DBMS. Note that SQL statements can be written into almost any program that has supporting SQL libraries.
Procedures may return a single tuple, a set of tuples, a single value, or nothing at all.
The discussion here is based upon Sybase's SQL Anywhere DBMS (ver. 7), using the Watcom-SQL dialect. (Sybase SQL Anywhere supports two procedural dialects: Transact-SQL and Watcom-SQL. Watcom-SQL is supported only by Sybase, while Transact-SQL is supported by many other SQL DBMSes. It is important to note that there are differences amongst various Transact-SQL implementations, usually arising out of differing SQL dialects. Watcom-SQL makes a distinction between procedures and functions. Procedures may return tuples, whereas functions may return only a single value).
All examples given here are in Watcom-SQL dialect. Although Watcom-SQL is used only by Sybase, it is not radically different from Transact-SQL - indeed, Sybase SQL Anywhere can easily translate scripts between the two dialects. Watcom-SQL is the default language used by Sybase Central (SQL Anywhere's user interface) when creating scripts. so we have chosen to use Watcom-SQL to avoid confusion.
Imagine you wish to return all the fields of one particular tuple from the member table, and assume that you know the tuple's unique member_id. The appropriate SQL statement is
SELECT * FROM member WHERE member_id = 12
Now imagine that you wish to repeat this statement multiple times, but with a different ID each time. Just as in a standard programming language, you can create a procedure with an appropriate parameter to do so. Given the above statement, and a parameter, SQL Anywhere creates the following script:
CREATE DEFINER=`dbrown`@`%` PROCEDURE `member_select`(IN `$member_id` INT) LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'Selects a member given a member_id' BEGIN /* Comments may be added to the procedure body */ SELECT member_id, last_name, first_name, title, institution, address, telephone, fax,email FROM member WHERE member_id=$member_id; END
The CREATE PROCEDURE clause creates a procedure named member_Select and stores it in the DBMS. (You may also store a procedure in a separate file, but you may not call that file from within the DBMS). The DBMS automatically expands the * reference in the original statement into fully qualified field names. (i.e. the name or alias of the table, in this case M, is placed in front of the field name). The parameter is defined as to its name, type, default value, and usage, in that order.
The '$' is not necessary for the parameter name, but is there
to distinguish between the field name and the parameter name.
The various other elements of the creation statement, LANGUAGE SQL, DETERMINISTIC, etc., are required by MySQL but are not significant to what we need to know here. Using a tool such as HeidiSQL automatically adds these items when it is used to create a procedure.
The values returned by a SELECT statement within a procedure are called a result set. A result set returns 0 or more tuples depending upon the the success of the select statement. A SELECT statement that returns no values is not necessarily in error - it simply means that there were no values in the table fitting the constraints. (The expected fields have values of NULL). Errors arise from bad syntax or parameters. The result(...) clause is automatically added to the procedure when it is created, and explicity lists the returned fields and their types. If the fields are changed, the procedure must be changed to match.
Comments appear between /* ... */.
When the procedure is edited, the CREATE PROCEDURE clause becomes ALTER PROCEDURE.
The procedure is executed with the CALL command:
CALL member_select(29)
Parameters
Procedures need not have any output parameters or result sets at all. member_update updates a member and returns neither parameters or result sets.
CREATE DEFINER=`dbrown`@`%` PROCEDURE `member_update`(
IN $member_id smallint,
IN $last_name varchar(25), IN $first_name varchar(25),
IN $title varchar(25), IN $institution varchar(50),
IN $address long varchar, IN $telephone varchar(25),
IN $fax varchar(25), IN $email varchar(25))
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'updates a member'
BEGIN
UPDATE member set
last_name=$last_name, first_name=$first_name,
title=$title, institution=$institution,
address=$address, telephone=$telephone,
fax=$fax, email=$email
where member_id=$member_id;
END
If a procedure does have parameters, they may be of most types supported by the DBMS. Parameters may be defined as IN, OUT, or INOUT. (These definitions carry the standard meanings).
CREATE PROCEDURE add_book( IN $title varchar(50), IN $publisher long varchar, OUT $id ) BEGIN INSERT INTO book (title, publisher) VALUES ($title, $publisher); /* Get the autoincremented id value */ SELECT LAST_INSERT_ID() INTO $id; END
add_book adds a new row to book and extracts the new
autoincremented id for that book using the LAST_INSERT_ID
function. Note carefully the use of the keyword INTO rather
than AS, which is used when the value to be saved is being put
into an already declared alias. AS is used when the alias has
not been previously declared.
This procedure is called with:
CALL add_book( 'My Life', 'Vanity Publications', @id ); SELECT @id;
The variable @id is a session variable that receives
the value of $id when it is returned from the procedure.
Multiple Statements
The previous procedure contains multiple SQL statements, separated by semi-colons. Procedures may be long, complex sets of SQL statements.