PostgreSQL的存储过程简单入门
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
[ <
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
[ <
5）、FOR（整数变种）
[ <
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()； --调用游标介绍：游标是一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务端的sql语句，或是批处理、存储过程、触发器中的数据处理请求。
游标的优点在于它允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力。缺点是处理大数据量时，效率低下，占用内存大。一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标。
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
PostgreSQL游标可以封装查询并对其中每一行记录进行单独处理。当我们想对大量结果集进行分批处理时可以使用游标，因为一次性处理可能造成内存溢出。另外我们可以定义函数返回游标类型变量，这是函数返回大数据集的有效方式，函数调用者根据返回游标对结果进行处理。
游标使用顺序：声明游标 > 打开游标 > 使用游标 > 关闭游标 。
先展示一个游标的示例，以下get_film_titles(integer)函数接受代表电影发行年份的参数。在函数内部，我们查询所有发行年份等于传递给该函数的发行年份的电影。我们使用光标在各行之间循环，并连接标题和标题包含ful 单词的电影发行年份。
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;