文档章节

数据转移高效方法(oracle)

剑尊
 剑尊
发布于 2017/04/05 11:22
字数 1520
阅读 10
收藏 0
  • 数据转移高效方式(从A表到B表)

procedure proc_insert_test(p_run_no in varchar2) is

  cursor c_stg is
    select * from 表名 where run_no = p_run_No;
  TYPE v_stg IS TABLE OF c_stg%rowtype index by pls_integer;
  x v_stg;
begin
  open c_stg;
  loop
    exit when c_stg%notfound;
    FETCH c_stg BULK COLLECT
      INTO x limit vs_commit_num;--指定提交量大小后面提供方法配制获取
    begin
      forall i in 1 .. x.count
        insert into 表名
          (BILL_NO,
           PC_NO,
           NAME,
           SEX,
           AGE,
           ID_NO,
           INSURANCE_NO,
           VISIT_TYPE,
           MEDICAL_TYPE,
           IN_HOSP_DATE,
           OUT_HOSP_DATE,
           IN_HOSP_TYPE,
           OUT_HOSP_REASON,
           DIAGNOSE_CODE,
           SUM_AMOUNT,
           APPLY_PAY_AMOUNT,
           SELF_PAY_ANOUNT,
           AREA_CODE,
           MEDICAL_ORG_CODE,
           MEDICAL_ORG_NAME,
           MEDICAL_DEPT_CODE,
           MEDICAL_DEPT_NAME,
           DOCTOR_CODE,
           DOCTOR_NAME,
           VISIT_NO,
           SETTLE_DATE,
           ID_BILL,
           INSURANCE_TYPE,
           FUND_PAY_AMOUNT,
           SELF_ACCOUNT_AMOUNT,
           SELF_CASH_AMOUNT,
           CHARGE_MAN,
           REGION_CODE,
           ID_VISIT,
           NON_INSURANCE_AMOUNT,
           INSURANCE_AMOUNT,
           PERSON_TYPE,
           SERIOUS_ILLNESS,
           DEDUCTION_AMOUNT_ALLOW,
           SUPPLY_INSURANCE_AMOUNT,
           SUPPLY_SERIOUS_AMOUNT,
           DATA_SOURCES,
           PC_STATUS,
           HOSPITAL_NO,
           HISTORICAL_ACCOUNT_AMOUNT,
           PUBLIC_SUBSIDY,
           PERSON_PREPAYMENT_AMOUNT,
           PERSONAL_STRAIN_AMOUNT,
           CIVIL_RELIEF_EXPENDITURE,
           SEVERE_SUBSIDY_PAYMENTS,
           CIVIL_SEVERE_SUBSIDY_PAYMENTS,
           MIDWAY_SETTLEMENT_SIGN,
           TREATMENT_DOCTOR,
           NUMBER_BEDS,
           INPATIENT_AREA,
           ORIGINAL_HOSPITAL_NO,
           SUPPLY_RESCUE_AMOUNT,
           MEDICAL_AMOUNT,
           YEAR_ACCOUNT_AMOUNT,
           COMPANY_CODE,
           COMPANY_NAME,
           CREATED_DATE,
           BATCH_NO,
           CHARGE_FLAG,
           STATUS,
           PRESCRIBING_DOCTOR,
           SOCIAL_SECURITY_STAFF_FLAG)
        values
          (x(i).BILL_NO,
           x(i).PC_NO,
           x(i).NAME,
           x(i).SEX,
           x(i).AGE,
           x(i).ID_NO,
           x(i).INSURANCE_NO,
           x(i).VISIT_TYPE,
           x(i).MEDICAL_TYPE,
           x(i).IN_HOSP_DATE,
           x(i).OUT_HOSP_DATE,
           x(i).IN_HOSP_TYPE,
           x(i).OUT_HOSP_REASON,
           x(i).DIAGNOSE_CODE,
           x(i).SUM_AMOUNT,
           x(i).APPLY_PAY_AMOUNT,
           x(i).SELF_PAY_ANOUNT,
           x(i).AREA_CODE,
           x(i).MEDICAL_ORG_CODE,
           x(i).MEDICAL_ORG_NAME,
           x(i).MEDICAL_DEPT_CODE,
           x(i).MEDICAL_DEPT_NAME,
           x(i).DOCTOR_CODE,
           x(i).DOCTOR_NAME,
           x(i).VISIT_NO,
           x(i).SETTLE_DATE,
           x(i).ID_BILL,
           x(i).INSURANCE_TYPE,
           x(i).FUND_PAY_AMOUNT,
           x(i).SELF_ACCOUNT_AMOUNT,
           x(i).SELF_CASH_AMOUNT,
           x(i).CHARGE_MAN,
           x(i).REGION_CODE,
           x(i).ID_VISIT,
           x(i).NON_INSURANCE_AMOUNT,
           x(i).INSURANCE_AMOUNT,
           x(i).PERSON_TYPE,
           x(i).SERIOUS_ILLNESS,
           x(i).DEDUCTION_AMOUNT_ALLOW,
           x(i).SUPPLY_INSURANCE_AMOUNT,
           x(i).SUPPLY_SERIOUS_AMOUNT,
           x(i).DATA_SOURCES,
           x(i).PC_STATUS,
           x(i).HOSPITAL_NO,
           x(i).HISTORICAL_ACCOUNT_AMOUNT,
           x(i).PUBLIC_SUBSIDY,
           x(i).PERSON_PREPAYMENT_AMOUNT,
           x(i).PERSONAL_STRAIN_AMOUNT,
           x(i).CIVIL_RELIEF_EXPENDITURE,
           x(i).SEVERE_SUBSIDY_PAYMENTS,
           x(i).CIVIL_SEVERE_SUBSIDY_PAYMENTS,
           x(i).MIDWAY_SETTLEMENT_SIGN,
           x(i).TREATMENT_DOCTOR,
           x(i).NUMBER_BEDS,
           x(i).INPATIENT_AREA,
           x(i).ORIGINAL_HOSPITAL_NO,
           x(i).SUPPLY_RESCUE_AMOUNT,
           x(i).MEDICAL_AMOUNT,
           x(i).YEAR_ACCOUNT_AMOUNT,
           x(i).COMPANY_CODE,
           x(i).COMPANY_NAME,
           x(i).created_date,
           x(i).batch_No,
           x(i).charge_flag,
           x(i).STATUS,
           x(i).PRESCRIBING_DOCTOR,
           x(i).SOCIAL_SECURITY_STAFF_FLAG);
    
      commit;
    end loop;
    close c_stg;
  end;
  • 分页查询的高效方式

修改前:

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM --取得rownum值
               (SELECT t.* FROM dept t ORDER BY t.deptno) a --查询整表的数据列,并按表某一个字段进行排序
         WHERE ROWNUM < 20) --对rownum大于20的进行过滤
WHERE rn > 2 --对rownum大于2的进行过滤

 

修改后:

SELECT t.*
  FROM dept t, --表连接,关联查询
       (SELECT rd
          FROM ( --取得所有的rowid
                SELECT rd, rn
                  FROM (SELECT ROWID rd, ROWNUM rn FROM dept ORDER BY deptno) --查询整表rowid和rownum,并按进行排序 
                 WHERE rn < 20) --过滤rownum大于20的数据
         WHERE rn > 2) t1 --过滤rownum小于2的数据
WHERE t.ROWID = t1.rd

 

数据转移常用方式:

create or replace procedure proc_transmit_trade(startDate in VARCHAR2,
                                                        endDate   in VARCHAR2) is
  v_count number;

  advance_info_row advance_info%rowtype;

begin
  v_count := 0;

  
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(startDate, 'yyyy-MM-dd')
                                  and rrr.deal_date <
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_status <> '1'
                                  and rrr.advance_type in ('1', '2')
                                  and not exists (select 1
                                         from advance_trade_info fati
                                        where fati.tran_serial_no =
                                              rrr.tran_serial_no)
                                  and not exists
                                (select 1
                                         from advance_trade_info fati
                                        where fati.id_advance_info =
                                              rrr.id_advance_info)) loop
    begin
      insert /*+append*/
      into advance_trade_info
        (ID_ADVANCE_INFO,
         TRAN_SERIAL_NO,
         FEEDBACK_TRAN_SERIAL_NO,
         DEAL_DATE,
         DEAL_STATUS,
         REGION_CODE,
         PC_NO,
         NAME,
         SEX,
         AGE,
         ID_NO,
         CARD_NO,
         VISIT_NO,
         VISIT_DATE,
         DOCTOR_ADVICE_NO,
         VISIT_TYPE,
         MEDICINE_TYPE,
         IN_HOSP_DATE,
         MEDICAL_ORG_CODE,
         MEDICAL_ORG_NAME,
         MEDICAL_DEPT_CODE,
         MEDICAL_DEPT_NAME,
         DOCTOR_CODE,
         DOCTOR_NAME,
         DEAL_WAY,
         REASON,
         REMARK)
      values
        (advance_info_row.ID_ADVANCE_INFO,
         nvl(advance_info_row.TRAN_SERIAL_NO, '0'),
         '0',
         nvl(advance_info_row.DEAL_DATE,
             to_date('2014-01-01', 'yyyy-mm-dd')),
         case when advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '2' then '1' when
         advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '3' then '2' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '2' then '3' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '3' then '4' ELSE '0' end,
         advance_info_row.REGION_CODE,
         nvl(advance_info_row.PC_NO, '0'),
         advance_info_row.NAME,
         advance_info_row.SEX,
         advance_info_row.AGE,
         advance_info_row.ID_NO,
         advance_info_row.CARD_NO,
         advance_info_row.VISIT_NO,
         advance_info_row.VISIT_DATE,
         advance_info_row.DOCTOR_ADVICE_NO,
         advance_info_row.VISIT_TYPE,
         advance_info_row.MEDICINE_TYPE,
         advance_info_row.IN_HOSP_DATE,
         nvl(advance_info_row.MEDICAL_ORG_CODE, '0'),
         nvl(advance_info_row.MEDICAL_ORG_NAME, '0'),
         nvl(advance_info_row.MEDICAL_DEPT_CODE, '0'),
         nvl(advance_info_row.MEDICAL_DEPT_NAME, '0'),
         nvl(advance_info_row.DOCTOR_CODE, '0'),
         nvl(advance_info_row.DOCTOR_NAME, '0'),
         advance_info_row.DEAL_WAY,
         advance_info_row.REASON,
         advance_info_row.REMARK);

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;

  
  v_count := 0;
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(startDate, 'yyyy-MM-dd')
                                  and rrr.deal_date <
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_status = '1'
                                  and not exists (select 1
                                         from advance_trade_exception fati
                                        where fati.tran_serial_no =
                                              rrr.tran_serial_no)
                                  and not exists
                                (select 1
                                         from advance_trade_exception fati
                                        where fati.id_advance_info =
                                              rrr.id_advance_info)) loop
    begin
      insert /*+append*/
      into advance_trade_exception
        (ID_ADVANCE_INFO,
         TRAN_SERIAL_NO,
         DEAL_DATE,
         DEAL_STATUS,
         REGION_CODE,
         PC_NO,
         NAME,
         SEX,
         AGE,
         ID_NO,
         CARD_NO,
         VISIT_NO,
         VISIT_DATE,
         DOCTOR_ADVICE_NO,
         VISIT_TYPE,
         MEDICINE_TYPE,
         IN_HOSP_DATE,
         MEDICAL_ORG_CODE,
         MEDICAL_ORG_NAME,
         MEDICAL_DEPT_CODE,
         MEDICAL_DEPT_NAME,
         DOCTOR_CODE,
         DOCTOR_NAME,
         DEAL_WAY,
         REASON,
         REMARK)
      values
        (advance_info_row.ID_ADVANCE_INFO,
         nvl(advance_info_row.TRAN_SERIAL_NO, '0'),
         nvl(advance_info_row.DEAL_DATE,
             to_date('2014-01-01', 'yyyy-mm-dd')),
         case when advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '1' then '1' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '1' then '2' ELSE '0' end,
         advance_info_row.REGION_CODE,
         nvl(advance_info_row.PC_NO, '0'),
         advance_info_row.NAME,
         advance_info_row.SEX,
         advance_info_row.AGE,
         advance_info_row.ID_NO,
         advance_info_row.CARD_NO,
         advance_info_row.VISIT_NO,
         advance_info_row.VISIT_DATE,
         advance_info_row.DOCTOR_ADVICE_NO,
         advance_info_row.VISIT_TYPE,
         advance_info_row.MEDICINE_TYPE,
         advance_info_row.IN_HOSP_DATE,
         nvl(advance_info_row.MEDICAL_ORG_CODE, '0'),
         advance_info_row.MEDICAL_ORG_NAME,
         advance_info_row.MEDICAL_DEPT_CODE,
         advance_info_row.MEDICAL_DEPT_NAME,
         advance_info_row.DOCTOR_CODE,
         advance_info_row.DOCTOR_NAME,
         advance_info_row.DEAL_WAY,
         advance_info_row.REASON,
         advance_info_row.REMARK);

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;


  v_count := 0;
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_type in ('1', '2')
                                  and rrr.advance_status <> '1'
                                  and not exists (select 1
                                         from advance_trade_info fati
                                        where fati.tran_serial_no =
                                              rrr.tran_serial_no)
                                  and not exists
                                (select 1
                                         from advance_trade_info fati
                                        where fati.id_advance_info =
                                              rrr.id_advance_info)) loop
    begin
      insert /*+append*/
      into advance_trade_info
        (ID_ADVANCE_INFO,
         TRAN_SERIAL_NO,
         FEEDBACK_TRAN_SERIAL_NO,
         DEAL_DATE,
         DEAL_STATUS,
         REGION_CODE,
         PC_NO,
         NAME,
         SEX,
         AGE,
         ID_NO,
         CARD_NO,
         VISIT_NO,
         VISIT_DATE,
         DOCTOR_ADVICE_NO,
         VISIT_TYPE,
         MEDICINE_TYPE,
         IN_HOSP_DATE,
         MEDICAL_ORG_CODE,
         MEDICAL_ORG_NAME,
         MEDICAL_DEPT_CODE,
         MEDICAL_DEPT_NAME,
         DOCTOR_CODE,
         DOCTOR_NAME,
         DEAL_WAY,
         REASON,
         REMARK)
      values
        (advance_info_row.ID_ADVANCE_INFO,
         nvl(advance_info_row.TRAN_SERIAL_NO, '0'),
         '0',
         nvl(advance_info_row.DEAL_DATE,
             to_date('2014-01-01', 'yyyy-mm-dd')),
         case when advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '2' then '1' when
         advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '3' then '2' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '2' then '3' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '3' then '4' ELSE '0' end,
         advance_info_row.REGION_CODE,
         nvl(advance_info_row.PC_NO, '0'),
         advance_info_row.NAME,
         advance_info_row.SEX,
         advance_info_row.AGE,
         advance_info_row.ID_NO,
         advance_info_row.CARD_NO,
         advance_info_row.VISIT_NO,
         advance_info_row.VISIT_DATE,
         advance_info_row.DOCTOR_ADVICE_NO,
         advance_info_row.VISIT_TYPE,
         advance_info_row.MEDICINE_TYPE,
         advance_info_row.IN_HOSP_DATE,
         nvl(advance_info_row.MEDICAL_ORG_CODE, '0'),
         nvl(advance_info_row.MEDICAL_ORG_NAME, '0'),
         nvl(advance_info_row.MEDICAL_DEPT_CODE, '0'),
         nvl(advance_info_row.MEDICAL_DEPT_NAME, '0'),
         nvl(advance_info_row.DOCTOR_CODE, '0'),
         nvl(advance_info_row.DOCTOR_NAME, '0'),
         advance_info_row.DEAL_WAY,
         advance_info_row.REASON,
         advance_info_row.REMARK);

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;


  v_count := 0;
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(startDate, 'yyyy-MM-dd')
                                  and rrr.deal_date <
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_status = '1'
                                  and not exists (select 1
                                         from advance_trade_exception fati
                                        where fati.tran_serial_no =
                                              rrr.tran_serial_no)
                                  and not exists
                                (select 1
                                         from advance_trade_exception fati
                                        where fati.id_advance_info =
                                              rrr.id_advance_info)) loop
    begin
      insert /*+append*/
      into advance_trade_exception
        (ID_ADVANCE_INFO,
         TRAN_SERIAL_NO,
         DEAL_DATE,
         DEAL_STATUS,
         REGION_CODE,
         PC_NO,
         NAME,
         SEX,
         AGE,
         ID_NO,
         CARD_NO,
         VISIT_NO,
         VISIT_DATE,
         DOCTOR_ADVICE_NO,
         VISIT_TYPE,
         MEDICINE_TYPE,
         IN_HOSP_DATE,
         MEDICAL_ORG_CODE,
         MEDICAL_ORG_NAME,
         MEDICAL_DEPT_CODE,
         MEDICAL_DEPT_NAME,
         DOCTOR_CODE,
         DOCTOR_NAME,
         DEAL_WAY,
         REASON,
         REMARK)
      values
        (advance_info_row.ID_ADVANCE_INFO,
         nvl(advance_info_row.TRAN_SERIAL_NO, '0'),
         nvl(advance_info_row.DEAL_DATE,
             to_date('2014-01-01', 'yyyy-mm-dd')),
         case when advance_info_row.advance_type = '1' and
         advance_info_row.advance_status = '1' then '1' when
         advance_info_row.advance_type = '2' and
         advance_info_row.advance_status = '1' then '2' ELSE '0' end,
         advance_info_row.REGION_CODE,
         nvl(advance_info_row.PC_NO, '0'),
         advance_info_row.NAME,
         advance_info_row.SEX,
         advance_info_row.AGE,
         advance_info_row.ID_NO,
         advance_info_row.CARD_NO,
         advance_info_row.VISIT_NO,
         advance_info_row.VISIT_DATE,
         advance_info_row.DOCTOR_ADVICE_NO,
         advance_info_row.VISIT_TYPE,
         advance_info_row.MEDICINE_TYPE,
         advance_info_row.IN_HOSP_DATE,
         nvl(advance_info_row.MEDICAL_ORG_CODE, '0'),
         advance_info_row.MEDICAL_ORG_NAME,
         advance_info_row.MEDICAL_DEPT_CODE,
         advance_info_row.MEDICAL_DEPT_NAME,
         advance_info_row.DOCTOR_CODE,
         advance_info_row.DOCTOR_NAME,
         advance_info_row.DEAL_WAY,
         advance_info_row.REASON,
         advance_info_row.REMARK);

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;

  v_count := 0;
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(startDate, 'yyyy-MM-dd')
                                  and rrr.deal_date <
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_type = '4') loop
    begin

      update advance_trade_info
         set FEEDBACK_TRAN_SERIAL_NO = advance_info_row.TRAN_SERIAL_NO,
             REASON                  = advance_info_row.reason,
             REMARK                  = advance_info_row.remark
       where TRAN_SERIAL_NO = advance_info_row.AUDIT_TRAN_SERIAL_NO;

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;

  --更新结果表处理时间
  v_count := 0;
  for advance_info_row in (select ID_ADVANCE_INFO,
                                      TRAN_SERIAL_NO,
                                      REGION_CODE,
                                      AUDIT_TRAN_SERIAL_NO,
                                      PC_NO,
                                      NAME,
                                      SEX,
                                      AGE,
                                      ID_NO,
                                      CARD_NO,
                                      VISIT_NO,
                                      VISIT_DATE,
                                      DOCTOR_ADVICE_NO,
                                      VISIT_TYPE,
                                      MEDICINE_TYPE,
                                      IN_HOSP_DATE,
                                      MEDICAL_ORG_CODE,
                                      MEDICAL_ORG_NAME,
                                      MEDICAL_DEPT_CODE,
                                      MEDICAL_DEPT_NAME,
                                      DOCTOR_CODE,
                                      DOCTOR_NAME,
                                      DEAL_WAY,
                                      REASON,
                                      REMARK,
                                      ADVANCE_STATUS,
                                      ADVANCE_TYPE,
                                      DEAL_DATE
                                 from advance_info rrr
                                where rrr.deal_date >=
                                      to_date(startDate, 'yyyy-MM-dd')
                                  and rrr.deal_date <
                                      to_date(endDate, 'yyyy-MM-dd')
                                  and rrr.advance_type in ('1', '2')) loop
    begin

      update fwa_advance_result a
         set deal_date         = advance_info_row.deal_date,
             a.id_advance_info = advance_info_row.id_advance_info
       where TRAN_SERIAL_NO = advance_info_row.TRAN_SERIAL_NO;

      update fwa_advance_audit_diagnose a
         set deal_date         = advance_info_row.deal_date,
             a.id_advance_info = advance_info_row.id_advance_info
       where TRAN_SERIAL_NO = advance_info_row.TRAN_SERIAL_NO;

      update FWA_ADVANCE_AUDIT_DETAIL a
         set deal_date         = advance_info_row.deal_date,
             a.id_advance_info = advance_info_row.id_advance_info
       where TRAN_SERIAL_NO = advance_info_row.TRAN_SERIAL_NO;

      v_count := v_count + 1;
      if v_count > 10000 then
        commit;
        v_count := 0;
      end if;
    end;
  end loop;
  commit;

end proc_transmit_trade;

 

 

© 著作权归作者所有

共有 人打赏支持
剑尊
粉丝 1
博文 42
码字总数 33741
作品 0
浦东
技术主管
Tungsten Replicator 2.1 发布,数据库复制引擎

Tungsten Replicator 2.1 发布,该版本支持 MySQL 和 Oracle 数据库之间的复制,同时支持 Oracle 到 Oracle 的复制。 主要改进特性包括: 提供低影响、实时的复制,超过 MySQL 本身吞吐量的 ...

oschina
2013/04/24
1K
3
ORACLE数据库数据文件转移方法 

ORACLE数据库数据文件转移方法 Oracle数据文件转移方法: 1) 手动拷贝要转移的数据数据文件'/opt/oracle/oradata/orcl/xxx.dbf'到新的位置'/opt/oracle/oradata/orcl2/xxx.dbf'。 新CP的数据...

李狗蛋丶
2016/11/03
1
0
『学概念找员外』预言机之应用(二)

欧链-服务层 OracleChain 服务层提供 Oracle 实例平台、状态通道和服务 API 等对外服务。这样,OracleChain 就能实现了对 Oracle 服务的撮合、计费等对外服务,展现它的价值,真正实现了 Or...

刘员外__
06/06
0
0
oracle简单运维

oracle简单运维 天使羊波波闪耀光芒2018-01-030 阅读 ORACLE oracle数据库磁盘满了,常用的处理方法: 1)清空无用的大表 2)删除日志 3)转移表空间文件 一、准备工作 0、切换到oracle用户 ...

天使羊波波闪耀光芒
01/03
0
0
Oracle数据转移Mysql中遇到的问题以及解决办法

前几天因工作需要我得将Oracle数据库中几十张表转移到Mysql中,由于之前没有相关经验,上网简单百度后选用Navcat Premium软件工作。 Navcat Premium进行数据转移有三种办法:1、复制表 2、导...

littleant2
2015/07/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Shell | linux安装包不用选择Y/N的方法

apt-get install -y packageOR echo "y" | sudo apt-get install package

云迹
3分钟前
0
0
Hadoop的大数据生态圈

基于Hadoop的大数据的产品圈 大数据产品的一句话概括 Apache Hadoop: 是Apache开源组织的一个分布式计算开源框架,提供了一个分布式文件系统子项目(HDFS)和支持MapReduce分布式计算的软件架...

zimingforever
18分钟前
1
0
八大包装类型的equals方法

先看其中一个源码 结论:八大包装类型的equals方法都是先判断类型是否相同,不相同则是false,相同则判断值是否相等 注意:包装类型不能直接用==来等值比较,否则编译报错,但是数值的基本类型...

xuklc
42分钟前
1
0
NoSQL , Memcached介绍

什么是NoSQL 非关系型数据库就是NoSQL,关系型数据库代表MySQL 对于关系型数据库来说,是需要把数据存储到库、表、行、字段里,查询的时候根据条件一行一行地去匹配,当量非常大的时候就很耗...

TaoXu
昨天
0
0
890. Find and Replace Pattern - LeetCode

Question 890. Find and Replace Pattern Solution 题目大意:从字符串数组中找到类型匹配的如xyy,xxx 思路: 举例:words = ["abc","deq","mee","aqq","dkd","ccc"], pattern = "abb"abc ......

yysue
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部