CREATE PROCEDURE Statement (Table UDF)

Creates an interface to an external table user-defined function (table UDF). Users must be specifically licensed to use table UDFs.

For CREATE PROCEDURE reference information for external procedures, see CREATE PROCEDURE Statement (External Procedures). For CREATE PROCEDURE reference information for Java UDFs, see CREATE PROCEDURE Statement (Java UDF)

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

Syntax

CREATEOR REPLACE ] PROCEDUREowner.]procedure-name ( [ parameter[, …]] )
| RESULT  result-column [, …] )
[ SQL SECURITY { INVOKER | DEFINER } ] 
EXTERNAL NAMEexternal-call’ 
    
parameter - (back to Syntax)IN ] parameter-name data-typeDEFAULT expression ] 
   | [ IN  ] parameter-name table-type

table-type - (back to parameter)
   TABLEcolumn-name data-type [, ...] ) 

external-call - (back to Syntax)
   [column-name:]function-name@library; ...

Parameters

(back to top)

Usage

(back to top)

You define table UDFs using the a_v4_extfn API. CREATE PROCEDURE statement reference information for external procedures that do not use the a_v3_extfn or a_v4_extfn APIs is located in a separate topic. CREATE PROCEDURE statement reference information for Java UDFs is located in a separate topic.

The CREATE PROCEDURE statement creates a procedure in the database. To create a procedure for themselves, a user must have the CREATE PROCEDURE system privilege. To create a procedure for others, a user must specify the owner of the procedure and must have either the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege. If the procedure contains an external reference, the user must have the CREATE EXTERNAL REFERENCE system privilege in addition to previously mentioned system privileges, regardless of who owns the procedure.

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

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. Use caution when referencing temporary tables within procedures.

You can use the CREATE PROCEDURE statement to create external table UDFs implemented in a different programming language than SQL. However, be aware of the table UDF restrictions before creating external UDFs.

The data type for a scalar parameter, a result column, and a column of a TABLE parameter must be a valid SQL data type.

Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type.

TPFs support a mix scalar parameters and single TABLE parameter. A TABLE parameter must define a schema for an input set of rows to be processed by the UDF. The definition of a TABLE parameter includes column names and column data types.

TABLE(c1 INT, c2 CHAR(20))

The above example defines a schema with the two columns c1 and c2 of types INT and CHAR(20). Each row processed by the UDF must be a tuple with two (2) values. TABLE parameters, unlike scalar parameters cannot be assigned a default value.

Standards

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Sybase Database product—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

(back to top)

Unless creating a temporary procedure, a user must have the CREATE PROCEDURE system privilege to create a UDF for themselves. To create a UDF for others, they must specify the owner of the procedure and must have either the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege. If the procedure contains an external reference, a user must also have the CREATE EXTERNAL REFERENCE system privilege, in addition to the previously mentioned system privileges.