在包中创建存储过程的语句是
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;