【SQL】工作中的存储过程

工作中做了几个存储过程。
建立了几个零时表：

-- Create table
create table D_20190129
(
  billno     VARCHAR2(20),
  transtime  DATE,
  sap_status VARCHAR2(10),
  sap_note   VARCHAR2(100),
  sap_result VARCHAR2(100)
)
tablespace H2DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table D_20190130
(
  billno     VARCHAR2(20),
  transtime  DATE,
  sap_status VARCHAR2(10),
  sap_note   VARCHAR2(100),
  sap_result VARCHAR2(100)
)
tablespace H2DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table D_20190131
(
  memcardno   VARCHAR2(40) not null,
  busno       NUMBER(10) not null,
  saleamount  NUMBER(14,4) not null,
  realamount  NUMBER(14,4) not null,
  puramount   NUMBER(14,4) not null,
  integral    NUMBER(14,4) not null,
  integrala   NUMBER(14,4),
  cardholder  VARCHAR2(40),
  mobile      VARCHAR2(20),
  createtime  DATE not null,
  lasttime    DATE,
  integralsum NUMBER(16,4),
  cutmonth    VARCHAR2(8)
)
tablespace H2DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table D_20190132
(
  busno        VARCHAR2(10),
  busname      VARCHAR2(40),
  warecode     VARCHAR2(20),
  warename     VARCHAR2(80),
  wareqty      NUMBER,
  awaitqty     NUMBER,
  makeno       VARCHAR2(20),
  stallname    VARCHAR2(10),
  applyno      VARCHAR2(20),
  objbusno     VARCHAR2(20),
  dj_execdate  DATE,
  pro_execdate DATE,
  apptype      VARCHAR2(10),
  note         VARCHAR2(20)
)
tablespace H2DB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

对应的procedure。

create or replace procedure hydee_sap_check_qh
as
   cout integer := 0;
   cout1 integer := 0;
   cout2 integer := 0;
   cout3 integer := 0;
   status varchar2(1);
   note varchar2(100);
begin
  --首先查询t_20190129表中的数据是否已经被执行，即其请货单已经存在配送单情况
   for t in (select * from d_20190129) loop
       select count(1) into cout from t_distapply_h where billcode='APP' and compid=2 and status=1  and distno is null and applyno=t.billno;
       if cout=0
         then 
           delete from d_20190129 where billno=t.billno;
           commit;
       else
          DBMS_OUTPUT.put_line('ok');
       end if;
   end loop;  
-- 查询2019年1月1日到当前时间2天内的请货单没有配送单信息
 FOR x IN (select billno,transtime from T_SAP_QUEUE_R where tableobj='T_SAP_PO_CRE_R'and status=2 and billno in (select applyno from t_distapply_h where billcode='APP' and compid=2 and status=1 and execdate>to_date('2019-01-01','yyyy-mm-dd') and execdate<trunc(sysdate-2)  and distno is null)     order by transtime) LOOP
   --如果此单信息已经存在t_20190129表中则不进行处理，如有增加则插入
   SELECT count(1) into cout1 FROM t_distapply_h t_distapply_h WHERE billcode = 'APP' AND compid=2 and t_distapply_h.notes='从'||x.billno||'复制得来';
   if cout1=0
     then select count(1) into cout2 from d_20190129 where billno=x.billno;
     if cout2=0
       then insert into d_20190129(billno,transtime) values(x.billno,x.transtime);
            commit;
       else
         DBMS_OUTPUT.put_line('ok');
     end if;
     --查询sap下发中间表状态及信息并插入t_20190129表
     SELECT count(1) into cout3 FROM t_sap_queue a left join T_SAP_BILL_IN b on b.pkid=a.pkid WHERE  b.storebillno=x.billno;
     if cout3>0
       then select a.status,a.notes into status,note FROM t_sap_queue a left join T_SAP_BILL_IN b on b.pkid=a.pkid WHERE  b.storebillno=x.billno;
        update d_20190129 set sap_status=status,sap_note=note where billno=x.billno;
        commit;
     else
        DBMS_OUTPUT.put_line('ok');
     end if;
   else
     DBMS_OUTPUT.put_line('ok');
  end if;
 END LOOP;
 

end;

create or replace procedure hydee_sap_check_djdb
as
   cout integer := 0;
   cout1 integer := 0;
   cout2 integer := 0;
   status varchar2(1);
   note varchar2(100);
begin

    --首先查询t_20190130表中的数据是否已经被执行，即其请货单已经存在配送单情况
   for t in (select * from d_20190130) loop
      -- select count(1) into cout from t_distapply_h where billcode='APP' and compid=2 and status=1  and distno is null and applyno=t.billno;
       SELECT count(1) into cout FROM t_dist_h WHERE billcode = 'DSSM' AND compid=2 and status=0 and checkbit2=1 and checkbit3=0 and distno=t.billno  and lasttime>to_date('2019-01-01','yyyy-mm-dd');
       if cout=0
         then
           delete from d_20190130 where billno=t.billno;
           commit;
       else
          DBMS_OUTPUT.put_line('ok');
       end if;
   end loop;

   -- 查询及今年内店间调拨单sap没有执行的数据
   for x in ( SELECT distno,lasttime FROM t_dist_h WHERE billcode = 'DSSM' AND compid=2 and status=0 and checkbit2=1 and checkbit3=0 and lasttime>to_date('2019-01-01','yyyy-mm-dd')) loop
     --如果此单信息已经存在t_20190130表中则不进行处理，如有增加则插入 
     select count(1) into cout1 from d_20190130 where billno=x.distno;
     if cout1=0
       then insert into d_20190130(billno,transtime) values(x.distno,x.lasttime);
       commit;
     else
       DBMS_OUTPUT.put_line('订单已存在');
     end if;
     
        
     
     --查询sap下发中间表状态及信息并插入t_20190130表
     SELECT count(1) into cout2 FROM t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno=x.distno;
     --select * from t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno='店间调拨单号';
     if cout2>0
        then select distinct a.status,a.notes into status,note FROM t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno=x.distno;
        update d_20190130 set sap_status=status,sap_note=note where billno=x.distno;
        commit;
     else
          DBMS_OUTPUT.put_line('ok');
     end if;
   end loop;


end;

create or replace procedure hydee_memcard_month_cut as
begin
  for u in (SELECT memcardno,
                   busno,
                   saleamount,
                   realamount,
                   puramount,
                   integral,
                   integrala,
                   cardholder,
                   mobile,
                   createtime,
                   lasttime,
                   integralsum,
                   to_char(add_months(trunc(sysdate), -1), 'yyyymm') as cutmonth
              FROM t_memcard_reg)

   loop
    insert into d_20190131
      (memcardno,
       busno,
       saleamount,
       realamount,
       puramount,
       integral,
       integrala,
       cardholder,
       mobile,
       createtime,
       lasttime,
       integralsum,
       cutmonth)
    values
      (u.memcardno,
       u.busno,
       u.saleamount,
       u.realamount,
       u.puramount,
       u.integral,
       u.integrala,
       u.cardholder,
       u.mobile,
       u.createtime,
       u.lasttime,
       u.integralsum,
       u.cutmonth);
    commit;
  end loop;

end;

create or replace procedure hydee_sap_check_dck as
       tc_count integer := 0;
       bsby_count integer := 0;
       djdb_count integer := 0;
       applyno varchar(20);--退仓单号
       objbusno varchar(10);
       dj_execdate date;
       abnormityno varchar(20);--报损报溢单号
       distno varchar(20);--店间调拨单单号
begin
  --清空记录表
  EXECUTE   IMMEDIATE 'truncate table d_20190132';
  --查询所有门店待出库商品及相关信息
  for c1 in (SELECT d.compid,d.busno,sz.zonename,vw.warecode,vw.warename,d.wareqty,d.awaitqty,i.makeno,ts.stallname stallname FROM t_store_d d JOIN t_ware vw
                ON d.wareid = vw.wareid AND d.compid = vw.compid LEFT JOIN t_stall ts ON d.stallno = ts.stallno AND d.compid = ts.compid AND d.busno = ts.busno
              LEFT JOIN s_zone sz ON ts.zoneno = sz.zoneno AND ts.compid = sz.compid LEFT JOIN t_store_i i ON d.compid = i.compid AND d.wareid = i.wareid
               AND d.batid = i.batid WHERE d.compid = 2  and awaitqty > 0 and length(d.busno) = 6) loop              
                    --首先查询退仓单是否存在
                     SELECT count(1) into tc_count FROM t_distapply_d JOIN t_distapply_h ON t_distapply_d.applyno = t_distapply_h.applyno LEFT JOIN v_ware_base
                     ON t_distapply_d.wareid = v_ware_base.wareid AND t_distapply_h.compid = v_ware_base.compid WHERE t_distapply_h.billcode = 'RAP'
                     AND t_distapply_h.compid = 2 and t_distapply_h.retdistno is null and (t_distapply_h.srcbusno = c1.busno and v_ware_base.warecode = c1.warecode and t_distapply_h.status = 1) and applyqty=c1.awaitqty and makeno=c1.makeno;
                     if tc_count=1
                         then
                             
                          SELECT t_distapply_d.applyno ,t_distapply_h.objbusno, t_distapply_h.lasttime into applyno, objbusno,dj_execdate FROM t_distapply_d JOIN t_distapply_h   ON t_distapply_d.applyno = t_distapply_h.applyno
                           LEFT JOIN v_ware_base    ON t_distapply_d.wareid = v_ware_base.wareid   AND t_distapply_h.compid = v_ware_base.compid WHERE t_distapply_h.billcode = 'RAP'
                           AND t_distapply_h.compid = 2 and t_distapply_h.retdistno is null   and (t_distapply_h.srcbusno = c1.busno and      v_ware_base.warecode = c1.warecode and t_distapply_h.status = 1) and applyqty=c1.awaitqty and makeno=c1.makeno;

                          insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,objbusno,apptype,dj_execdate,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,applyno
                          ,objbusno,'退仓单',dj_execdate,sysdate);
                          commit;
                         elsif tc_count>1
                           then
                           insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'退仓单','需要手工执行',sysdate);
                           commit;
                         else
                           DBMS_OUTPUT.put_line('其他问题');
                         end if;
                      ---其次查询报损报溢单是否存在
                     SELECT count(1) into bsby_count  FROM t_abnormity_d t_abnormity_d  JOIN t_abnormity_h t_abnormity_h   ON t_abnormity_d.abnormityno = t_abnormity_h.abnormityno
                     and t_abnormity_h.billcode = 'ABN'  LEFT JOIN v_ware_base v_ware_base   ON t_abnormity_d.wareid = v_ware_base.wareid  AND t_abnormity_h.compid = v_ware_base.compid
                     left join s_busi s_busi    on t_abnormity_h.busno = s_busi.busno   and t_abnormity_h.compid = s_busi.compid WHERE  t_abnormity_h.compid = 2
                     and (v_ware_base.warecode = c1.warecode and  makeno=c1.makeno and wareqtyb=c1.awaitqty and s_busi.busno=c1.busno);
                     if bsby_count=1
                         then
                             SELECT t_abnormity_d.abnormityno,t_abnormity_h.lasttime into abnormityno,dj_execdate  FROM t_abnormity_d t_abnormity_d
                             JOIN t_abnormity_h t_abnormity_h    ON t_abnormity_d.abnormityno = t_abnormity_h.abnormityno   and t_abnormity_h.billcode = 'ABN'  LEFT JOIN v_ware_base v_ware_base
                             ON t_abnormity_d.wareid = v_ware_base.wareid   AND t_abnormity_h.compid = v_ware_base.compid  left join s_busi s_busi
                             on t_abnormity_h.busno = s_busi.busno   and t_abnormity_h.compid = s_busi.compid WHERE  t_abnormity_h.compid = 2
                              and (v_ware_base.warecode = c1.warecode and  makeno=c1.makeno and wareqtyb=c1.awaitqty and s_busi.busno=c1.busno);
                          insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,dj_execdate,apptype,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,abnormityno,dj_execdate,'报损报溢单',sysdate);
                          commit;
                         elsif bsby_count>1
                           then
                           insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'报损报溢单','需要手工执行',sysdate);
                           commit;
                         else
                           DBMS_OUTPUT.put_line('其他问题');
                         end if;
                         
                         
                           ---最后查店间调拨单是否存在
                     SELECT count(1) into djdb_count FROM t_dist_d t_dist_d  JOIN t_dist_h t_dist_h   ON t_dist_d.distno = t_dist_h.distno and t_dist_h.billcode='DSSM'
                     LEFT JOIN v_ware_base t_ware    ON t_dist_d.wareid = t_ware.wareid AND t_dist_h.compid = t_ware.compid WHERE 
                      t_dist_h.compid=2 and (  t_dist_h.srcbusno =c1.busno and  t_ware.warecode = c1.warecode and  t_dist_h.status =0  and t_dist_d.makeno=c1.makeno and  t_dist_d.wareqty=c1.awaitqty);
                     if djdb_count=1
                         then
                              SELECT t_dist_h.distno, t_dist_h.objbusno, t_dist_h.lasttime into distno,objbusno,dj_execdate FROM t_dist_d t_dist_d  JOIN t_dist_h t_dist_h   ON t_dist_d.distno = t_dist_h.distno and t_dist_h.billcode='DSSM'
                     LEFT JOIN v_ware_base t_ware    ON t_dist_d.wareid = t_ware.wareid AND t_dist_h.compid = t_ware.compid WHERE 
                      t_dist_h.compid=2 and (  t_dist_h.srcbusno =c1.busno and  t_ware.warecode = c1.warecode and  t_dist_h.status =0  and t_dist_d.makeno=c1.makeno and  t_dist_d.wareqty=c1.awaitqty);
                          insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,objbusno,dj_execdate,apptype,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,distno,objbusno,dj_execdate,'店间调拨单',sysdate);
                          commit;
                         elsif djdb_count>1
                           then
                           insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'店间调拨单','需要手工执行',sysdate);
                           commit;
                         else
                           DBMS_OUTPUT.put_line('其他问题');
                         end if;
                         
                         
  end loop;

end;