CREATE PROCEDURE (SQL) 语句

CREATE PROCEDURE (SQL) 语句可在当前服务器上定义一个 SQL 过程。

调用

此语句可以嵌入在应用程序中，也可通过动态 SQL 语句来发出。它是一个可执行语句，仅当 DYNAMICRULES 运行行为对于程序包有效时才能动态编译 (SQLSTATE 42509)。

授权

语句授权标识所拥有的特权必须至少包括下列其中一项权限：

如果过程的隐式或显式模式名不存在，那么为数据库的 IMPLICIT_SCHEMA 权限。
如果过程的模式名引用了现有模式，那么为该模式的 CREATEIN 特权。
如果过程的模式名引用了现有模式，那么为该模式的 SCHEMAADM 权限。
DBADM 权限

此语句的授权标识所拥有的特权还必须包括调用过程体中指定的 SQL 语句所需的所有特权。

要替换现有过程，该语句的授权标识必须是匹配的过程的所有者 (SQLSTATE 42501)。

对于 CREATE PROCEDURE (SQL) 语句中指定的任何表或视图，不会考虑组特权。

描述

OR REPLACE

指定如果当前服务器上已存在过程定义，那么替换该定义。在目录中替换新定义之前，将有效地删除现有定义，但不会影响在该过程上授予的特权。此选项只能由对象所有者指定。如果当前服务器上不存在过程定义，那么将忽略此选项。要替换现有过程，新定义的特定名称和过程名称必须与旧定义的特定名称和过程名称相同，或者新定义的特征符必须与旧定义的特征符匹配。否则，将会创建新过程。

程序名要定义的过程的名称。这是用于指定过程的限定名或非限定名。非限定格式的 procedure-name 是 SQL 标识。在动态 SQL 语句中，使用 CURRENT SCHEMA 专用寄存器作为未限定的对象名的限定符。在静态 SQL 语句中，QUALIFIER 预编译/绑定选项隐式地指定未限定的对象名的限定符。限定格式为 schema-name 后跟句点和 SQL 标识。

此名称（包括隐式或显式限定符以及参数数目）不能标识目录中描述的过程 (SQLSTATE 42723)。未限定的名称与参数数目在其模式中唯一，但不必跨模式唯一。

如果指定了由两部分组成的名称，那么 schema-name 不能以“SYS”开头；否则将返回错误 (SQLSTATE 42939)。

(IN | OUT | INOUT parameter-name data-type default-clause ， ...)

标识过程的参数，并指定每个参数的模式、名称、数据类型和可选的缺省值。对于该过程所期望的每个参数，必须在列表中指定一个条目。

可以注册一个不含参数的过程。在这种情况下，仍必须对括号进行编码，并且中间不插入数据类型。例如：

   CREATE PROCEDURE SUBWOOFER() ...

不允许模式中的两个同名过程正好具有相同数目的参数。重复特征符返回一个 SQL 错误 (SQLSTATE 42723)。

例如，给定以下语句：

   CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

即使数据类型不同，第二条语句也会失败，因为过程中的参数数目相同。

IN | OUT | INOUT

指定参数的模式。

如果该过程返回错误，那么 OUT 参数未定义，INOUT 参数则保持不变。

IN: 识别作为过程输入参数的参数。控制权返回后，该过程对该参数所作的任何更改对于调用 SQL 应用程序都不可用。缺省值为 IN。
OUT: 识别作为过程输出参数的参数。
INOUT: 识别作为过程输入输出参数的参数。

参数名指定参数的名称。参数名称对于该过程必须是唯一的 (SQLSTATE 42734)。

data-type指定参数的数据类型。不能指定结构化类型或引用类型 (SQLSTATE 429BB)。

内置类型指定内置数据类型。有关不能为表指定的每个内置数据类型（BOOLEAN 和 CURSOR 除外）的更完整描述，请参阅“CREATE TABLE”。

布尔值: 适用于布尔型。
CURSOR: 表示对基础游标的引用。

anchored-data-type标识用于定义数据类型的另一个对象。锚点对象的数据类型的限制与直接指定数据类型或在使用行的情况下创建行类型时的限制相同。

ANCHOR DATA TYPE TO

指示使用锚定数据类型来指定数据类型。

variable-name标识全局变量。全局变量的数据类型将用作 parameter-name 的数据类型。

table-name.column-name标识现有表或视图的列名。列的数据类型将用作 parameter-name 的数据类型。

ROW OF table-name 或 view-name

根据 table-name 标识的表的列名和列数据类型或 view-name标识的视图，指定具有名称和数据类型的字段行。 parameter-name 的数据类型是未命名的行类型。

ROW OF 游标变量名

指定名称和数据类型的字段，这些字段基于 cursor-variable-name 标识的游标变量的字段名和字段数据类型。指定的游标变量必须是以下元素之一 (SQLSTATE 428HS)：

具有强类型游标数据类型的全局变量
具有弱类型游标数据类型的全局变量，该数据类型是通过使用 CONSTANT 子句指定已命名所有结果列的 select-statement 来创建或声明。

如果使用命名行类型对游标变量的游标类型进行强类型转换，那么 parameter-name 的数据类型是未命名的行类型。

array-type-name

指定用户定义数组类型的名称。如果在不指定模式名称的情况下指定 array-type-name，那么将通过在 SQL 路径中搜索模式来解析该数组类型。

cursor-type-name

指定游标类型的名称。如果指定了 cursor-type-name 而没有指定模式名，那么通过在 SQL 路径中搜索模式来解析游标类型。

distinct-type-name

指定单值类型的名称。该参数的长度、精度和小数位数分别是单值类型的源类型的长度、精度和小数位数。单值类型参数将作为单值类型的源类型传递。如果指定了 distinct-type-name 而没有指定模式名，那么通过在 SQL 路径中搜索模式来解析单值类型。

row-type-name

指定用户定义的行类型的名称。该参数的字段是行类型的字段。如果指定了 row-type-name 而没有指定模式名，那么通过在 SQL 路径中搜索模式来解析行类型。

缺省值

指定参数的缺省值。缺省值可以是常量、专用寄存器、全局变量、表达式或关键字 NULL。可指定为缺省值的专用寄存器与可为列缺省值指定的专用寄存器相同（请参阅 CREATE TABLE 语句中的 default-clause）。通过使用表达式，可以指定其他专用寄存器作为缺省值。

expression 可以是 Expressions 中描述的类型的任何表达式。如果未指定缺省值，那么该参数没有缺省值，并且调用该过程时不得省略相应的自变量。 expression 的最大大小为 64K 字节。

缺省表达式不得修改 SQL 数据（SQLSTATE 428FL 或 SQLSTATE 429BL）。该表达式必须与参数数据类型赋值兼容 (SQLSTATE 42821)。

在下列情况下，不得指定缺省值：

对于 INOUT 或 OUT 参数 (SQLSTATE 42601)
对于 ARRAY、ROW 或 CURSOR 类型的参数 (SQLSTATE 429BB)

SPECIFIC 具体名称

提供唯一名称给所要定义的过程实例。对该过程进行变更、删除或添加注释时，可以使用此特定名称。此名称不能用于调用该过程。非限定格式的 specific-name 是 SQL 标识。限定格式为 schema-name 后跟句点和 SQL 标识。该名称（包括隐式或显式限定符）不能标识应用程序服务器上存在的另一个过程实例；否则将引发错误 (SQLSTATE 42710)。

specific-name 可与现有 procedure-name 相同。

如果未指定限定符，那么将使用先前用于 procedure-name 的限定符。如果指定了限定符，那么它必须与 procedure-name 的显式或隐式限定符相同，否则将引发错误 (SQLSTATE 42882)。

如果未指定 specific-name，那么数据库管理器会生成唯一名称。唯一名称是“SQL”后跟字符时间戳记：“SQLyymmddhhmmssxxx”。

如果要使用 GET ROUTINE 命令来归档该过程，请确保 specific-name 的最大长度为 18 个字符。

动态结果集整数

指出该过程所返回的结果集的估计上限。

MODIFIES SQL DATA、CONTAINS SQL、READS SQL DATA

指定可由该过程运行的 SQL 语句的分类，或该过程所调用的任何例程的分类。数据库管理器会确认该过程所发出的 SQL 语句以及该过程所调用的所有例程与此规范一致。

有关每个语句的分类，请参阅可在例程和触发器中执行的 SQL 语句。

缺省值为 MODIFIES SQL DATA。

MODIFIES SQL DATA: 指定过程可以运行任何 SQL 语句，但过程中不支持的语句除外（SQLSTATE 38003 或 42985）。
CONTAINS SQL: 指定该过程只能运行数据访问分类为 CONTAINS SQL 的语句（SQLSTATE 38003、38004 或 42985）。
READS SQL DATA: 指定过程可以运行数据访问分类为 READS SQL DATA 或 CONTAINS SQL 的语句（SQLSTATE 38002、38003 或 42985）。

如果在复合 SQL 过程中使用 BEGIN ATOMIC 子句，那么只有在定义为 MODIFIES SQL DATA 时才能创建该过程。

DETERMINISTIC 或 NOT DETERMINISTIC

此子句指定对于给定的自变量值，该过程是始终返回相同的结果 (DETERMINISTIC)，还是依赖于某些影响结果的状态值 (NOT DETERMINISTIC)。即，使用相同的输入连续调用时，DETERMINISTIC 过程必须始终返回相同的结果。

此子句当前不影响过程的处理。

CALLED ON NULL INPUT

CALLED ON NULL INPUT 始终应用于过程。这意味着，无论是否有任何 null 自变量，都会调用该过程。任何 OUT 或 INOUT 参数都可以返回 null 值或正常（非 null）值。过程负责测试 null 自变量值。

COMMIT ON RETURN

指示从该过程返回时是否发出落实。缺省值为 NO。

否: 该过程返回时，不会发出落实。
是: 该过程返回时，如果 CALL 语句返回正数 SQLCODE，那么将发出落实。

落实操作包括由调用应用程序进程和该过程执行的工作。

如果该过程返回结果集，那么与结果集相关联的游标必须已定义为 WITH HOLD，以便在落实后可用。

AUTONOMOUS

指出该过程应该在它自己的自主事务作用域中执行。

INHERIT SPECIAL REGISTERS

这个可选子句指定，该过程中的可更新专用寄存器将从调用语句的环境继承它们的初始值。对于在嵌套对象（例如触发器或视图）中调用的例程，将从运行时环境继承初始值（而不是从对象定义继承）。

专用寄存器的更改不会传回给过程调用者。

不可更新的专用寄存器（例如，日期时间专用寄存器）反映当前正在执行的语句的属性，因此会设置为它们的缺省值。

OLD SAVEPOINT LEVEL 或 NEW SAVEPOINT LEVEL

指定该过程是否为保存点名称和效果建立新的保存点级别。缺省行为是 OLD SAVEPOINT LEVEL。有关保存点级别的更多信息，请参阅 SAVEPOINT 中的规则。

LANGUAGE SQL

该子句用于指定要用 SQL 语言编写过程主体。

EXTERNAL ACTION 或 NO EXTERNAL ACTION

指定该过程是会执行某项操作来更改不受数据库管理器管理的对象的状态 (EXTERNAL ACTION)，还是并非如此 (NO EXTERNAL ACTION)。缺省值为 EXTERNAL ACTION。如果指定了 NO EXTERNAL ACTION，那么系统可以假定该过程没有外部影响，以进行特定优化。

PARAMETER CCSID

指定编码方案，用于所有传入该过程以及从该过程传出的字符串数据。如果未指定 PARAMETER CCSID 子句，那么缺省值为 PARAMETER CCSID UNICODE（对于 Unicode 数据库）和 PARAMETER CCSID ASCII（对于所有其他数据库）。

ASCII: 指定字符串数据以数据库代码页编码。如果该数据库是 Unicode 数据库，那么不能指定 PARAMETER CCSID ASCII (SQLSTATE 56031)。
UNICODE: 指定字符数据采用 UTF-8 格式，而图形数据采用 UCS-2 格式。如果数据库不是 Unicode 数据库，那么不能指定 PARAMETER CCSID UNICODE (SQLSTATE 56031)。

SQL-procedure-body

指定作为 SQL 过程主体的 SQL 语句。

请参阅复合 SQL（编译型）语句中的 SQL-procedure-statement。

规则

自治例程限制：自治例程无法返回结果集，并且不支持以下数据类型 (SQLSTATE 428H2)：
- 用户定义的游标类型
- 用户定义的结构化类型
- XML AS IN、OUT 和 INOUT 参数
不能在自治作用域内引用游标类型的会话变量。
使用锚定数据类型：锚定数据类型不能引用以下对象 (SQLSTATE 428HS)：昵称、类型化表、类型化视图、与基于表达式的索引关联的统计视图、声明的临时表、与弱类型游标关联的行定义、具有与数据库代码页或数据库排序规则不同的代码页或排序规则的对象。
使用游标和行类型：只能从复合 SQL（编译型）语句中调用一个使用游标类型或行类型作为参数的过程 (SQLSTATE 428H2)，但使用 JDBC 的 Java 应用程序除外，这些应用程序可以使用游标类型的 OUT 参数调用过程。不支持从 Java 外部过程调用。

注意

如果该语句的授权标识具有 IMPLICIT_SCHEMA 权限，那么使用尚不存在的模式名创建过程将导致隐式创建该模式。模式所有者是 SYSIBM。该模式上的 CREATEIN 特权会授予 PUBLIC。
从复合 SQL（内联）语句中调用的过程将像创建时指定 NEW SAVEPOINT LEVEL 一样执行，即使在创建过程时指定或缺省指定了 OLD SAVEPOINT LEVEL。
创建最初无效的过程：如果过程主体中引用的对象不存在或被标记为无效或者定义者暂时无权访问该对象，并且数据库配置参数 auto_reval 未设置为 DISABLED，那么仍会成功创建该过程。该过程将被标记为无效，并在下次调用时会重新验证该过程。
设置缺省值：定义有缺省值的过程参数在该过程被调用时设置为缺省值，但是，仅当调用该过程时没有提供值给相应的自变量，或者将值指定为 DEFAULT 时，才会这样做。
特权：过程的定义者始终会获授予该过程的 EXECUTE 特权 WITH GRANT OPTION，以及删除该过程的权限。
重新绑定从属包：每个 SQL 过程都有一个从属包。可通过运行 REBIND_ROUTINE_PACKAGE 过程随时重新绑定该包。显式重新绑定从属包不会重新验证无效过程。应使用自动重新验证或显式运行 ADMIN_REVALIDATE_DB_OBJECTS 过程来重新验证无效过程。过程重新验证会自动重新绑定从属包。
语法替代方法: 为了与先前版本的 Db2® 兼容，支持以下语法替代方法以及其他数据库产品。这些备用项是非标准的，不应使用。
- 可以指定 RESULT SETS 以代替 DYNAMIC RESULT SETS。
- 可以指定 NULL CALL 以代替 CALLED ON NULL INPUT。
接受将下列语法作为缺省行为：
- ASUTIME NO LIMIT
- NO COLLID
- STAY RESIDENT NO

示例

创建一个返回中值员工薪水的 SQL 过程。返回一个结果集，其中包括所有收入超过中值薪水的员工的姓名、职位和薪水。

   CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
     DECLARE v_numRecords INT DEFAULT 1;
     DECLARE v_counter INT DEFAULT 0;

     DECLARE c1 CURSOR FOR
       SELECT CAST(salary AS DOUBLE)
         FROM staff
         ORDER BY salary;
     DECLARE c2 CURSOR WITH RETURN FOR
       SELECT name, job, CAST(salary AS INTEGER)
         FROM staff
         WHERE salary > medianSalary
         ORDER BY salary;

     DECLARE EXIT HANDLER FOR NOT FOUND
       SET medianSalary = 6666;

     SET medianSalary = 0;
     SELECT COUNT(*) INTO v_numRecords
       FROM STAFF;
     OPEN c1;
     WHILE v_counter < (v_numRecords / 2 + 1)
     DO
       FETCH c1 INTO medianSalary;
       SET v_counter = v_counter + 1;
     END WHILE;
     CLOSE c1;
     OPEN c2;
   END

CREATE PROCEDURE (SQL) 语句

调用

授权

语法

描述

规则

注意

示例