欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PostgreSQL的存储过程简单入门

程序员文章站 2022-04-20 09:08:06
PostgreSQL的存储过程简单入门 PostgreSQL 存储过程定义格式如下: ■结构 PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建F...

PostgreSQL的存储过程简单入门

PostgreSQL 存储过程定义格式如下:
■结构 PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。
基本上是这样的:
CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 int4, 整型数组 _int4, ...])  RETURNS 返回值类型 AS  $BODY$  DECLARE 变量声明  BEGIN 函数体  END;  $BODY$  LANGUAGE ‘plpgsql’ VOLATILE;
■变量类型 除了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语句的时候特别有用)
--简单的例子:
例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;
最后再加上如何执行这个存储过程(函数)
-- 执行存储过程方法1
SELECT * FROM 函数名称(参数1,参数2,...)
-- 执行存储过程方法2
SELECT  函数名称('0参数1,参数2,...)

1、创建一个存储过程PostgreSQL 好像没有专门的 CREATE OR REPLACE PROCEDURE 全部都是 FUNCTION 的样子。
对于没有返回值的。 可以通过 RETURNS void  来实现。
2、要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。
3、我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。
同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。
要做这些事情,你必须删除并重新创建函数。
(如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。)
 如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。
4、使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。
5、举例:好像没有 print 之类的语句。只好把结果写入临时表
CREATE TABLE test_helloworld(
   data  varchar(30)
);
请注意, 定义存储过程内使用的变量, 需要定义在 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;
---
# SELECT HelloWorld();
 helloworld
------------
(1 行记录)

# select * from test_helloworld;
---------------------------
 Hello World
 First Test! Second Test !
(2 行记录)

修改存储过程
    要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。 
    我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。
    同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。 
    要做这些事情,你必须删除并重新创建函数。
   
   (如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。)
    如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。 
    使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。
    具体代码略. 需要注意的一点。 删除函数的时候, 需要传递完整的参数列表, 仅仅指定一个  函数的名称, 是无法删除的。
    例如:

# drop FUNCTION HelloWorld;
错误:  语法错误 在 ";" 或附近的 第1行drop FUNCTION HelloWorld;                            

# drop FUNCTION HelloWorld();
DROP FUNCTION
#

参数定义 单个参数

# truncate table test_helloworld;
TRUNCATE TABLE
# CREATE OR REPLACE FUNCTION HelloWorld1(vUserName VARCHAR) RETURNS void AS
$$
BEGIN
   INSERT INTO test_helloworld
     VALUES('Hello ' || vUserName);
END;
 $$
LANGUAGE plpgsql;

# SELECT HelloWorld1('ABC'); 
-------------
(1 行记录)

# select * from test_helloworld;   
----------- 
Hello ABC (1 行记录)

函数参数的别名
# truncate table test_helloworld;
 请注意这里:定义参数的时候, 没有定义参数名称, 仅仅定义了参数的数据类型
 然后在定义变量的位置,通过  vUserName ALIAS FOR $1  来为 第一个参数, 指定一个变量名称,
 叫做  vUserName
 # CREATE OR REPLACE FUNCTION HelloWorld2 (varchar) RETURNS void AS
  $$
 DECLARE
  vUserName ALIAS FOR $1;
 BEGIN
 INSERT INTO test_helloworld
 VALUES('Hello ' || vUserName);
 END;
 $$
 LANGUAGE plpgsql;
 
 # SELECT HelloWorld2('XYZ'); 
 -------------
 (1 行记录)
 #select * from test_helloworld;
 ----------- 
 Hello XYZ (1 行记录)

某些情况下, 希望定义参数的时候, 数据类型,与某个表中的某一列的数据类型一样。
这样,将来万一业务变化, 表的数据类型变化了,不需要修改存储过程代码。
定义的方式,是 
表名.列名%TYPE
#CREATE TABLE test_type (
  test_ID    INT,
  test_name  varchar(20)
);

# CREATE OR REPLACE FUNCTION HelloWorld20 (
    p_user_name  test_type.test_name%TYPE
    ) RETURNS void AS
    $$
  BEGIN
    INSERT INTO test_type VALUES(1, p_user_name);
  END;
  $$
 LANGUAGE plpgsql;
注意:  类型关联 test_type.test_name%TYPE 转换为 character varying

# select HelloWorld20('Test');
 helloworld20
--------------
(1 行记录)

# select * from test_type;
 test_id | test_name
---------+-----------
       1 | Test
(1 行记录)


参数定义- IN、OUT、IN OUT

# 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 行记录)


Test=# select * from test_helloworld;
   data
-----------
 Hello ABC
(1 行记录)

参数的默认值

PostgreSQL  不直接支持 参数的默认值。
但是支持 重载。
# TRUNCATE TABLE test_helloworld;
TRUNCATE TABLE
# CREATE OR REPLACE FUNCTION HelloWorld3(
    p_user_name VARCHAR,
    p_val1 VARCHAR,
    p_val2 VARCHAR) RETURNS void AS
  $$
  BEGIN
    INSERT INTO test_helloworld (data)
      VALUES (p_user_name || p_val1 || p_val2);
  END;
 $$
  LANGUAGE plpgsql;

# CREATE OR REPLACE FUNCTION HelloWorld3(
    p_user_name VARCHAR,
    p_val1 VARCHAR) RETURNS void AS
  $$
 BEGIN
    PERFORM HelloWorld3(p_user_name, p_val1, ' XYZ');
  END;
  $$
  LANGUAGE plpgsql;

 # CREATE OR REPLACE FUNCTION HelloWorld3(
    p_user_name VARCHAR) RETURNS void AS
  $$
  BEGIN
    PERFORM HelloWorld3(p_user_name, ' OPQ ');
  END;
  $$
  LANGUAGE plpgsql;

# SELECT HelloWorld3('ABC');
 helloworld3
-------------
(1 行记录)

# select * from test_helloworld;
     data
--------------
 ABC OPQ  XYZ
(1 行记录)

返回结果集

简单查询的函数

请注意:
这里最后写的是  LANGUAGE SQL;  不是  LANGUAGE plpgsql;
因为函数里面, 没有任何逻辑, 只有一条 SQL 语句.

CREATE OR REPLACE FUNCTION GetTestMain (int)  RETURNS test_main AS
$$
  SELECT * FROM test_main WHERE id = $1;
$$
LANGUAGE SQL;


Test=# SELECT * FROM GetTestMain(1) AS t;
 id | value
----+-------
  1 | ONE
(1 行记录)

# CREATE OR REPLACE FUNCTION GetTestMain (int)  RETURNS test_main AS
  $$
    SELECT * FROM test_main WHERE id != $1;
  $$ LANGUAGE SQL;

# SELECT * FROM GetTestMain(0) AS t;
 id | value
----+-------
  1 | ONE
(1 行记录)


Test=# SELECT * FROM GetTestMain(1) AS t;
 id | value
----+-------
  2 | TWO
(1 行记录)


请注意: 上面这种写法, 如果查询返回多行数据的情况下,这个函数仅仅会返回第一行。

# CREATE OR REPLACE FUNCTION GetTestMain2(int) RETURNS setof test_main AS $$
   SELECT * FROM test_main WHERE id != $1;
  $$ LANGUAGE SQL;
 
# SELECT * FROM GetTestMain2(1) AS t;
 id | value
----+-------
  2 | TWO
  4 | FOUR
(2 行记录)

通过定义  RETURNS setof ... 使得函数能过返回多行记录.

假如业务逻辑比较复杂,无法简单 SQL 处理的情况下
需要使用 RETURN NEXT ... 来把当前行数据,加入结果集.
使用 RETURN; 把整个结果集返回.

# CREATE OR REPLACE FUNCTION GetTestMain3(int)
 RETURNS SETOF test_main AS
  $$
  DECLARE
    v_test_main_data test_main%ROWTYPE;
  BEGIN
    FOR v_test_main_data IN SELECT * FROM test_main LOOP
      IF v_test_main_data.id = $1 THEN
        -- 模拟一点逻辑操作.
        CONTINUE;
      END IF;
        -- 把当前行数据,加入结果集.
        RETURN NEXT v_test_main_data;
    END LOOP;
    -- 把整个结果集返回.
    RETURN;
  END;
 $$ LANGUAGE plpgsql;

# SELECT * FROM GetTestMain3(1) AS t;
 id | value
----+-------
  2 | TWO
  4 | FOUR
(2 行记录)


普通返回的函数
# CREATE OR REPLACE FUNCTION HelloWorld4() RETURNS varchar AS
  $$
  BEGIN
   RETURN 'Hello World!';
  END;
  $$
  LANGUAGE plpgsql;

# select HelloWorld4();
 helloworld4
--------------
 Hello World!
(1 行记录)