oracle存储过程的例子,oracle存储过程简单例子

过程中直接执行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;