文档首页/ 数据仓库服务 DWS/ SQL语法参考/ SQL语法参考(9.1.0.x)/ DDL语法/ CREATE PROCEDURE
更新时间：2025-07-10 GMT+08:00
查看PDF
分享

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的示例。
  • 存储过程指定PACKAGE属性时支持重载。
  • 存储过程不支持分段提交。
  • 函数创建也适用于存储过程，更多内容请参考CREATE FUNCTION。创建存储过程后查询定义时会返回CREATE FUNCTION的定义。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
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 
/

参数说明

表1 CREATE PROCEDURE参数说明

参数

描述

取值范围

OR REPLACE

当存在同名的存储过程时，替换原来的定义。

-

procedure_name

要创建的存储过程名字，可以用模式修饰。

字符串，要符合标识符的命名规范。参见标识符命名规范。

argmode

参数的模式。

OUT和INOUT模式的参数不能用在RETURNS TABLE的存储过程定义中。
IN，OUT，INOUT或VARIADIC。
  • IN：输入参数，缺省值是IN。
  • OUT：输出参数，只有OUT模式的参数后面能跟VARIADIC。
  • INOUT：既是输入又是输出参数。
  • OUT VARIADIC: 表示输出参数，且声明为数组类型。

argname

参数的名字。

argname和argmode的顺序没有严格要求，推荐按照argname、argmode、argtype的顺序使用。

字符串，要符合标识符的命名规范。参见标识符命名规范。

argtype

参数的数据类型。

可用的数据类型。

IMMUTABLE | STABLE | VOLATILE

行为约束可选项。各参数的功能与CREATE FUNCTION类似，详细说明见CREATE FUNCTION。

-

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，并输出结果。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    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。

    1
    		 
    CALL prc_add(2,3);
  • 创建存储过程pro_variadic，param1参数模式为VARIADIC，将传入的整数数组转换为TEXT格式，并通过param2输出。 
    1
2
3
4
5
6
    		 
    CREATE OR REPLACE PROCEDURE pro_variadic (param1 VARIADIC int4[],param2 OUT TEXT)
AS
BEGIN
    param2:= param1::text;
END;
/

    执行存储过程pro_variadic。

    1
    		 
    SELECT pro_variadic(VARIADIC param1=> array[1,2,3,4]);
  • 创建带有PACKAGE属性的存储过程package_func_overload。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    CREATE OR REPLACE PROCEDURE package_func_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，调用存储过程参见CALL。

父主题： DDL语法