CREATE PROCEDURE 语句

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 | variable-name

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

parameter-mode :
IN 
| OUT 
| INOUT

result-column : column-name data-type

您可以创建用于调用使用各种编程语言编写的外部或本地过程的永久存储过程。可使用 PROC 作为 PROCEDURE 的同义词。

OR REPLACE 子句 指定 OR REPLACE 将创建一个新过程或替换同名的现有过程。此子句将更改过程的定义，但保留现有权限。如果尝试替换已使用的过程，则将返回错误。

TEMPORARY 子句 如果指定 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' );

参数参数名必须符合其它数据库标识符（如列名）的规则。它们必须是有效的 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 OR REPLACE PROCEDURE 语句时，将关闭连接的已打开的游标。

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

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

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 应用程序可以使用可变结果集过程。
Web 服务 Web 服务根据存储过程的 RESULTS 子句来判断结果集中列的编号和类型。Web 服务不支持返回多个结果集的过程，也不通过使用 EXECUTE IMMEDIATE 来支持变量结果集。

注意

如果在该过程中使用包含 WITH RESULT SET ON 子句的 EXECUTE IMMEDIATE 语句，以及从该语句返回的结果集与从该过程返回的结果集相同时，则仅返回 EXECUTE IMMEDIATE 语句的结果集的第一列。

如果过程仅返回一个结果集，则应使用 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 子句中不要使用显式错误处理代码。
AT location-string 子句 在当前数据库中为 location-string 指定的远程过程创建代理存储过程。AT 子句支持分号 (;) 作为 location-string 中的字段分隔符。如果没有分号，则使用句号作为字段分隔符。这样一来，便可在数据库和所有者字段中使用文件名和扩展名。
当使用远程过程时，variable-name 将被替换为 SQL 变量 variable-name 的内容，且变量类型必须为 CHAR、VARCHAR 或 LONG VARCHAR。有关使用 AT 子句中的变量的详细信息，请参见创建目录访问服务器 (Sybase Central)中的示例 2。

如果远程过程可以返回结果集，即使并不是始终都返回，本地过程定义也必须包含 RESULT 子句。

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

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

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

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

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

自动提交，即使对临时过程也同样。

SQL/2008 CREATE PROCEDURE 是 SQL/2008 标准的核心功能，但 SQL Anywhere 中支持的它的某些组件是可选 SQL 语言功能。这些功能的子集包括：
- SQL SECURITY 子句是 SQL/2008 可选语言功能 T324。
- 将 LONG VARCHAR、LONG NVARCHAR 或 LONG BINARY 值传递给 SQL 过程的功能是 SQL/2008 语言功能 T041。
- 使用 CREATE TABLE 或 DROP TRIGGER 等语句在 SQL 过程中创建或修改模式对象的功能是 SQL/2008 语言功能 T651。
- 在 SQL 过程中使用动态 SQL 语句（包括 EXECUTE IMMEDIATE、PREPARE 和 DESCRIBE 等语句）的功能是 SQL/2008 语言功能 T652。
CREATE PROCEDURE 语句的几个子句是服务商扩充。其中包括：
- TEMPORARY 子句。
- ON EXCEPTION RESUME 子句。
- AT 子句。
- 特定例程参数的可选 DEFAULT 子句。
- RESULT 和 NO RESULT SET 子句。SQL/2008 标准使用 RETURNS 关键字。
- 可选的 OR REPLACE 子句。
Transact-SQL Adaptive Server Enterprise 支持 CREATE PROCEDURE。

CREATE PROCEDURE 语句 [T-SQL]

以下过程查询 Employees 表，并返回在指定薪水 (sal) 的指定百分比 (percentage) 内的薪水：

CREATE OR REPLACE PROCEDURE AverageEmployees( IN percentage NUMERIC( 5,3), IN sal NUMERIC( 20, 3 ) )
RESULT( Department CHAR(40), GivenName person_name_t, Surname person_name_t, Salary NUMERIC( 20, 3) )
BEGIN
    DECLARE maxS NUMERIC( 20, 3 );
    DECLARE minS NUMERIC( 20, 3 );

    IF percentage >= 1 THEN
        SET percentage = percentage / 100;
    ELSEIF percentage < 0 THEN
        SELECT 'Percentage error', 'Err','Err', -1;
        RETURN;
    END IF;

    SELECT MIN( E.Salary ), MAX( E.Salary ) INTO minS, maxS
    FROM Employees E;

    IF sal < minS OR sal > maxS THEN
        SELECT 'Salary out of bounds', 'Err', 'Err', -2;
        RETURN;
    END IF;

    SELECT D.DepartmentName, E.GivenName, E.Surname, E.Salary
    FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID
    WHERE E.Salary BETWEEN sal *( 1 - percentage ) AND sal * ( 1 + percentage );
END;

以下过程使用 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;

CREATE PROCEDURE 语句

语法

参数

注意

注释

权限

副作用

另请参见

标准和兼容性

另请参见

示例