oracle 存储过程示例

create or replace procedure p_oncefee_wj_netconn(return_result out varchar, --返回结果，如果没有费用返回空字符串
err_code out number, --异常编码
err_msg out varchar, --异常消息
p_order_item_id in varchar, --订单标识
p_comp_inst_id in varchar, --销售品实列标识
p_sales_res_inst_id in varchar, --营销资源实例标识
p_acct_item_type_id in varchar, --费用项类型标识
p_prod_offer_id in number) as
--宽带 使用性质算费
--订购
--家庭用户 300
--办公用户 500
--移机
--家庭用户 200
--办公用户 300
cursor cur_feeret(v_order_item_id varchar,v_comp_inst_id varchar) is
select town_flag from prod_inst a where a.comp_inst_id =v_comp_inst_id and a.prod_id in ('80000030','80069701');
cursor cur_flag(v_serv varchar2,v_order_item_id varchar2,v_comp_inst_id varchar2) is
select b.action_type as v_action_chg,c.action_type as v_action_fun from order_item a,order_item_change b,prod_inst c
where a.order_item_id=b.order_item_id
and a.obj_id=b.comp_inst_id
and a.obj_id=c.comp_inst_id
and b.table_name='PROD_INST'
and a.service_offer_id=v_serv
and a.order_item_id=v_order_item_id
and a.obj_id=v_comp_inst_id;
cursor cur_prod_inst_rel (v_order_item_id varchar,v_comp_inst_id varchar) is --查询同一订单下是否有关联加装移机 add by ligaogu
select oi.cust_order_id,obj_id,api1.prod_inst_id rela_prod_inst_id,'13'
from order_item oi,prod_inst_ext apix1,prod_inst api1,prod_inst api,prod_inst_ext apix
where oi.cust_order_id in
(select cust_order_id
from order_item
where order_item_id =p_order_item_id
and service_offer_id = 2815
)
and oi.obj_id=api.comp_inst_id
and api.owner_cust_id=api1.owner_cust_id
and apix1.attrib_34 = apix.attrib_34
and api.prod_inst_id = apix.prod_inst_id
and api1.prod_inst_id = apix1.prod_inst_id
and api1.prod_id = '80000030'
and api.prod_id = '80000000'
and api1.prod_inst_id in(select prod_inst_id from prod_inst where comp_inst_id=p_comp_inst_id)
and oi.apply_obj_spec not in  (60000030,60069701);
v_action_chg varchar2(50);
v_action_fun varchar2(50);
v_serv number(10);
v_prod_inst_id number(12);
v_cursor cur_feeret%rowtype;
return_flag cur_flag%rowtype;---定义游标变量
v_cursor_rel cur_prod_inst_rel%rowtype;
v_order_item_prop number(12);
v_old_order_item_id number(32);
v_old_need_amount number(32);
v_sql varchar2(500);
v_count number(4);
begin
return_result := '0';
err_code := '0';
err_msg := '';
v_old_need_amount :=0;
select a.service_offer_id into v_serv from order_item a where a.order_item_id=p_order_item_id;
if v_serv=1 then
open cur_feeret(p_order_item_id,p_comp_inst_id);
fetch cur_feeret into v_cursor;
if (cur_feeret%rowcount = 0) then
return_result := 0;
elsif v_cursor.town_flag in ('1','4','6','7') then
open cur_flag(v_serv,p_order_item_id,p_comp_inst_id);
fetch cur_flag into return_flag;
if (cur_flag%rowcount = 0) then
return_result := 300;
elsif return_flag.v_action_chg='A' and return_flag.v_action_fun='A' then--新增新增
return_result := 300;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='A' then--修改新增
return_result := 300;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='D' then--修改删除
return_result := 0;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='K' then--修改保留
return_result := 300;
end if;
elsif v_cursor.town_flag in ('2','3','5') then
open cur_flag(v_serv,p_order_item_id,p_comp_inst_id);
fetch cur_flag into return_flag;
if (cur_flag%rowcount = 0) then
return_result := 500;
elsif return_flag.v_action_chg='A' and return_flag.v_action_fun='A' then--新增新增
return_result := 500;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='A' then--修改新增
return_result := 500;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='D' then--修改删除
return_result := 0;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='K' then--修改保留
return_result := 500;
end if;
end if;
close cur_feeret;
elsif v_serv=2815 then
open cur_feeret(p_order_item_id,p_comp_inst_id);
fetch cur_feeret into v_cursor;
if (cur_feeret%rowcount = 0) then
return_result := 0;
elsif v_cursor.town_flag in ('1','4','6','7') then
open cur_flag(v_serv,p_order_item_id,p_comp_inst_id);
fetch cur_flag into return_flag;
if (cur_flag%rowcount = 0) then
return_result := 200;
elsif return_flag.v_action_chg='A' and return_flag.v_action_fun='A' then--新增新增
return_result := 200;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='A' then--修改新增
return_result := 200;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='D' then--修改删除
return_result := 0;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='K' then--修改保留
return_result := 200;
end if;
elsif v_cursor.town_flag in ('2','3','5') then
open cur_flag(v_serv,p_order_item_id,p_comp_inst_id);
fetch cur_flag into return_flag;
if (cur_flag%rowcount = 0) then
return_result := 300;
elsif return_flag.v_action_chg='A' and return_flag.v_action_fun='A' then--新增新增
return_result := 300;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='A' then--修改新增
return_result := 300;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='D' then--修改删除
return_result := 0;
elsif return_flag.v_action_chg='M' and return_flag.v_action_fun='K' then--修改保留
return_result := 300;
end if;
end if;
open cur_prod_inst_rel(p_order_item_id,p_comp_inst_id);--是否有加装关系 add by ligaogu
fetch cur_prod_inst_rel into v_cursor_rel;
-- if(v_cursor_rel.rela_prod_inst_id !='') then
--select prod_inst_id into v_prod_inst_id from access_prod_inst where comp_inst_id=p_comp_inst_id;
--if(v_cursor_rel.rela_prod_inst_id= v_prod_inst_id) then --如果相等表示是关联加载移机，宽带的移机费用项为0
--return_result := 0;
if  v_cursor_rel.rela_prod_inst_id is not null then
  return_result := 0;
end if;
select oper_type,old_order_item_id into v_order_item_prop,v_old_order_item_id from order_item where order_item_id=p_order_item_id;
if (v_order_item_prop=1) then
select nvl(sum(need_amount),0) into v_old_need_amount from acct_item_his where order_item_id = v_old_order_item_id and acct_item_type_id=15;
if(v_old_need_amount =0 ) then
return_result := 0;
end if;
end if;
-- end if;
close cur_prod_inst_rel;--关闭游标
close cur_feeret;
-- UR-星级客户 6,7 有有效服务时，免费移机  by yangbiao
select count(1) into v_count  from prod_inst a where not exists(select 1 from CLUB_MEMBER b,MEM_SERV_ACCT c where b.STATUS_CD = '1000'  and b.cust_id = a.owner_cust_id
and b.exp_date>=sysdate and b.MEMBERSHIP_LEVEL>5 and c.cust_id=b.cust_id and c.MEMBER_SERVICE_ID in ('11000','11001') and c.USEABLE_COUNT>0) and a.comp_inst_id = p_comp_inst_id;
if v_count = 0 then
  return_result := 0;
end if;
end if;
exception when no_data_found then
return_result := return_result;
when dup_val_on_index then
err_code := sqlcode;
err_msg := sqlerrm;
when others then
err_code := sqlcode;
err_msg := sqlerrm;
end p_oncefee_wj_netconn;