CREATE PROCEDURE

功能描述

创建一个新的存储过程。

注意事项

• 如果创建存储过程时参数或返回值带有精度，不进行精度检测。
• 创建存储过程时，存储过程定义中对表对象的操作建议都显示指定模式，否则可能会导致存储过程执行异常。
• 创建存储过程时，仅对CREATE的存储过程或PACKAGE本身加写锁，仅对执行过程中编译、执行会对函数和函数依赖的PACKAGE均加读锁。
• 创建存储过程时，存储过程内部通过SET语句设置current_schema和search_path无效。执行完函数search_path和current_schema与执行函数前的search_path和current_schema保持一致。
• SELECT、CALL调用函数时，必须要在出参位置提供实参进行调用，实参不会发生作用。
• 存储过程指定package属性时支持重载。
• 不能创建仅形参名字不同（存储过程名和参数列表类型都一样）的重载存储过程。
• 重载的存储过程在调用时变量需要明确具体的类型。
• 不能创建与函数拥有相同名称和参数列表的存储过程。
• 不支持仅默认值不同的存储过程重载。
• 存储过程仅in、out、inout这三种类型不同的参数，打开guc参数behavior_compat_options='proc_outparam_override'后，不允许重载。关闭该参数后，可以重载。
• A兼容模式的数据库，建立A风格的函数；PG兼容的库，建立PG风格的函数。不建议混合创建。
• 函数如果支持重载，需要添加PACKAGE关键字。
• 在存储过程内部使用未声明的变量，存储过程被调用时会报错。
• 在创建procedure时，不能在avg函数外面嵌套其他agg函数，或者其他系统函数。
• 存储过程中不支持需要return集合的操作。
• 在存储过程内部调用其它无参数的存储过程时，可以省略括号，直接使用存储过程名进行调用。
• 在存储过程内部调用其他有出参的函数，如果在赋值表达式中调用时，需要打开guc参数 set behavior_compat_options = 'proc_outparam_override' ，并提前定义与出参类型相同的变量，然后将变量作为出参调用带有出参的其他函数，出参才能生效。否则，被调函数的出参会被忽略。
• 在表达式中使用out参数作为出参时，如下情况不会生效，例如：使用execute immediate sqlv using func语法执行函数、使用select func into语法执行函数、使用insert、update等DML语句执行以及带out出参的函数作为入参时，fun（func（out b），a），out出参b未生效等。
• 存储过程支持参数注释的查看与导出、导入。
• 存储过程支持介于IS/AS与plsql_body之间的注释的查看与导出、导入。
• 存储过程默认为SECURITY INVOKER权限，如果想将默认行为改为SECURITY DEFINER权限，需要设置guc参数behavior_compat_options='plsql_security_definer'。
• 被授予CREATE ANY FUNCTION权限的用户，可以在用户模式下创建/替换存储过程。
• out/inout参数必须传入变量，不能传入常量。
• 集中式环境下，想要调用in参数相同，out参数不同的存储过程，需要设置guc参数behavior_compat_options='proc_outparam_override',并且打开参数后，无论使用select还是call调用存储过程，都必须加上out参数。打开参数后，不支持使用perform调用存储过程或函数。
• 存储过程创建时依赖未定义对象，如参数enable_force_create_obj打开，创建可执行，通过WARNING提示；如参数未打开，存储过程创建不可执行。
• 当打开三权分立时，对于定义者权限的存储过程，只能由本用户自己重建。
• 如果将定义者权限的存储过程创建到其他用户Schema下，则会以其他用户的权限执行该存储过程，有越权风险，请谨慎使用。
• 在存储过程内部函数复杂调用，如：func(x).a，函数调用返回复合类型，支持跨schema调用，不支持通过database.schema.package.func(x).b的方式调用。
• 调用带out出参的存储过程，设置GUC参数set behavior_compat_options = 'proc_outparam_transfer_length'后可以传递参数长度。规格限制如下：
  1. 支持的基本类型包括：CHAR(n)、CHARACTER(n)、NCHAR(n)、VARCHAR(n)、VARYING(n)、VARCHAR2(n)、NVARCHAR2(n)。
  2. out出参不生效的情况下（比如perform）不需要传递长度。
  3. 不支持精度传递的基本类型包括：NUMERIC、DECIMAL、NUMBER、FLOAT、DEC、INTEGER、TIME、TIMESTAMP、INTERVAL、TIME WITH TIME ZONE、TIMESTAMP WITH TIME ZONE、TIME WITHOUT TIME ZONE、TIMESTAMP WITHOUT TIME ZONE。
  4. 无论GUC参数set behavior_compat_options是否设置为proc_outparam_override都支持传递参数长度。
  5. 要传递集合类型的元素长度和被集合类型嵌套的数组类型的元素长度需要在GUC参数behavior_compat_options里同时开启tableof_elem_constraints选项。
• 函数中存在通过GUC参数控制特性的语法、函数等，如果在会话内更改相关GUC参数，修改参数后，调用函数可能会维持修改前的行为，请谨慎变更GUC参数。

语法格式

CREATE [ OR REPLACE ] PROCEDURE procedure_name
    [ ( {[ argname ] [ argmode ] 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
       | SET configuration_parameter { [ TO | = ] value | FROM CURRENT }
    ][ ... ]
 { IS | AS } 
plsql_body 
/

参数说明

• OR REPLACE

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

• procedure_name

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

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

  

  创建和系统函数重名的函数时，调用时需要指定函数的schema。

• argmode

  参数的模式。

  

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

  取值范围：IN，OUT，INOUT或VARIADIC。缺省值是IN。只有OUT模式的参数能跟在VARIADIC参数之后。

• argname

  参数的名称。

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

• argtype

  参数的数据类型。可以使用%TYPE或%ROWTYPE间接引用变量或表的类型，详细可参考存储过程章节定义变量。

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

  

  PACKAGE外PROCEDURE argtype中%TYPE不支持引用PACKAGE变量的类型。

• expression

  参数的默认表达式。

  

  • 在参数a_format_version值为10c和a_format_dev_version值为s2的情况下，参数为INOUT模式时不支持默认表达式。
  • 推荐使用方式：将所有默认值参数定义在所有非默认值参数后。
  • 调用带有默认参数的函数时，入参从左往右排入函数，如果有非默认参数的入参缺失则报错。
  • 打开 proc_uncheck_default_param 参数，调用带有默认参数的函数时，入参从左往右排入函数，允许缺省默认参数个入参，如果有非默认参数的入参缺失，则会用错位的默认值填充该参数。
  • 在参数a_format_version值为10c、a_format_dev_version值为s1和关闭proc_outparam_override，函数参数同时包括out出参和default时，默认值不可缺省。
• configuration_parameter
  • value

    把指定的配置参数设置为给定的值。如果value是DEFAULT，则在新的会话中使用系统的缺省设置。OFF关闭设置。

    取值范围：字符串

    • DEFAULT
    • OFF
    • 指定默认值。
  • from current

    取当前会话中的值设置为configuration_parameter的值。

• IMMUTABLE、STABLE等

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

• plsql_body

  PL/SQL存储过程体。

  创建一个存储过程时，plsql_body支持以“END;”或“END procedure_name;”两种形式结尾。

  

  以END procedure_name;结尾时遵循以下约束：

  1. 仅A兼容模式（数据库级别）下支持使用。
  2. 仅支持A风格创建语法。
  3. 仅支持END后设置存储过程名称的场景。

  4. 存储过程END后名称与实际名称不匹配报错；PACKAGE内存储过程，END后名称与实际名称不匹配报错。

     以上两种报错场景，即使参数enable_force_create_obj=on打开，仍会创建失败并提示报错信息。

  5. 嵌套子程序END后名称与实际名称不匹配报错场景，如参数enable_force_create_obj=on打开，创建成功，通过WARNING提示报错信息；如参数未打开，存储过程或package创建失败并提示报错信息。
  6. 创建的存储过程在DBE_PLDEVELOPER.gs_source、my_source、db_source、adm_source视图中显示与存储过程创建时定义相同（创建时“END+名称”则显示“END+名称”）；在pg_proc视图以及\sf查看时，最外层END后在任何情况下都不跟名称，内部则按照创建时定义（创建时“END+名称”则显示“END+名称”）显示。
  7. gs_dump生成的sql文件，存储过程无法保留最外层END后名称。
  8. 以IF/LOOP命名存储过程或嵌套子程序时，不支持以END IF/LOOP结束存储过程。
  

  当在存储过程体中进行创建用户、修改密码或加解密等涉及密码或密钥相关操作时，系统表及日志中会记录密码或密钥的明文信息。为防止敏感信息泄露，不建议用户在存储过程体中进行涉及密码或密钥等敏感信息的相关操作。

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

示例

• 创建一个存储过程。

  --创建存储过程，打印传入参数的和。
  gaussdb=# CREATE OR REPLACE PROCEDURE proc_add(i int, j int)
  AS 
  BEGIN
       dbe_output.print_line('result is：'|| i+j);
  END;
  /
  
  --使用CALL命令调用存储过程。
  gaussdb=# CALL proc_add(16,17);
  
  --使用程序块调用存储过程。
  gaussdb=# BEGIN
      proc_add(16,17);
  END;
  /
  
  --删除。
  gaussdb=# DROP PROCEDURE proc_add;

• A兼容模式数据库下以END结尾创建一个存储过程。

  创建一个存储过程。
  --创建存储过程，打印传入参数的和。
  gaussdb=# CREATE OR REPLACE PROCEDURE proc_add(i int, j int)
  AS 
  BEGIN
       dbe_output.print_line('result is：'|| i+j);
  END proc_add;
  /
  
  --使用CALL命令调用存储过程。
  gaussdb=# CALL proc_add(16,17);
  
  --使用程序块调用存储过程。
  gaussdb=# BEGIN
      proc_add(16,17);
  END;
  /
  
  --删除。
  gaussdb=# DROP PROCEDURE proc_add;

• 创建一个参数模型为VARIADIC的存储过程。

  --创建存储过程pro_variadic。
  gaussdb=# CREATE OR REPLACE PROCEDURE pro_variadic (var1 VARCHAR2(10) DEFAULT 'hello!',var4 VARIADIC int4[])
  AS
  BEGIN
      dbe_output.print_line(var1);
  END;
  /
  
  --执行此存储过程。
  gaussdb=# SELECT pro_variadic(var1=>'hello', VARIADIC var4=> array[1,2,3,4]);
  
  --删除。
  gaussdb=# DROP PROCEDURE pro_variadic;

• 参数模型IN与OUT。
  • IN表示该参数是传入的参数（缺省项）。
  • OUT表示该参数是传出的参数。
  • IN OUT表示该参数既要传入也要传出。

  --创建存储过程proc_add1，num1和num2为传入的参数，num3为传出的参数。
  gaussdb=# CREATE PROCEDURE proc_add1 (num1 in int, num2 in int, num3 out int)
  AS
  BEGIN 
      num3 := num1 + num2;
  END;
  /
  
  --使用程序块调用改存储过程，使用变量c来接收该存储过程传出的参数。
  gaussdb=# DECLARE 
      a int := 20;
      b int := 32;
      c int := 0;
  BEGIN 
      proc_add1(a,b,c);
      dbe_output.put_line(c);
  END; 
  /
  
  --删除。
  gaussdb=# DROP PROCEDURE proc_add1;

相关链接

ALTER PROCEDURE，DROP PROCEDURE

优化建议

• analyse | analyze
  • 不支持在事务或匿名块中执行analyze 。
  • 不支持在函数或存储过程中执行analyze操作。