IT忍者神龟之oracle存储过程——面向对象编程，

IT忍者神龟之oracle存储过程——面向对象编程，oracle存储过程

第一步：创建包接口类似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 replace 

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;

create or replace procedure proc_test( strAge in string, strName in string, ret_code out string, v_error_message out string) is
begin
declare
strsql varcchar(1024);
v_error_desc varcchar(1024);
begin
v_error_desc :='student';
strsql :='select name from student where age=100';
execute immediate strsql using strAge ;

v_error_desc :='teacher';
strsql :='select age from teacher where name=jill';
execute immediate strsql using strName ;

ret_code:=0;
v_error_desc :='OK';
v_error_message:='OK';
EXCEPTION
WHEN OTHERS THEN
ret_code := sqlcode;
v_error_desc := '错误位置:' || v_error_desc;
v_error_message := sqlerrm;
rollback;

end;
end proc_cancel_digital_id;
 

直接调用即可，例如：
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //存储过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
runbyparmeters(realsal,realname,realjob); －－调用存储，参数必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束