小白剛接觸存盤程序這一塊，求大神指點，跪謝。
下浮代碼：
存盤程序：
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=https://bbs.csdn.net/topics/((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=https://bbs.csdn.net/topics/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=https://bbs.csdn.net/topics/((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=https://bbs.csdn.net/topics/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();
uj5u.com熱心網友回復：
第1行，第7列沒問題呀。uj5u.com熱心網友回復：
呼叫的地方有問題吧，去java板塊問吧uj5u.com熱心網友回復：
存盤程序沒有輸入和輸出引數。怎么在JAVA呼叫時會有個objs引數？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76482.html
標籤：開發
上一篇：oracle基礎入門
下一篇：oracle 如何回傳陣列