PL/SQL 补充历史每天数据

1.通过嵌套子程序实现历史数据补充

场景问题：该表nhip_mdm.dc_practitioner是存放对护士信息操作记录的表，操作时间是res_published，emp_nature_name字段为护士的职业状态，如在职，退休，离职，合同工等。emp_name 字段来唯一标识该护士。
业务操作是护士入职，护士离职等状态变更，不管什么操作都会在nhip_mdm.dc_practitioner表中插入一条日志记录，并标识最新的操作修改时间。比如新入职护士，会在nhip_mdm.dc_practitioner表中插入一条护士入职记录，emp_nature_name=‘在职’，该记录带操作时间res_published。护士离职，会新增一条记录，并且emp_nature_name字段为“离职”，并标识该记录操作时间res_published。
需求：统计出每天医院在职护士信息存放到一个表中。
思考逻辑：nhip_mdm.dc_practitioner存放的是对护士信息的操作日志，就是不会删除数据，只会对数据利用res_published新增记录。 如果想统计某一天的护士数，应该是在统计那天及其之前时间，护士的状态为“入职”的那些护士数量加和。但是操作日志中会存在同一个护士状态变更多次情况，即统计那天及其之前时间，同一个护士存在多条“在职”记录（比如一个护士在职->离职->在职->离职->在职的状态变更有多条时），不能作为当天的护士数，会重复统计。
所以我们应该加以限制，应该取当天及其当天之前的日期每个护士最新的状态（按res_published排序的最新记录）为**“在职”**的护士数进行加和。
下面的plsql过程使用了嵌套子程序insertbyday统计每天在职护士信息，外层子程序是指定了一段时间，按照开始时间到结束时间，循环执行子程序，把每天的在职护士存入表oa_person_property中。

CREATE OR REPLACE Procedure  Run_oa_person_property(In_Tableid   Number,
                                                        In_Begintime Varchar2,
                                                        In_Endtime   Varchar2,
                                                        Out_Result   Out Number,
                                                        Out_Msg      Out Varchar2,
                                                        Out_Rowcount Out Number)
 As
          Pragma Autonomous_Transaction;
          V_Begintime Date := To_Date(In_Begintime, 'yyyy-mm-dd hh24:mi:ss');
          V_Endtime   Date := To_Date(In_Endtime, 'yyyy-mm-dd hh24:mi:ss');
		   v_date date:=trunc(V_Begintime);
		   N Integer:=trunc(V_Endtime)-trunc(V_Begintime);
          procedure insertbyday(p_begintime Date) 
          is 
          begin  
              Insert Into oa_person_property
                            (DOCT_CODE,
                             DOCT_NAME,
                             DEPT_CODE,
                             DEPT_NAME,
                             BIRTHDAY,
                             ENTRY_TIME,
                             STATUS,
                             RYLB,
                             RYZC,
                             ZCHQSJ,
                             BYXX,
                             ZHUANYE,
                             BYSJ,
                             XUELI,
                             MODIFY_TIME,
                             SEX_CODE,
                             EMPL_TYPE)
                        select  t.emp_code doct_code,
								t.emp_name doct_name,
								t.dept_code,
								t.dept_name,
								t.birth_date        BIRTHDAY, 
								trunc(p_begintime)  ENTRY_TIME,----当天日期
								'1'                 STATUS,
								t.emp_nature_code   rylb,
								t.title_herp_code   ryzc,
								t.filing_date       ZCHQSJ,
								t.education_name    BYXX,
								'' ZHUANYE,
								t.educate_date      BYSJ,
								t.education         XUELI,
								t.res_updated       MODIFY_TIME,
								t.gender_name       sex_code,
								t.emp_type_code    empl_type 
								from 
							   (  select   row_number() over(partition by m.emp_code order by m.res_published desc) seq, 
										 V_Begintime,
										 m.*
										 from nhip_mdm.dc_practitioner m
										where m.res_published <=p_begintime   --- to_date('2019-10-30','yyyy-mm-dd')
										  and m.emp_code not in ('TTTTTT','TEST') 
								   )t
							where t.seq=1
							  and t.emp_nature_name not in ('离职', '退休', '合同','离休','实习') ;  ---限定在职 
                          Commit;
          end insertbyday;  
          
Begin
        Out_Result := 0;
        Delete oa_person_property
          Where ENTRY_TIME >= V_Begintime
           And ENTRY_TIME < V_Endtime; 
        for i in 1..N 
            loop
                 insertbyday(v_date);
                 v_date :=trunc(v_date)+1;
            end loop;  
        Out_Rowcount := Sql%Rowcount;
Exception
  When Others Then
    Out_Result := -1;
    Out_Msg    := Sqlerrm;
    Rollback;
End;

2.总结

1、使用sql解决数据统计问题，首先要理解业务场景，即理解数据源表nhip_mdm.dc_practitioner中所含有的业务操作是什么，数据如何存储，更新、删除的。这是基础和关键，没有这一步下面的业务sql脚本无从谈起。
2、要理解当天在职的数据含义是什么。在职就是，当天及其之前的历史数据中，该护士的状态一直是在职的这些护士，即操作时间res_published 小于等于统计时间这段的数据。res_published操作时间是标识护士信息记录的时间戳。
3、由于emp_code护士被res_published 标识了多条，我们只res_published 小于等于统计日期中最新的一条。该条记录标识了护士的最新状态。
4、掌握嵌套子程序以及循环执行嵌套子程序的相关知识。