oracle 一个存储过程的问题，在线等

[csuxp2008]

create procedure transfer(in_account int,out_account int,amount float)
as declare
totalDeposit float;
begin
select total into totalDepostit from account where accountnum = out_account;
if totalDeposit is null then
rollback;
return;
end if;
if totalDeposit < amount then
rollback;
return;
end if;
update account set total = total - amount where accountnum = out_account;
update account set total = total + amount where accountnum = in_account;
commit;
end;

下面是show error：
PROCEDURE TRANSFER 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------
2/8 PLS-00103: 出现符号 "DECLARE"在需要下列之一时：
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language

这是一个银行帐户转帐的procedure，哪儿大侠指点下，哪儿错了，该怎么改？

[kk19840210]

create procedure transfer(in_account int,out_account int,amount float)
as

totalDeposit float;
begin
select total into totalDepostit from account where accountnum = out_account;
if totalDeposit is null then
rollback;
return;
end if;
if totalDeposit < amount then
rollback;
return;
end if;
update account set total = total - amount where accountnum = out_account;
update account set total = total + amount where accountnum = in_account;
commit;
end;

[superhsj]

试试

create procedure transfer(in_account in number,out_account in number,amount in number) 
    as 
          totalDeposit number; 
    begin 
        select total into totalDepostit from account where accountnum = out_account; 
        if totalDeposit >= amount then     
             update account set total = total - amount where accountnum = out_account; 
             update account set total = total + amount where accountnum = in_account; 
             commit; 
        else
             rollback;
        end if; 
    end; 

[阿三]

create or replace procedure transfer(in_account int,out_account int,amount float)
as
totalDepostit float;
begin
select total into totalDepostit from account where accountnum = out_account;
if totalDepostit is null then
--rollback; 这里不用回滚
return;
end if;
if totalDepostit < amount then
--rollback;
return;
end if;
update account set total = total - amount where accountnum = out_account;
update account set total = total + amount where accountnum = in_account;
commit;
　　exception
when others then rollback; --出错回滚
end;

[csuxp2008]

totalDeposit 需要声明

[superhsj]

oracle语法就是如此，不用declare

[csuxp2008]

如果去掉declare，错误如下

PROCEDURE TRANSFER 出现错误:

LINE/COL ERROR
-------- -------------------------------------------
5/9 PL/SQL: SQL Statement ignored
5/27 PLS-00201: 必须声明标识符 'TOTALDEPOSTIT'
5/41 PL/SQL: ORA-00904: : 标识符无效

[csuxp2008]

哪位大虾知道原因，如何解决？

[mrdavidtian]

create procedure transfer(in_account int,out_account int,amount float)
as declare
totalDeposit float;
begin
select total into totalDepostit from account where accountnum = out_account;
if totalDeposit is null then
rollback;
return;
end if;
if totalDeposit < amount then
rollback;
return;
end if;
update account set total = total - amount where accountnum = out_account;
update account set total = total + amount where accountnum = in_account;
commit;
end;
把第二行的DECLARE去掉

[superhsj]

晕啊，你声明的和用的写的不一样，差个t，改过来就可以了
[Quote=引用 6 楼 csuxp2008 的回复:]
如果去掉declare，错误如下

PROCEDURE TRANSFER 出现错误:

LINE/COL ERROR
-------- -------------------------------------------
5/9 PL/SQL: SQL Statement ignored
5/27 PLS-00201: 必须声明标识符 'TOTALDEPOSTIT'
5/41 PL/SQL: ORA-00904: : 标识符无效
[/Quote]

[csuxp2008]

确实，还是楼上的细心，真没发现

[semifish]

哈哈，多了一个t