存储过程简单写法

create or replace procedure d_emr_out_c_zy_20220223(SI_CODE in number,SI_TIME in number) is
  
  
  V_MAXTIME  VARCHAR2(10);
  V_PLAYTIME VARCHAR2(10);
  V_OUTPRONUM VARCHAR2(20);--数量
  V_OUTFZPONUM VARCHAR2(20);
  V_RECIPEKJ_NUM VARCHAR2(20);
  V_RECIPEJB_NUM VARCHAR2(20);
  V_OUTPDRUG_AMT VARCHAR2(20);
  V_OUTPANTDRUG_NUM VARCHAR2(20);
  cursor ALL_ORGCODES is select t.code as ORG2_CODE from area_nexus t where  code='123';
  
  begin

   select  max(DATE_TIME)  into  V_MAXTIME  from d_emr_out;
   commit;

   if SI_TIME =0
     then
       if V_MAXTIME is NULL
       then  V_PLAYTIME:='20190701';
       else  V_PLAYTIME:=V_MAXTIME;
       end if;
     else
       if V_MAXTIME>SI_TIME
       then V_PLAYTIME:=V_MAXTIME;
       else V_PLAYTIME:= SI_TIME;
       end if;
   end if;
 
   V_PLAYTIME:= SI_TIME;
    while V_PLAYTIME< '20220106'
      loop
      insert into d_emr_out(ORG_NAME,ORG_CODE)  select EA_NAME,CODE from area_nexus where  code='23432' ;
      
        commit;
        update d_emr_out set DATE_TIME=V_PLAYTIME where DATE_TIME is null;
        commit;
         for EVERY_ORG in ALL_ORGCODES  loop
           begin
            
             select 
              sum(case when is_anti =1 and DRUG_CLASS_CODE is not null then 1 else 0 end) ,--抗菌药物处方数量
              sum(case when is_essential=1 and DRUG_CLASS_CODE is not null then 1 else 0 end ), --基本药物处方数量
              count(distinct a.id) recipe_num--处方数量
              into  v_anti_drug_num,v_jb_drug_num,v_recipe_num
               from EMR_OUTPATIENT_RECIPE@data_ehr_cen_zy a
              left join  EMR_OUTPATIENT_RECIPE_ITEM@data_ehr_cen_zy b on a.id=b.OR_ID
            where  substr(prescribe_date_gb,0,8)  = V_PLAYTIME and  a.ORG_CODE=EVERY_ORG.ORG2_CODE;
            

          select sum(RECEIVABLE_AMOUNT), --门急诊总费用
          sum(case when DRUG_CLASS_CODE is not null then RECEIVABLE_AMOUNT else 0 end),--药物金额
          sum(case when IS_ANTI ='1' then RECEIVABLE_AMOUNT else 0 end) --抗菌药物费用
          into V_ORGEVDPNUM,V_OUTPDRUG_AMT,V_OUTPANTDRUG_AMT
          from  EMR_OUTPATIENT_ITEM@data_ehr_cen_zy t 
           where to_char(TRANSACTION_DATE,'yyyyMMdd')=V_PLAYTIME--operateTime
          and TRANSACTION_DATE is not null and org_code=EVERY_ORG.ORG2_CODE;

         
               select 
             count(distinct(OUTPATIENT_NUM)) OUTPATIENT_NUM , --门急诊人次数
              count(distinct case when FALG_FIRST_VISIT_name like '%复诊%' then OUTPATIENT_NUM else null end) as FALG_FIRST_VISIT--复诊人次
             into  V_OUTPPER_NUM ,V_OUTFZPONUM
            from EMR_OUTPATIENT_RECORD@data_ehr_cen_zy b  
             where
             substr(replace(TREATMENT_DATE,'-',''),0,8)=V_PLAYTIME
             and  b.ORG_CODE=EVERY_ORG.ORG2_CODE
             and TREATMENT_DATE is not null;
            
             select 
            count(distinct case when is_anti='1' then outpatient_num_gb else null end), --门诊使用抗菌药物患者人数
             count(distinct case when is_essential='1' then outpatient_num_gb else null end) --门诊使用基本药物患者人数
              into v_anti_num,V_BasicMedicine_NUM
            from EMR_OUTPATIENT_RECIPE@data_ehr_cen_zy a
            inner join EMR_OUTPATIENT_RECIPE_ITEM@data_ehr_cen_zy b on a.id=b.or_id 
             where
            -- substr(prescribe_date_gb,0,8)=V_PLAYTIME
             substr(replace(prescribe_date_gb,'-',''),0,8)=V_PLAYTIME
             and  a.ORG_CODE= EVERY_ORG.ORG2_CODE
             and prescribe_date_gb is not null;
             
   
              -- dbms_output.put_line('==============');
             update d_emr_out
                 set
                     OUTFEE_PROJ=V_ORGEVDPNUM,
                    OUTPRO_NUM=V_OUTPPER_NUM, 
                 where ORG_CODE= EVERY_ORG.ORG2_CODE and DATE_TIME=V_PLAYTIME;
            commit;
           end;
         end loop;
         V_PLAYTIME := to_char(TRUNC(to_date(V_PLAYTIME,'yyyymmdd')+1),'YYYYMMDD');
       end loop;
   commit;
end d_emr_out_c_zy_20220223;