Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 参考 » 使用 SQL » SQL 语句 » SQL 语句 (A-D)

 

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;