包中创建存储过程

在包中创建存储过程的语句是
CREATE OR REPLACE PACKAGE PackageName AS
         --Insert Roles Declaration---
         PROCEDURE procedurename(
         );
        
END PackageName ;

注意：写存储过程时要注意参数名不能与数据库 字段名相同 . 否则 Oracle 会把这个参数名看成是字段名的 , 即使你用表的别名区分也不行 . 所以起参数名的时候一定要注意这点了 .

e.g:在oracle存储过程中返回一个结果集

包
create or replace package XX_ECIF_VIP_PKG is

   -- Author   : HUAWANG
   -- Created : 2007-11-1 14:33:38
   -- Purpose :
   g_vip_info_source_type_etl   constant varchar2(30) := 'ETL';
   g_vip_info_sourec_type_card constant varchar2(30) := 'CARD';
   g_vip_count_mode_zc          constant varchar2(10) := '10';
   g_vip_count_mode_dk          constant varchar2(10) := '20';
   g_credit_rating_cd_normal    constant NUMBER(5) := 0;
   g_currency_cd                constant varchar2(3) := 'CNY';
   g_vip_mode_count_status_a    constant varchar2(1) := 'A';
   -- Public procedure declarations
   procedure CHECK_VIP_PRC;
   procedure insert_vip_prc(v_party_id in number, v_vip_code in varchar2);  

end XX_ECIF_VIP_PKG;

包体：create or replace package body XX_ECIF_VIP_PKG is

   -- procedure implementations
   procedure CHECK_VIP_PRC as
   begin
     insert into XX_ECIF_VIP_INFO
       (PARTY_ID, VIP_CODE, AS_OF_DATE, EFFECTIVE_DATE, SOURCE_TYPE)
       select uv.PARTY_ID                 PARTY_ID,
              uv.VIP_CODE                 VIP_CODE,
              uv.AS_OF_DATE               AS_OF_DATE,
              sysdate                     EFFECTIVE_DATE,
              g_vip_info_source_type_etl SOURCE_TYPE
         from (
               --取日个人客户资产总额对应VIP等级、贷款总额对应VIP等级、卡系统个人客户对应VIP等级中最大的
               select u.PARTY_ID PARTY_ID,
                       max(u.VIP_CODE) VIP_CODE,
                       max(u.AS_OF_DATE) AS_OF_DATE
                 From (
                        /*                                             */
                        --日个人客户贷款总额对应VIP等级
                        select clv.PARTY_ID    PARTY_ID,
                                xevcc.vip_code VIP_CODE,
                                clv.AS_OF_DATE AS_OF_DATE
                          from (select xalcny.PARTY_ID PARTY_ID,
                                        sum(xalcny.ORG_BOOK_BAL *
                                            NVL(oerh.EXCHANGE_RATE, 1)) CUR_AMOUNT_BAL,
                                        max(xalcny.AS_OF_DATE) AS_OF_DATE
                                   from (select xal.PARTY_ID,
                                                xal.ORG_BOOK_BAL,
                                                xal.AS_OF_DATE,
                                                CURRENCY
                                           from (select PARTY_ID,
                                                        PROD_CODE,
                                                        AS_OF_DATE,
                                                        ORG_BOOK_BAL,
                                                        CUR_BOOK_BAL,
                                                        CREDIT_RATING_CD,
                                                        CURRENCY,
                                                        row_number() over(partition by PARTY_ID, PROD_CODE order by AS_OF_DATE desc) as row_number
                                                   from XX_ACCTINFO_LOAN) xal
                                           join XX_ECIF_VIP_STAT_MODE vsm on xal.PROD_CODE =
                                                                             vsm.PROD_CD
                                          where xal.row_number = 1
                                            and xal.CREDIT_RATING_CD =
                                                g_credit_rating_cd_normal
                                            and xal.CUR_BOOK_BAL > 0
                                            and vsm.COUNT_MODE = g_vip_count_mode_dk
                                            and vsm.STATUS =
                                                g_vip_mode_count_status_a) xalcny
                                   left join (select FROM_CURRENCY_CD,
                                                    EXCHANGE_RATE,
                                                    row_number() over(partition by FROM_CURRENCY_CD, TO_CURRENCY_CD order by EFFECTIVE_DATE desc) as row_number
                                               from OFSA_EXCHANGE_RATE_HIST
                                              where TO_CURRENCY_CD = g_currency_cd) oerh on (xalcny.CURRENCY =
                                                                                            oerh.FROM_CURRENCY_CD and
                                                                                            row_number = 1)
                                  group by PARTY_ID) clv
                          join XX_ECIF_VIP_CHK_CONDIT xevcc on (clv.CUR_AMOUNT_BAL >=
                                                               xevcc.count_lowest and
                                                               clv.CUR_AMOUNT_BAL <
                                                               xevcc.count_highest)
                                                            or (clv.CUR_AMOUNT_BAL >=
                                                               xevcc.count_lowest and
                                                               xevcc.count_highest is null)
                         where xevcc.COUNT_MODE = g_vip_count_mode_dk
                        Union All
                        --卡系统个人客户对应VIP等级
                        Select PARTY_ID, VIP_CODE, AS_OF_DATE
                          from (select party_id,
                                       vip_code,
                                       as_of_date,
                                       row_number() over(partition by party_id order by vip_code desc) as row_number
                                  from XX_ECIF_VIP_INFO
                                 where source_type = g_vip_info_sourec_type_card
                                   and vip_code is not null)
                         where row_number = 1) u
                Group by PARTY_ID) uv
         left join (
                    --OFDM中系统已判断个人客户VIP等级
                    select evi.PARTY_ID, evi.AS_OF_DATE, evi.VIP_CODE
                      from (select party_id,
                                    as_of_date,
                                    vip_code,
                                    row_number() over(partition by party_id order by as_of_date desc) as row_number
                               from XX_ECIF_VIP_INFO
                              where source_type = g_vip_info_source_type_etl) evi
                     where row_number = 1) viv on uv.PARTY_ID = viv.PARTY_ID
        where uv.VIP_CODE > NVL(viv.VIP_CODE, '');
     commit;
   exception
     when others then
       null;
    
   end CHECK_VIP_PRC;

   procedure insert_vip_prc(v_party_id in number, v_vip_code in varchar2) is
   begin
  
     insert into bocofsa.xx_ecif_vip_info
       (party_id, as_of_date,vip_code,effective_date, source_type)
     values
       (v_party_id, sysdate,v_vip_code,   sysdate, 'CUST');
     commit;
   exception
     when others then
       null;
   end insert_vip_prc;

end XX_ECIF_VIP_PKG;