CREATE PROCEDURE Statement (External Procedures)

For CREATE PROCEDURE reference information for Java UDFs, see CREATE PROCEDURE Statement (Java UDF). For CREATE PROCEDURE reference information for table UDFs, see CREATE PROCEDURE Statement (Table UDF)

Syntax

CREATE[ OR REPLACE ] PROCEDURE [ owner.]procedure-name ( [ parameter, …] ) 
   [ SQL SECURITY { INVOKER | DEFINER } ] 
   [ RESULT ( result-column, …) | NO RESULT SET ]  
   [ DYNAMIC RESULT SETS integer-expression ]
   [ EXTERNAL NAME ‘native-call’
      | EXTERNAL NAME 'c-call' LANGUAGE { C_ESQL32 | C_ESQL64 | C_ODBC32 | C_ODBC64 }

      | EXTERNAL NAME 'perl-call' LANGUAGE PERL
      | EXTERNAL NAME 'php-call' LANGUAGE PHP 
      | 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

native-call:
   [operating-system:]function-name@library

c-call:
   [operating-system:]function-name@library; ...

perl-call:
   <file=perl-file> $sa_perl_return = perl-subroutine( $sa_perl_arg0[, ... ] ) 

php-call:
   <file=php-file> print php-func( $argv[1][, ... ] ) 

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

operating-system:
   Unix

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

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

Parameters

CREATE – creates a new procedure.

OR REPLACE – replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions.

parameter – parameter names must conform to the rules for other database identifiers, such as column names, and must be a valid SQL data type. The keywords have the following meanings:
Parameters can be prefixed by one of the keywords IN, OUT or INOUT. If no keyword is specified, parameters are INOUT by default. The keywords have the following meanings:
- IN – parameter is an expression that provides a value to the procedure.
- OUT – parameter is a variable that could be given a value by the procedure.
- INOUT – parameter is a variable that provides a value to the procedure, and could be given a new value by the procedure.
Note: TABLE parameters cannot be declared as INOUT or OUT. See CREATE PROCEDURE Statement (Table UDF).

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.
Note: You cannot CALL a table UDF. Use the CREATE PROCEDURE statement.

RESULT – 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 Reference: Building Blocks, Tables, and Procedures > SQL Data Types.
Embedded SQL (LANGUAGE C_ESQL32, LANGUAGE C_ESQL64) or ODBC (LANGUAGE C_ODBC32, LANGUAGE C_ODBC64) external procedures can return 0 or 1 result sets.

Perl or PHP (LANGUAGE PERL, LANGUAGE PHP) external procedures cannot return result sets. Procedures that call native functions loaded by the database server cannot return result sets.

CLR or Java (LANGUAGE CLR, LANGUAGE JAVA) external procedures can return 0, 1, or more result sets.

NO RESULT SET – 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.

DYNAMIC RESULT SETS – Use this clause with LANGUAGE CLR and LANGUAGE JAVA calls. This clause is meaningful only if you specify LANGUAGE. If you specify a RESULT clause, DYNAMIC RESULT SETS defaults to 1. If you do not specify a RESULT clause, DYNAMIC RESULT SETS defaults to 0. Note that procedures that call into Perl or PHP (LANGUAGE PERL, LANGUAGE PHP) external functions cannot return result sets. Procedures that call native functions loaded by the database server cannot return result sets.
The C_ESQL32, C_ESQL64, C_ODBC32, and C_ODBC64 external environments can also return result sets (like CLR and JAVA), but they are restricted to only one dynamic result set.

Procedures that call into Perl or PHP (LANGUAGE PERL, LANGUAGE PHP) external functions cannot return result sets. Procedures that call native functions loaded by the database server cannot return result sets.

SQL SECURITY – defines whether the procedure is executed as the INVOKER (the user who is calling the procedure), or as the DEFINER (the user who owns the procedure). The default is DEFINER. For external calls, this clause establishes the ownership context for unqualified object references in the external environment.
Extra memory is used when you specify SQL SECURITY INVOKER, because annotation must be done for each user that calls the procedure. Also, name resolution is performed as the invoker as well. Therefore, qualify all object names (tables, procedures, and so on) with their appropriate owner. For example, suppose user1 creates this procedure:
```
 CREATE PROCEDURE user1.myProcedure()
	RESULT( columnA INT )
	SQL SECURITY INVOKER
	BEGIN
		SELECT columnA FROM table1;
	END;
```
If user2 attempts to run this procedure and a table user2.table1 does not exist, a table lookup error results. Additionally, if a user2.table1 does exist, that table is used instead of the intended user1.table1. To prevent this situation, qualify the table reference in the statement (user1.table1, instead of just table1).

EXTERNAL NAME – a procedure using the EXTERNAL NAME clause with no LANGUAGE attribute defines an interface to a native function written in a programming language such as C. The native function is loaded by the database server into its address space.
The library name can include the file extension, which is typically .dll on Windows and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries. This is a formal example:
```
CREATE PROCEDURE mystring( IN instr LONG VARCHAR ) 
EXTERNAL NAME
'mystring@mylib.dll;Unix:mystring@mylib.so';
```
A simpler way to write the preceding EXTERNAL NAME clause, using platform-specific defaults:
```
CREATE PROCEDURE mystring( IN instr LONG VARCHAR )
EXTERNAL NAME 'mystring@mylib';
```
When called, the library containing the function is loaded into the address space of the database server. The native function executes as part of the server. In this case, if the function causes a fault, then the database server terminates. Because of this, loading and executing functions in an external environment using the LANGUAGE attribute is recommended. If a function causes a fault in an external environment, the database server continues to run.

EXTERNAL NAME c-call LANGUAGE { C_ESQL32 | C_ESQL64 | C_ODBC32 | C_ODBC64 } : – to call a compiled native C function in an external environment instead of within the database server, the stored procedure or function is defined with the EXTERNAL NAME clause followed by the LANGUAGE attribute specifying one of C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.
When the LANGUAGE attribute is specified, then the library containing the function is loaded by an external process and the external function will execute as part of that external process. In this case, if the function causes a fault, then the database server will continue to run.
The following is a sample procedure definition.
```
CREATE PROCEDURE ODBCinsert( 
  IN ProductName CHAR(30),
  IN ProductDescription CHAR(50)
)
NO RESULT SET
EXTERNAL NAME 'ODBCexternalInsert@extodbc.dll'
LANGUAGE C_ODBC32; 
```

EXTERNAL NAME perl-call LANGUAGE CLR – to call a Perl function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PERL attribute.
A Perl stored procedure or function behaves the same as a SQL stored procedure or function with the exception that the code for the procedure or function is written in Perl and the execution of the procedure or function takes place outside the database server (that is, within a Perl executable instance).
Sample procedure definition:
```
CREATE PROCEDURE PerlWriteToConsole( IN str LONG VARCHAR) 
NO RESULT SET
EXTERNAL NAME '<file=PerlConsoleExample> 
WriteToServerConsole( $sa_perl_arg0 )' 
LANGUAGE PERL;
```

EXTERNAL NAME perl-call LANGUAGE PHP – to call a PHP function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PHP attribute.
A PHP stored procedure or function behaves the same as a SQL stored procedure or function with the exception that the code for the procedure or function is written in PHP and the execution of the procedure or function takes place outside the database server (that is, within a PHP executable instance).
Sample procedure definition:
```
CREATE PROCEDURE PHPPopulateTable() 
NO RESULT SET
EXTERNAL NAME '<file=ServerSidePHPExample>
ServerSidePHPSub()'
LANGUAGE PHP;
```

EXTERNAL NAME java-call LANGUAGE JAVA – A Java method signature is a compact character representation of the types of the parameters and the type of the return value.

To call a Java method in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE JAVA attribute.

A Java-interfacing stored procedure or function behaves the same as a SQL stored procedure or function with the exception that the code for the procedure or function is written in Java and the execution of the procedure or function takes place outside the database server (that is, within a Java Virtual Machine).

Sample procedure definition:

CREATE PROCEDURE HelloDemo( IN
name LONG VARCHAR ) 
NO RESULT SET
EXTERNAL NAME 'Hello.main([Ljava/lang/String;)V'
LANGUAGE JAVA;

The descriptors for arguments and return values from Java methods have the following meanings:

Field Type	Java Data Type
B	byte
C	char
D	double
F	float
I	int
J	long
L`class-name`;	an instance of the `class-name` class. The class name must be fully qualified, and any dot in the name must be replaced by a backslash. 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 ) {
}

has the signature:

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

Usage

The body of a procedure consists of a compound statement. For information on compound statements, see BEGIN … END Statement.

Note: There are two ways to create stored procedures: ISO/ANSI SQL and T-SQL. BEGIN TRANSACTION, for example, is T-SQL specific when using CREATE PROCEDURE syntax. Do not mix syntax when creating stored procedures. See CREATE PROCEDURE Statement [T-SQL].

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

You cannot create TEMPORARY external call procedures.

When referencing a temporary table from multiple procedures, a potential issue can arise if the temporary table definitions are inconsistent and statements referencing the table are cached.

You can create permanent stored procedures that call external or native procedures written in a variety of programming languages. You can use PROC as a synonym for PROCEDURE.

Side Effects

Automatic commit

Standards

SQL—ISO/ANSI SQL compliant.
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.

Permissions

External procedure to be owned by self – Requires:

CREATE EXTERNAL REFERENCE system privilege.
CREATE PROCEDURE system privilege.

External procedure to be owned by any user – Requires CREATE EXTERNAL REFERENCE system privilege. Also requires one of:

CREATE ANY PROCEDURE system privilege.
CREATE ANY OBJECT system privilege.