create function transfer (account_source int, account_dest int, amount decimal)
returns BOOLEAN
BEGIN
DECLARE currency_source VARCHAR(20);
DECLARE currency_dest VARCHAR(20);
DECLARE current_balance DECIMAL;
DECLARE new_amount DECIMAL;
select balance into current_balance
from accounts
where number = account_source;
IF amount > current_balance THEN
return false;
END IF;
select currency into currency_source
from accounts
where number = account_source;
select currency into currency_dest
from accounts
where number = account_dest;
IF STRCMP(currency_source,'dollar') = 0 THEN
IF STRCMP(currency_dest,'euro') = 0 THEN
set new_amount := amount / 1.23;
ELSE IF STRCMP(currency_dest,'lbp') = 0 THEN
set new_amount := amount / 1500;
END IF;
end if;
IF STRCMP(currency_source,'euro') = 0 THEN
IF STRCMP(currency_dest,'dollar') = 0 THEN
set new_amount := amount * 1.23;
ELSE IF STRCMP(currency_dest,'lbp') = 0 THEN
set new_amount := (amount * 1.23) * 1500;
END IF;
end if;
IF STRCMP(currency_source,'lbp') = 0 THEN
IF STRCMP(currency_dest,'dollar') = 0 THEN
set new_amount := amount / 1500;
ELSE IF STRCMP(currency_dest,'euro') = 0 THEN
set new_amount := (amount / 1.23) / 1500;
END IF;
END IF;
update accounts
set balance = balance - amount
where number = account_source;
update accounts
set balance = balance + new_amount
where number = account_dest;
return true;
END;
Trying to create this function is giving an error #1064 at line 1, so i can't guess where is the actual mistake i did.
i created 'withdraw' function before it, containing almost same code and it worked, the only difference between transfer function and withdraw function is declaration of varchar(20) type at its begining.
Any help would be appreciated...