数据转移高效方法(oracle)
博客专区 > 剑尊 的博客 > 博客详情
数据转移高效方法(oracle)
剑尊 发表于11个月前
数据转移高效方法(oracle)
  • 发表于 11个月前
  • 阅读 9
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

  • 数据转移高效方式(从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
博文 17
码字总数 33549
×
剑尊
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: