CREATE PROCEDURE Statement (Table UDF)

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.

Syntax

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter[, …]] )
| RESULT  result-column [, …] )
[ SQL SECURITY { INVOKER | DEFINER } ] 
EXTERNAL NAME ‘external-call’ 
    
 

Parameters

• parameter: – [ IN ] parameter-name data-type [ DEFAULT expression ]

  | [ IN  ] parameter-name table-type

• table-type: – TABLE(  column-name data-type [, ...] )
• external-call: – [column-name:]function-name@library; ...

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.

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.

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.

• IN keyword – Parameters can be prefixed with the keyword IN:

  • IN—The parameter is an object that provides a value for a scalar parameter or a set of values for a TABLE parameter to the UDF.

  Note: TABLE parameters cannot be declared as INOUT or OUT. You can only have one table parameter (the position of which is not important).
• OR REPLACE – Specifying OR REPLACE (CREATE OR REPLACE PROCEDURE) creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions. An error is returned if you attempt to replace a procedure that is already in use.
• RESULT – Declares the column names and their data types for the result set of the external UDF. The data types of the columns must be a valid SQL data type (e.g., a column in the result set cannot have TABLE as data type). The set of datums in the result implies the TABLE. External UDFs can only have one result set of type TABLE.
  Note: TABLE is not an output value. A table UDF cannot have LONG VARBINARY or LONG VARCHAR data types in its result set, but a table parameterized function (TPF) can have large object (LOB) data in its result set.

  A TPF cannot produce LOB data, but can have columns in the result set as LOB data types. However, the only way to get LOB data in the output is to pass a column from an input table to the output table. The describe attribute EXTFNAPIV4_DESCRIBE_COL_VALUES_SUBSET_OF_INPUT allows this, as illustrated in the sample file tpf_blob.cxx.

  For more information on returning result sets from procedures, see System Administration Guide: Volume 2 > Using Procedures and Batches.

• SQL SECURITY – Defines whether the procedure is executed as the INVOKER (the user who is calling the UDF), or as the DEFINER (the user who owns the UDF). The default is DEFINER.

  When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, care should be taken to 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 – EXTERNAL NAME ‘external-call’: external-call: [operating-system: ]function-name@library; ....

  An external UDF must have EXTERNAL NAME clause which defines an interface to a function written in a programming language such as C. The 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 CHAR(255),
  	IN input_table TABLE(A INT) )
  	RESULT (CHAR(255))
  EXTERNAL NAME
  'mystring@mylib.dll;Unix:mystring@mylib.so'

  A simpler way to write the preceding EXTERNAL NAME clause, using platform-specific defaults, is as follows:

  CREATE PROCEDURE mystring( IN instr CHAR(255),
  	IN input_table TABLE(A INT) ) 
  	RESULT (CHAR(255))
  EXTERNAL NAME ‘mystring@mylib’

Standards

Permissions

Must have RESOURCE authority, unless creating a temporary procedure. Users with DBA authority can create UDFs for other users by specifying an owner. Users must have DBA authority to create an external UDFs or to create an external UDF for another user