postgres 存储过程

PostgreSQL存储过程

postgres存储过程查询结构

***PostgreSQL函数也称为PostgreSQL存储过程 *

• 变量类型 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。
• 赋值 赋值和Pascal有点像：“变量 := 表达式;” 有些奇怪的是连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;
• 判断 判断又和VB有些像： IF 条件 THEN … ELSEIF 条件 THEN … ELSE … END IF;
• 循环 循环有好几种写法： WHILE expression LOOP statements END LOOP; 还有常用的一种是：（从1循环到9可以写成FOR i IN 1…9 LOOP） FOR name IN [ REVERSE ] expression … expression LOOP statements END LOOP;
• 其他 还有几个常用的函数： SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型） PERFORM query; 表示执行query并丢弃结果 EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

参数说明

function_name:指定函数的名称。
[OR REPLACE]:是可选的，它允许修改/替换现有函数。
declaration:定义参数(参数名写在前面 类型写在后面)。
BEGIN~END: 在中间写方法主体。
RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。
LANGUAGE:它指定实现该函数的语言的名称。
**RETURNS return_datatype ** return_datatype 为 返回类型

两个 $variabl{e}_{n}ame$ 之间为返回体；$XXXX$ XXXX可以为任意字符

存储过程示例

例1：无返回值

CREATE OR REPLACE FUNCTION 函数名称( 参数1,参数2,...) RETURNS void
AS
$BODY$
DECLARE  --定义 
BEGIN
INSERT INTO "表名" VALUES(参数1，参数2，...);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;  -- 最后别忘了这个。

例2：有返回值

CREATE OR REPLACE FUNCTION 函数名称(deptcode VARCHAR(20) ,deptname VARCHAR(60) ,pycode VARCHAR(60),isenabled CHAR(1))
RETURNS BOOLEAN  --返回值，布尔类型
AS
$body$
DECLARE
deptcode VARCHAR(20);
deptname VARCHAR(60);
pycode  VARCHAR(60);
isenabled CHAR(1);
BEGIN
UPDATE "deptDict" SET deptcode=deptcode,deptname=deptname,pycode=pycode,isenabled=isenabled,updatedhisdatetime=CURRENT_TIMESTAMP
WHERE deptcode=deptcode;
RETURN TRUE;  
END
$body$ 
LANGUAGE 'plpgsql' VOLATILE;

例3 多个变量用分隔符分隔

请注意, 定义存储过程内使用的变量, 需要定义在 BEGIN 之前， 需要加 DECLARE 关键字。
多个变量之间用分号分隔。
CREATE OR REPLACE FUNCTION HelloWorld() RETURNS void AS
$$
    DECLARE
    testvalue1  VARCHAR(20);
    testvalue2  VARCHAR(20);
 BEGIN
   testvalue1 := 'First Test! ';
   SELECT 'Second Test !' INTO testvalue2;
   INSERT INTO test_helloworld
     SELECT 'Hello World' ;
   INSERT INTO test_helloworld (data)
     VALUES (testvalue1 || testvalue2);
 END;
 $$
 LANGUAGE plpgsql;

参数定义- IN、OUT、IN OUT

--删除表 test_helloworld 数据--
# truncate table test_helloworld;
TRUNCATE TABLE
 
# CREATE OR REPLACE FUNCTION HelloWorld3 (
    IN vUserName VARCHAR,
    OUT vOutValue VARCHAR
  ) AS
  $$
  BEGIN
    INSERT INTO test_helloworld
      VALUES('Hello ' || vUserName);
    vOutValue := 'A';
  END;
  $$
  LANGUAGE plpgsql;
 
# SELECT HelloWorld3('ABC');
 helloworld3
-------------
 A
(1 行记录)**

存储过程中返回类型

【1】第一种选择：声明setof 某表/某视图 返回类型
【2】第二种选择：声明setof record返回类型
【3】 第三种选择：声明refcursor返回类型

参考：https://blog.csdn.net/cdnight/article/details/83450035
通过定义 RETURNS setof … 使得函数能过返回多行记录. 假如业务逻辑比较复杂，无法简单 SQL 处理的情况下
需要使用 RETURN NEXT … 来把当前行数据，加入结果集. 使用 RETURN; 把整个结果集返回.

存储过程操作

drop FUNCTION HelloWorld();

参考：

https://blog.csdn.net/cdnight/article/details/83450035 postgresql存储过程中返回类型
http://www.jasongj.com/2015/12/27/SQL4_%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b_Store%20Procedure/