Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unhappy Unanswered: what's wrong with this function

    I wrote a function to update a table record, Before the update clause, everything works fine, but at last it can't execute update clause. WHat's wrong with my function? Thank you for any advices.

    FUNCTION deposit_checking(
    acct_num IN Checking_Accounts.account_num%TYPE,
    deposit_amt IN NUMBER,
    overdraft OUT NUMBER
    )
    RETURN NUMBER IS

    BEGIN
    BEGIN
    SELECT distinct a.overdraft_amount, a.balance
    into overdraft_amt, cust_balance
    from Customers c, Has_Account b, Checking_Accounts a
    where c.customer_num = b.customer_num
    and b.account_num = a.account_num
    and a.account_num=acct_num;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN null;
    END;
    IF (cust_balance=0) and (overdraft_amt=0) THEN
    overdraft_amt := 0;
    cust_balance :=deposit_amt;
    elsif (cust_balance=0) and (overdraft_amt > 0) then
    overdraft_amt := overdraft_amt-deposit_amt;
    cust_balance := 0;
    ELSIF (overdraft_amt>=deposit_amt) then
    overdraft_amt := overdraft_amt-deposit_amt;
    cust_balance := 0;
    elsIF(overdraft<deposit_amt)then
    overdraft_amt:=0;
    cust_balance:=deposit_amt-overdraft_amt;
    else
    cust_balance := cust_balance+deposit_amt;
    overdraft_amt := 0;
    END IF;
    --update the checking_accounts record
    UPDATE Checking_Accounts
    SET balance = cust_balance,
    overdraft_amount = overdraft_amt
    where account_num = acct_num;


    overdraft := overdraft_amt;
    RETURN cust_balance;
    END deposit_checking;

  2. #2
    Join Date
    May 2003
    Posts
    12

    Re: what's wrong with this function

    If u r using the function in a pl/sql block then it works. Or else if u r using the function in the select statement in SQL> prompt then it won't as INSERT/UPDATE/DELETE will won't give guarantee execution in function.

    Although if u require this update statement, convert the function as procedure as try.

    Actually, I hv not gone through the logic of the program.

    bye,
    Raghavendra.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: what's wrong with this function

    Please be more explicit about "can't execute update". Is there an error message or what?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •