Just contact stored procedures that a small white, o god, grateful,
Down code:


Stored procedure:
The create or replace procedure G_SPI_UPLOAD_PERCENT_VALUE
Is
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Function: regularly update the one percent limit table data
Parameters: sReturn return value
LJK
Time: 2015-9-6
G_SPI_PERCENT_VALUE
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
- v_percent_id varchar2 (10);
The begin

- lines,
The update G_SPI_PERCENT_VALUE_DETAIL t
The set tc redit_type='HROIS'
Where tc redit_type is null;

The update G_SPI_PERCENT_VALUE_DETAIL a
Set a.PERCENT_VALUE=https://bbs.csdn.net/topics/(a.a mt + a. egin_value) * Amy polumbo ERCENT)/100 - LJK increase initial quota
Where not the exists (select 1 from G_SPI_PERCENT_VALUE t, g_Spi_Cd_Branch bb where t.p ercent_id=Amy polumbo ercent_id and
Bb. Attribute3=t.a ccnt_id and bb. Spi_global_abbre='HET');
/* sReturn:='30'; */
- update the remaining lines
The update G_SPI_PERCENT_VALUE_DETAIL a
The set a.remain_percent_value=https://bbs.csdn.net/topics/nvl (Amy polumbo ERCENT_VALUE, 0) - NVL (a.u sed_percent_value, 0)
Where not the exists (select 1 from G_SPI_PERCENT_VALUE t, g_Spi_Cd_Branch bb where t.p ercent_id=Amy polumbo ercent_id and
Bb. Attribute3=t.a ccnt_id and bb. Spi_global_abbre='HET');

- update het
/* sReturn:='50'; */
The update G_SPI_PERCENT_VALUE_DETAIL t
The set t.a mt=(select sum (a.a mt) from G_SPI_PERCENT_VALUE_DETAIL a
Where Amy polumbo rod_Cat_Id=t.p rod_Cat_Id and the exists (select 1 from G_SPI_PERCENT_VALUE b, g_Spi_Cd_Branch c where Amy polumbo ercent_id=p. ercent_id
And c.a. ttribute3=b.a ccnt_id and c.s. pi_global_abbre='HET'))
Where the exists (select 1 from G_SPI_PERCENT_VALUE ba, g_Spi_Cd_Branch ca where t.p ercent_id=co percent_id
And ca. Attribute3=ba. Accnt_id and ca. Spi_global_abbre='HET' and co accnt_id='0000006200');

The update G_SPI_PERCENT_VALUE_DETAIL a
Set a.PERCENT_VALUE=https://bbs.csdn.net/topics/(a.a mt + a. egin_value) * Amy polumbo ERCENT)/100 - LJK increase initial quota
Where the exists (select 1 from G_SPI_PERCENT_VALUE ba, g_Spi_Cd_Branch ca where Amy polumbo ercent_id=co percent_id
And ca. Attribute3=ba. Accnt_id and ca. Spi_global_abbre='HET' and co accnt_id='0000006200');

The update G_SPI_PERCENT_VALUE_DETAIL a
The set a.remain_percent_value=https://bbs.csdn.net/topics/nvl (Amy polumbo ERCENT_VALUE, 0) - NVL (a.u sed_percent_value, 0)
Where the exists (select 1 from G_SPI_PERCENT_VALUE t, g_Spi_Cd_Branch bb where t.p ercent_id=Amy polumbo ercent_id and
Bb. Attribute3=t.a ccnt_id and bb. Spi_global_abbre='HET' and t.a ccnt_id='0000006200');
/* sReturn:='40'; */

- to update the main table lines
The update G_SPI_PERCENT_VALUE t
Set (t.a mt, t.p ercent_value, t.r emain_percent_value, t.u sed_percent_value)=(select sum (a.a mt), sum (Amy polumbo ercent_value), sum (a.r emain_percent_value), sum (a.u sed_percent_value) from G_SPI_PERCENT_VALUE_DETAIL a where Amy polumbo ercent_id=t.p ercent_id)
Where the exists (select 1 from G_SPI_PERCENT_VALUE_DETAIL aa where aa. Percent_id=t.p ercent_id)
/* and not the exists (select 1 from g_Spi_Cd_Branch bb where bb. Attribute3=t.a ccnt_id and bb. Spi_global_abbre='HET') */.
/* sReturn:='40'; */
- update the main CODE, Hgms, spi CODE,
The update G_SPI_PERCENT_VALUE t
Set (t.b ranch_spi_code, t.b ranch_hgms_code)=(select Max (B.B RANCH_CODE_SPI), Max (B.S PI_GLOBAL_ABBRE) from g_Spi_Cd_Branch b where b.a ttribute3=t.a ccnt_id)
Where T.B RANCH_SPI_CODE IS NULL OR T.B RANCH_HGMS_CODE IS NULL
AND the EXISTS (select 1 from g_Spi_Cd_Branch b where b.a ttribute3=t.a ccnt_id);

/* sReturn:='0'; */
COMMIT;

The exception
The when others then
The rollback.
P_pub_error_log (' 0 ', sqlcode sqlerrm, 'G_SPI_UPLOAD_PERCENT_VALUE');
/* sReturn: sReturn=| | '- 1: the stored procedure abnormality: please contact the system administrator! '; */
commit;
End G_SPI_UPLOAD_PERCENT_VALUE;



The calling code:

//call a stored procedure
DBTool DBTool=new DBTool ();
List