数据更新

 

1、查询所有领药记录并插入新表 tb_real_dispense_copy 
create table tb_real_dispense_copy as 
select case papid
         when 101 then
          1
         when 221 then
          2
         else
          3
       end domain,
       tr.plntitlea,
       tr.plandate,
       tr.sendcount,
       tr.leaddrugpatient,
       tr.dispenseman,
       tr.plandescribe,
       tr.nextdate,
       tr.returnbox,
       tp.ppin,
       tp.papid,
       tp.doctorname,
       tps.counting,
       tps.standardname,
       tp.ccfcode,
       tr.drugid
  from tb_real_dispense tr
  left join tb_patient tp
    on tr.ppapid = tp.id
  left join tb_pap_standard tps
    on tr.plandescribe = tps.id
 where tr.dstatus = 0
   and tr.papid = 101
   and (to_date(tr.plandate, 'yyyy-mm-dd hh24:mi:ss') >=
       to_date('2000-01-01', 'yyyy-mm-dd hh24:mi:ss') and
       to_date(tr.plandate, 'yyyy-mm-dd hh24:mi:ss') <
       to_date('2014-07-30', 'yyyy-mm-dd hh24:mi:ss'))
 order by tr.id

 2、将 ccf表空间下的表 tb_real_dispense_copy 导出为dmp文件，导入到tipap数据库表空间 
 3、tipap表空间下 表tb_real_dispense_copy 新建列 medicatement（药品规格）、drtype（剂型 1：片剂 2：胶囊） 并根据列 plandescribe 赋值。

 4、
-- 向领药记录表中插入数据（先备份并删除原表2009-01-16之后数据项）
insert into tb_drawrecord (DRID, DRPATIENT, DRTIPAPID, DRGIPAPID, DRIDCARD, DRMEDICAMENT, DRNUMBER, DRREMARK, DRDATE, DRUPDATE, 
DRTYPE, DRJOBID, DRDOCTORNAME, DRRECIPEDATE, DRCREATETYPE, DRCREATEDATE, DRTIMESCOUNT, DRHTIMESCOUNT, DRTRANSFORMCOUNT, DRDISPENSEMAN, 
DRNEXTDATE, DRRETURNBNUMBER, DRCCFCODE, DRPIN, DRDOMAIN, ISLAST)
select seq_drawrecord_drid.nextval, 
       p.pid,
       null,
       p.pgipapid,
       p.pidcard,
       c.medicatement,
       c.sendcount,null,c.plandate,null,c.drtype,null,c.doctorname,null,'0',c.plandate,1,1,0,c.dispenseman,substr(c.nextdate,0,10),c.returnbox,c.ccfcode,c.ppin,'1',c.drugid
  from tb_real_dispense_copy c, tb_patient p
 where p.pgpin = c.ppin and p.pdomain = c.domain
   and c.domain = 1;
 
insert into tb_drawrecord (DRID, DRPATIENT, DRTIPAPID, DRGIPAPID, DRIDCARD, DRMEDICAMENT, DRNUMBER, DRREMARK, DRDATE, DRUPDATE, 
DRTYPE, DRJOBID, DRDOCTORNAME, DRRECIPEDATE, DRCREATETYPE, DRCREATEDATE, DRTIMESCOUNT, DRHTIMESCOUNT, DRTRANSFORMCOUNT, DRDISPENSEMAN, 
DRNEXTDATE, DRRETURNBNUMBER, DRCCFCODE, DRPIN, DRDOMAIN, ISLAST)
   select seq_drawrecord_drid.nextval,
          p.pid,
       p.ptipapid,
       null,
       p.pidcard,
       c.medicatement,
       c.sendcount,null,c.plandate,null,c.drtype,null,c.doctorname,null,'0',c.plandate,1,1,0,c.dispenseman,substr(c.nextdate,0,10),c.returnbox,c.ccfcode,c.ppin,'2',c.drugid
  from tb_real_dispense_copy c, tb_patient p
 where p.ppin = c.ppin and p.pdomain = c.domain
   and c.domain = 2;
   
   
   -- 查询全部数据
   select seq_drawrecord_drid.nextval, 
       p.pid,
       null,
       p.pgipapid,
       p.pidcard,
       c.medicatement,
       c.sendcount,null,c.plandate,null,c.drtype,null,c.doctorname,null,'0',c.plandate,1,1,0,c.dispenseman,substr(c.nextdate,0,10),c.returnbox,c.ccfcode,c.ppin,'1',c.drugid
  from tb_real_dispense_copy c, tb_patient p
 where p.pgpin = c.ppin and p.pdomain = c.domain
   and c.domain = 1
   union all 
     select seq_drawrecord_drid.nextval,
          p.pid,
       p.ptipapid,
       null,
       p.pidcard,
       c.medicatement,
       c.sendcount,null,c.plandate,null,c.drtype,null,c.doctorname,null,'0',c.plandate,1,1,0,c.dispenseman,substr(c.nextdate,0,10),c.returnbox,c.ccfcode,c.ppin,'2',c.drugid
  from tb_real_dispense_copy c, tb_patient p
 where p.ppin = c.ppin and p.pdomain = c.domain
   and c.domain = 2