PostgreSql存储过程模板（PROCEDURE ）

淩亂°似流年 2022-10-22 12:57 517阅读 0赞

# PROCEDURE #

## 语法 ##

CREATE [ OR REPLACE ] PROCEDURE
        name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
      { LANGUAGE lang_name
        | TRANSFORM { FOR TYPE type_name } [, ... ]
        | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
        | SET configuration_parameter { TO value | = value | FROM CURRENT }
        | AS 'definition'
        | AS 'obj_file', 'link_symbol'
      } ...

CREATE PROCEDURE定义一个新的过程。CREATE OR REPLACE PROCEDURE将会创建一个新过程或者替换一个已有的定义。为了能够定义过程，用户必须具有所使用的语言上的USAGE特权。

如果这个命令中包括了一个方案名称，则该过程将被创建在该方案中。否则过程将被创建在当前的方案中。新过程的名称不能匹配同一方案中具有相同输入参数类型的任何现有过程或函数。不过，具有不同参数类型的过程和函数可以共享同一个名称（这被称为重载）。

要替换一个已有过程的当前定义，请使用CREATE OR REPLACE PROCEDURE。不能用这种方式更改过程的名称或者参数类型（如果尝试这样做，实际上会创建一个新的、不同的过程）。

当CREATE OR REPLACE PROCEDURE被用来替换一个现有的过程时，该过程的拥有关系和权限保持不变。所有其他的过程属性会被赋予这个命令中指定的或者暗示的值。必须拥有（包括成为拥有角色的成员）该过程才能替换它。

创建过程的用户将成为该过程的拥有者。

为了能够创建一个过程，用户必须具有参数类型上的USAGE特权。

## 参数 ##

name

要创建的过程的名称（可以是被方案限定的）。

argmode

参数的模式可以是：IN、INOUT或者VARIADIC。如果省略，则默认为IN（当前对过程不支持OUT参数，可使用INOUT）。

argname

参数的名称。

argtype

过程的参数（如果有）的数据类型（可以是被方案限定的）。参数类型可以是基础类型、组合类型或者域类型，或者可以引用一个表列的类型。
    
    根据具体的实现语言，还可能可以指定“伪类型”，例如cstring。伪类型表示实际的参数类型没有完全确定，或者是位于普通SQL数据类型的集合之外。
    
    写上table_name.column_name%TYPE可以引用某个列的类型。使用这种特性有时可以让过程不受表定义改变的影响。

default\_expr

没有指定参数时要被用作默认值的表达式。这个表达式必须符合该参数的参数类型。跟在有默认值的参数后面的输入参数也都必须有默认值。

lang\_name

用于实现该过程的语言名称。它可以是sql、c、internal或者一种用户定义的过程语言的名称，例如plpgsql。将名称包裹在单引号内的方式已经被废弃，并且要求大小写匹配。

TRANSFORM \{ FOR TYPE type\_name \} \[, … \] \}

列出对过程的调用应该应用哪些Transform。Transform负责在SQL类型和语言相关的数据类型之间进行转换，请参考CREATE TRANSFORM。过程语言实现通常采用硬编码的方式保存内建类型的知识，因此它们无需在这里列出。但如果一种过程语言实现不知道如何处理一种类型并且没有提供Transform，它将回退到默认的行为来转换数据类型，但是这依赖于其实现。

\[EXTERNAL\] SECURITY INVOKER  
\[EXTERNAL\] SECURITY DEFINER

SECURITY INVOKER指示过程以调用它的用户的特权来执行。这是默认方式。SECURITY DEFINER指定过程以拥有它的用户的特权来执行。
    
    为了符合SQL标注，允许使用EXTERNAL关键词，但它是可选的，因为和SQL中不同，这个特性适用于所有的过程而不仅仅是外部过程。
    
    SECURITY DEFINER过程不能执行事务控制语句（例如COMMIT和ROLLBACK，具体取决于实现的语言）。

configuration\_parameter  
value

SET子句导致在进入该过程时指定的配置参数被设置为指定的值，并且在过程退出时恢复到之前的值。SET FROM CURRENT把CREATE PROCEDURE执行时该参数的当前值保存为在进入该过程时要应用的值。
    
    如果对过程附加一个SET子句，那么在该过程中为同一个变量执行的SET LOCAL命令的效果就被限制于该过程：在过程退出时还是会恢复到该配置参数的以前的值。不过，一个普通的SET命令（没有LOCAL）会重载这个SET子句，很像它对一个之前的SET LOCAL命令所做的事情：这样一个命令的效果将持续到过程退出之后，除非当前事务被回滚。
    
    如果对过程附加一个SET子句，则该过程不能执行事务控制语句（例如COMMIT和ROLLBACK，具体取决于实现的语言）。

definition

一个定义该过程的字符串常量，其含义取决于语言。它可以是一个内部的过程名、一个对象文件的路径、一个SQL命令或者以一种过程语言编写的文本。
    
    在编写过程的定义字符串时，使用美元引用 而不是普通的单引号语法常常会很有帮助。如果没有美元引用，过程定义中的任何单引号或者反斜线必须以双写的方式进行转义。

obj\_file, link\_symbol

当C语言源码中的过程名与SQL过程的名称不同时，这种形式的AS子句被用于动态可装载的C语言过程。字符串obj_file是包含已编译好的C过程的共享库文件名，并且被按照LOAD命令的方式解析。字符串link_symbol是该过程的链接符号，也就是该过程在C语言源代码中的名称。如果链接符号被省略，则会被假定为与正在被定义的SQL过程的名称相同。
    
    当重复的CREATE PROCEDURE调用引用同一个对象文件时，只会对每一个会话装载该文件一次。要卸载或者重新载入该文件（可能是在开发期间），应该开始一个新的会话。

## 存储过程模板 ##

存储过程日志表

CREATE TABLE public.sys_procedurelog (
    	id serial NOT NULL,
    	logger varchar(50) NULL,
    	procedurename varchar(50) NULL,
    	timespan int4 NULL,
    	errcode varchar(5) NULL,
    	errmessage varchar(200) NULL,
    	logtime timestamp NULL,
    	issuccess bool NULL,
    	CONSTRAINT sys_procedurelog_pkey PRIMARY KEY (id)
    );
    CREATE INDEX "NON-ProcedureName-sys_procedurelog" ON public.sys_procedurelog USING btree (procedurename);

存储过程模板

CREATE OR REPLACE PROCEDURE public.proc_template(INOUT sys_errcode character varying)
     LANGUAGE plpgsql
    AS $procedure$
    
    --author:zhangyongze
    --createdat:2021年3月17日16点38分
    
    declare    -- 定义变量
    
    sys_starttime  timestamp;
    sys_errmessage varchar(200);
    
    iloop int;
    begin   --事务开始 
      
    sys_errcode='00000';
    sys_starttime = now();
    iloop=1;
    
    
     begin     -- 为记日志需要再开一个begin
    
    while (iloop<3)  loop  -- 循环
    
    insert  into  tm_template values(iloop);
    
    iloop=iloop+1;
    end loop;
    
     
     -- create table tm_example as select * from tm_examplez;
    
    
     
      
    --捕获异常并回滚
    
    exception when others then rollback;
        get stacked diagnostics 
    	sys_errcode = returned_sqlstate,sys_errmessage = message_text;
     end ;
    
    	-----删除临时表
    -- drop table if exists tm_template ;
     
     
    
    -----日志记录生成模块
    	insert into sys_procedurelog(issuccess,logger,procedurename,timespan,errcode,errmessage,logtime)
    	select 
    		   case when sys_errcode ='00000' then 1::bool else 0::bool end as issuccess,
               'template' as logger,
               'proc_template'    as procedurename,
               floor(extract(epoch  from (now() -  sys_starttime)))   as timespan,
    		   case when sys_errcode='00000'then null else sys_errcode end    as errcode,
               case when sys_errcode='00000' then null else sys_errmessage end as errmessage,
               now() as logtime;
              
    end;
    $procedure$
    ;