create withdrawal procedure in oracle pl/sql

Question

I want to create a withdrawal procedure in oracle pl/sql.

I have this table

ACCOUNT_NO BRANCH_NAME                                        AMOUNT_BALANCE
---------- -------------------------------------------------- --------------
       102 MA                                                      32900
       101 NA                                                      32000
       103 IA                                                      50000
       104 SA                                                      45000
       105 MSA                                                     20000

I try to use this code

CREATE OR REPLACE  PROCEDURE withdrawal_proc IS

con  number(6);
con1 number(6);
bal1 number(20);
bal2 number(20);

begin

con := &con;
bal1 := &bal;

select Account_No, Amount_Balance into con1, bal2 from Accounts where Account_No=con;

if (bal2 < bal1)

dbms_output.put_line('the amount enterd is more than the amount balance');
else

update Accounts set Amount_Balance=(bal1-bal2) where con =con1;
end if; 

dbms_output.put_line('Money has been Withdraw succesfully');
END;
/

but there is a Warning: Procedure created with compilation errors.

Answer 1

You can't use SQL*Plus-style variables such as &con and &bal1 in stored procedures. In this case the con and bal values should probably be passed to the procedure as parameters:

CREATE OR REPLACE  PROCEDURE withdrawal_proc(account_no_in IN NUMBER(6),
                                             bal_in IN NUMBER(20))
IS
  current_balance number(20);
BEGIN
  select Amount_Balance
    into current_balance
    from Accounts
    where Account_No = account_no_in;

  if current_balance < bal_in then
   dbms_output.put_line('The amount entered is more than the amount balance');
  else
    update Accounts
      set Amount_Balance = bal_in - current_balance
      where Account_No = account_no_in;
  end if; 

  dbms_output.put_line('Money has been withdrawn successfully');
END;

You also used improper syntax for your IF statement - compare the original code with the version above.

In addition, note that I suspect that the account balance computation may be incorrect. You may need to debug this a bit.

Best of luck.