create or replace PACKAGE BODY YQSBJKXX IS
procedure P_YQGL_JMJKXX(p_cur out sys_refcursor,p_fw number,p_sj date) iS
/* CREATE GLOBAL TEMPORARY TABLE "TMP_YQDT_JMJKXXB"
(
jmid INTEGER,--居民id
sq INTEGER,--社区
xq INTEGER,--小区
ld INTEGER,--楼栋
mp INTEGER,--门牌
xm VARCHAR2(100), --姓名
sfzh VARCHAR2(100), --身份证号
sjh VARCHAR2(100),--手机号
zao_a VARCHAR2(30),--早
zhong_a VARCHAR2(30),--中
wan_a VARCHAR2(30),--晚
sffr VARCHAR2(100), --发热
sfks VARCHAR2(100), --咳嗽
sfot VARCHAR2(100), --呕吐
sffl VARCHAR2(100), --乏力
yczz VARCHAR2(100), --异常
rq_a VARCHAR2(100), --第一天
zao_b VARCHAR2(30),--早
zhong_b VARCHAR2(30),--中
wan_b VARCHAR2(30),--晚
sffrb VARCHAR2(100), --发热
sfksb VARCHAR2(100), --咳嗽
sfotb VARCHAR2(100), --呕吐
sfflb VARCHAR2(100), --乏力
yczzb VARCHAR2(100), --异常
rq_b VARCHAR2(100), --第二天
zao_c VARCHAR2(30),--早
zhong_c VARCHAR2(30),--中
wan_c VARCHAR2(30),--晚
sffrc VARCHAR2(100), --发热
sfksc VARCHAR2(100), --咳嗽
sfotc VARCHAR2(100), --呕吐
sfflc VARCHAR2(100), --乏力
yczzc VARCHAR2(100), --异常
rq_c VARCHAR2(100), --第三天
zao_d VARCHAR2(30),--早
zhong_d VARCHAR2(30),--中
wan_d VARCHAR2(30),--晚
sffrd VARCHAR2(100), --发热
sfksd VARCHAR2(100), --咳嗽
sfotd VARCHAR2(100), --呕吐
sffld VARCHAR2(100), --乏力
yczzd VARCHAR2(100), --异常
rq_d VARCHAR2(100),-- 第四天
zao_e VARCHAR2(30),--早
zhong_e VARCHAR2(30),--中
wan_e VARCHAR2(30),--晚
sffre VARCHAR2(100), --发热
sfkse VARCHAR2(100), --咳嗽
sfote VARCHAR2(100), --呕吐
sffle VARCHAR2(100), --乏力
yczze VARCHAR2(100), --异常
rq_e VARCHAR2(100)-- 第五天
)on commit preserve rows;
*/
BEGIN
delete from TMP_YQDT_JMJKXXB;
--插入居民信息
insert into TMP_YQDT_JMJKXXB (jmid,sq,xq,ld,mp,xm,sfzh,sjh)
select id,sq,xq,ld,mp,xm,sfzh,sjh
from T_QZGZ_YQDT where sfzh is not null order by sq,xq,ld,mp;
--更新第一天时间
update TMP_YQDT_JMJKXXB set rq_a=(select to_char(p_sj,'yyyy-MM-dd') as rq_a from dual );
--更新第一天异常
update TMP_YQDT_JMJKXXB a set a.sffr=(select (case b.brfr when 1 then '发热' when 2 then '' else '' end) as sffr from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfks=(select (case b.brks when 1 then '咳嗽' when 2 then '' else '' end) as sfks from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfot=(select (case b.brot when 1 then '呕吐' when 2 then '' else '' end) as sfot from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sffl=(select (case b.brfx when 1 then '乏力' when 2 then '' else '' end) as sffl from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.yczz=(select (b.sffr||b.sfks||b.sfot||b.sffl) as yczz from TMP_YQDT_JMJKXXB b where a.sfzh=b.sfzh and rownum=1);
--更新第一天体温
update TMP_YQDT_JMJKXXB a set a.zao_a=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='07:00:00' and b.sbsj<='12:00:00');
update TMP_YQDT_JMJKXXB a set a.zhong_a=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='12:00:01' and b.sbsj<='17:00:00');
update TMP_YQDT_JMJKXXB a set a.wan_a=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_a=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='17:00:01' and b.sbsj<='22:00:00');
commit;
--更新第二天时间
update TMP_YQDT_JMJKXXB set rq_b=(select to_char(p_sj-1,'yyyy-MM-dd') as rq_b from dual );
--更新第二天异常
update TMP_YQDT_JMJKXXB a set a.sffrb=(select (case b.brfr when 1 then '发热' when 2 then '' else '' end) as sffr from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfksb=(select (case b.brks when 1 then '咳嗽' when 2 then '' else '' end) as sfks from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfotb=(select (case b.brot when 1 then '呕吐' when 2 then '' else '' end) as sfot from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfflb=(select (case b.brfx when 1 then '乏力' when 2 then '' else '' end) as sffl from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.yczzb=(select (b.sffrb||b.sfksb||b.sfotb||b.sfflb) as yczz from TMP_YQDT_JMJKXXB b where a.sfzh=b.sfzh and rownum=1);
--更新第二天体温
update TMP_YQDT_JMJKXXB a set a.zao_b=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='07:00:00' and b.sbsj<='12:00:00');
update TMP_YQDT_JMJKXXB a set a.zhong_b=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='12:00:01' and b.sbsj<='17:00:00');
update TMP_YQDT_JMJKXXB a set a.wan_b=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_b=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='17:00:01' and b.sbsj<='22:00:00');
commit;
--更新第三天时间
update TMP_YQDT_JMJKXXB set rq_c=(select to_char(p_sj-2,'yyyy-MM-dd') as rq_c from dual );
--更新第三天异常
update TMP_YQDT_JMJKXXB a set a.sffrc=(select (case b.brfr when 1 then '发热' when 2 then '' else '' end) as sffr from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfksc=(select (case b.brks when 1 then '咳嗽' when 2 then '' else '' end) as sfks from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfotc=(select (case b.brot when 1 then '呕吐' when 2 then '' else '' end) as sfot from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfflc=(select (case b.brfx when 1 then '乏力' when 2 then '' else '' end) as sffl from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.yczzc=(select (b.sffrc||b.sfksc||b.sfotc||b.sfflc) as yczz from TMP_YQDT_JMJKXXB b where a.sfzh=b.sfzh and rownum=1);
--更新第三天体温
update TMP_YQDT_JMJKXXB a set a.zao_c=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='07:00:00' and b.sbsj<='12:00:00');
update TMP_YQDT_JMJKXXB a set a.zhong_c=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='12:00:01' and b.sbsj<='17:00:00');
update TMP_YQDT_JMJKXXB a set a.wan_c=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_c=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='17:00:01' and b.sbsj<='22:00:00');
commit;
--更新第四天时间
update TMP_YQDT_JMJKXXB set rq_d=(select to_char(p_sj-3,'yyyy-MM-dd') as rq_d from dual );
--更新第四天异常
update TMP_YQDT_JMJKXXB a set a.sffrd=(select (case b.brfr when 1 then '发热' when 2 then '' else '' end) as sffr from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfksd=(select (case b.brks when 1 then '咳嗽' when 2 then '' else '' end) as sfks from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfotd=(select (case b.brot when 1 then '呕吐' when 2 then '' else '' end) as sfot from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sffld=(select (case b.brfx when 1 then '乏力' when 2 then '' else '' end) as sffl from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.yczzd=(select (b.sffrd||b.sfksd||b.sfotd||b.sffld) as yczz from TMP_YQDT_JMJKXXB b where a.sfzh=b.sfzh and rownum=1);
--更新第四天体温
update TMP_YQDT_JMJKXXB a set a.zao_d=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='07:00:00' and b.sbsj<='12:00:00');
update TMP_YQDT_JMJKXXB a set a.zhong_d=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='12:00:01' and b.sbsj<='17:00:00');
update TMP_YQDT_JMJKXXB a set a.wan_d=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_d=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='17:00:01' and b.sbsj<='22:00:00');
commit;
--更新第五天时间
update TMP_YQDT_JMJKXXB set rq_e=(select to_char(p_sj-4,'yyyy-MM-dd') as rq_e from dual );
--更新第五天异常
update TMP_YQDT_JMJKXXB a set a.sffre=(select (case b.brfr when 1 then '发热' when 2 then '' else '' end) as sffr from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfkse=(select (case b.brks when 1 then '咳嗽' when 2 then '' else '' end) as sfks from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sfote=(select (case b.brot when 1 then '呕吐' when 2 then '' else '' end) as sfot from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.sffle=(select (case b.brfx when 1 then '乏力' when 2 then '' else '' end) as sffl from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1);
update TMP_YQDT_JMJKXXB a set a.yczze=(select (b.sffre||b.sfkse||b.sfote||b.sffle) as yczz from TMP_YQDT_JMJKXXB b where a.sfzh=b.sfzh and rownum=1);
--更新第五天体温
update TMP_YQDT_JMJKXXB a set a.zao_e=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='07:00:00' and b.sbsj<='12:00:00');
update TMP_YQDT_JMJKXXB a set a.zhong_e=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='12:00:01' and b.sbsj<='17:00:00');
update TMP_YQDT_JMJKXXB a set a.wan_e=(select b.brtw from T_QZGZ_MRJC b where a.jmid=b.sbjm and a.rq_e=to_char(b.sbrq,'yyyy-MM-dd') and rownum=1 and b.sbsj>='17:00:01' and b.sbsj<='22:00:00');
commit;
open p_cur for
select mp.dz,a.xm,a.sfzh,a.sjh,a.zao_a,a.zhong_a,a.wan_a,a.yczz,a.rq_a,a.zao_b,a.zhong_b,a.wan_b,a.yczzb,a.rq_b,a.zao_c,a.zhong_c,a.wan_c,a.yczzc,a.rq_c,
a.zao_d,a.zhong_d,a.wan_d,a.yczzd,a.rq_d,a.zao_e,a.zhong_e,a.wan_e,a.yczze,a.rq_e
from TMP_YQDT_JMJKXXB a
left join T_dmdz_mp mp on mp.id=a.mp
where a.sq=p_fw;
end P_YQGL_JMJKXX;
end YQSBJKXX;
pl/sql dev有一个调试存储过程的功能，他会记录每一步的耗时，可以针对性优化，直接在存储过程上右键就可以调试，详细可以参考这个文档：
《使用PLSQL Developer Profiler调优Oracle存储过程》