一个oracle的实例（包含传参数）

CREATE OR REPLACE PROCEDURE "OSP_FACT_FUNDBAL_D_SUM_2"
-- *************************************************************************
-- SYSTEM: 数据中心
-- SUBSYS: 管理区划份额汇总日表
-- DESCRIPTION: 加载公募和一对多专户（非货币）份额汇总数据到目标表
-- AUTHOR: SUNCJ
-- REVIEWER: NONE
-- CREATE DATE: 20160821
-- COPYRIGHT: (C)COPYRIGHT 2006 BusinessMatrix ALL RIGHTS RESERVED.
-- EDIT HISTORY:
-- ****************************************************
(
--修改参数顺序
PI_START_DATE in number,--加载起始日期
PI_END_DATE in number,--加载截止日期
PI_PART_NAME in varchar2,--分区名称
PI_PART_NO in number--分区序号
)
IS
v_sql varchar2(4000);
v_wrt_count number(30);
v_proc_name varchar2(100);
BEGIN
v_proc_name:='OSP_FACT_FUNDBAL_D_SUM_2';
--写运行日志，运行开始
osp_wrt_etlrun_log(v_proc_name,'I','Program Starting...',null,null);

--删除目标表对应记录，为避免对数据库产生过大压力，循环分批删除
loop
delete from fact_fundbal_d_sum t
where t.sk_confirmdate between PI_START_DATE and PI_END_DATE
and t.partno=PI_PART_NO
and rownum < 100000;
exit when SQL%ROWCOUNT=0;
commit;
end loop;
commit;
--写运行日志，删除目标表数据结束
osp_wrt_etlrun_log(v_proc_name,'R','Delete Target Table Data Finished.',null,null);

--汇总数据，写入目标表
v_sql:='insert into fact_fundbal_d_sum(sk_area,
sk_area_of_bl,
sk_agency,
custtype,
sk_invpty_type,
sk_account_type_of_org,
sk_account_type_of_icbc,
sk_product,
sharetype,
sk_confirmdate,
sk_fdate,
sk_trxexchgtype,
sk_capitalmode,
tano,
tailratio,
agserviceratio,
managerfeeratio,
trusteeratio,
shares,
allshares,
capital,
allcapital,
lastshares,
lastallshares,
lastcapital,
lastallcapital,
tailbonus,
agservicefare,
selfagservicefare,
managerfee,
facapital,
lastfacapital,
famanagerfee,
srcsys,
batchno,
inserttime,
partno,
FAPERFPAY,
sk_employee
)
select sk_area,
sk_area_of_bl,
sk_agency,
custtype,
sk_invpty_type,
sk_account_type_of_org,
sk_account_type_of_icbc,
sk_product,
sharetype,
sk_confirmdate,
max(sk_fdate) as sk_fdate,
sk_trxexchgtype,
sk_capitalmode,
tano,
max(tailratio) as tailratio,
max(agserviceratio) as agserviceratio,
max(managerfeeratio) as managerfeeratio,
max(trusteeratio) as trusteeratio,
sum(shares) as shares,
sum(allshares) as allshares,
sum(capital) as capital,
sum(allcapital) as capital,
sum(lastshares) as lastshares,
sum(lastallshares) as lastallshares,
sum(lastcapital) as lastcapital,
sum(lastallcapital) as lastallcapital,
sum(tailbonus) as tailbonus,
sum(agservicefare) as agservicefare,
sum(selfagservicefare) as agservicefare,
sum(managerfee) as managerfee,
sum(facapital) as facapital,
sum(lastfacapital) as lastfacapital,
sum(famanagerfee) as famanagerfee,
max(srcsys) as srcsys,
max(batchno) as batchno,
sysdate,
:1,
sum(FAPERFPAY) as FAPERFPAY
,sk_employee
from tmp_fact_fbl_d_sum_part_2 partition('||PI_PART_NAME||')
where sk_confirmdate between :2 and :3
group by sk_area,
sk_area_of_bl,
sk_agency,
custtype,
sk_invpty_type,
sk_account_type_of_org,
sk_account_type_of_icbc,
sk_product,
sharetype,
sk_confirmdate,
sk_trxexchgtype,
sk_capitalmode,
tano,
sk_employee';
execute immediate v_sql using PI_PART_NO,PI_START_DATE,PI_END_DATE;
V_WRT_COUNT:=SQL%ROWCOUNT;
--写运行日志，写入目标表
osp_wrt_etlrun_log(v_proc_name,'R','Writing Target Table Finished.','FACT_FUNDBAL_D_SUM',V_WRT_COUNT);
commit;
--runstat(pkg_globals.GC_SCHEMA_DCDW,'fact_fundbal_d_sum',null);
--写运行日志，运行结束
osp_wrt_etlrun_log(v_proc_name,'S','Program Finished.',null,null);
END;