iAnywhere.com - CREATE PROCEDURE statement

CREATE PROCEDURE statement

Description

Use this statement to create a procedure in the database.

CREATE PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
{   [ RESULT ( result-column, ... ) | NO RESULT SET ]
    [ ON EXCEPTION RESUME ]
      compound-statement
   | AT location-string
   | EXTERNAL NAME library-call
   | [ DYNAMIC RESULT SETS integer-expression ]
     [ EXTERNAL NAME java-call LANGUAGE JAVA ]
    }

Syntax 2

CREATE PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
compound-statement

Syntax 3

CREATE PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
URL url-string
[ TYPE { 'HTTP[:{GET|POST}]' | 'SOAP[:{RPC|DOC}]' } ]
[ NAMESPACE namespace-string ]
[ CERTIFICATE certificate-string ]
[ CLIENTPORT clientport-string ]
[ PROXY proxy-string ]

url-string :
'{HTTP|HTTPS}://[user:password@]hostname[:port][/path]'

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; ...'

operating-system :
Windows95 | WindowsNT | NetWare | UNIX

java-call :
'[package-name.]class-name.method-name method-signature'

method-signature :
([field-descriptor, ... ] ) return-descriptor

field-descriptor | return-descriptor :
Z | B | S | I | J | F | D | C | V | [descriptor | Lclass-name;

Parameters

CREATE PROCEDURE clause 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). Parameters can be prefixed them with one of the keywords IN, OUT, or INOUT. If you do not specify one of these values, parameters are INOUT parameters by default. 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 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 clause 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. Allowable data types are listed in SQL Data Types.

For more information on returning result sets from procedures, see Returning results from procedures.

Some procedures can produce 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, OLE DB, ADO.NET Variable result-set procedures can be used by applications using these interfaces. The proper description of the result sets is carried out by the driver or provider.
Open Client applications Variable result-set procedures can be used by Open Client applications.

If your procedure returns only one result set, you should use a RESULT clause. The presence of this clause prevents ODBC and Open Client applications from redescribing 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'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 clause Declares that no result set is returned by this procedure. This is useful when an external environment needs to know that a procedure does not return a result set.

ON EXCEPTION RESUME clause 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

You should not use explicit error handling code with an ON EXCEPTION RESUME clause.

For more information, see ON_TSQL_ERROR option [compatibility].

EXTERNAL NAME clause A procedure using the EXTERNAL NAME clause is a wrapper around a call to an external library. A stored procedure using EXTERNAL NAME can have no other clauses following the parameter list. The library name may include the file extension, which is typically .dll on Windows, .so on UNIX, and .nlm on NetWare. In the absence of the extension, the software appends the platform-specific default file extension for libraries. On NetWare, if no NLM name is given, the NLM containing the symbol must already be loaded when the function is called.

For information about external library calls, see Calling external libraries from procedures.

AT location-string clause 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 filenames and extensions to be used in the database and owner fields.

For example, the following statement creates a 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 Using remote procedure calls (RPCs).

DYNAMIC RESULT SETS clause 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.

URL clause For use only when defining a HTTP or SOAP web services client function. Specifies the URI of the web service. The optional username and password parameters provide a means of supplying the credentials needed for HTTP Basic Authentication. HTTP Basic Authentication base-64 encodes the user and password information and passes it in the "Authentication" header of the HTTP request.

TYPE clause Used to specify the format used when making the web service request. If SOAP is specified or no type clause is included, the default type SOAP:RPC is used. HTTP implies HTTP:POST. Since SOAP requests are always sent as XML documents, HTTP:POST is always used to send SOAP requests.

NAMESPACE clause Applies to SOAP client procedures only. This clause identifies the method namespace usually required for both SOAP:RPC and SOAP:DOC requests. The SOAP server handling the request uses this namespace to interpret the names of the entities in the SOAP request message body. The namespace can be obtained from the WSDL description of the SOAP service available from the web service server. The default value is the procedure's URL, up to but not including the optional path component.

CERTIFICATE clause In order to make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. The certificate may be placed in a file and the name of the file provided using the file key, or the whole certificate may be placed in a string, but not both. The following keys are available:

Key	Abbreviation	Description
file		The filename of the certificate.
certificate	cert	The certificate itself.
company	co	The company specified in the certificate.
unit		The company unit specified in the certificate.
name		The common name specified in the certificate.

Certificates are required only for requests that are either direced to an HTTPS server, or may be redirected from a non-secure to a secure server. A certificate value is not required when sent to an Adpative Server Anywhere database server that accepts HTTPS requests, unless a certificate parameter was used when the database server was started.

CLIENTPORT clause Identifies the port number on which the HTTP client procedure communicates using TCP/IP. It is provided for and recommended only for connections across firewalls, as firewalls filter according to the TCP/UDP port. You may specify a single port numbers, ranges of port numbers, or a combination of both; for example, CLIENTPORT '85,90-97'.

For more information, see ClientPort protocol option [CPORT].

PROXY clause Specifies the URI of a proxy server. For use when the client must access the network through a proxy. Indicates that the procedure is to connect to the proxy server and send the request to the web service through it.

EXTERNAL NAME LANGUAGE JAVA clause 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 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 a compact character representation of the types of the parameters and the type of the return value. It is an error to put a space before the signature.

The field-descriptor and return-descriptor have the following meanings:

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[] rs ) {
}

would have the following signature:

'(ZILjava/math/BigDecimal;[[B[Ljava/SQL/ResultSet;)D'

For more information, see Returning result sets from Java methods.

Usage

The CREATE PROCEDURE statement 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.

If a stored procedure returns a result set, it cannot also set output parameters or return a return value.

Parameter values are passed as part of the request. The syntax used depends on the type of request. For HTTP:GET, the paramters are passed as part of the URL; for HTTP:POST requests, the values are placed in the body of the request. Parameters to SOAP requests are always bundled in the request body.

For web service client procedures, parameter values are passed as part of the request. The syntax used depends on the type of request. For HTTP:GET, the paramters are passed as part of the URL; for HTTP:POST requests, the values are placed in the body of the request. Parameters to SOAP requests are always bundled in the request body.

Permissions

Must have RESOURCE authority.

Must have DBA authority for external procedures or to create a procedure for another user.

Side effects

Automatic commit.

Standards and compatibility

SQL/92 Persistent Stored Module feature.
SQL/99 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.

Examples

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