# CREATE PROCEDURE #### 功能描述 创建一个新的存储过程。 存储过程（PROCEDURE）是由一组预编译的SQL语句组成的集合，可以在数据库中进行存储并反复调用。 #### 注意事项 - 创建存储过程时，请显式指定表对象的所属模式（如schema.table），否则可能引发执行异常。 - 创建存储过程时，存储过程内部通过SET语句设置的current_schema/search_path无效，函数search_path和current_schema执行完成后与函数执行前保持一致。 - 创建存储过程时，禁止在avg函数外面嵌套其他聚合函数或系统函数。 - 创建存储过程时，参数或返回值带有精度，不进行精度检测。 - 在多个CN的集群中，禁止将存储过程的入参/出参设置为临时表类型（因为在非当前连接CN上执行创建存储过程时，无法单独根据表名获取到正确的临时schema，进而无法获取到准确的表类型。） - 存储过程可以有多个返回值，也可以没有返回值。无返回值的存储过程调用后会有空的回显。 - 如果存储过程参数中带有出参：SELECT调用存储过程必须缺省出参；CALL调用存储过程适配Oracle；调用非重载函数时必须指定出参；对于重载的PACKAGE函数，out参数可以缺省。具体信息参见[CALL](https://support.huaweicloud.com/sqlreference-dws/dws_06_0229.html)的示例。 - 存储过程指定PACKAGE属性时支持重载。 - 存储过程不支持分段提交。 - 函数创建也适用于存储过程，更多内容请参考[CREATE FUNCTION](https://support.huaweicloud.com/sqlreference-dws/dws_06_0163.html)。创建存储过程后查询定义时会返回CREATE FUNCTION的定义。 #### 语法格式 ``` CREATE [ OR REPLACE ] PROCEDURE procedure_name [ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ] [ { IMMUTABLE | STABLE | VOLATILE } | { SHIPPABLE | NOT SHIPPABLE } | {PACKAGE} | [ NOT ] LEAKPROOF | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER} | COST execution_cost | ROWS result_rows | SET configuration_parameter { [ TO | = ] value | FROM CURRENT } ][ ... ] { IS | AS } plsql_body / ``` #### 参数说明 表1CREATE PROCEDURE参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | OR REPLACE | 当存在同名的存储过程时，替换原来的定义。 | - | | procedure_name | 要创建的存储过程名字，可以用模式修饰。 | 字符串，要符合标识符的命名规范。参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | argmode | 参数的模式。 OUT和INOUT模式的参数不能用在RETURNS TABLE的存储过程定义中。 | IN，OUT，INOUT或VARIADIC。 - IN：输入参数，缺省值是IN。 - OUT：输出参数，只有OUT模式的参数后面能跟VARIADIC。 - INOUT：既是输入又是输出参数。 - OUT VARIADIC: 表示输出参数，且声明为数组类型。 | | argname | 参数的名字。 argname和argmode的顺序没有严格要求，推荐按照argname、argmode、argtype的顺序使用。 | 字符串，要符合标识符的命名规范。参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | argtype | 参数的数据类型。 | 可用的数据类型。 | | IMMUTABLE \| STABLE \| VOLATILE | 行为约束可选项。各参数的功能与CREATE FUNCTION类似，详细说明见[CREATE FUNCTION](https://support.huaweicloud.com/sqlreference-dws/dws_06_0163.html)。 | - | | SHIPPABLE \| NOT SHIPPABLE | 表示该存储过程是否可以下推到DN上执行。 - 对于IMMUTABLE类型的存储过程，存储过程始终可以下推到DN上执行。 - 对于STABLE/VOLATILE类型的存储过程，仅当存储过程的属性是SHIPPABLE的时候，存储过程可以下推到DN执行。 用户在定义存储过程的SHIPPABLE属性时也需特别慎重，SHIPPABLE意味着整个存储过程会下推到DN上执行，如果设置不当，会导致结果错误等严重问题。 与定义IMMUTABLE属性一样，SHIPPABLE属性的定义也有诸多约束，简单来说就是存储过程内不能有不可下推的因素，存储过程下推到单DN执行后，存储过程内部的计算逻辑仅依赖当前DN的数据集合。 举例如下： 1. 如果存储过程内部引用了表，并且表为HASH分布，那么该存储过程通常不能定义为SHIPPABLE。 2. 存储过程内部有不可下推的因素，存储过程，语法等，那么该存储过程不能定义为SHIPPABLE，可参考语句下推调优。 3. 存储过程内部的计算过程可能需要跨DN数据，这种情况该存储过程通常不能定义为SHIPPABLE，例如一些聚合运算等。 | - | | PACKAGE | 表示该存储过程是否支持重载。PostgreSQL风格的存储过程本身就支持重载，此参数主要是针对Oracle风格的存储过程。 - 不允许package存储过程和非package存储过程重载或者替换。 - package存储过程不支持VARIADIC类型的参数。 - 不允许修改存储过程的package属性。 | - | | LEAKPROOF | 指出该存储过程的参数只包括返回值。LEAKPROOF只能由系统管理员设置。 | - | | CALLED ON NULL INPUT | 表明该存储过程的某些参数是NULL时可以按照正常的方式调用。该参数可以省略。 | - | | RETURNS NULL ON NULL INPUT \| STRICT | STRICT用于指定如果存储过程的某个参数是NULL，此存储过程总是返回NULL。如果声明了这个参数，当有NULL值参数时该存储过程不会被执行；而只是自动返回一个NULL结果。 RETURNS NULL ON NULL INPUT和STRICT的功能相同。 | - | | EXTERNAL | 目的是和SQL兼容，是可选的，这个特性适合于所有存储过程，而不仅是外部存储过程。 | - | | SECURITY INVOKER \| AUTHID CURRENT_USER | 表明该存储过程将带着调用它的用户的权限执行。该参数可以省略。 SECURITY INVOKER和AUTHID CURRENT_USER的功能相同。 | - | | SECURITY DEFINER \| AUTHID DEFINER | 声明该存储过程将以创建它的用户的权限执行。 AUTHID DEFINER和SECURITY DEFINER的功能相同。 | - | | COST execution_cost | 用来估计存储过程的执行成本。execution_cost以cpu_operator_cost为单位。 | 正数。 | | ROWS result_rows | 估计存储过程返回的行数。用于存储过程返回的是一个集合。 | 正数，默认值是1000行。 | | configuration_parameter | 该存储过程配置的参数值。 - **value** 把指定的数据库会话参数值设置为给定的值。取值范围：字符串。需指定默认值。 - DEFAULT，在新的会话中使用系统的缺省设置。 - OFF，关闭设置。 - RESET，在新的会话中使用系统的缺省设置。 - **from current** 取当前会话中的值设置为configuration_parameter的值。 | - | | plsql_body | PL/SQL存储过程体。 当在存储过程体中进行创建用户等涉及用户密码相关操作时，系统表及csv日志中会记录密码的明文。因此不建议用户在存储过程体中进行涉及用户密码的相关操作。 | - | #### 示例 - 创建存储过程prc_add，param1为输入参数，param2为输入输出参数，计算param1+param2，并将结果赋值给param2，并输出结果。 ``` CREATE OR REPLACE PROCEDURE prc_add ( param1 IN INTEGER, param2 IN OUT INTEGER ) AS BEGIN param2:= param1 + param2; dbms_output.put_line('result is: '||to_char(param2)); END; / ``` 调用存储过程prc_add。 ``` CALL prc_add(2,3); ``` - 创建存储过程pro_variadic，param1参数模式为VARIADIC，将传入的整数数组转换为TEXT格式，并通过param2输出。 ``` CREATE OR REPLACE PROCEDURE pro_variadic (param1 VARIADIC int4[],param2 OUT TEXT) AS BEGIN param2:= param1::text; END; / ``` 执行存储过程pro_variadic。 ``` SELECT pro_variadic(VARIADIC param1=> array[1,2,3,4]); ``` - 创建带有PACKAGE属性的存储过程package_proc_overload。 ``` CREATE OR REPLACE PROCEDURE package_proc_overload(col int, col2 out varchar) PACKAGE AS DECLARE col_type text; BEGIN col2 := '122'; dbms_output.put_line('two varchar parameters ' || col2); END; / ``` #### 相关链接 删除存储过程参见[DROP PROCEDURE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0201.html)，调用存储过程参见[CALL](https://support.huaweicloud.com/sqlreference-dws/dws_06_0229.html)。