如何调用存储过程，代码如下：
注：TPL_FEE_UPDATE_REAL_FEE为存储过程名。
附加：TPL_FEE_UPDATE_REAL_FEE存储过程SQL语句(开发项目中的一个存储过程实例)，如下.
Connection dbConn = this.getSessionFactory().getCurrentSession().connection();
CallableStatement proc= null;
try {
proc = dbConn.prepareCall("{call TPL_FEE_UPDATE_REAL_FEE(?)}");
proc.setString(1, messageFee.getPlanSeq());
proc.execute();
proc.close();
dbConn.close();
} catch (Exception e) {
e.printStackTrace();
} finally{
if (proc!=null){
proc.close();
}
if (dbConn!=null){
dbConn.close();
}
}
注：TPL_FEE_UPDATE_REAL_FEE为存储过程名。
附加：TPL_FEE_UPDATE_REAL_FEE存储过程SQL语句(开发项目中的一个存储过程实例)，如下.
create or replace procedure TPL_FEE_UPDATE_REAL_FEE(v_trans_plan_id tpl_trans_plan.trans_plan_id%type) is
--计划信息;
n_plan_id tpl_trans_plan.id%type;
--v_trans_plan_id tpl_trans_plan.trans_plan_id%type;
v_provider_id tpl_trans_plan.provider_id%type;
v_unit_id tpl_trans_plan.unit_id%type;
v_unit_name tpl_trans_plan.unit_name%type;
v_lading_spot tpl_trans_plan.lading_spot%type;
v_lading_spot_name tpl_trans_plan.lading_spot_name%type;
v_dest_spot tpl_trans_plan.dest_spot%type;
v_dest_spot_name tpl_trans_plan.dest_spot_name%type;
v_consinee_code tpl_trans_plan.consinee_code%type;
v_consinee_name tpl_trans_plan.consinee_name%type;
d_excute_date tpl_trans_plan.excute_date%type;
v_trans_type tpl_trans_plan.trans_type%type;
v_manu_id tpl_trans_plan.manu_id%type;
v_ship_id tpl_trans_plan.ship_id%type;
v_ship_name tpl_trans_plan.ship_name%type;
v_bill_id tpl_trans_plan.bill_id%type;
v_pick_num tpl_trans_plan.pick_num%type;
--合同信息;
n_seq_id tpl_trans_pack.seq_id%type;
n_order_plan_id tpl_trans_pack.plan_id%type;
v_product_id tpl_trans_pack.product_id%type;
v_product_type tpl_trans_pack.product_type%type;
v_product_name tpl_trans_pack.product_name%type;
v_order_num tpl_trans_pack.order_num%type;
v_company_code tpl_trans_pack.company_code%type;
n_insurance_rate tpl_trans_pack.insurance_rate%type;
n_insurance_price tpl_trans_pack.insurance_price%type;
n_total_count tpl_trans_pack.unit_count%type;
n_gross_weight tpl_trans_pack.gross_weight%type;
n_net_weight tpl_trans_pack.net_weight%type;
--保单信息;
n_insurance_total_money tpl_real_fee.total_insur_money%type := 0;
v_insur_id tpl_real_fee.insur_id%type;
v_train_id tpl_trans_seq.train_id%type;
--费率信息;
v_order_type tpl_plan_fee.order_type%type;
v_order_user_type tpl_plan_fee.order_user_type%type;
v_bill_type tpl_plan_fee.bill_type%type;
v_settle_code tpl_plan_fee.settle_code%type;
v_fee_provider_id tpl_plan_fee.provider_id%type;
v_creditor_code tpl_plan_fee.creditor_code%type;
v_invoice_title_code tpl_plan_fee.invoice_title_code%type;
v_invoice_title_name tpl_plan_fee.invoice_title_name%type;
v_invoice_title_tax_no tpl_plan_fee.invoice_title_tax_no%type;
v_consinee_tax_no tpl_plan_fee.consinee_tax_no%type;
v_sale_org_code tpl_plan_fee.sale_org_code%type;
n_gross_weight_fee tpl_plan_fee.unit_total_money%type;
n_net_weight_fee tpl_plan_fee.unit_total_money%type;
--其它信息;
n_rec_num number := 0;
d_create_date date;
d_modify_date date;
d_arrive_date date;
v_create_id tpl_real_fee.create_id%type;
v_modify_id tpl_real_fee.modify_id%type;
n_plan_fee_num number := 0;
n_total_money tpl_real_fee.total_money%type := 0;
n_pay_money tpl_real_fee.pay_money%type := 0;
n_part_num number := 0;
n_part_insurance tpl_real_fee.total_insur_money%type;
v_plan_seq tpl_real_fee.plan_seq%type;
v_plan_seq_end_string varchar(5);
n_invoice_sys_id tpl_real_fee.invoice_sys_id%type;
v_invoice_status tpl_real_fee.invoice_status%type;
v_procmt_org_code tpl_plan_fee.procmt_org_code%type;
v_procmt_type tpl_plan_fee.procmt_type%type;
begin
for plan_list in (select t.id,
t.trans_plan_id,
t.provider_id,
t.unit_id,
t.unit_name,
t.lading_spot,
t.lading_spot_name,
t.dest_spot,
t.dest_spot_name,
t.consinee_code,
t.consinee_name,
t.excute_date,
t.trans_type,
t.manu_id,
t.ship_id,
t.ship_name,
t.bill_id,
t.pick_num
from tpl_trans_plan t
where t.trans_plan_id = v_trans_plan_id) loop
n_plan_id := plan_list.id;
--v_trans_plan_id:=plan_list.trans_plan_id;
v_provider_id := plan_list.provider_id;
v_unit_id := plan_list.unit_id;
v_unit_name := plan_list.unit_name;
v_lading_spot := plan_list.lading_spot;
v_lading_spot_name := plan_list.lading_spot_name;
v_dest_spot := plan_list.dest_spot;
v_dest_spot_name := plan_list.dest_spot_name;
v_consinee_code := plan_list.consinee_code;
v_consinee_name := plan_list.consinee_name;
d_excute_date := plan_list.excute_date;
v_trans_type := plan_list.trans_type;
v_manu_id := plan_list.manu_id;
v_ship_id := plan_list.ship_id;
v_ship_name := plan_list.ship_name;
v_bill_id := plan_list.bill_id;
v_pick_num := plan_list.pick_num;
--select count(1) into n_rec_num from tpl_message_fee_cancel t
--where t.plan_id=v_trans_plan_id and t.cancel_status='1'
--and t.update_status='0';
--if n_rec_num > 0 then
select count(1)
into n_rec_num
from tpl_real_fee t
where t.plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and (t.invoice_status = '1' or t.over_status = 1);
if n_rec_num > 0 then
--reopen add by zhengfei 20080807
insert into tpl_message_log
(ERR_TITLE, CREATE_DATE, OPER_MEMO)
values
('计划：' || v_trans_plan_id || '已生成发票或者运输实绩已经发送，不能更新',
sysdate,
'TPL_FEE_UPDATE_REAL_FEE');
else
--reopen add by zhengfei 20080807
--合同列表;
for order_list in (select min(t.seq_id) as seq_id,
min(t.plan_id) as plan_id,
min(t.product_id) as product_id,
min(t.order_num) as order_num,
min(t.company_code) as company_code,
min(t.product_type) as product_type,
min(t.product_name) as product_name,
min(t.insurance_rate) as insurance_rate,
min(t.insurance_price) as insurance_price,
count(1) as total_count,
sum(t.gross_weight) as gross_weight,
sum(t.net_weight) as net_weight
from tpl_trans_pack t
where t.plan_id = n_plan_id
and t.pack_status = '1'
group by t.seq_id, t.plan_id, t.order_num) loop
n_seq_id := order_list.seq_id;
n_order_plan_id := order_list.plan_id;
v_product_id := order_list.product_id;
v_product_type := order_list.product_type;
v_product_name := order_list.product_name;
v_order_num := order_list.order_num;
v_company_code := order_list.company_code;
n_insurance_rate := order_list.insurance_rate;
n_insurance_price := order_list.insurance_price;
n_total_count := order_list.total_count;
n_gross_weight := order_list.gross_weight;
n_net_weight := order_list.net_weight;
--运输码单号;
v_plan_seq_end_string := null;
--原始费用信息;
select max(t.create_id),
max(t.modify_id),
max(t.create_date),
max(t.create_date),
max(t.modify_date),
max(t.invoice_sys_id),
max(t.invoice_status)
into v_create_id,
v_modify_id,
d_create_date,
d_arrive_date,
d_modify_date,
n_invoice_sys_id,
v_invoice_status
from tpl_real_fee t
where t.trans_seq = n_seq_id
and t.plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and t.order_num = v_order_num;
if v_invoice_status is not null then
--增加删除日志记录
insert into tpl_real_fee_deleted_log
(id,
insur_id,
trans_seq,
plan_seq,
plan_id,
plan_date,
plan_type,
trans_type,
type,
order_num,
company_code,
order_type,
product_id,
product_type,
product_type_name,
bill_id,
settle_code,
unit_id,
unit_name,
manu_id,
provider_id,
provider_name,
invoice_sys_id,
ship_id,
ship_name,
lading_spot,
lading_spot_name,
dest_spot,
dest_spot_name,
good_bill_code,
train_id,
gross_weight,
net_weight,
act_count,
stock_days,
free_stock_days,
creditor_code,
total_money,
total_insur_money,
pay_money,
invoice_title_code,
invoice_title_name,
invoice_title_tax_no,
consinee_code,
consinee_name,
consinee_tax_no,
over_status,
invoice_made_status,
insur_status,
invoice_status,
memo,
create_id,
create_date,
modify_id,
modify_date,
order_user_type,
bill_type,
transport_fee,
insurance_price_fee,
service_fee,
rope_fee,
bill_trans_fee,
save_fee,
con_fee,
fix_fee,
other_fee,
other_fee1,
other_fee2,
other_memo1,
other_memo2,
sale_org_code,
pick_num,
delete_id,
delete_date,
delete_type,
procmt_org_code,
procmt_type)
select real_fee.id,
real_fee.insur_id,
real_fee.trans_seq,
real_fee.plan_seq,
real_fee.plan_id,
real_fee.plan_date,
real_fee.plan_type,
real_fee.trans_type,
real_fee.type,
real_fee.order_num,
real_fee.company_code,
real_fee.order_type,
real_fee.product_id,
real_fee.product_type,
real_fee.product_type_name,
real_fee.bill_id,
real_fee.settle_code,
real_fee.unit_id,
real_fee.unit_name,
real_fee.manu_id,
real_fee.provider_id,
real_fee.provider_name,
real_fee.invoice_sys_id,
real_fee.ship_id,
real_fee.ship_name,
real_fee.lading_spot,
real_fee.lading_spot_name,
real_fee.dest_spot,
real_fee.dest_spot_name,
real_fee.good_bill_code,
real_fee.train_id,
real_fee.gross_weight,
real_fee.net_weight,
real_fee.act_count,
real_fee.stock_days,
real_fee.free_stock_days,
real_fee.creditor_code,
real_fee.total_money,
real_fee.total_insur_money,
real_fee.pay_money,
real_fee.invoice_title_code,
real_fee.invoice_title_name,
real_fee.invoice_title_tax_no,
real_fee.consinee_code,
real_fee.consinee_name,
real_fee.consinee_tax_no,
real_fee.over_status,
real_fee.invoice_made_status,
real_fee.insur_status,
real_fee.invoice_status,
real_fee.memo,
real_fee.create_id,
real_fee.create_date,
real_fee.modify_id,
real_fee.modify_date,
real_fee.order_user_type,
real_fee.bill_type,
real_fee.transport_fee,
real_fee.insurance_price_fee,
real_fee.service_fee,
real_fee.rope_fee,
real_fee.bill_trans_fee,
real_fee.save_fee,
real_fee.con_fee,
real_fee.fix_fee,
real_fee.other_fee,
real_fee.other_fee1,
real_fee.other_fee2,
real_fee.other_memo1,
real_fee.other_memo2,
real_fee.sale_org_code,
real_fee.pick_num,
'system',
sysdate,
'auto fee cancel',
real_fee.procmt_org_code,
real_fee.procmt_type
from tpl_real_fee real_fee
where real_fee.trans_seq = n_seq_id
and real_fee.plan_id = v_trans_plan_id
and real_fee.unit_id = v_unit_id
and real_fee.order_num = v_order_num;
--删除计划实绩;
delete tpl_real_fee t
where t.trans_seq = n_seq_id
and t.plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and t.order_num = v_order_num;
--select t.seq into v_plan_seq_end_string from tpl_system_code t where t.table_name='TplRealFee' and t.rule_name='planSeqs';
--update tpl_system_code t set t.seq=to_char(to_number(v_plan_seq_end_string)+1)
--where t.table_name='TplRealFee' and t.rule_name='planSeqs';
TPL_FEE_PLAN_SEQ(v_plan_seq_end_string);
v_plan_seq := 'TR' || to_char(sysdate, 'yymmdd') ||
lpad(v_plan_seq_end_string, 5, 0);
--取保费金额;
n_insurance_total_money := 0;
v_insur_id := null;
select count(1)
into n_rec_num
from tpl_insurance_bill t
where t.ship_seq_id = n_seq_id
and t.trans_plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and t.order_num = v_order_num;
if n_rec_num > 0 then
select nvl(t.insurance_total_num, 0), nvl(t.insur_id, null)
into n_insurance_total_money, v_insur_id
from tpl_insurance_bill t
where t.ship_seq_id = n_seq_id
and t.trans_plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and t.order_num = v_order_num;
end if;
--取车皮信息;
v_train_id := null;
select count(1)
into n_rec_num
from tpl_trans_seq t
where t.id = n_seq_id;
if n_rec_num > 0 then
select t.train_id
into v_train_id
from tpl_trans_seq t
where t.id = n_seq_id;
end if;
--是否有费率信息;
select count(1)
into n_plan_fee_num
from tpl_plan_fee t
where t.plan_id = v_trans_plan_id
and t.unit_id = v_unit_id
and t.order_num = v_order_num;
n_total_money := nvl(n_total_money, 0);
--应付费用;
n_pay_money := round(n_total_money + n_insurance_total_money, 2);
--如果没有没有费率信息，只返回实绩;
if n_plan_fee_num < 1 then
insert into tpl_real_fee t
(t.id,
t.insur_id,
t.trans_seq,
t.plan_seq,
t.plan_id,
t.plan_date,
t.plan_type,
t.trans_type,
t.order_num,
t.product_id,
t.product_type,
t.product_type_name,
t.bill_id,
t.unit_id,
t.unit_name,
t.manu_id,
t.provider_id,
t.ship_id,
t.ship_name,
t.lading_spot,
t.lading_spot_name,
t.dest_spot,
t.dest_spot_name,
t.train_id,
t.gross_weight,
t.net_weight,
t.act_count,
t.total_money,
t.total_insur_money,
t.pay_money,
t.consinee_code,
t.consinee_name,
t.consinee_tax_no,
t.over_status,
t.invoice_made_status,
t.memo,
t.create_id,
t.create_date,
t.modify_id,
t.modify_date,
t.invoice_sys_id,
t.invoice_status,
t.pick_num)
values
(seq_tpl_real_fee.nextval,
v_insur_id,
n_seq_id,
v_plan_seq,
v_trans_plan_id,
d_excute_date,
'0',
v_trans_type,
v_order_num,
v_product_id,
v_product_type,
v_product_name,
v_bill_id,
v_unit_id,
v_unit_name,
v_manu_id,
v_provider_id,
v_ship_id,
v_ship_name,
v_lading_spot,
v_lading_spot_name,
v_dest_spot,
v_dest_spot_name,
v_train_id,
n_gross_weight,
n_net_weight,
n_total_count,
n_total_money,
n_insurance_total_money,
n_pay_money,
v_consinee_code,
v_consinee_name,
v_consinee_tax_no,
'1',
decode(v_trans_type, '2', '0', '1'),
'update fee',
v_create_id,
d_create_date,
v_modify_id,
d_modify_date,
n_invoice_sys_id,
v_invoice_status,
v_pick_num);
end if;
--否则计算各合同费用;
n_part_num := 0;
for plan_fee_group in (select count(1)
from tpl_plan_fee t
where t.plan_id = v_trans_plan_id
and t.order_num = v_order_num
and t.unit_id = v_unit_id
group by t.creditor_code) loop
n_part_num := n_part_num + 1;
end loop;
for plan_fee_list in (select max(t.order_type) as order_type,
max(t.order_user_type) as order_user_type,
max(t.bill_type) as bill_type,
max(t.settle_code) as settle_code,
max(t.provider_id) as provider_id,
max(t.invoice_title_code) as invoice_title_code,
max(t.invoice_title_name) as invoice_title_name,
max(t.invoice_title_tax_no) as invoice_title_tax_no,
max(t.consinee_tax_no) as consinee_tax_no,
max(sale_org_code) as sale_org_code,
max(t.procmt_org_code) as procmt_org_code,
max(t.procmt_type) as procmt_type,
sum(round(decode(t.fee_type_code,
'1101',
n_gross_weight /
t.gross_weight *
t.unit_total_money,
n_gross_weight *
t.unit_price),
2)) as gross_weight_fee,
sum(round(decode(t.fee_type_code,
'1101',
n_net_weight /
t.net_weight *
t.unit_total_money,
n_net_weight *
t.unit_price),
2)) as net_weight_fee,
t.creditor_code as creditor_code
from tpl_plan_fee t
where t.plan_id = v_trans_plan_id
and t.order_num = v_order_num
and t.unit_id = v_unit_id
group by t.creditor_code,
t.sale_org_code,
t.procmt_org_code,
t.procmt_type) loop
v_order_type := plan_fee_list.order_type;
v_order_user_type := plan_fee_list.order_user_type;
v_bill_type := plan_fee_list.bill_type;
v_settle_code := plan_fee_list.settle_code;
v_fee_provider_id := plan_fee_list.provider_id;
v_creditor_code := plan_fee_list.creditor_code;
v_invoice_title_code := plan_fee_list.invoice_title_code;
v_invoice_title_name := plan_fee_list.invoice_title_name;
v_invoice_title_tax_no := plan_fee_list.invoice_title_tax_no;
v_consinee_tax_no := plan_fee_list.consinee_tax_no;
v_sale_org_code := plan_fee_list.sale_org_code;
n_gross_weight_fee := plan_fee_list.gross_weight_fee;
n_net_weight_fee := plan_fee_list.net_weight_fee;
v_procmt_org_code := plan_fee_list.procmt_org_code;
v_procmt_type := plan_fee_list.procmt_type;
--按毛重还是净重计算总金额;
if v_order_num like 'A%' or v_order_num like 'B%' then
n_total_money := n_net_weight_fee;
else
n_total_money := n_gross_weight_fee;
end if;
if n_gross_weight_fee = 0 then
n_total_money := n_net_weight_fee;
end if;
n_total_money := nvl(n_total_money, 0);
--应付费用;
n_part_insurance := round(n_insurance_total_money / n_part_num,
2);
n_pay_money := round(n_total_money + n_part_insurance, 2);
insert into tpl_real_fee t
(t.id,
t.insur_id,
t.trans_seq,
t.plan_seq,
t.plan_id,
t.plan_date,
t.plan_type,
t.trans_type,
t.order_num,
t.company_code,
t.order_type,
t.product_id,
t.product_type,
t.product_type_name,
t.bill_id,
t.settle_code,
t.unit_id,
t.unit_name,
t.manu_id,
t.provider_id,
t.ship_id,
t.ship_name,
t.lading_spot,
t.lading_spot_name,
t.dest_spot,
t.dest_spot_name,
t.train_id,
t.gross_weight,
t.net_weight,
t.act_count,
t.creditor_code,
t.total_money,
t.total_insur_money,
t.pay_money,
t.invoice_title_code,
t.invoice_title_name,
t.invoice_title_tax_no,
t.consinee_code,
t.consinee_name,
t.consinee_tax_no,
t.over_status,
t.invoice_made_status,
t.memo,
t.create_id,
t.create_date,
t.modify_id,
t.modify_date,
t.order_user_type,
t.bill_type,
t.sale_org_code,
t.invoice_sys_id,
t.invoice_status,
t.pick_num,
t.procmt_org_code,
t.procmt_type)
values
(seq_tpl_real_fee.nextval,
v_insur_id,
n_seq_id,
v_plan_seq,
v_trans_plan_id,
d_excute_date,
'0',
v_trans_type,
v_order_num,
v_company_code,
v_order_type,
v_product_id,
v_product_type,
v_product_name,
v_bill_id,
v_settle_code,
v_unit_id,
v_unit_name,
v_manu_id,
v_creditor_code,
v_ship_id,
v_ship_name,
v_lading_spot,
v_lading_spot_name,
v_dest_spot,
v_dest_spot_name,
v_train_id,
n_gross_weight,
n_net_weight,
n_total_count,
v_creditor_code,
n_total_money,
n_part_insurance,
n_pay_money,
v_invoice_title_code,
v_invoice_title_name,
v_invoice_title_tax_no,
v_consinee_code,
v_consinee_name,
v_consinee_tax_no,
'1',
decode(v_trans_type, '2', '0', '1'),
'update fee',
v_create_id,
d_create_date,
v_modify_id,
d_modify_date,
v_order_user_type,
v_bill_type,
v_sale_org_code,
n_invoice_sys_id,
v_invoice_status,
v_pick_num,
v_procmt_org_code,
v_procmt_type);
end loop;
end if;
end loop;
--更新费用状态;
update tpl_message_fee_cancel t
set t.update_status = '1'
where t.plan_id = v_trans_plan_id;
end if; --reopen add by zhengfei 20080807
--end if;
end loop;
end TPL_FEE_UPDATE_REAL_FEE;
234
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
