Postgre存储过程写法

最新推荐文章于 2023-07-09 08:52:04 发布

wind_gogo

最新推荐文章于 2023-07-09 08:52:04 发布

阅读量2.8k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_26597927/article/details/80253403

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

declare
tempSql text;
store_refcursor refcursor; 
id BIGINT;
name VARCHAR;
exist int;

BEGIN
tempSql :='select id as storeid,storename as storename  
from kx_kq_store 
where updatetime BETWEEN '''||begintime||''' AND '''||endtime||'''and platstatus=1';

raise notice 'tempSql is : %',tempSql;

	-- 游标遍历纪录 to_char(l.createtime,''yyyy-MM-dd'') as date
	open store_refcursor for execute tempSql;
		loop 
			fetch store_refcursor into id,name;
					if found then 
			
						-- 对于数据库里有的数据就做增量修改，没有的数据就做新增
						select count(*) from bi_dim_store where storeid=id into exist;
							if exist > 0 
								then
									update bi_dim_store set storename = name,platupdatetime = now()
									where storeid=id;
							else
								  insert into bi_dim_store(storeid,storename,platcreatetime,platupdatetime) 
                                                 values (id,name,now(),now());
							end if;
					else 
				     exit;
				  end if;
		end loop;
	close store_refcursor;

END

下面是一个比较复杂的存储过程：

declare
deleteSql text;
totalSql text;
worksetmemberSql text;
tempworkdateSql text;
plantempSql text;
plandatenumtempSql text;
actualdatenumtempSql text;
othersSql text;
nianjiatempSql text;
tiaoxiujiatempSql text;
bingjiatempSql text;
shijiatempSql text;
hunjiatempSql text;
chanjiatempSql text;
peichanjiatempSql text;
selectTempSql text;
insertSql text;
begin	
  
	if currdate is null OR currdate  = '' THEN
			currdate := '' || now();
	end if;
	
	deleteSql:= 'delete from kx_kq_monthreport_quick where reportmonth = to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')';

  raise notice 'deleteSql is : %',deleteSql;
	
	execute deleteSql;


	--查询出所有人员，营销区域，年月
	totalSql = 'select distinct m.userid,to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'') as reportmonth,p.orgstructid  as saleareaid 
	from kx_kq_signmiddleresult m
	inner join pl_userinfo p on p.userinfoid=m.userid
	where m.recorddate>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and m.recorddate<to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')';

	--获取人员考勤组
	 worksetmemberSql = 'select  case when qk.worksetid is null then 3001 else qk.worksetid end as worksetid,pl.userinfoid as userid  from pl_userinfo pl
	left join kx_qk_worksetmember qk on qk.userid=pl.userinfoid';


	--临时表，查询的结果依次为：人员，考勤组，工作日，当天是否出勤，当天应出勤工时，当天实出勤工时（暂时不计算),
	--迟到次数，迟到时间，早退次数，早退时间，缺卡次数，
	 tempworkdateSql = 'select m.userid,m.recorddate,
	sum(case when m.signdatetime is not null or m.signoffdatetime is not null then 1 else 0 end ) as issign ,
	sum(m.timesegmenthours) as planworkhours,
	sum(case when (m.signdatetime is null or m.signoffdatetime is null)or (m.signdatetime>=m.signoffdatetime) then 0  
			when m.begintime is null or m.endtime is null then  ((extract(epoch from m.signoffdatetime)-extract(epoch from m.signdatetime)))/3600
			when m.signdatetime<=m.begintime and m.signoffdatetime>=m.endtime then ((extract(epoch from m.signoffdatetime)-extract(epoch from m.signdatetime))-(extract(epoch from m.endtime)-extract(epoch from m.begintime)))/3600
			when m.signdatetime>=m.begintime and m.signoffdatetime<=m.endtime then 0
			when (m.signdatetime<=m.begintime and m.signoffdatetime<=m.begintime) or (m.signdatetime>=m.endtime and m.signoffdatetime>=m.endtime)  then (extract(epoch from m.signoffdatetime)-extract(epoch from m.signdatetime))/3600
			when m.signdatetime>=m.begintime and m.signoffdatetime>=m.endtime then (extract(epoch from m.signoffdatetime)-extract(epoch from m.endtime))/3600
			when m.signdatetime<=m.begintime and m.signoffdatetime<=m.endtime then (extract(epoch from m.begintime)-extract(epoch from m.signdatetime))/3600
			else 0 
	end )  as actualhours,
	sum(case when m.signstatus=''1'' then 1   else 0 end) as latenum, 
	sum(m.signduration) as signduration,
	sum(case when m.signoffstatus=''2'' then 1  else 0 end) as leaveearlynum, 
	sum(m.signoffduration) as leaveearlyhours,
	sum(case when m.signstatus=''4'' then 1   else 0 end) as notsignnum1, 
	sum(case when m.signoffstatus=''4'' then 1   else 0 end) as notsignnum2,
	sum(case when m.signstatus=''0'' then 1   else 0 end) as normal1,
	sum(case when m.signoffstatus=''0'' then 1  else 0 end) as normal2
	from kx_kq_signmiddleresult m
	where m.recorddate>= to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and m.recorddate<to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and m.isworkdate=''1'' 
	group by m.userid,m.recorddate';


	--其他指标:应出勤工时，实出勤工时,迟到次数，迟到时间，早退次数，早退时间，缺卡次数，
	 othersSql = 'select t.userid,t.reportmonth,t.saleareaid,
	case when sum(m.planworkhours)=0 then 0 else  round(sum(m.planworkhours)::numeric,2) end as planworkhours,
	case when sum(m.actualhours)=0 then 0 else round(sum(m.actualhours)::numeric,2) end as actualhours,
	sum(m.latenum) as latenum, 
	case when sum(m.signduration)=0 then 0 else round(sum(m.signduration)::numeric,2) end as latehours, 
	sum(m.leaveearlynum) as leaveearlynum, 
	case when sum(m.leaveearlyhours)=0 then 0 else  round(sum(m.leaveearlyhours)::numeric,2) end as leaveearlyhours, 
	sum(m.notsignnum1+m.notsignnum2) as notsignnum ,
	sum(m.normal1+m.normal2) as normalsign
	from (' || totalSql || ') as t
	left join (' || tempworkdateSql || ') as m on m.userid=t.userid 
	group by t.userid,t.reportmonth,t.saleareaid';

	--年假
	 nianjiatempSql = 'select l.usercode as userid, sum(l.datespan) as nianjia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895839991220015104
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';


	--调休假
	 tiaoxiujiatempSql = 'select l.usercode as userid, sum(l.datespan) as tiaoxiujia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840294887624704
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';


	--病假
	 bingjiatempSql = 'select l.usercode as userid, sum(l.datespan) as bingjia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840420637052928
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';


	--事假
	 shijiatempSql = 'select l.usercode as userid, sum(l.datespan) as shijia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840335027113984
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode' 
	;


	--婚假
	 hunjiatempSql = 'select l.usercode as userid, sum(l.datespan) as hunjia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840738766622720
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';


	--产假
	 chanjiatempSql = 'select l.usercode as userid, sum(l.datespan) as chanjia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840767371776000
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';


	--陪产假
	 peichanjiatempSql = 'select l.usercode as userid, sum(l.datespan) as peichanjia
	from kx_leave l
	where l.approvalstatus=''审批通过''  
	and l.leavetype=895840799688888320
	and ((l.starttime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'')
	and l.starttime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD'')) 
	or(l.endtime>=to_timestamp((to_char('''|| currdate  ||'''::timestamp,''YYYY-MM'')||''-01''),''YYYY-MM-DD'') 
	and l.endtime< to_timestamp((to_char(( '''|| currdate  ||'''::timestamp + interval ''1 month''),''YYYY-MM'')||''-01''),''YYYY-MM-DD''))
	)
	group by l.usercode';



	selectTempSql = 'SELECT t.userid,t.reportmonth,t.saleareaid,o.latenum,o.leaveearlynum,o.notsignnum,o.normalsign,
						COALESCE (t1.nianjia, 0),COALESCE (t2.tiaoxiujia, 0),COALESCE (t3.bingjia, 0),COALESCE (t4.shijia, 0),COALESCE (t5.hunjia, 0),COALESCE (t6.chanjia, 0),COALESCE (t7.peichanjia, 0)
									 FROM (' || totalSql || ') as t
						left join (' || othersSql || ') as o on o.userid=t.userid and o.reportmonth=t.reportmonth and o.saleareaid=t.saleareaid 
						left join (' || nianjiatempSql || ') as t1 on t1.userid=t.userid
						left join (' || tiaoxiujiatempSql || ') as t2 on t2.userid=t.userid
						left join (' || bingjiatempSql || ') as t3 on t3.userid=t.userid 
						left join (' || shijiatempSql || ') as t4 on t4.userid=t.userid 
						left join (' || hunjiatempSql || ') as t5 on t5.userid=t.userid  
						left join (' || chanjiatempSql || ') as t6 on t6.userid=t.userid 
						left join (' || peichanjiatempSql || ') as t7 on t7.userid=t.userid';

	insertSql := 'INSERT INTO kx_kq_monthreport_quick(userid,reportmonth,saleareaid,latenum,leaveearlynum,notsignnum,normalsign,
               nianjia,tiaoxiujia,bingjia,shijia,hunjia,chanjia,peichanjia) '|| selectTempSql ;
		
	execute insertSql;

  -- 更新跨月审批问题

	perform  f_update_leave_type_onemonth_time_quick();

end;

wind_gogo

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Postgre存储过程写法

declaretempSql text;store_refcursor refcursor; id BIGINT;name VARCHAR;exist int;BEGINtempSql :='select id as storeid,storename as storename from kx_kq_store where updatetime BETWEEN '''||be...
复制链接

扫一扫

专栏目录