SQL in a Nutshell, 2nd Edition

Name

CREATE/ALTER FUNCTION/PROCEDURE Statements

Synopsis

The CREATE FUNCTION and CREATE PROCEDURE statements are very similar in syntax and coding (as are the respective ALTER statements).

The CREATE PROCEDURE statement creates a stored procedure, which takes input arguments and performs conditional processing against various objects in the database. According to the ANSI standard, a stored procedure returns no result set (though it may return a value in an OUTPUT parameter). For example, you might use a stored procedure to perform all the processes that close an accounting cycle.

The CREATE FUNCTION statement creates a user-defined function (UDF), which takes input arguments and returns a single value output in the same way as system-supplied functions like CAST( ) or UPPER( ). These functions, once created, can be called in queries and data-manipulation operations, such as INSERT, UPDATE, and the WHERE clause of DELETE statements. Refer to Chapter 4 for descriptions of built-in SQL functions and their individual vendor implementations.

Platform	Command
DB2	Supported, with variations
MySQL	Supported, with limitations
Oracle	Supported, with variations
PostgreSQL	Supported, with limitations
SQL Server	Supported, with variations

SQL2003 Syntax

Use the following syntax to create a stored procedure or function:

CREATE {PROCEDURE | FUNCTION} object_name
   ( [{[IN | OUT | INOUT] [parameter_name] datatype [AS LOCATOR] [RESULT]}
      [,...] ] )
   [ RETURNS datatype [AS LOCATOR] [CAST FROM ...