CREATE PROCEDURE 语句

使用此语句在数据库中创建用户定义的 SQL 过程。要创建外部过程接口，请参见CREATE PROCEDURE 语句（外部过程）。要创建 Web 服务过程，请参见CREATE PROCEDURE 语句（Web 服务）。

语法

CREATE [ OR REPLACE | TEMPORARY ] PROCEDURE [ owner.]procedure-name 
( [ parameter, ... ] )
[ RESULT ( result-column, ... ) | NO RESULT SET ]
[ SQL SECURITY { INVOKER | DEFINER } ]
[ ON EXCEPTION RESUME ]
compound-statement | AT location-string

parameter :
  parameter-mode parameter-name data-type [ DEFAULT expression ]
| SQLCODE
| SQLSTATE

parameter-mode : IN 
| OUT 
| INOUT

result-column : column-name data-type

参数

CREATE PROCEDURE 可以创建永久或临时 (TEMPORARY) 存储过程。可使用 PROC 作为 PROCEDURE 的同义词。

参数名必须符合其它数据库标识符（如列名）的规则。它们必须是有效的 SQL 数据类型。有关有效数据类型的列表，请参见SQL 数据类型。

参数可以使用关键字 IN、OUT 或 INOUT 作为前缀。如果未指定上述任何值，则缺省情况下将使用 INOUT 参数。这些关键字具有以下含义：

IN 此参数是一个为过程提供值的表达式。
OUT 此参数是一个可由过程赋值的变量。
INOUT 此参数是一个为过程提供值的变量，并且可由过程赋值。

使用 CALL 语句执行过程时，不需要指定所有参数。如果在 CREATE PROCEDURE 语句中提供了缺省值，缺少的参数会被分配缺省值。如果 CALL 语句中既未提供参数也未设置缺省值，则会给出错误。

SQLSTATE 和 SQLCODE 是特殊 OUT 参数，它们在过程结束时输出 SQLSTATE 或 SQLCODE 值。在过程调用后可立即检查 SQLSTATE 和 SQLCODE 特殊值，以测试过程的返回状态。

SQLSTATE 和 SQLCODE 特殊值会由下一个 SQL 语句修改。如果将 SQLSTATE 或 SQLCODE 作为过程参数提供，则会允许返回代码存储在变量中。

指定 CREATE OR REPLACE PROCEDURE 将创建一个新过程或替换同名的现有过程。此子句将更改过程的定义，但保留现有权限。不能将 OR REPLACE 子句与临时过程一起使用。或者，如果正在替换的过程已经使用，则会返回错误。如果指定 CREATE TEMPORARY PROCEDURE，则会意味着该存储过程仅对创建它的连接可见，并在删除该连接时随之自动删除。也可以显式删除临时存储过程。您无法在临时存储过程上执行 ALTER、GRANT 或 REVOKE，而且与其它存储过程不同，临时存储过程不会在目录或事务日志中予以记录。

临时过程使用其创建者（当前用户）或指定所有者的权限执行。可在以下情况下为临时过程指定所有者：

临时过程是在永久存储过程中创建的
临时过程与永久过程的所有者相同

要删除临时过程的所有者，必须首先删除该临时过程。

临时存储过程可在连接到只读数据库时加以创建和删除，且不能为外部过程。

例如，以下临时存储过程会删除名为 CustRank 的表（如果此表存在）。对于此示例，该过程假定表名是唯一的，且可由过程创建者引用而不必指定表所有者：

CREATE TEMPORARY PROCEDURE drop_table( IN @TableName char(128) )
BEGIN
    IF EXISTS  ( SELECT * FROM SYS.SYSTAB WHERE table_name = @TableName ) THEN
   EXECUTE IMMEDIATE 'DROP TABLE "' || @TableName || '"';
   MESSAGE 'Table "' || @TableName || '" dropped' to client;
    END IF;
END;
CALL drop_table( 'CustRank' );

RESULT 子句 RESULT 子句声明结果集中的列的数量和类型。RESULT 关键字后面括在括号内的列表定义结果的列名和类型。描述 CALL 语句时，嵌入式 SQL DESCRIBE 或 ODBC SQLDescribeCol 会返回此信息。有关有效数据类型的列表，请参见SQL 数据类型。

有关从过程返回结果集的详细信息，请参见从过程返回结果。

视执行方式而定，某些过程可产生多个结果集，并且列数也不同。例如，以下过程在有些情况下会返回两列，而在有些情况下则会返回一列。

CREATE PROCEDURE names( IN formal char(1))
BEGIN
   IF formal = 'n' THEN
      SELECT GivenName
      FROM Employees
   ELSE
      SELECT Surname, GivenName
      FROM Employees
   END IF
END;

包含可变结果集的过程必须用不带 RESULT 子句的语句编写，或者用 Transact-SQL 编写。它们的使用受以下限制制约：

嵌入式 SQL 必须在打开用于结果集的游标之后、返回任何行之前对过程调用执行 DESCRIBE，才能获取正确形式的结果集。这需要使用 DESCRIBE 语句的 CURSOR cursor-name 子句。
ODBC、OLE DB、ADO.NET 使用这些接口的应用程序可以使用可变结果集过程。结果集的正确描述由驱动程序或提供程序完成。
Open Client 应用程序 Open Client 应用程序可以使用可变结果集过程。

如果过程仅返回一个结果集，则应使用 RESULT 子句。有了这个子句，便可防止 ODBC 和 Open Client 应用程序在游标打开后重新描述结果集。

要处理多个结果集，ODBC 必须描述当前正在执行的游标，而不是过程的已定义结果集。因此，ODBC 不会始终按过程定义的 RESULT 子句中的定义来描述列名。为避免这种问题，请在生成结果集的 SELECT 语句中使用列的别名。

NO RESULT SET 子句 声明此过程不返回结果集。当外部环境需要知道某个过程不返回结果集时，这将非常有用。

SQL SECURITY 子句 SQL SECURITY 子句定义该过程是作为 INVOKER（调用该过程的用户）执行还是作为 DEFINER（拥有该过程的用户）执行。缺省值为 DEFINER。

指定 SQL SECURITY INVOKER 后，必须对每个调用该过程的用户加以标注，因此会使用更多内存。另外，指定 SQL SECURITY INVOKER 后，也会作为调用者进行名称解析。因此，应注意用适合的所有者限定所有对象名称（表、过程等）。例如，假定 user1 创建了以下过程：

CREATE PROCEDURE user1.myProcedure()
   RESULT( columnA INT )
   SQL SECURITY INVOKER
   BEGIN
     SELECT columnA FROM table1;
   END;

如果 user2 试图运行此过程，而表 user2.table1 不存在，则会产生表查寻错误。另外，如果 user2.table1 存在，则使用该表而不使用预定的 user1.table1。为了防止出现这种情况，请在语句中限定表引用（user1.table1，而不只是 table1）。

ON EXCEPTION RESUME 子句 该子句使类似 Transact-SQL 的错误处理能够在 Watcom-SQL 语法过程中使用。
如果使用 ON EXCEPTION RESUME，则过程会根据 on_tsql_error 选项的设置来执行操作。如果 on_tsql_error 设置为 Conditional（缺省值），则会在下一条语句能处理错误时继续执行该语句；否则，便会退出。

错误处理语句包括以下这些：
- IF
- SELECT @variable =
- CASE
- LOOP
- LEAVE
- CONTINUE
- CALL
- EXECUTE
- SIGNAL
- RESIGNAL
- DECLARE
- SET VARIABLE
在 ON EXCEPTION RESUME 子句中不要使用显式错误处理代码。

请参见on_tsql_error 选项 [兼容性]。
AT location-string 子句 在当前数据库中为 location-string 指定的远程过程创建代理存储过程。AT 子句支持分号 (;) 作为 location-string 中的字段分隔符。如果没有分号，则将句号用作字段分隔符。这样一来，便可在数据库和所有者字段中使用文件名和扩展名。
远程过程接受长度最多 254 个字节的输入参数，并在输出变量中返回最多 254 个字符。
如果远程过程可以返回结果集，即使并不是在所有情况下都返回，本地过程定义也必须包含 RESULT 子句。
有关远程服务器的信息，请参见CREATE SERVER 语句。有关使用远程过程的信息，请参见使用远程过程调用 (RPC)。

注释

CREATE PROCEDURE 语句在数据库中创建过程。具有 DBA 权限的用户可以通过指定所有者为其他用户创建过程。过程可用 CALL 语句进行调用。

如果存储过程返回一个结果集，则它不能同时设置输出参数或返回一个返回值。

从多个过程引用临时表时，如果该临时表定义不一致且高速缓存引用该表的语句，则会出现潜在问题。请参见在过程中引用临时表。

权限

除非创建临时过程，否则必须具有 RESOURCE 权限。

引用外部过程或者为其他用户创建过程必须有 DBA 权限。

副作用

自动提交。

另请参见

标准和兼容性

SQL/2003 持久存储模块特性。Java 结果集的语法扩展如可选的 J621 特性中所指定。

示例

下面的过程使用 case 语句对查询结果归类。

CREATE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
   DECLARE prod_name CHAR(20);
   SELECT name INTO prod_name FROM Products
   WHERE ID = product_ID;
   CASE prod_name
   WHEN 'Tee Shirt' THEN
      SET type = 'Shirt'
   WHEN 'Sweatshirt' THEN
      SET type = 'Shirt'
   WHEN 'Baseball Cap' THEN
      SET type = 'Hat'
   WHEN 'Visor' THEN
      SET type = 'Hat'
   WHEN 'Shorts' THEN
      SET type = 'Shorts'
   ELSE
      SET type = 'UNKNOWN'
   END CASE;
END;

以下示例将替换在上一个示例中创建的 ProductType 过程。在代替该过程后，将更新 Tee Shirt 和 Sweatshirt 的参数：

CREATE OR REPLACE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
   DECLARE prod_name CHAR(20);
   SELECT name INTO prod_name FROM Products
   WHERE ID = product_ID;
   CASE prod_name
   WHEN 'Tee Shirt' THEN
      SET type = 'T Shirt'
   WHEN 'Sweatshirt' THEN
      SET type = 'Long Sleeve Shirt'
   WHEN 'Baseball Cap' THEN
      SET type = 'Hat'
   WHEN 'Visor' THEN
      SET type = 'Hat'
   WHEN 'Shorts' THEN
      SET type = 'Shorts'
   ELSE
      SET type = 'UNKNOWN'
   END CASE;
END;

以下过程使用游标并遍历游标各行，返回单个值。

CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
   DECLARE err_notfound EXCEPTION
   FOR SQLSTATE '02000';
   DECLARE curThisCust CURSOR FOR
      SELECT CompanyName,
          CAST(SUM(SalesOrderItems.Quantity *
          Products.UnitPrice) AS INTEGER) VALUE
      FROM Customers
      LEFT OUTER JOIN SalesOrders
      LEFT OUTER JOIN SalesOrderItems
      LEFT OUTER JOIN Products
      GROUP BY CompanyName;
   DECLARE ThisValue INT;
   DECLARE ThisCompany CHAR(35);
   SET TopValue = 0;
   OPEN curThisCust;
   CustomerLoop:
   LOOP
      FETCH NEXT curThisCust
      INTO ThisCompany, ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CustomerLoop;
      END IF;
      IF ThisValue > TopValue THEN
         SET TopValue = ThisValue;
         SET TopCompany = ThisCompany;
         END IF;
   END LOOP CustomerLoop;
   CLOSE curThisCust;
END;