IT忍者神龟之oracle存储过程简单从ODS抽取数据_面向对象思想
来源:互联网 发布:oimo.js 编辑:程序博客网 时间:2024/05/22 04:24
第一步:创建包接口类似java中的接口定义
create or replace PACKAGE pack_sc_hmd_khIS --方法的声明 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;
0 0
- IT忍者神龟之oracle存储过程简单从ODS抽取数据_面向对象思想
- IT忍者神龟之oracle存储过程——面向对象编程
- IT忍者神龟之oracle存储过程中is和as区别描述
- IT忍者神龟之理解回顾面向对象的 JavaScript
- Oracle抽取数据的存储过程
- oracle 增量抽取数据存储过程
- IT忍者神龟之oracle常用日期计算
- IT忍者神龟之oracle高级CRUD
- IT忍者神龟之 oracle行转列、列转行
- IT忍者神龟之oracle 集合的使用
- IT忍者神龟之Oracle查询树形结构
- IT忍者神龟之MySQL数据库简单操作
- IT忍者神龟之windows7安装oracle 10g安装过程及注意事项
- Oracle存储过程中的面向对象特性
- 解读数据存储ODS
- ODS(操作数据存储)
- IT忍者神龟之Oracle 的数据导入导出及 Sql Loader (sqlldr) 的用法
- ODS系统基于存储过程实现数据清洗实例
- delphi RichEdit的内容保存为图片
- Android自定义控件之自定义tabhost
- Mina、Netty、Twisted一起学(七):发布/订阅(Publish/Subscribe)
- 第十六章 16.1.2节练习
- Mina、Netty、Twisted一起学(七):发布/订阅(Publish/Subscribe)
- IT忍者神龟之oracle存储过程简单从ODS抽取数据_面向对象思想
- Mina、Netty、Twisted一起学(七):发布/订阅(Publish/Subscribe)
- swfobject 转载
- sim卡数据交互流程数据
- 3D打印教材的出现更进一步推动3D打印进校园的步伐
- 看完这六个问题,你还敢再迷茫吗?
- discuz updatemembercount,notification_add 解析
- 关于git的错误
- C#调用C++生成的Dll调试异常的问题