It Ninja Turtles Oracle stored procedures simply extract data from ODS _ object-oriented thinking

First step: Create a package interface similar to the interface definition in Java

Create or Replace package Pack_sc_hmd_khis--declaration of methods  PROCEDURE Proc_hmd_kh (      p_y VARCHAR2,      p_m VARCHAR2,      P_d VARCHAR2); END Pack_sc_hmd_kh;

The second step: the common package is also known as the implementation of the package is similar to Java implementation of the interface of the specific class

Package Body Pack_sc_hmd_kh

Is

PROCEDURE Proc_hmd_kh (

    P_y in VARCHAR2,

    P_m in VARCHAR2,

    P_d in VARCHAR2)

Is

  CURSOR Cis_sc_hmd_kh_cursor

  Is

    SELECT

      D_sems_rel_d_ddpa_appl_list.fact_date,

      D_SEMS_REL_D_DDPA_APPL_LIST.APPLY_ID,

      D_sems_rel_d_ddpa_appl_list.marketing_org,

      D_sems_rel_d_ddpa_appl_list.open_org,

      D_sems_rel_d_ddpa_appl_list.open_branch,

      D_sems_rel_d_ddpa_appl_list.open_area,

      D_sems_rel_d_ddpa_appl_list.cust_mgr,

      D_sems_rel_d_ddpa_appl_list.channel_type,

      D_SEMS_REL_D_DDPA_APPL_LIST.CUST_ID,

      D_sems_rel_d_ddpa_appl_list.cert_no,

      D_sems_rel_d_ddpa_appl_list.cust_name,

      D_sems_rel_d_ddpa_appl_list.birthday,

      D_sems_rel_d_ddpa_appl_list.edu_grade,

      D_sems_rel_d_ddpa_appl_list.industry_name,

      D_sems_rel_d_ddpa_appl_list.bussiness_type,

      D_SEMS_REL_D_DDPA_APPL_LIST.SCHEME_ID,

      D_sems_rel_d_ddpa_appl_list.project_name,

      D_sems_rel_d_ddpa_appl_list.apply_amount,

      D_sems_rel_d_ddpa_appl_list.apply_date,

      D_sems_rel_d_ddpa_appl_list.term,

      D_sems_rel_d_ddpa_appl_list.loan_purpose,

      D_sems_rel_d_ddpa_appl_list.guaranty_type,

      D_sems_rel_d_ddpa_appl_list.return_card_no,

      D_sems_rel_d_ddpa_appl_list.final_approve_date,

      D_sems_rel_d_ddpa_appl_list.final_approve_user,

      D_sems_rel_d_ddpa_appl_list.final_approve_result,

      D_sems_rel_d_ddpa_appl_list.credit_result,

      D_sems_rel_d_ddpa_appl_list.comprehensive_mark,

      D_sems_rel_d_ddpa_appl_list.quarlity_check_time,

      D_sems_rel_d_ddpa_appl_list.credit_time,

      D_sems_rel_d_ddpa_appl_list.approve_time,

      D_sems_rel_d_ddpa_appl_list.return_num,

      D_sems_rel_d_ddpa_appl_list.return_time,

      D_sems_rel_d_ddpa_appl_list.contract_no,

      D_sems_rel_d_ddpa_appl_list.inputchannel,

      D_sems_rel_d_ddpa_appl_list.contract_amount,

      D_sems_rel_d_ddpa_appl_list.inputchannel_name

    From

      D_sems_rel_d_ddpa_appl_list

    WHERE

      Fact_date=to_date (p_y

      || '-'

      || P_m

      || '-'

      || P_d, ' yyyy-mm-dd ');

  V_fact_date Cis_sc_hmd_kh.fact_date%type;

  V_APPLY_ID Cis_sc_hmd_kh.apply_id%type;

  V_marketing_org Cis_sc_hmd_kh.marketing_org%type;

  V_open_org Cis_sc_hmd_kh.open_org%type;

  V_open_branch Cis_sc_hmd_kh.open_branch%type;

  V_open_area Cis_sc_hmd_kh.open_area%type;

  V_cust_mgr Cis_sc_hmd_kh.cust_mgr%type;

  V_channel_type Cis_sc_hmd_kh.channel_type%type;

  V_CUST_ID Cis_sc_hmd_kh.cust_id%type;

  V_cert_no Cis_sc_hmd_kh.cert_no%type;

  V_cust_name Cis_sc_hmd_kh.cust_name%type;

  V_birthday Cis_sc_hmd_kh.birthday%type;

  V_edu_grade Cis_sc_hmd_kh.edu_grade%type;

  V_industry_name Cis_sc_hmd_kh.industry_name%type;

  V_bussiness_type Cis_sc_hmd_kh.bussiness_type%type;

  V_SCHEME_ID Cis_sc_hmd_kh.scheme_id%type;

  V_project_name Cis_sc_hmd_kh.project_name%type;

  V_apply_amount Cis_sc_hmd_kh.apply_amount%type;

  V_apply_date Cis_sc_hmd_kh.apply_date%type;

  V_term Cis_sc_hmd_kh.term%type;

  V_loan_purpose Cis_sc_hmd_kh.loan_purpose%type;

  V_guaranty_type Cis_sc_hmd_kh.guaranty_type%type;

  V_return_card_no Cis_sc_hmd_kh.return_card_no%type;

  V_final_approve_date Cis_sc_hmd_kh.final_approve_date%type;

  V_final_approve_user Cis_sc_hmd_kh.final_approve_user%type;

  V_final_approve_result Cis_sc_hmd_kh.final_approve_result%type;

  V_credit_result Cis_sc_hmd_kh.credit_result%type;

  V_comprehensive_mark Cis_sc_hmd_kh.comprehensive_mark%type;

  V_quarlity_check_time Cis_sc_hmd_kh.quarlity_check_time%type;

  V_credit_time Cis_sc_hmd_kh.credit_time%type;

  V_approve_time Cis_sc_hmd_kh.approve_time%type;

  V_return_num Cis_sc_hmd_kh.return_num%type;

  V_return_time Cis_sc_hmd_kh.return_time%type;

  V_contract_no Cis_sc_hmd_kh.contract_no%type;

  V_inputchannel Cis_sc_hmd_kh.inputchannel%type;

  V_contract_amount Cis_sc_hmd_kh.contract_amount%type;

  V_inputchannel_name Cis_sc_hmd_kh.inputchannel_name%type;

  V_CUST_NAME_PY_QP Cis_sc_hmd_kh. Cust_name_py_qp%type;

  V_CUST_NAME_PY_JP Cis_sc_hmd_kh. Cust_name_py_jp%type;

  V_filed1 Cis_sc_hmd_kh. Filed1%type;

  V_filed2 Cis_sc_hmd_kh. Filed2%type;

  V_spyj Cis_sc_hmd_kh. Spyj%type;

  V_CNT number;

BEGIN

  Pack2_gg.proc_add_list_partition (' Cis_sc_hmd_kh ', p_y | | p_m | | p_d);

  COMMIT;

  For V_row in Cis_sc_hmd_kh_cursor

  LOOP

    V_fact_date           : = v_row.fact_date;

    v_apply_id            : =v_row.apply_id;

    v_marketing_org       : =v_row.marketing_org;

    v_open_org            : =v_row.open_org;

    V_open_branch         : = V_row.open_branch;

    V_open_area           : = V_row.open_area;

    V_cust_mgr            : = v_row.cust_mgr;

    V_channel_type        : = V_row.channel_type;

    v_cust_id             : =v_row.cust_id;

    V_cert_no             : = V_row.cert_no;

    V_cust_name           : =v_row.cust_name;

    V_birthday            : =v_row.birthday;

    V_edu_grade           : =v_row.edu_grade;

    V_industry_name       : =v_row.industry_name;

    V_bussiness_type      : =v_row.bussiness_type;

    v_scheme_id           : =v_row.scheme_id;

    V_project_name        : =v_row.project_name;

    V_apply_amount        : =v_row.apply_amount;

    V_apply_date          : =v_row.apply_date;

    V_term                : =v_row.term;

    V_loan_purpose        : =v_row.loan_purpose;

    V_guaranty_type       : = V_row.guaranty_type;

    V_return_card_no      : =v_row.return_card_no;

    V_final_approve_date  : = v_row.final_approve_date;

    V_final_approve_user  : =v_row.final_approve_user;

    V_final_approve_result:= V_row.final_approve_result;

    V_credit_result       : =v_row.credit_result;

    V_comprehensive_mark  : =v_row.comprehensive_mark;

    V_quarlity_check_time: = V_row.quarlity_check_time;

    V_credit_time         : =v_row.credit_time;

    V_approve_time        : =v_row.approve_time;

    V_return_num          : =v_row.return_num;

    V_return_time         : =v_row.return_time;

    V_contract_no         : =v_row.contract_no;

    V_inputchannel        : =v_row.inputchannel;

    V_contract_amount     : =v_row.contract_amount;

    V_inputchannel_name   : =v_row.inputchannel_name;

    V_CUST_NAME_PY_QP     : =f_getspell (V_row. cust_name,1);

    V_CUST_NAME_PY_JP     : =f_getspell (V_row. Cust_name);

    V_spyj                : = ' hhhh ';

    INSERT

    Into

      Cis_sc_hmd_kh

      (

        Fact_date,

        APPLY_ID,

        Marketing_org,

        Open_org,

        Open_branch,

        Open_area,

        Cust_mgr,

        Channel_type,

        cust_id,

        Cert_no,

        Cust_name,

        Birthday

        Edu_grade,

        Industry_name,

        Bussiness_type,

        SCHEME_ID,

        Project_Name,

        Apply_amount,

        Apply_date,

        Term

        Loan_purpose,

        Guaranty_type,

        Return_card_no,

        Final_approve_date,

        Final_approve_user,

        Final_approve_result,

        Credit_result,

        Comprehensive_mark,

        Quarlity_check_time,

        Credit_time,

        Approve_time,

        Return_num,

        Return_time,

        Contract_no,

        Contract_amount,

        Inputchannel,

        Inputchannel_name,

        CUST_NAME_PY_QP,

        CUST_NAME_PY_JP,

        Filed1,

        Filed2,

        Spyj

      )

      VALUES

      (

        V_fact_date,

        V_APPLY_ID,

        V_marketing_org,

        V_open_org,

        V_open_branch,

        V_open_area,

        V_cust_mgr,

        V_channel_type,

        V_CUST_ID,

        V_cert_no,

        V_cust_name,

        V_birthday,

        V_edu_grade,

        V_industry_name,

        V_bussiness_type,

        V_SCHEME_ID,

        V_project_name,

        V_apply_amount,

        V_apply_date,

        V_term,

        V_loan_purpose,

        V_guaranty_type,

        V_return_card_no,

        V_final_approve_date,

        V_final_approve_user,

        V_final_approve_result,

        V_credit_result,

        V_comprehensive_mark,

        V_quarlity_check_time,

        V_credit_time,

        V_approve_time,

        V_return_num,

        V_return_time,

        V_contract_no,

        V_inputchannel,

        V_contract_amount,

        V_inputchannel_name,

        V_CUST_NAME_PY_QP,

        V_CUST_NAME_PY_JP,

        '',

        '',

        V_spyj

      );

    v_cnt   : = v_cnt + 1;

    IF v_cnt = 10000 Then

      COMMIT;

      V_CNT: = 0;

    END IF;

  END LOOP;

  COMMIT;

END Proc_hmd_kh;

BEGIN

  NULL;

END Pack_sc_hmd_kh;

It Ninja Turtles Oracle stored procedures simply extract data from ODS _ object-oriented thinking