O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required