PostgreSQL的存储过程简单入门

一、存储过程结构：

 Create or replace function 过程名(参数名 参数类型,…..) returns 返回值类型 as

                   $body$



                            //声明变量

                            Declare

                            变量名变量类型；

                            如：

                            flag Boolean;



                            变量赋值方式（变量名类型 ：=值；）

                            如：

                            str  text :=值; / str  text;  str :=值；



                            Begin

                                     函数体；



                             return 变量名； //存储过程中的返回语句



                            End;

                   $body$

         Language plpgsql;

二、变量类型：

除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

整数数据类型：

浮点数据类型：

（浮点数据也可以再细分，分为提供通用功能的浮点值和固定精度的数字）

注：

存储float和real类型的数据的行为非常相似，但是numeric列的行为有点不同。Numeric类型不是存储接近的数，而是在小数后面进行后超出固定长度的部分进行四舍五入。如果我们存储太大的数据到其中，INSERT将失败。还要注意float和real也会对数字四舍五入；例如123.456789被四舍五入为123.457。

时间数据类型：

特殊数据类型：

注：PostgreSQL也允许你使用SQL命令CREATE TYPE在数据库中建立你自己的类型。这通常不需要，而且在一定程度上，它是PostgreSQL独有的

数组

通常，一个数组需要通过使用一个附加表实现。但是，数组的能力有时候很有用。建立数组的方法有两种：传统的PostgreSQL的方法和SQL99标准的方法。

PostgreSQL样式的数组

要将一个表的列定义为数组，你可以简单地在类型后面添加[]；不需要定义元素的个数。即使定义了个数，也不会强制要求存储的个数。
Eg：

test=> CREATE TABLE empworkday (

test(>     refcode char(5),

test(>     workdays int[]

test(> );

往数组列中插入值：

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

SQL99样式的数字

在SQL99标准中，必须指出元素的个数。

Eg:

test=> CREATE TABLE empworkday (

test(>     refcode char(5),

test(>     workdays int array[7]

test(> );

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

三、连接字符：

Postgresql存储过程中的连接字符不再是“+”,而是使用“||”。

四、控制结构：

1、if 条件（五种形式）

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF

IF ... THEN ... ELSIF ... THEN ... ELSE

IF ... THEN ... ELSEIF ... THEN ... ELSE（注：ELSEIF 是ELSIF 的别名）

2、循环

1）、LOOP

[ <<label>> ]
 LOOP
     statements
 END LOOP [ label ];
 --LOOP 定义一个无条件的循环，无限循环， 直到由EXIT或者RETURN语句终止。可选的label 可以由EXIT 和CONTINUE 语句使用， 用于在嵌套循环中声明应该应用于哪一层循环。

2）、EXIT

EXIT [ label ] [ WHEN expression ];
如果没有给出label，那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。如果给出 label，那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。
如果声明了WHEN，循环退出只有在expression 为真的时候才发生，否则控制会落到EXIT 后面的语句上。

EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句。

例如:

Loop  --循环

If … then  --条件判断

Exit ;-- 条件成立，则退出循环。

End if;

End loop;

3）、CONTINUE

CONTINUE [label ] [ WHENexpression ];

如果没有给出 label，那么就开始最内层的循环的下一次执行。也就是说，控制传递回给循环控制表达式（如果有），然后重新计算循环体。如果出现了label，它声明即将继续执行的循环的标签。

如果声明了 WHEN，那么循环的下一次执行只有在expression 为真的情况下才进行。否则，控制传递给CONTINUE 后面的语句。

CONTINUE 可以用于所有类型的循环；它并不仅仅限于无条件循环。

例如:

LOOP
     --一些计算
    EXIT WHEN count > 100;
     CONTINUE WHEN count < 50;
     ---一些在count 数值在 [50 .. 100] 里面时候的计算
END LOOP;

4）、WHILE

     [ <<label>> ]
     WHILE expression LOOP
         statements
    END LOOP [ label ];
--只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.
例如:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
     --- 可以在这里做些计算
END LOOP;
 WHILE NOT BOOLEAN_expression LOOP
     --- 可以在这里做些计算
END LOOP;

5）、FOR（整数变种）

  [ <<label>> ]
     FOR name IN [ REVERSE ] expression .. expression LOOP
         statements
     END LOOP [ labal ];
--这种形式的FOR对一定范围的整数数值进行迭代的循环。变量name 会自动定义为integer类型并且只在循环里存在。给出范围上下界的两个表达式在进入循环的时候计算一次。 迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

--一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP 表示1循环到10
  -- 这里可以放一些表达式
    RAISE NOTICE 'i IS %', i;
 END LOOP;

 FOR i IN REVERSE 10..1 LOOP
   --  这里可以放一些表达式
END LOOP;
---如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。而且不会抛出任何错误。

3、异常捕获

EXCEPTION

WHEN 错误码（如：STRING_DATA_RIGHT_TRUNCATION：字串数据右边被截断） THEN

/**后台打印错误信息*/

RAISE NOTICE '错吴信息';

五、示例代码：

/**

批量插入一批数据，经纬度字段值要满足中国地理位置上的经纬度范围；

注：时间不能指定为同一时间，否则会扫描全表，导致性能低下。下列脚本未考虑时间的分段，采用的一个时间点。

create orreplace function intobatch() returns integer as
$body$
declare
    skyid integer;
    lot float;
         lat float;
         sex varchar;
         level integer;
         ctime int :=1325404914;
         num integer :=0;
         total integer :=0;
    begin

                   lot='73.6666666';
                   lat='3.8666666';
                   FOR skyid IN 404499817 ..404953416 loop
                        if(lot > 135.0416666) then
                               lot=73.6666666;
                         end if;
                        if(lat > 53.5500000) then
                            lat=3.8666666;
                         end if;
                         if(skyid%2 <> 0) then
                                  sex='1';
                                  level=0;
                             else
                                 sex='2';
                                 level=1;
                             end if;
                            INSERT INTO user_last_location(user_id,app_id,lonlat,sex,accurate_level,lonlat_point,create_time)
                            VALUES(skyid,2934,ST_GeomFromText('POINT('||lot||' '||lat||')',4326),sex,level,POINT(lot,lat),to_timestamp(ctime));


                            lot=lot+0.1;
                            lat=lat+0.1;
                            skyid=skyid+1;

                     end loop;
                   return skyid;

    end

$body$
languageplpgsql;

SELECT *from intobatch();

---postgresql 游标，函数，存储 过程使用例子
CREATE OR REPLACE FUNCTION cursor_demo()
  RETURNS refcursor AS  --返回一个游标
$BODY$
declare  --定义变量及游标
    unbound_refcursor refcursor;  --游标
    t_accid varchar;    --变量
        t_accid2 int;    --变量

begin  --函数开始
    open unbound_refcursor for execute 'select name from cities_bak';  --打开游标 并注入要搜索的字段的记录
    loop  --开始循环
        fetch unbound_refcursor into t_accid;  --将游标指定的值赋值给变量

        if found then  --任意的逻辑
            raise notice '%-',t_accid;
        else
            exit;
        end if;
    end loop;  --结束循环
    close unbound_refcursor;  --关闭游标
    raise notice 'the end of msg...';  --打印消息
    return unbound_refcursor; --为函数返回一个游标
exception when others then  --抛出异常
    raise exception 'error-----(%)',sqlerrm;--字符“%”是后面要显示的数据的占位符
end;  --结束
$BODY$

  LANGUAGE plpgsql;  --规定语言

select cursor_demo()； --调用

postgresql 使用游标笔记

　　游标介绍：游标是一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务端的sql语句，或是批处理、存储过程、触发器中的数据处理请求。

游标的优点在于它允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力。缺点是处理大数据量时，效率低下，占用内存大。一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标。

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

PostgreSQL游标可以封装查询并对其中每一行记录进行单独处理。当我们想对大量结果集进行分批处理时可以使用游标，因为一次性处理可能造成内存溢出。另外我们可以定义函数返回游标类型变量，这是函数返回大数据集的有效方式，函数调用者根据返回游标对结果进行处理。

　　游标使用顺序：声明游标 > 打开游标 > 使用游标 > 关闭游标。

　　先展示一个游标的示例，以下get_film_titles(integer)函数接受代表电影发行年份的参数。在函数内部，我们查询所有发行年份等于传递给该函数的发行年份的电影。我们使用光标在各行之间循环，并连接标题和标题包含ful 单词的电影发行年份。

CREATE OR REPLACE FUNCTION get_film_titles(p_year INTEGER)
   RETURNS text AS $$
-- 声明游标
DECLARE 
 titles TEXT DEFAULT '';
 rec_film   RECORD;
 cur_films CURSOR(p_year INTEGER) FOR SELECT * FROM film WHERE release_year = p_year;
BEGIN
   -- 打开游标
   OPEN cur_films(p_year);
 
   LOOP
    -- 获取记录放入film
      FETCH cur_films INTO rec_film;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;
 
    -- 构建输出
      IF rec_film.title LIKE '%ful%' THEN 
         titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
      END IF;
   END LOOP;
  
   -- 关闭游标
   CLOSE cur_films;
 
   RETURN titles;
END; $$
 
LANGUAGE plpgsql;

SELECT get_film_titles(2006);

--返回结果 ,Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful:2006,Unfaithful Kill:2006,Wonderful Drop:2006

一、声明游标

　　PostgreSQL声明游标有两种方法，一种是使用特殊类型REFCURSOR声明游标变量，另一种是声明和查询绑定使用。

-- 第一种方式
DECLARE  my_cursor REFCURSOR;

-- 第二种方式
cursor_name [ [NO] SCROLL ] CURSOR [( name datatype, name data type, ...)] FOR query;

　　首先，为光标指定一个变量名。接着指定是否可以使用向后滚动光标SCROLL，如果使用NO SCROLL，则光标无法向后滚动。然后，在CURSOR关键字后面加上一个逗号分隔的参数列表（name datatype），这些参数定义了查询的参数。打开游标时，这些参数将被值替换。最后，可以在FOR关键字之后指定查询，使用任何有效的SELECT语句。

　　示例：

DECLARE
    cur_films  CURSOR FOR SELECT * FROM film;
    cur_films2 CURSOR (year integer) FOR SELECT * FROM film WHERE release_year = year;

-- 该cur_films 包含film表所有行。
-- 本cur_films2 包含film表特定发行年份的记录。

二、打开游标

PostgreSQL提供了用于打开未绑定和绑定的游标的语法。

　　1.打开未绑定的游标

OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query;

-- 由于声明时未绑定的游标变量未绑定到任何查询，因此在打开它时必须指定查询。请参见以下示例：
OPEN my_cursor FOR SELECT * FROM city WHERE counter = p_country;

-- PostgreSQL允许我们打开游标并将其绑定到动态查询。语法如下：
OPEN unbound_cursor_variable[ [ NO ] SCROLL ]
FOR EXECUTE query_string [USING expression [, ... ] ];

-- 在下面的示例中，我们构建一个动态查询，该动态查询根据一个sort_field参数对行进行排序，并打开执行该动态查询的游标。
query := 'SELECT * FROM city ORDER BY $1';
OPEN cur_city FOR EXECUTE query USING sort_field;

　　2.打开绑定的游标

　因为绑定游标在声明时已经绑定到查询，所以当我们打开它时，只需要在必要时将参数传递给查询。

OPEN cursor_variable[ (name:=value,name:=value,...)];

-- 在下面的示例中，我们打开了绑定游标，cur_films并cur_films2在上面声明了该游标：
OPEN cur_films;
OPEN cur_films2(year:=2005);

三、使用游标

　　使用FETCH，MOVE，UPDATE或DELETE语句操作游标。

1.获取下一行

FETCH [ direction { FROM | IN } ] cursor_variable INTO target_variable;

该FETCH语句从游标中获取下一行，并为其分配一个target_variable，它可以是记录，行变量或逗号分隔的变量列表。如果找不到更多行，则将target_variable其设置为NULL（s）。

　　如果不显示指定方向，方向缺省为NEXT。可以有下面值：

NEXT
LAST
PRIOR
FIRST
ABSOLUTE count
RELATIVE count
FORWARD
BACKWARD

　　请注意，FORWARD和BACKWARD方向仅适用于用SCROLL option 声明的游标。

　　示例：

FETCH cur_films INTO row_film;
FETCH LAST FROM row_film INTO title, release_year;

　　2.移动光标

MOVE [ direction { FROM | IN } ] cursor_variable;

　　如果只想移动游标而不检索任何行，则使用该MOVE语句。方向接受与FETCH语句相同的值。

MOVE cur_films2;
MOVE LAST FROM cur_films;
MOVE RELATIVE -1 FROM cur_films;
MOVE FORWARD 3 FROM cur_films;

　　3.删除和更新行

　　使用DELETE WHERE CURRENT OF或UPDATE WHERE CURRENT OF语句删除或更新游标标识的行。

UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_variable;
 
DELETE FROM table_name
WHERE CURRENT OF cursor_variable;

示例：
UPDATE film SET release_year = p_year
WHERE CURRENT OF cur_films;

四、关闭游标

使用CLOSE关闭打开的游标，CLOSE语句释放资源或释放游标变量，以允许使用该OPEN语句再次打开它。

CLOSE cursor_variable;

五、其他

-- 临时表返回结果例子
BEGIN;
DO $$
    DECLARE
        temp_geometry st_geometry;  
        geometry_record RECORD;
        cur_geometry CURSOR FOR SELECT shape as shape FROM mainbasin;
    BEGIN
        OPEN cur_geometry;
        FETCH cur_geometry INTO temp_geometry;
        LOOP
            FETCH cur_geometry INTO geometry_record;
            EXIT WHEN NOT FOUND;
            temp_geometry := st_union(temp_geometry,geometry_record.shape);
        END LOOP;
        CLOSE cur_geometry;

        DROP TABLE IF EXISTS temp_table;
        CREATE TEMP TABLE temp_table AS 
        SELECT st_envelope(temp_geometry) shape;
    END; 
$$;
COMMIT;
SELECT st_astext(shape) FROM temp_table;

PostgreSQL function里面调用function

1. 调用无参无返回值的function

create or replace function func01()returns void as $$
begin
	raise notice ' from func01(): hello PG';
end ;
$$language plpgsql;



create or replace function func02() returns void as $$
begin
	perform  func01();
end;
$$language plpgsql;

----运行：
select  func02();

--注意:   from func01(): hello PG
CONTEXT:  SQL statement "SELECT func01()"
在PERFORM的第3行的PL/pgSQL函数"func02"

--查询总耗时: 14 ms.
--检索到 1 行。

2. 调用无参有返回值的function

create or replace function func03()returns integer as $$
begin
	return 1;
end ;
$$language plpgsql;

create or replace function func02() returns void as $$
begin
	perform  func03();
end;
$$language plpgsql;

执行select fun02()的时候是没有任何返回值的，因为perform已经将结果丢弃。

将perform更改为select into：

create or replace function func02() returns void as $$
declare n int;
begin
	select into n func03();
	raise notice 'n: %',n;
end;
$$language plpgsql;

执行：　

select  func02();

注意:  n: 1

查询总耗时: 12 ms.
检索到 1 行。

3. 有参有返回值

create or replace function func04(n int)returns integer as $$
begin
	return n;
end ;
$$language plpgsql;


create or replace function func02() returns void as $$
declare n int;
begin
	n=func04(4);
	raise notice 'n: %',n;
end;
$$language plpgsql;

执行：

select  func02();

注意:  n: 4

查询总耗时: 11 ms.
检索到 1 行。

PostgreSQL执行动态sql，应用在存储过程

drop function if exists exe_dynamic_sql(bigint);
drop function if exists exe_dynamic_count(bigint);
--返回记录集
create or replace function exe_dynamic_sql(ival bigint)
    returns table(objectid bigint,name varchar(128))
as $$
	declare
	begin
		return query execute 'select objectid,name from dictionarys where parentid=$1 order by parentid,sort' using $1;
	end;
$$ language plpgsql;

--赋值给变量
create or replace function exe_dynamic_count(ival bigint)
    returns bigint
as $$
	declare
		v_count bigint;
	begin
		execute 'select count(*) from dictionarys where parentid=$1' using $1 into v_count;
		return v_count;
	end;
$$ language plpgsql;
--测试
select * from exe_dynamic_sql(26);
select exe_dynamic_count(26);

要点:

returns table(objectid bigint,name varchar(128)),定义返回的字段和类型
using $1执行时使用过程参数;
准备一个语句用于执行,这个就比较重要了,查询参数绑定,开发利器
有朋友抱怨同一sql时快时慢,这是因为值在表中的占比不同,占比小的值就可以使用索引,值占比超过5%里sql就很慢了.此时就可以用下面的sql调式sql,可以根据不同的值来观察执行计划.

--getDictionarys仅在当前会话下有效
prepare getDictionarys (bigint) as
    select objectid,name from dictionarys where parentid=$1 order by parentid,sort;

explain (analyze,verbose,costs,buffers,timing)
execute getDictionarys(24);

explain (analyze,verbose,costs,buffers,timing)
execute getDictionarys(25);

explain (analyze,verbose,costs,buffers,timing)
execute getDictionarys(26);

--释放指定的预备语句
deallocate getDictionarys;
--释放所有预备语句
deallocate all;

Postgresql 存储过程--sql语句的where条件的拼接操作

--1、存储过程返回一个表

CREATE OR REPLACE FUNCTION 存储过程名(

    IN 参数1text,
    IN 参数2text,
    IN 参数3text)
  RETURNS TABLE(v_id integer, v_n text, v_me text, v_sid integer, v_sno integer, v_sname text) AS
$BODY$


DECLARE sql text;

BEGIN
       sql:='SELECT a.sid,a.no,a.name,b.sid,b.no,b.name 
FROM  表名 a
INNER JOIN 表名 b ON a.station_id = b.sid'; 


       IF 参数1= '' AND 参数2= '' AND 参数3= '' THEN
RETURN QUERY EXECUTE sql;

        ELSEIF 参数1= '' AND 参数2 = '' THEN
          sql:= sql || ' WHERE b.no = '''||in_stationNo||'''';
          RETURN QUERY EXECUTE sql;


        ELSEIF 参数2 = '' AND 参数3= '' THEN
           sql:= sql || '   WHERE a.name LIKE ''%'||参数1||'%''';
           RETURN QUERY EXECUTE sql;

        ELSEIF 参数1= '' AND 参数3= '' THEN
           sql:= sql || '   WHERE a.no LiKE ''%'||参数2||'%''';
           RETURN QUERY EXECUTE sql;


        ELSEIF 参数1= '' THEN
           sql:= sql || '   WHERE a.name LIKE ''%'||参数2||'%''AND b.no = '''||参数3||'''';
            RETURN QUERY EXECUTE sql;


        ELSEIF 参数2= '' THEN
            sql:= sql || '   WHERE a.no LIKE ''%'||参数1||'%''AND b.no = '''||参数3||'''';
            RETURN QUERY EXECUTE sql;


        ELSEIF 参数3= '' THEN
            sql:= sql || '   WHERE a.name LIKE ''%'||参数2||'%''AND a.no LIKE ''%'||参数1||'%''';
            RETURN QUERY EXECUTE sql;
        ELSE
             sql:= sql || '   WHERE a.name LIKE ''%'||参数2||'%''AND a.no LIKE ''%'||参数1||'%''AND b.no = '''||参数3||'''';
            RETURN QUERY EXECUTE sql;
              END IF;
END
$BODY$
  LANGUAGE plpgsql VOLATILE;

posted @ 2022-03-25 17:24 一品堂.技术学习笔记阅读(7080) 评论(0) 编辑收藏举报

刷新页面返回顶部

✧꧁一品堂.技术学习笔记꧂✧.

PostgreSQL的存储过程简单入门