代码如下:
create procedure putdraw
(ID char,money number)
as
vbalance number,vname char;
begin
select Bmoney,name into vbalance,vname from bankcheck
where CIN=ID;
if(vname is null) then
raise_application_error(-20000,'please create a zhanghao');
end if;
update bankcheck set Bmoney=Bmoney+money
where CIN=ID;
end;
/要实现对bankcheck存钱,如果没有账户就需要重新开新账户,上面出错不知怎么处理,望各位高手指点下
create procedure putdraw
(ID char,money number)
as
vbalance number,vname char;
begin
select Bmoney,name into vbalance,vname from bankcheck
where CIN=ID;
if(vname is null) then
raise_application_error(-20000,'please create a zhanghao');
end if;
update bankcheck set Bmoney=Bmoney+money
where CIN=ID;
end;
/要实现对bankcheck存钱,如果没有账户就需要重新开新账户,上面出错不知怎么处理,望各位高手指点下
改成VARCHAR2(5)
merge into tablea
using tableb
on (conditions)
when matched then
update
set ...
when not matched then
insert into
values (...)
SQL> create procedure putdraw
2 (ID char,money number)
3 as
4 vbalance number;
5 vname char;
6 begin
7 select Bmoney,name into vbalance,vname from bankcheck
8 where CIN=ID;
9 if(vname is null) then
10 raise_application_error(-20000,'please create a zhanghao');
11 end if;
12 update bankcheck set Bmoney=Bmoney+money
13 where CIN=ID;
14 end;
15 /
Procedure created
SQL> select * from bankcheck;
BMONEY NAME CIN
---------- -------------------- ----------
100 a 1
200 a 2
SQL> exec putdraw(1,200); --执行存储过程进行更新
PL/SQL procedure successfully completed
SQL> select * from bankcheck; --更新成功
BMONEY NAME CIN
---------- -------------------- ----------
300 a 1
200 a 2
(ID char, money number)
as
vbalance number;
begin
select Cmoney into vbalance from credit
where cid=ID;
if(vbalance<money) then
raise_application_error(-20000,'money is not enough');
end if;
update credit set cmoney=cmoney-money
where cid=ID;
end;
/我是参照这个来做的,其实上面都不懂,这个是正确的取钱代码,我那存钱的不知哪错的,去括号还是错的,改成VARCHAR2(5)也错的
2 putdraw('3',200);
3 end;
4 /
begin
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"XUXIAO5936.PUTDRAW", line 7
ORA-06512: 在line 2
什么问题?过程创建成功了
vname 的定义太小 定义大点 定义成你要 into 的那个字段的大小
varchar2(插入字段的大小)
2 (ID char,money number)
3 as
4 vbalance number;
5 vname char; -- 你把这里改成 vname varchar2(长度和你要查的 bankcheck表里的 name 相等就可以了 )
6 begin
7 select Bmoney,name into vbalance,vname from bankcheck
8 where CIN=ID;
9 if(vname is null) then
10 raise_application_error(-20000,'please create a zhanghao');
11 end if;
12 update bankcheck set Bmoney=Bmoney+money
13 where CIN=ID;
14 end;
2 putdraw('1',100);
3 end;
4 /PL/SQL procedure successfully completed.我的是可以执行成功的
你最好把你表里字段的类型和创建存储过程的代码都贴出来看看
vname varchar(20);
SQL->begin
2 putdraw('1',100);
3 end;
4 /
是正确的,那错误的怎么写??哪位大大指导下?
---------- -------------------- ----------
500 a 1
200 a 2
SQL> insert into bankcheck(bmoney,cin) values(100,3);1 row created.SQL> select * from bankcheck; BMONEY NAME CIN
---------- -------------------- ----------
500 a 1
200 a 2
100 3SQL> exec putdraw(3,100);
BEGIN putdraw(3,100); END;*
ERROR at line 1:
ORA-20000: please create a zhanghao
ORA-06512: at "SCOTT.PUTDRAW", line 10
ORA-06512: at line 1
其实你存储过程里的判断条件写的是有些问题的
如果账号不存在 那么在前面的select...into...就会报错
根本不会执行到下面的raise_application_error
我举得例子只是根据你给出的条件来写的 即vname is null
create procedure zhuandraw
(ID1 char,ID2 varchar2(10), money number)
as
vbalance1 number;
vbalance2 number;
vname varchar2(10);
begin
select Cmoney into vbalance1 from credit where cid=ID1;
select Bmoney into vbalance2 from bankcheck where cin=ID2;
update credit set cmoney=cmoney-money where cid=ID1;
update bankcheck set bmoney=bmoney+boney where cin=ID2;
end;
/
有什么错误??