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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.