调用存储过程报oracle 6550 第1行 第7列
小白刚接触存储过程这一块,求大神指点,跪谢。
下浮代码:
存储过程:
create or replace procedure G_SPI_UPLOAD_PERCENT_VALUE
is
/************************************
功能:定时更新百分之一额度表的数据
参数:sReturn 返回值
ljk
时间:2015-9-6
G_SPI_PERCENT_VALUE
************************************/
-- v_percent_id varchar2(10);
begin
--额度,
update G_SPI_PERCENT_VALUE_DETAIL t
set t.credit_type='HROIS'
where t.credit_type is null;
update G_SPI_PERCENT_VALUE_DETAIL a
set a.PERCENT_VALUE=((a.amt+a.begin_value)*a.PERCENT)/100--ljk 增加初始额度
where not exists(select 1 from G_SPI_PERCENT_VALUE t ,g_Spi_Cd_Branch bb where t.percent_id=a.percent_id and
bb.attribute3=t.accnt_id and bb.spi_global_abbre='HET');
/* sReturn :='30';*/
--更新剩余额度
update G_SPI_PERCENT_VALUE_DETAIL a
set a.remain_percent_value=nvl(a.PERCENT_VALUE,0)-nvl(a.used_percent_value,0)
where not exists(select 1 from G_SPI_PERCENT_VALUE t ,g_Spi_Cd_Branch bb where t.percent_id=a.percent_id and
bb.attribute3=t.accnt_id and bb.spi_global_abbre='HET');
--更新het
/*sReturn :='50';*/
update G_SPI_PERCENT_VALUE_DETAIL t
set t.amt=(select sum(a.amt) from G_SPI_PERCENT_VALUE_DETAIL a
where a.Prod_Cat_Id=t.prod_cat_id and exists(select 1 from G_SPI_PERCENT_VALUE b,g_Spi_Cd_Branch c where a.percent_id=b.percent_id
and c.attribute3=b.accnt_id and c.spi_global_abbre='HET'))
where exists(select 1 from G_SPI_PERCENT_VALUE ba,g_Spi_Cd_Branch ca where t.percent_id=ba.percent_id
and ca.attribute3=ba.accnt_id and ca.spi_global_abbre='HET' and ba.accnt_id='0000006200');
update G_SPI_PERCENT_VALUE_DETAIL a
set a.PERCENT_VALUE=((a.amt+a.begin_value)*a.PERCENT)/100--ljk 增加初始额度
where exists(select 1 from G_SPI_PERCENT_VALUE ba,g_Spi_Cd_Branch ca where a.percent_id=ba.percent_id
and ca.attribute3=ba.accnt_id and ca.spi_global_abbre='HET' and ba.accnt_id='0000006200');
update G_SPI_PERCENT_VALUE_DETAIL a
set a.remain_percent_value=nvl(a.PERCENT_VALUE,0)-nvl(a.used_percent_value,0)
where exists(select 1 from G_SPI_PERCENT_VALUE t ,g_Spi_Cd_Branch bb where t.percent_id=a.percent_id and
bb.attribute3=t.accnt_id and bb.spi_global_abbre='HET' and t.accnt_id='0000006200');
/*sReturn :='40';*/
--更新主表的额度
update G_SPI_PERCENT_VALUE t
set (t.amt,t.percent_value,t.remain_percent_value,t.used_percent_value)=(select sum(a.amt),sum(a.percent_value),sum(a.remain_percent_value),sum(a.used_percent_value) from G_SPI_PERCENT_VALUE_DETAIL a where a.percent_id=t.percent_id)
where exists(select 1 from G_SPI_PERCENT_VALUE_DETAIL aa where aa.percent_id=t.percent_id)
/*and not exists(select 1 from g_Spi_Cd_Branch bb where bb.attribute3=t.accnt_id and bb.spi_global_abbre='HET')*/ ;
/*sReturn :='40';*/
----更新主表CODE, Hgms,spi CODE,
update G_SPI_PERCENT_VALUE t
set (t.branch_spi_code,t.branch_hgms_code)=(select max(B.BRANCH_CODE_SPI),max(B.SPI_GLOBAL_ABBRE) from g_Spi_Cd_Branch b where b.attribute3=t.accnt_id)
where T.BRANCH_SPI_CODE IS NULL OR T.BRANCH_HGMS_CODE IS NULL
AND EXISTS(select 1 from g_Spi_Cd_Branch b where b.attribute3=t.accnt_id);
/* sReturn := '0';*/
COMMIT;
exception
when others then
rollback;
p_pub_error_log('0',sqlcode,sqlerrm,'G_SPI_UPLOAD_PERCENT_VALUE');
/* sReturn := sReturn || ' -1:存储过程异常:请联系系统管理员!';*/
commit;
end G_SPI_UPLOAD_PERCENT_VALUE;
调用代码:
//调用存储过程
DBTool dbTool = new DBTool();
List<Object> objs = new ArrayList<Object>();
objs.add("");
dbTool.executeProcedure("G_SPI_UPLOAD_PERCENT_VALUE", objs);
String result=objs.get(0).toString();