create or replace Procedure Cljs_Curt (v_XLBH in varchar2) is
tmpVar number;
/******************************************************************************
PURPOSE: 计算各层电流与功率的分布 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-17 Fred Zhang 1. Created this procedure.
******************************************************************************/
Type node_type is table of Tcalculate.SJDH%Type index by binary_integer;
node1 node_type;
max_layer integer; --某一线路的最大层数
Begin
tmpVar := 0;
/*计算各层输入,输出有功功率与无功功率*/
select Max(JGBM) into max_layer from Tcalculate where xlbh=v_XLBH;
for m in Reverse 1.. max_layer Loop
select * Bulk Collect into node1 from
((select SJDH from Tcalculate where xlbh=v_XLBH and jgbm=m-1
union
select MJDH from Tcalculate where xlbh=v_XLBH and jgbm=m-1)
intersect
(select SJDH from Tcalculate where xlbh=v_XLBH and jgbm=m
union
select MJDH from Tcalculate where xlbh=v_XLBH and jgbm=m));
Exit When node1.Count=0;
for n in 1..node1.Count Loop
/*计算线路中线路段的线电流,输入有功功率,无功功率*/
update Tcalculate set SRYG=Nvl(SCYG,0)+3*Nvl(DZ,0)*Power( Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*10000),2),
SRWG=Nvl(SCWG,0)+3*Nvl(DK,0)*Power( Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*10000),2),
JSDL= Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*JSDY)
where JGBM=m and xlbh=v_XLBH;
Commit;
/*计算线路中线路段上一层的输出有功功率,无功功率*/
update Tcalculate set (SCYG,SCWG)=
(select Sum(SRYG),Sum(SRWG) from Tcalculate where xlbh=v_XLBH and JGBM=m and (SJDH=node1(n) or MJDH=node1(n)))
where xlbh=v_XLBH and JGBM=m-1and (SJDH=node1(n) or MJDH=node1(n));
Commit;
End Loop;
End Loop;
update Tcalculate set SRYG=SCYG,SRWG=SCWG,JSDL= Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*JSDY) where xlbh=v_XLBH and KGZT=1;
Exception
When No_Data_Found then
null;
When Others then
-- Consider logging the error and then re-raise
Raise;
End Cljs_Curt;/
tmpVar number;
/******************************************************************************
PURPOSE: 计算各层电流与功率的分布 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-17 Fred Zhang 1. Created this procedure.
******************************************************************************/
Type node_type is table of Tcalculate.SJDH%Type index by binary_integer;
node1 node_type;
max_layer integer; --某一线路的最大层数
Begin
tmpVar := 0;
/*计算各层输入,输出有功功率与无功功率*/
select Max(JGBM) into max_layer from Tcalculate where xlbh=v_XLBH;
for m in Reverse 1.. max_layer Loop
select * Bulk Collect into node1 from
((select SJDH from Tcalculate where xlbh=v_XLBH and jgbm=m-1
union
select MJDH from Tcalculate where xlbh=v_XLBH and jgbm=m-1)
intersect
(select SJDH from Tcalculate where xlbh=v_XLBH and jgbm=m
union
select MJDH from Tcalculate where xlbh=v_XLBH and jgbm=m));
Exit When node1.Count=0;
for n in 1..node1.Count Loop
/*计算线路中线路段的线电流,输入有功功率,无功功率*/
update Tcalculate set SRYG=Nvl(SCYG,0)+3*Nvl(DZ,0)*Power( Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*10000),2),
SRWG=Nvl(SCWG,0)+3*Nvl(DK,0)*Power( Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*10000),2),
JSDL= Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*JSDY)
where JGBM=m and xlbh=v_XLBH;
Commit;
/*计算线路中线路段上一层的输出有功功率,无功功率*/
update Tcalculate set (SCYG,SCWG)=
(select Sum(SRYG),Sum(SRWG) from Tcalculate where xlbh=v_XLBH and JGBM=m and (SJDH=node1(n) or MJDH=node1(n)))
where xlbh=v_XLBH and JGBM=m-1and (SJDH=node1(n) or MJDH=node1(n));
Commit;
End Loop;
End Loop;
update Tcalculate set SRYG=SCYG,SRWG=SCWG,JSDL= Power((Power(Nvl(SCYG,0),2)+Power(Nvl(SCWG,0),2)),1/2)/(Power(3,1/2)*JSDY) where xlbh=v_XLBH and KGZT=1;
Exception
When No_Data_Found then
null;
When Others then
-- Consider logging the error and then re-raise
Raise;
End Cljs_Curt;/
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;
create or repalce procedure pro_test(p_id in number) as
begin
update user a set a.user_name='OK' where a.user_id=p_id;
commit;
exception
when others then
rollback;
end;