oracle package实现同步

--please switch to app_ops_task schema
-- create package declaration
create or replace package invoice_check_package AS
    procedure generate_data_from_dwh;
    procedure generate_invoice_from_dwh;
    procedure generate_contract_his_from_dwh;
    procedure clean_invoice_dwh_data;
    function get_purpose_code(v_str varchar2) return varchar2;
end invoice_check_package;


--create package body
    CREATE OR REPLACE PACKAGE BODY invoice_check_package AS
    procedure generate_contract_his_from_dwh as
        CURSOR CUR_DWH_INVOICE IS
            SELECT T.CONTRACT_UUID,
                   T.CONTRACT_NUMBER,
                   T.INVOICE_NUMBER,
                   T.INVOICE_AMOUNT,
                   T.INVOICE_RESULT
            FROM OWNER_HUB.TEMP_DPDCN_1250 T;
        INFO_ROLE CUR_DWH_INVOICE%ROWTYPE;
    begin
        OPEN CUR_DWH_INVOICE;
        LOOP
            begin
                FETCH CUR_DWH_INVOICE
                    INTO INFO_ROLE;
                EXIT WHEN CUR_DWH_INVOICE%notfound;
                INSERT INTO T_H_CONTRACT_OPERATION
                (ID,
                 CONTRACT_UUID,
                 CONTRACT_NUMBER,
                 EVENT_CODE,
                 DWH_CONTRACT_CALCULATE_STATUS,
                 CREATOR,
                 CDATE,
                 EDITOR,
                 EDATE)
                VALUES
                (SEQ_CONTRACT_OPERATION.NEXTVAL,
                 LOWER(INFO_ROLE.CONTRACT_UUID),
                 INFO_ROLE.CONTRACT_NUMBER,
                 '2',
                 '0',
                 'System',
                 SYSDATE,
                 'System',
                 SYSDATE);
            EXCEPTION
                WHEN OTHERS THEN
                    CONTINUE;
            END;
        END LOOP;
        CLOSE CUR_DWH_INVOICE;

        DELETE FROM T_H_CONTRACT_OPERATION d1
        where d1.EVENT_CODE = '2'
          and d1.ROWID <
              (select MAX(ROWID)
               from T_H_CONTRACT_OPERATION d2
               where d1.CONTRACT_UUID = d2.CONTRACT_UUID);
        COMMIT;
    end generate_contract_his_from_dwh;

    procedure generate_invoice_from_dwh as
        CURSOR CUR_DWH_INVOICE IS
            SELECT T.CONTRACT_UUID,
                   T.CONTRACT_NUMBER,
                   T.INVOICE_NUMBER,
                   T.INVOICE_AMOUNT,
                   T.INVOICE_RESULT,
                   T.ANSWER_CL4_1,
                   T.Answer_Comments
            FROM OWNER_HUB.TEMP_DPDCN_1250 T;
        INFO_ROLE CUR_DWH_INVOICE%ROWTYPE;
    begin
        OPEN cur_dwh_invoice;
        LOOP
            begin
                FETCH CUR_DWH_INVOICE
                    INTO INFO_ROLE;
                exit when CUR_DWH_INVOICE%notfound;
                INSERT INTO T_INVOICE_INFO
                (ID,
                 CONTRACT_UUID,
                 CABINET_UUID,
                 DWH_INVOICE_NUMBER,
                 UPLOAD_CHANNEL,
                 UPLOAD_TIME,
                 CL_INVOICE_AMOUNT,
                 INVOICE_RESULT,
                 cl_invoice_real_purpose,
                 CL_REMARK,
                 CREATOR,
                 CDATE,
                 EDITOR,
                 EDATE)
                VALUES
                (SEQ_INVOICE_INFO.NEXTVAL,
                 LOWER(INFO_ROLE.CONTRACT_UUID),
                 rawtohex(sys_guid()),
                 INFO_ROLE.INVOICE_NUMBER,
                 'DWH',
                 SYSDATE,
                 INFO_ROLE.INVOICE_AMOUNT,
                 INFO_ROLE.INVOICE_RESULT,
                 get_purpose_code(INFO_ROLE.ANSWER_CL4_1 || INFO_ROLE.Answer_Comments),
                 INFO_ROLE.Answer_Comments,
                 'System',
                 SYSDATE,
                 'System',
                 SYSDATE);
            EXCEPTION
                WHEN OTHERS THEN
                    begin
                        insert into T_INVOICE_DATA_DWH_ERROR_LOG
                        (ID,
                         CONTRACT_UUID,
                         Contract_Number,
                         INVOICE_NUMBER,
                         INVOICE_AMOUNT,
                         Invoice_Result)
                        values
                        (SEQ_INVOICE_DATA_DWH_ERROR_LOG.NEXTVAL,
                         INFO_ROLE.CONTRACT_UUID,
                         INFO_ROLE.Contract_Number,
                         INFO_ROLE.INVOICE_NUMBER,
                         INFO_ROLE.Invoice_Amount,
                         INFO_ROLE.Invoice_Result);
                    EXCEPTION
                        WHEN OTHERS THEN
                            CONTINUE;
                    end;
                    CONTINUE;
            END;
        END LOOP;
        CLOSE CUR_DWH_INVOICE;
        COMMIT;
    end generate_invoice_from_dwh;

    procedure generate_data_from_dwh as
    begin
        begin
            invoice_check_package.clean_invoice_dwh_data;
            invoice_check_package.generate_invoice_from_dwh;
            invoice_check_package.generate_contract_his_from_dwh;
        end;
    end generate_data_from_dwh;

    procedure clean_invoice_dwh_data as
    begin
        begin
            delete from T_H_CONTRACT_OPERATION ts
            where EVENT_CODE = '2'
              and ts.CDATE >= SYSDATE - 10;
            delete from T_INVOICE_INFO ts
            where lower(ts.UPLOAD_CHANNEL) = lower('DWH')
              and ts.CDATE >= SYSDATE - 10;
            commit;
        end;
    end clean_invoice_dwh_data;

    function get_purpose_code(v_str VARCHAR2) return varchar2 is
        v_retrun VARCHAR2(1000);
    BEGIN
        IF v_str IS NULL THEN
            RETURN NULL;
        ELSE
            BEGIN
                SELECT CASE
                           WHEN v_str IS NULL THEN
                               NULL
                           ELSE
                                   (CASE
                                        WHEN instr(v_str, '日用品(食品、洗护用品等)') >= 1 THEN
                                            'dc,' --'日用品(食品、洗护用品等),'
                                        ELSE
                                            ''
                                       END) || (CASE
                                                    WHEN instr(v_str, '家庭装修') >= 1 THEN
                                                        'f1,' --'家庭装修,'
                                                    ELSE
                                                        ''
                                   END) || (CASE
                                                WHEN instr(v_str, '家用电器') >= 1 THEN
                                                    'ha,' -- '家用电器,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '手机数码') >= 1 THEN
                                                    'ce,' --'手机数码,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '旅游度假') >= 1 THEN
                                                    'd,' --'旅游度假,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '教育培训') >= 1 THEN
                                                    't,' ---'教育培训,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '家具家居') >= 1 THEN
                                                    'f2,' --'家具家居,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '服饰箱包') >= 1 THEN
                                                    'p4,' ---'服饰箱包,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '摩托车/电动自行车/三轮车') >= 1 THEN
                                                    's1,' ---'摩托车/电动自行车/三轮车,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '医疗美容') >= 1 THEN
                                                    'h,' --- '医疗美容,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '租房自住') >= 1 THEN
                                                    'ra,' --- '租房自住,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '红白喜事') >= 1 THEN
                                                    'w,' --- '红白喜事,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '母婴用品/玩具乐器') >= 1 THEN
                                                    'bt,' --'母婴用品/玩具乐器,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '运动户外') >= 1 THEN
                                                    'so,' ---'运动户外,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '车类服务') >= 1 THEN
                                                    'cr,' --'车类服务,'
                                                ELSE
                                                    ''
                                   END) || (CASE
                                                WHEN instr(v_str, '休闲娱乐') >= 1 THEN
                                                    'le,' --- '休闲娱乐,'
                                   END)
                           END
                       INTO v_retrun
                FROM dual;

                SELECT TRIM(',' FROM v_retrun) INTO v_retrun FROM dual;
            EXCEPTION
                WHEN invalid_number THEN
                    RETURN 0;
            END;
            RETURN v_retrun;
        END IF;
    end;

end invoice_check_package;

-- please switch to app_ops_task schema
-- Call the procedure， data range [100w,200w],uat call this, need  about 10-12 min,only need call one time
--       begin
--         invoice_check_package.generate_data_from_dwh;
--       end;