第一步：创建包接口类似java中的接口定义
create or replace PACKAGE pack_sc_hmd_kh IS --方法的声明 PROCEDURE proc_hmd_kh( p_y VARCHAR2, p_m VARCHAR2, p_d VARCHAR2); END pack_sc_hmd_kh;
第二步：常见包体又称包的实现类似于java中实现接口的具体类
create or replacePACKAGE body pack_sc_hmd_khISPROCEDURE proc_hmd_kh(p_y IN VARCHAR2 ,p_m IN VARCHAR2 ,p_d IN VARCHAR2)ISCURSOR cis_sc_hmd_kh_cursorISSELECTd_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_nameFROMd_sems_rel_d_ddpa_appl_listWHEREfact_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;BEGINpack2_gg.proc_add_list_partition('cis_sc_hmd_kh', p_y || p_m || p_d);COMMIT;FOR v_row IN cis_sc_hmd_kh_cursorLOOPv_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';INSERTINTOcis_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 THENCOMMIT;v_cnt := 0;END IF;END LOOP;COMMIT;END proc_hmd_kh;BEGINNULL;END pack_sc_hmd_kh;