Creates a new procedure in the database.
CREATE PROCEDURE [ owner.]procedure-name ( [ parameter , ... ] ) { [ RESULT ( result-column, ... ) | NO RESULT SET ] [ ON EXCEPTION RESUME ] compound statement | AT location-string | | [ DYNAMIC RESULT SETS integer-expression ] [ EXTERNAL NAME java-call LANGUAGE JAVA ] }
parameter_mode parameter-name data-type [ DEFAULT expression ] | SQLCODE | SQLSTATE
IN | OUT | INOUT
column-name data-type
'function-name@library.dll; ...'
'[package-name.]class-name.method-name method-signature'
( [ field-descriptor,... ] ) return-descriptor
Z | B | S | I | J | F | D | C | V | [descriptor | Lclass-name;
This 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
This 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
CREATE PROCEDURE creates a procedure in the database. Users with DBA authority can create procedures for other users by specifying an owner. A procedure is invoked with a CALL statement.
The body of a procedure consists of a compound statement. For information on compound statements, see BEGIN... END statement.
CREATE PROCEDURE Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type (see Chapter 4, “SQL Data Types”), and must be prefixed by 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 CALL, 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 an argument is not provided in the CALL statement, and no default is set, an error is given.
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 values can always be checked immediately after a procedure call to test the return status of the procedure.
The SQLSTATE and SQLCODE special 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.
RESULT The RESULT clause declares the number and type of columns in the result set. The parenthesized list following the RESULT keyword defines the result column names and types. This information is returned by the Embedded SQL DESCRIBE or by ODBC SQLDescribeCol when a CALL statement is being described. Allowed data types are listed in Chapter 4, “SQL Data Types.”
For more information on returning result sets from procedures, see Chapter 8, “Using Procedures and Batches” in the Sybase IQ System Administration Guide.
Some procedures can return more than one result set, 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. The CURSOR cursor-name clause on the DESCRIBE statement is required.
ODBC Variable result-set procedures can be used by ODBC applications. The proper description of the 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 returns only one result set, use a RESULT clause. The presence of this clause prevents ODBC and Open Client applications from describing the result set again after a cursor is open.
To handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure’s defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the procedure definition. To avoid this problem, use column aliases in the SELECT statement that generates the result set.
NO RESULT SET This clause declares that this procedure returns no result set. This is useful when an external environment needs to know that a procedure does not return a result set.
ON EXCEPTION RESUME This clause enables Transact-SQL -like error handling to be used within a Watcom-SQL syntax procedure.
If you use ON EXCEPTION RESUME, the procedure takes an action that depends on the setting of the ON_TSQL_ERROR option. If ON_TSQL_ERROR is set to CONDITIONAL (which is the default) the execution continues if the next statement handles the error; otherwise, it exits.
Error-handling statements include the following:
IF
SELECT @variable =
CASE
LOOP
LEAVE
CONTINUE
CALL
EXECUTE
SIGNAL
RESIGNAL
DECLARE
SET VARIABLE
Do not use explicit error-handling code with an ON EXCEPTION RESUME clause.
For more information, see “ON_TSQL_ERROR option [TSQL]”.
AT location-string Create a proxy stored procedure on the current database for a remote procedure specified by location-string. The AT clause supports the semicolon (;) as a field delimiter in location-string. If no semicolon is present, a period is the field delimiter. This allows file names and extensions to be used in the database and owner fields.
For example, the following statement creates the proxy procedure remotewho that calls the dbo.sp_who procedure on the master database of the bostonase server:
CREATE PROCEDURE remotewho () AT 'bostonase.master.dbo.sp_who'
Remote procedures can return only up to 254 characters in output variables.
For information on remote servers, see CREATE SERVER statement. For information on using remote procedures, see the section “Using remote procedure calls (RPCs)” in Chapter 16, “Accessing Remote Data” in the Sybase IQ System Administration Guide.
DYNAMIC RESULT SETS This clause is for use with procedures that are wrappers around Java methods. If the DYNAMIC RESULT SETS clause is not provided, it is assumed that the method returns no result set.
EXTERNAL NAME LANGUAGE JAVA A procedure that uses EXTERNAL NAME with a LANGUAGE JAVA clause is a wrapper around a Java method.
If the number of parameters is less than the number indicated in the method-signature, 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;.
Java method signatures A Java method signature is a compact character representation of the types of the parameters and the type of the return value.
The meanings of field-descriptor and return-descriptor are listed in Table 6-7.
Field type |
Java data type |
---|---|
B |
byte |
C |
char |
D |
double |
F |
float |
I |
int |
J |
long |
Lclass-name; |
an instance of the class class-name. The class name must be fully qualified, and any dot in the name must be replaced by a /. For example, java/lang/String |
S |
short |
V |
void |
Z |
boolean |
[ |
use one for each dimension of an array |
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'
As procedures are dropped and created, databases created prior to Sybase IQ 12.6 may eventually reach the maximum proc_id limit of 32767, causing CREATE PROCEDURE to return an “Item already exists” error in Sybase IQ 12.6. For workaround, see “Insufficient procedure identifiers,” Sybase IQ Troubleshooting and Recovery Guide.
Automatic commit.
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.
Must have RESOURCE authority. For external procedures, must have DBA authority.
EXECUTE IMMEDIATE statement [ESQL] [SP]
“Copy Definition utility (defncopy)” in Chapter 3, “Database Administration Utilities” of the Sybase IQ Utility Guide