CREATE PROCEDURE

功能描述

创建一个新的存储过程。

注意事项

• 函数创建也适用于存储过程，更多内容请参考CREATE FUNCTION。

• 如果创建存储过程时参数或返回值带有精度，不进行精度检测。
• 创建存储过程时，存储过程定义中对表对象的操作建议都显示指定模式，否则可能会导致存储过程执行异常。
• 在创建存储过程时，存储过程内部通过SET语句设置current_schema和search_path无效。执行完函数search_path和current_schema与执行函数前的search_path和current_schema保持一致。
• 如果存储过程参数中带有出参，SELECT调用存储过程必须缺省出参，CALL调用存储过程适配Oracle，调用非重载函数时必须指定出参，对于重载的package函数，out参数可以缺省，具体信息参见CALL的示例。
• 存储过程指定package属性时支持重载。
• 在创建procedure时，不能在avg函数外面嵌套其他agg函数，或者其他系统函数。
• 在多个CN的集群中，不能将存储过程的入参或出参设置为临时表类型，因为在非当前连接CN上执行创建存储过程时，无法单独根据表名获取到正确的临时schema，进而无法获取到准确的表类型。
• 创建存储过程后查询定义时会返回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 
/

参数说明

• OR REPLACE

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

• procedure_name

  创建的存储过程名字，可以带有模式名。

  取值范围：字符串，要符合标识符的命名规范。

• argmode

  参数的模式。

  

  VARIADIC用于声明数组类型的参数。

  取值范围： IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数后面能跟VARIADIC。并且OUT和INOUT模式的参数不能用在RETURNS TABLE的过程定义中。

• argname

  参数的名字。

  取值范围：字符串，要符合标识符的命名规范。

• argtype

  参数的数据类型。

  取值范围：可用的数据类型。

  

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

• IMMUTABLE、STABLE等

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

• plsql_body

  PL/SQL存储过程体。

  

  当在存储过程体中进行创建用户等涉及用户密码相关操作时，系统表及csv日志中会记录密码的明文。因此不建议用户在存储过程体中进行涉及用户密码的相关操作。

示例

创建一个存储过程：

 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;
/

调用此存储过程：

1

CALL prc_add(2,3);

创建一个参数模式为VARIADIC的存储过程：

1
2
3
4
5
6

CREATE OR REPLACE PROCEDURE pro_variadic (param1 VARIADIC int4[],param2 OUT TEXT)
AS
BEGIN
    param2:= param1::text;
END;
/

执行此存储过程：

1

SELECT pro_variadic(VARIADIC param1=> array[1,2,3,4]);

创建带有package属性的存储过程：

 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