PostgreSQL存储过程

最新推荐文章于 2024-04-29 23:47:34 发布

huang_ftpjh

最新推荐文章于 2024-04-29 23:47:34 发布

阅读量1.1w

点赞数

分类专栏：数据库文章标签： postgresql 数据库 sql

本文链接：https://blog.csdn.net/huang_ftpjh/article/details/115488902

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

PostgreSQL安装和创建用户和创建数据库 - 短毛兔 - 博客园

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。

PostgreSQL 开发者把它念作 post-gress-Q-L。免费和开源。

基本类型：Integer, Numeric, String, Boolean
结构类型：Date/Time, Array, Range, UUID
文档类型：JSON/JSONB, XML, Key-value(Hstore)
几何类型：Point, Line, Circle, Polygon
自定义类型：Composite, Custom Types

PostgresQL11在1核2G1M带宽上显著优于Mysql5.7 时间比1:2

在Navicat上创建函数

CREATE FUNCTION "public"."proc_h_testdemo"()
	RETURNS void AS $BODY$BEGIN
	-- Routine body goes here...

	RETURN;
END$BODY$
	LANGUAGE plpgsql

a:= b;
等价于C语言里面的：a = b 将b的值赋值给a的意思

语法

PostgreSQL-存储过程(一)基础篇 - 努力的孔子 - 博客园

a:= b;
等价于C语言里面的：a = b 将b的值赋值给a的意思

mysql:='select count('
    || quote_ident(columnName) 
    || ') from '
    || quote_ident(tableName);

|| 表示字符串拼接符号

open ref_cursor for select sysdate from dual; 把 select sysdate from dua 查询的结果付给游标ref_cursor

v_sql :=' select count(*) FROM activity '||v_con;

open resultset for EXECUTE v_sql; 把sql执行完付给游标

select count(*) into v_count from freetopolice where name=p_name; into也是赋值给游标的意思
if v_count >0 then

GET DIAGNOSTIC 语句用于获取前面执行的 SQL 语句的相关信息，常用于获取影响行数 GET DIAGNOSTICS v_rowcount := ROW_COUNT;

RAISE EXCEPTION '%',v_result; 异常处理例子用于if函数里面出现异常 EXCEPTION

IF v_rowcount != 1 THEN
	v_result := 2000;
	RAISE  EXCEPTION '%',v_result;
end if;
EXCEPTION

返回拼接的字符串？对 2.4.4 r16526

CREATE OR REPLACE FUNCTION "public"."postgis_raster_scripts_installed"()
  RETURNS "pg_catalog"."text" AS $BODY$ SELECT '2.4.4'::text || ' r' || 16526::text AS version $BODY$
  LANGUAGE sql IMMUTABLE

and creatat <now()-1*'60 min'::interval

PostgreSQL中的几何类型如点（point）、直线（line）、线段（lseg）矩形（box）、路径（path）、多边形（polygon）、圆（circle） PostgreSQL中的几何类型_万里归来少年心-CSDN博客_几何类型

CREATE OR REPLACE FUNCTION "public"."box"("public"."geometry")
  RETURNS "pg_catalog"."box" AS '$libdir/postgis-2.4', 'LWGEOM_to_BOX'
  LANGUAGE c IMMUTABLE STRICT
  COST 10

RETURNS "pg_catalog"."box" AS '$libdir/postgis-2.4', 'LWGEOM_to_BOX'

CREATE OR REPLACE FUNCTION "public"."st_addband"("rast" "public"."raster", "outdbfile" text, "outdbindex" _int4, "index" int4=NULL::integer, "nodataval" float8=NULL::double precision)
  RETURNS "public"."raster" AS $BODY$ SELECT public.ST_AddBand($1, $4, $2, $3, $5) $BODY$
  LANGUAGE sql IMMUTABLE

存在多个st_addband 名称的函数但是ST_AddBand为什么是大写？存在raster前缀的函数

存储过程需要获取当前时间，用clock_timestamp()，不要用now();

如果昨天运行一个事务，今天才commit，若是使用now()，那么提交到数据库的时间是昨天的时间，，clock_timestamp是今天提交的时间

return

RETURNS "pg_catalog"."int4"

RETURNS "pg_catalog"."bool" 返回布尔类型

RETURNS "pg_catalog"."refcursor" 返回名字是refcursor的游标

RETURNS "pg_catalog"."text" 返回字符串

postgreSQL函数的一些使用

to_char(completeat,'YYYY-MM-DD') 时间错转成字符串  to_timestamp相反

POSTGRESQL函数(存储过程)返回多条记录

PostgreSQL函数(存储过程)返回多条记录的实现方式 - 灰信网（软件开发博客聚合）

访问速度探讨

CREATE OR REPLACE FUNCTION "public"."proc_ffh_getbustype"("p_type" int4, "p_id" int4, "p_search" varchar, "p_offset" int4, "p_limit" int4, "p_attype" int4, "p_begintime" varchar, "p_endtime" varchar)
v_con:=v_con||' and (updateat between '''||p_begintime::TIMESTAMP||''' and '''||p_endtime::TIMESTAMP||''')';

这里的 between '''||p_begintime::TIMESTAMP||''' and '''||p_endtime::TIMESTAMP||''' 属于强制转换，然而似乎可以直接使用 between p_begintime and p_endtime 他们哪个速度快

traderecord outtradeno == completerecord .tradeno
completerecord .tradeno == tradepaytype .tradeno 然后得到tradepaytype表的支付方式字段

测试

测试案例

-- BEGIN;
-- select * from proc_ffh_getbustype(2,0,-1,-1,-1,-1,-1,'mainid','2016-08-30 00:00:00','2019-08-30 23:59:59',1,-1,1,0,50) as result;
-- FETCH ALL IN  "<unnamed portal 1>";
-- COMMIT;

SELECT count(1) over() as recordcount,id,bustypeno,bustypename,citycode,extra,updateat,creatat	FROM bustype
where (creatat between '2021-04-12 17:07:56' and '2021-04-14 17:07:56')




BEGIN;
select * from proc_h_getridingrecord(1,0,50,'','','','','',-1,'',-1,0,'2020-07-27 00:00:00','2021-07-27 23:59:59','','',0) as result;
FETCH ALL IN  "<unnamed portal 4>";
COMMIT;

-- 
-- SELECT
--    cr.tradeno,
--    cr.outtradeno,
--    tr.tradeno,
--    tr.outtradeno,
--    p.tradeno,
--    p.paytype,
--    p.bankcard
-- 
--    from completerecord cr
--    left join traderecord tr on cr.tradeno = tr.outtradeno
--    left join tradepaytype p on tr.tradeno = p.tradeno
-- where p.id is not null

例子

--查询当前月份表是否存在
		v_tablename := 'rechargerecord'||to_char(p_creatat::timestamp,'YYMM');
		SELECT count(1) into v_count from pg_tables where tablename = v_tablename;
        IF v_count<=0 THEN
            --当前表不存在，基于初始表复制一张备份表
            v_sql:='CREATE TABLE '||v_tablename||'  
						(LIKE rechargerecord1907 including constraints including indexes including defaults)';
            EXECUTE v_sql;
        end if;
				
--判断当前插入的id是否已存在
		v_sql:='SELECT count(1) from '||v_tablename||' WHERE id=$1';
		EXECUTE v_sql into v_count using p_id;
		IF v_count>0 THEN
				v_result :=1003; --数据已存在
				RAISE  EXCEPTION '%',v_result;
		end if;	
		
v_sql := 'INSERT into '||v_tablename||'(
			id,tradeno,uno,paychannel,payamount,amount,afterbalance,
			payfee,status,extra,thirdorder,completeat,payat,creatat,
			isdownload,downloadno,downloadat)
			VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)';
			
EXECUTE v_sql  using p_id,p_tradeno,p_uno,p_paychannel,p_payamount,p_amount,p_afterbalance,
p_payfee,p_status,p_extra,p_thirdorder,
p_completeat::timestamp,p_payat::timestamp,p_creatat::timestamp,p_isdownload,p_downloadno,
p_downloadat::timestamp;
GET DIAGNOSTICS v_count := ROW_COUNT;
IF v_count>0 THEN
			v_result :=1; --插入成功
	end if;

CREATE OR REPLACE FUNCTION "public"."proc_h_testedit"("p_type" int4, "p_id" int4, "p_name" varchar, "p_phoneno" varchar, "p_idno" varchar, "p_operatorid" int4, "p_createat" varchar)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare
        v_result int2 default 0;
        v_count int2;
        v_rowcount int2 default 0;
       
       
    begin
			BEGIN
            if p_type =1 then
                --参数验证
                if length(p_name)=0  then
                    v_result :=2002;
                    raise exception '%',v_result;
                end if;
                --排重
                select count(*) into v_count from freetopolice where name=p_name;
                if v_count >0 then
                    v_result :=2010;
                    raise exception '%',v_result;
                end if;
                --执行插入
                insert  into freetopolice
                    (name,phoneno,idno,operatorid,createat)
                    values
                           (p_name,p_phoneno,p_idno,p_operatorid,to_timestamp(p_createat,'YYYY-MM-DD HH24:MI:SS'));
                get diagnostics v_rowcount := ROW_COUNT ;
                    if v_rowcount != 1 then
                        v_result :=2000;
                        raise  exception '%',v_result;
                    end if;
                v_result :=1;
            elseif p_type = 2 then
                --参数验证
                if p_id <=0 or p_age <=0 or length(p_name) <=0 then
                    v_result :=2002;
                    raise exception '%',v_result;
                end if;
               
                    update freetopolice set phoneno=p_phoneno,
                                       name=p_name,
																			 idno=p_idno,
																			 operatorid=p_operatorid
                    where id=p_id;
               
                get diagnostics v_rowcount :=ROW_COUNT ;
                if v_rowcount !=1 then
                    v_result :=2000;
                    raise exception '%',v_result;
                end if;
                v_result :=1;
            else
						--删除
							--参数验证
							if p_id <=0 THEN
								v_result :=2002;
								raise EXCEPTION '%',v_result;
							end if;
							DELETE FROM freetopolice where id=p_id;
							get DIAGNOSTICS v_rowcount := ROW_COUNT;
							if v_rowcount != 1 then
								v_result :=2000;
								raise EXCEPTION '%',v_result;
							end if;
							v_result :=1;
            end if;
			EXCEPTION
			WHEN OTHERS THEN
			END;
        return v_result;

    end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE OR REPLACE FUNCTION "public"."proc_h_testselect"("p_type" int4, "p_id" int4, "p_offset" int4, "p_limit" int4)
  RETURNS "pg_catalog"."refcursor" AS $BODY$
declare
    resultset refcursor;--返回游标
    v_rowcount int;
    v_con varchar;
    v_sql varchar;
begin
    --查询单条记录
    if p_type=1 then
    open resultset for select 1 as recordcount,s.id,s.phoneno,s.idno,s.name,s.operatorid,s.creatat
        from freetopolice s
        where s.id=p_id;
    --查询多条记录
    else
      v_con := ' where 1=1';
      v_sql :=' select count(*) from freetopolice s '||v_con;
      execute v_sql into v_rowcount;
      v_sql :=' select '||v_rowcount||' as recordcount,s.id,s.phoneno,s.idno,s.name,s.operatorid,s.creatat from freetopolice s'||v_con||
        'order by s.id desc offset '||p_offset||' limit '||p_limit;
      open resultset for execute v_sql;
    end if;
    return resultset;


end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

psql 出现问题， sql里面嵌套子查询，子查询查出的paytype字段，在外面直接select paytype报了个语法错误，解决需要在groupby中加上这个字段。。。。

SELECT   businessname,'' as maincardname, -1 as istac,to_char(completeat,'yyyy-MM-dd') as datetime,
sum(account) as countnum,paytype,
sum(price) as price,sum(actualprice) as actualprice,sum(couponvalue) as couponvalue 
from 
(	
			SELECT a.completeat,a.businessname,a.companyname,a.linename,a.paytype,
			a.busno,a.posno,nc.netname,COALESCE(ic.issuername,'异地卡') as issuername,a.ordertype,a.maincardname,a.istac,a.price,a.account,
			COALESCE(a.actualprice,0) as actualprice,
			COALESCE(a.couponvalue,0) as couponvalue
			from summerycompleterecord a 
			left join netconfig nc on nc.netcode = a.netcode 
			left join issuerconfig ic on ic.issuercode = a.issuercode 
			where 1=1 
)d
GROUP BY datetime,businessname,paytype
ORDER BY datetime desc,businessname  LIMIT 20;
							
							
SELECT 
companyname,
companyno,
sum(case when issuercode = 'D1035510FFFFFFFF' then price else 0 end) as  dtallprice,
sum(case when issuercode = 'D1035510FFFFFFFF' then account else 0 end) as  dtcount,
sum(case when issuercode = 'F1035510FFFFFFFF' then price else 0 end) as  bdallprice,
sum(case when issuercode = 'F1035510FFFFFFFF' then account else 0 end) as  bdcount,
sum(case when issuercode = 'B1035510FFFFFFFF' then price else 0 end) as  hballprice,
sum(case when issuercode = 'B1035510FFFFFFFF' then account else 0 end) as  hbcount,
sum(case when issuercode = '11035510FFFFFFFF' then price else 0 end) as  ylallprice,
sum(case when issuercode = '11035510FFFFFFFF' then account else 0 end) as  ylcount,
sum(case when issuercode = '51035510FFFFFFFF' then price else 0 end) as  wdcsallprice,
sum(case when issuercode = '51035510FFFFFFFF' then account else 0 end) as  wdcscount,
sum(case when issuercode = 'E1035510FFFFFFFF' then price else 0 end) as  eqzallprice,
sum(case when issuercode = 'E1035510FFFFFFFF' then account else 0 end) as  eqzcount,
sum(price) as allprice,
sum(account) as count
FROM summerycompleterecord
GROUP BY companyname,companyno
ORDER BY companyname,companyno
LIMIT 30;

case when..........then.......else的用法总结 HiveQL中case when..........then.......else的用法总结_什么最重要？算法！！-CSDN博客

select sum(1) as a1 
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and before_prefr_unit_price < 100

select sum(1) as a2 
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and 
      (before_prefr_unit_price >= 100 and before_prefr_unit_price < 200 )

select sum(1) as a3 
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and 
      (before_prefr_unit_price >= 200 and before_prefr_unit_price < 300 )

select sum(1) as a4 
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE' and 
      (before_prefr_unit_price >= 300 )

select sum(case when before_prefr_unit_price < 100 then 1 else 0 end ) as a1,
       sum(case when before_prefr_unit_price >= 100 and before_prefr_unit_price < 200 then 1 else 0 end) as a2,
       sum(case when before_prefr_unit_price >= 200 and before_prefr_unit_price < 300 then 1 else 0 end) as a3,
       sum(case when before_prefr_unit_price >= 300 then 1 else 0 end) as a4
from gdm_m04_ord_det_sum
where  to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE'

-- 从优待警表
DROP TABLE IF EXISTS "public"."freetopolice";
CREATE TABLE "public"."freetopolice" (
  "id" serial NOT NULL,
  "phoneno" varchar(30) COLLATE "pg_catalog"."default",
  "idno" varchar(50) COLLATE "pg_catalog"."default",
  "name" varchar(30) COLLATE "pg_catalog"."default",
  "operatorid" int4,
  "creatat" timestamp(0)
)
;
COMMENT ON COLUMN "public"."freetopolice"."id" IS '主键,自增';
COMMENT ON COLUMN "public"."freetopolice"."phoneno" IS '手机号码';
COMMENT ON COLUMN "public"."freetopolice"."idno" IS '身份证号码';
COMMENT ON COLUMN "public"."freetopolice"."name" IS '姓名';
COMMENT ON COLUMN "public"."freetopolice"."operatorid" IS '操作员id';
COMMENT ON COLUMN "public"."freetopolice"."creatat" IS '创建日期';

ALTER TABLE "public"."freetopolice" ADD CONSTRAINT "freetopolice_pkey" PRIMARY KEY ("id");


--更新线路
CREATE OR REPLACE FUNCTION "public"."proc_h_linenameedit"("p_type" int4, "p_id" int4, "p_lineno" varchar, "p_linename" varchar, "p_companyno" varchar, "p_isfreetopolice" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE  
v_result INT2 DEFAULT 1;
v_rowcount int; 
v_type int default 0;
BEGIN
BEGIN
	if p_type =1 THEN
			if  length(p_lineno)> 0 and length(p_linename)> 0 and length(p_companyno)> 0 and p_isfreetopolice>0 THEN
			
			select count(1) into v_rowcount from gjline where xlbh=p_lineno;
					if v_rowcount = 1 THEN
								v_result := 2027; --该线路已存在
								RAISE  EXCEPTION '%',v_result;
					end if;
					
       insert into gjline(xlbh,dwbh,xlname,creatat,isfreetopolice) 
					VALUES(p_lineno,p_companyno,p_linename,now(),p_isfreetopolice
					);
				GET DIAGNOSTICS v_rowcount := ROW_COUNT;
				IF v_rowcount !=1 THEN
					v_result := 2000;
					RAISE  EXCEPTION '%',v_result;
				END IF;
			else
				v_result :=2002;
			end if;
  elseif p_type = 2 THEN
		UPDATE gjline set xlbh=p_lineno,
				dwbh=p_companyno,xlname=p_linename,creatat=now(),isfreetopolice=p_isfreetopolice where id=p_id;
			GET DIAGNOSTICS v_rowcount := ROW_COUNT;
			IF v_rowcount !=1 THEN
					v_result := 2000;
					RAISE  EXCEPTION '%',v_result;
			END IF;
	ELSE
			delete from gjline where id=p_id;
			GET DIAGNOSTICS v_rowcount := ROW_COUNT;
			IF v_rowcount !=1 THEN
					v_result := 2000;
					RAISE  EXCEPTION '%',v_result;
			END IF;
	end if; 

EXCEPTION
WHEN OTHERS THEN
end; 
RETURN v_result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


-- 获取线路列表
CREATE OR REPLACE FUNCTION "public"."proc_h_getlinenamelist"("p_type" int4, "p_id" int4, "p_offset" int4, "p_limit" int4, "p_operatorid" int4, "p_search" varchar)
  RETURNS "pg_catalog"."refcursor" AS $BODY$ 
DECLARE
	resultset refcursor;  --返回游标 
	v_rowcount int;
	v_con VARCHAR;
	v_sql VARCHAR;
	v_count int;	
	v_companyno VARCHAR;
BEGIN

	if p_type=1 THEN
		open resultset for SELECT 1 as  recordcount,g.id,g.xlbh as lineno,g.xlname as linename,j.dwmc as companyname,j.dwbh as companyno,g.creatat as creatat,g.isfreetopolice   
				FROM gjline g 
				left join gjcompany j on g.dwbh=j.dwbh where g.id=p_id;
	ELSE
			v_con := ' where 1=1 ';
						
			--判断当前登录权限
			select count(1) into v_count from operationcompany where operatorid=p_operatorid;
			if v_count=1 then 
					--根据操作员id查询所属公司
					select companyno into v_companyno from operationcompany where operatorid=p_operatorid;
						if length(v_companyno) > 0 THEN
							v_con:=v_con||' AND  g.dwbh='''||v_companyno||'''';
						end if; 
			end if;		
			
			if length(p_search)>0 THEN
				v_con:=v_con||'and (g.xlbh like ''%'||p_search||'%'' or g.xlname like ''%'||p_search||'%''or j.dwmc like ''%'||p_search||'%'')';
			end if;
			
			--总记录数
			v_sql :=' select count(*) from gjline g left join gjcompany j on g.dwbh=j.dwbh '||v_con;
			execute v_sql INTO v_rowcount;
			
			v_sql :='
				SELECT '||v_rowcount||' as recordcount,g.id,g.xlbh as lineno,g.xlname as linename,j.dwmc as companyname,j.dwbh as companyno,g.creatat as creatat,g.isfreetopolice   
				FROM gjline g 
				left join gjcompany j on g.dwbh=j.dwbh '||v_con||' ORDER BY j.dwbh,g.xlbh,g.xlname OFFSET '||p_offset||' LIMIT '||p_limit;

			open resultset for EXECUTE v_sql; 
	end if;


  return resultset;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

插入操作（更新删除）出错时，注释掉下面代码上再运行会显示出错的位置

EXCEPTION
WHEN OTHERS THEN

PostgreSQL报错:

No operator matches the given name and argument type(s). You might need to add explicit type casts.
说明你的字段数据类型与数据库字段类型不一致, 需要进行转换.
原文链接：https://blog.csdn.net/yongshiaoteman/article/details/81100034

存储过程，逗号写成句号

编译一下查看日志才看出问题

private static final Logger LOGGER = LoggerFactory.getLogger(ApiFilter.class);
LOGGER.error("错误：" + e);

Integer throughNum = BusteamDao.importBusteam(modelList.getData(), dataSourceName);
int size = modelList.getData().size();
if(size == throughNum){
    out.println("ok");
}else {
    out.println("通过了 "+throughNum + "条,  失败了" + (size-throughNum) +"条");
}

无法获取批处理后的结果集，一个一个sql executeUpdate后才可以，但这个就不是批处理

//                proc.executeUpdate();
//                int temp = proc.getInt(1);
//                if (temp==1){
//                    result++;
//                }

调用插入数据存储过程返回成功，但是没插进数据，因为v_result :=1时，在后面不能添加RAISE EXCEPTION '%',v_result; 会回滚（后端遇到exception会回滚）

GET DIAGNOSTICS v_rowcount := ROW_COUNT;
           if v_rowcount >0 THEN
               v_result :=1; --成功
           else
               v_result :=1000; --失败
               RAISE EXCEPTION '%',v_result;
           end if;

postgresql如果查询出的数据为空，则设为0 或 ‘’

COALESCE(p.paytype,0) as paytype,
COALESCE(p.bankcard, '') as bankcard

其它错误

只要有一个数据不存在，他就显示不存在

解决办法是让分组字段companyname，companyno为空的不参与统计

但是，如果是业务要求是这样，你不能改，因为业务上有的要求金额必须不能缺失，不参与统计必定造成缺失；这种情况找领导，你无法决定

SELECT 
			companyname,
			companyno,
			sum(case when issuercode = 'D1035510FFFFFFFF' then price else 0 end) as  dtallprice,
			sum(case when issuercode = 'D1035510FFFFFFFF' then account else 0 end) as  dtcount,
			sum(price) as allprice,
			sum(account) as count
FROM summerycompleterecord 
where companyname!='' and companyno!=''
GROUP BY companyno,companyname
ORDER BY companyno,companyname
OFFSET 0 LIMIT 20

内容等级什么时候出现的，应该初级吧

huang_ftpjh

关注

0
点赞
踩
26

收藏

觉得还不错? 一键收藏
打赏
0
评论
PostgreSQL存储过程

在Navicat上创建函数CREATE FUNCTION "public"."proc_h_testdemo"() RETURNS void AS $BODY$BEGIN -- Routine body goes here... RETURN;END$BODY$ LANGUAGE plpgsqla:= b;等价于C语言里面的：a = b 将b的值赋值给a的意思语法https://www.cnblogs.com/yanshw/p/12072093.htmla:= b;等.
复制链接

扫一扫