过程中直接执行sql时候切记变量名不能同列名一样(下面where 条件是错误的会变成全局匹配的类似1=1)
update ac_account a set a.now_balance=zzCur where a.account_id=account_id;
过程中可能会遇到数据查询为空的情况赋值会抛异常的,这个时候先判断下
querySql:='select count(*)
from fc_DayAccountView
where organ_id = '||chr(39)||88||chr(39)||
' and account_id = '||chr(39)||account_id||chr(39);
execute immediate querysql into isHis;
if isHis>0 then
querysql:='select nvl(dm,0), nvl(cm,0) , nvl(c_num,0)
from fc_DayAccountView
where organ_id = '||chr(39)||88||chr(39)||
' and account_id = '||chr(39)||account_id||chr(39);
execute immediate querysql into debBalance, creBalance, c_num/*into debBalance,creBalance,c_num*/;
else
dbms_output.put_line('**没有借贷记录**');
end if;
基本语法
create or replace procedure test1(a in integer,
b in integer,
c out integer)
as
begin
c:=a+b;
if a=1 then
dbms_output.put_line('输入了1');
elsif a=2 then
dbms_output.put_line('输入了2');
end if;
end test1;
返回个游标
create or replace procedure test2(s out sys_refcursor)
as
begin
open s for select p.user_name from pm_user p;
end test2;
调用
declare
c integer;
s sys_refcursor;
ss pm_user.user_name%type;
begin
test1(1,3,c);
dbms_output.put_line(c);
test2(s);
loop
fetch s into ss;
exit when s%notfound;
dbms_output.put_line(ss);
end loop;
end;
declare
f_date varchar2(32);--积数日期
f_account varchar2(50);--积数账号
f_debit_money number(15,2);--积数借方余额
str varchar2(1000);--打印输出语句
s sys_refcursor;--游标
debit_dql varchar2(2000);--查询借方余额的sql
real_debit_money number(15,2);--计算出来的借方余额
errorException exception; --申明异常
errorCode number; --异常代号
errorMsg varchar2(1000); --异常信息
flag varchar2(10);
out_return varchar2(2000);--错误信息拼接
begin
DBMS_OUTPUT.ENABLE (buffer_size=>null);--表示没有限制.
str :='';--制空
open s for
select to_char(f.balance_date,'yyyy-MM-dd'),f.account,f.debit_bal from FC_ACCU_BALANCE f where f.interest_sign='0' order by f.account asc,f.balance_date asc;
loop
fetch s into f_date,f_account,f_debit_money;
exit when s%notfound;
str :='日期'||f_date||'账号'||f_account||'借方余额'||f_debit_money;
dbms_output.put_line(str);
dbms_output.put_line('--开始计算次日期实际借方余额--');
--重新计算借方金额
debit_dql := 'select badd.debit_add-asub.debit_sub
from(
select nvl(sum(b.trademoney),0) debit_sub
from books_bus_allocate_order b
where b.relationaccount ='||chr(39)||f_account||chr(39)||
' and b.billstatus='||chr(39)||100||chr(39)||
' and b.is_day_over ='||chr(39)||1||chr(39)||
' and b.allocatetype ='||chr(39)||1||chr(39)||
'and b.day_over <='||chr(39)||f_date||chr(39)||')asub,
(
select nvl(sum(b.trademoney),0) debit_add
from books_bus_allocate_order b
where b.relationaccount ='||chr(39)||f_account||chr(39)||
'and b.billstatus='||chr(39)||100||chr(39)||
' and b.is_day_over ='||chr(39)||1||chr(39)||
' and b.allocatetype ='||chr(39)||0||chr(39)||
' and b.day_over <= '||chr(39)||f_date||chr(39)||')badd';
execute immediate debit_dql into real_debit_money;
str :='实际借方余额'||real_debit_money;
dbms_output.put_line(str);
if real_debit_money<>f_debit_money then
dbms_output.put_line('**金额有差别**');
dbms_output.put_line('**开始更新余额**');
update FC_ACCU_BALANCE f set f.debit_bal=real_debit_money where f.account=f_account and to_char(f.balance_date,'yyyy-MM-dd')=f_date;
else
dbms_output.put_line('**正确**');
end if;
end loop;
dbms_output.put_line('**更新结束提交事务**');
commit; --提交事务
dbms_output.put_line('**结束**');
exception
when errorException then
rollback;
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
flag := 'false';
out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
dbms_output.put_line(out_return);
when others then
rollback;
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
flag := 'false';
out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
dbms_output.put_line(out_return);
end;
这里可以在数据库创建定时任务 job目录新建
CREATE OR REPLACE PROCEDURE JNMD_INIT_BAL
as
now_bank_acc varchar2(32);--当前银行账号
now_bank_type varchar2(1);--账号类型
now_acc_no varchar2(32);--保留
now_bif_code varchar2(32);
amt0 number(15,2);--初始余额
amt1 number(15,2);--当日收入明细蓝之和
amt2 number(15,2);--当日收入明细红之和
amt3 number(15,2);--当日支出明细蓝之和
amt4 number(15,2);--当日支出明细蓝之和
bal number(15,2);--计算后余额
nowDate varchar2(32);--当前日期
-------------------------------------------------------------------
s sys_refcursor;--游标
--------------------------------------------------------------------
status varchar2(1);--字典状态
over varchar2(1);--是否覆盖
str varchar2(1000);--打印输出语句
querysql varchar2(2000);--查询的sql
isHis integer;--判断是否有数据
--------------------------------------------------------------------
errorException exception; --申明异常
errorCode number; --异常代号
errorMsg varchar2(1000); --异常信息
flag varchar2(10);
out_return varchar2(2000);--错误信息拼接
begin
DBMS_OUTPUT.ENABLE (buffer_size=>null);--表示没有限制.
nowDate:=to_char(sysdate,'YYYY-MM-DD');--(当前日期)
str :='';--制空
status:='';
over:='';
--判断是否执行
querysql:='select b.status,b.over from BT_INITBAL_DICTIONARY b';
execute immediate querysql into status,over;
if status <> '1' then
return;
end if;
open s for
select b.bankacc,'1',bank_type as bank_type from bt_bankacc_app b
union
select bb.bank_acc,'2',bb.bif_code from bt_bank_acc bb where bb.bif_code='9999';
loop
--清楚数据
now_bank_acc :='';
now_bank_type:='';
now_bif_code:='';
amt0:=0;
amt1:=0;
amt2:=0;
amt3:=0;
amt4:=0;
bal:=0;
isHis:=0;
--首先查询账号
fetch s into now_bank_acc,now_bank_type,now_bif_code;
exit when s%notfound;
str:='账号:'||now_bank_acc||
'类型'||now_bank_type||
'日期'||nowDate||
'余额计算开始了...';
dbms_output.put_line(str);
if now_bank_acc is null or now_bank_acc = '' then
CONTINUE;--账号为空这里跳过循环执行
end if;
querysql:='select count(0) from (select bank_bal as baseAmt
from bis_acc_his_bal a
where a.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and a.bal_date =
(select min(b.bal_date)
from bis_acc_his_bal b
where b.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(b.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||')),'||
'(select sum(amt) as amt1
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||0||chr(39)||
'and rb_sign = '||chr(39)||2||chr(39)||
'and status <> -2),
(select sum(amt) as amt2
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||0||chr(39)||
'and rb_sign = '||chr(39)||1||chr(39)||
'and status <> -2),
(select sum(amt) as amt3
from bis_acc_his_dtl
where bank_acc ='||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||1||chr(39)||
'and rb_sign = '||chr(39)||2||chr(39)||
'and status <> -2),
(select sum(amt) as amt4
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||1||chr(39)||
'and rb_sign = '||chr(39)||1||chr(39)||
'and status <> -2)';
execute immediate querysql into isHis;
if isHis > 0 then --存在数据
querysql:='select nvl(baseAmt,0)baseAmt,nvl(amt1,0)amt1,nvl(amt2,0)amt2,nvl(amt3,0)amt3,nvl(amt4,0)amt4 from (select bank_bal as baseAmt
from bis_acc_his_bal a
where a.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and a.bal_date =
(select min(b.bal_date)
from bis_acc_his_bal b
where b.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(b.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||')),'||
'(select sum(amt) as amt1
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||0||chr(39)||
'and rb_sign = '||chr(39)||2||chr(39)||
'and status <> -2),
(select sum(amt) as amt2
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||0||chr(39)||
'and rb_sign = '||chr(39)||1||chr(39)||
'and status <> -2),
(select sum(amt) as amt3
from bis_acc_his_dtl
where bank_acc ='||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||1||chr(39)||
'and rb_sign = '||chr(39)||2||chr(39)||
'and status <> -2),
(select sum(amt) as amt4
from bis_acc_his_dtl
where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
'and cd_sign = '||chr(39)||1||chr(39)||
'and rb_sign = '||chr(39)||1||chr(39)||
'and status <> -2)';
dbms_output.put_line(querysql);
execute immediate querysql into amt0,amt1,amt2,amt3,amt4;
end if;
bal:=amt0+amt1+amt4-amt2-amt3;
str:='初始金额:'||amt0||
'收入蓝之和:'||amt1||
'收入红之和:'||amt2||
'支出蓝之和:'||amt3||
'支出红之和:'||amt4||
'计算余额:'||bal;
dbms_output.put_line(str);
isHis:=0;
querysql:='select count(1) from bis_acc_his_bal t where t.bank_acc='||chr(39)||now_bank_acc||chr(39)||' and to_char(t.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||')= '||chr(39)||nowDate||chr(39);
dbms_output.put_line(querysql);
execute immediate querysql into isHis;
if isHis = 0 then
str:='插入操作开始了...';
dbms_output.put_line(str);
insert into bis_acc_his_bal
(acc_no,
bank_acc,
bif_code,
bal_date,
get_date,
bank_bal,
--status,
--nextchecker,
--corp_id,
avail_bal)
values
(now_acc_no,now_bank_acc,now_bif_code, to_date(nowDate,'YYYY-MM-DD'),sysdate, bal,bal);
elsif over = '1' then
str:='更新操作开始了...';
dbms_output.put_line(str);
update bis_acc_his_bal set bank_bal=bal,avail_bal=bal where bank_acc=now_bank_acc and to_char( bal_date,'YYYY-MM-DD')=nowDate;
end if;
end loop;
dbms_output.put_line('**更新结束提交事务**');
commit; --提交事务
dbms_output.put_line('**结束**');
exception
when errorException then
rollback;
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
flag := 'false';
out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
dbms_output.put_line(out_return);
when others then
rollback;
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
flag := 'false';
out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;
dbms_output.put_line(out_return);
end;