![CREATE MESSAGE statement [T-SQL]](gif/backward.gif)
![CREATE PROCEDURE statement [T-SQL]](gif/forward.gif)
Reference Manual
CHAPTER 9. SQL Statements
To create a procedure in the database.
CREATE PROCEDURE [ owner.]procedure-name ( [ parameter , ... ] )
... { [ RESULT ( result-column , ... ) ]
[ ON EXCEPTION RESUME ]
... compound-statement
| EXTERNAL NAME library-call
| [ DYNAMIC RESULT SETS integer-expression ]
EXTERNAL NAME java-call LANGUAGE JAVA
}
parameter:
parameter_mode parameter-name data-type [ DEFAULT expression ]
| SQLCODE
| SQLSTATE
parameter_mode:
IN | OUT | INOUT
result-column:
column-name data-type
library-call:
'[operating-system:]function-name@library.dll; ...'
operating-system:
OS2
| Windows3X
| Windows95
| WindowsNT
| NetWare
java-call:
'[package-name.]ClassName.methodName method-signature'
Must have RESOURCE authority.
For external procedures, must have DBA authority.
Automatic commit.
Using Procedures, Triggers, and Batches
The CREATE PROCEDURE statement creates (stores) a procedure in the database. A procedure can be created for another user by specifying a owner. A procedure is invoked with a CALL statement
The body of a procedure consists of a compound statement. For information about compound statements, see BEGIN... END statement.
For information about error handling in stored procedures, see Errors and warnings in procedures and triggers.
Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type (see SQL Data Types), and must be prefixed by one of the keywords IN, OUT or INOUT. The keywords have the following meanings:
IN The parameter is an expression that provides a value to the procedure.
OUT The parameter is a variable that could be given a value by the procedure.
INOUT The parameter is a variable that provides a value to the procedure, and could be given a new value by the procedure.
When procedures are executed using the CALL statement, not all parameters need to be specified. If a default value is provided in the CREATE PROCEDURE statement, missing parameters are assigned the default values. If no default value is supplied, the parameter is NULL.
SQLSTATE and SQLCODE are special parameters that output the SQLSTATE or SQLCODE value when the procedure ends (they are OUT parameters). Whether or not a SQLSTATE and SQLCODE parameter is specified, the SQLSTATE and SQLCODE special constants can always be checked immediately after a procedure call to test the return status of the procedure.
The SQLSTATE and SQLCODE special constant values are modified by the next SQL statement. Providing SQLSTATE or SQLCODE as procedure arguments allows the return code to be stored in a variable.
A procedure that returns result sets (Returning results from procedures) may have a RESULT clause. The parenthesized list following the RESULT keyword defines the number of result columns and name and type. This information is returned by the Embedded SQL DESCRIBE or by ODBC SQLDescribeCol when a CALL statement is being described. Allowable data types are listed in SQL Data Types.
Some procedures can return different result sets, with different numbers of columns, depending on how they are executed. For example, the following procedure returns two columns under some circumstances, and one in others.
CREATE PROCEDURE names( IN formal char(1))
BEGIN
IF formal = 'n' THEN
SELECT emp_fname
FROM employee
ELSE
SELECT emp_lname,emp_fname
FROM employee
END IF
END
Procedures with variable result sets must be written without a RESULT clause, or in Transact-SQL. Their use is subject to the following limitations:
Embedded SQL You must DESCRIBE the procedure call after the cursor for the result set is opened, but before any rows are returned, in order to get the proper shape of result set.
ODBC Variable result-set procedures can be used by ODBC applications. The proper description of the variable result sets is carried out by the ODBC driver.
Open Client applications Variable result-set procedures can be used by Open Client applications.
If your procedure does not return variable result sets, you should use a RESULT clause. The presence of this clause prevents ODBC and Open Client applications from re-describing the result set after a cursor is open.
In order to handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the stored procedure definition. To avoid this problem, you can use column aliases in your procedure result set cursor.
A procedure using the EXTERNAL NAME clause is a wrapper around a call to an external dynamic link library, and is called an external stored procedure. An external stored procedure can have no clauses other than the EXTERNAL NAME clause following the parameter list. For a description of external procedures, see Calling external libraries from procedures.
A procedure that uses EXTERNAL NAME with a LANGUAGE JAVA clause is a wrapper around a Java method.
If the DYNAMIC RESULT SETS clause is not provided, it is assumed that no result sets are being returned from the method.
If the number of parameters is less than the number indicated in the method-signature then the difference must equal the number specified in DYNAMIC RESULT SETS, and each parameter in the method signature in excess of those in the procedure parameter list must have a method signature of [Ljava.sql.ResultSet.
A Java method signature is compact character representation of the types of the parameters and the type of the return value. The format is...
'( [type,...] ) type'
... where type is as follows:
'Z' boolean
'B' byte
'S' short
'I ' int
'J' long
'F ' float
'D ' double
'C ' char
'V ' void
[type array of type
Lclass-name object class
For example,
double some_method(
boolean a,
int b,
java.math.BigDecimal c,
byte [][] d,
java.sql.ResultSet[] d ) {
}
would have the following signature:
'(ZILjava/math/BigDecimal;[[B[Ljava/sql/ResultSet;)D'
SQL/92 Persistent Stored Module feature.
Sybase The Transact-SQL CREATE PROCEDURE statement is different.
SQLJ The syntax extensions for Java result sets are as specified in the proposed SQLJ1 standard.
The following procedure uses a case statement to classify the results of a query.
CREATE PROCEDURE ProductType (IN product_id INT, OUT type CHAR(10))
BEGIN
DECLARE prod_name CHAR(20) ;
SELECT name INTO prod_name FROM "DBA"."product"
WHERE id = product_id;
CASE prod_name
WHEN 'Tee Shirt' THEN
SET type = 'Shirt'
WHEN 'Sweatshirt' THEN
SET type = 'Shirt'
WHEN 'Baseball Cap' THEN
SET type = 'Hat'
WHEN 'Visor' THEN
SET type = 'Hat'
WHEN 'Shorts' THEN
SET type = 'Shorts'
ELSE
SET type = 'UNKNOWN'
END CASE ;
END
The following procedure uses a cursor and loops over the rows of the cursor to return a single value.
CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
DECLARE err_notfound EXCEPTION
FOR SQLSTATE '02000' ;
DECLARE curThisCust CURSOR FOR
SELECT company_name, CAST( sum(sales_order_items.quantity *
product.unit_price) AS INTEGER) VALUE
FROM customer
LEFT OUTER JOIN sales_order
LEFT OUTER JOIN sales_order_items
LEFT OUTER JOIN product
GROUP BY company_name ;
DECLARE ThisValue INT ;
DECLARE ThisCompany CHAR(35) ;
SET TopValue = 0 ;
OPEN curThisCust ;
CustomerLoop:
LOOP
FETCH NEXT curThisCust
INTO ThisCompany, ThisValue ;
IF SQLSTATE = err_notfound THEN
LEAVE CustomerLoop ;
END IF ;
IF ThisValue > TopValue THEN
SET TopValue = ThisValue ;
SET TopCompany = ThisCompany ;
END IF ;
END LOOP CustomerLoop ;
CLOSE curThisCust ;
END