Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Question Unanswered: What's wrong with this pl/sql procedure???

    create or replace procedure upd_balance(
    p_pnr number(2),p_amount number(7,2))

    is
    v_balance number(7,2);
    v_pnr number(2) := p_pnr;
    v_amount number(7,2) := p_amount;
    begin
    select balance into v_balance
    from player
    where pnr=v_pnr;

    v_balance := v_balance+v_amount;

    update player
    set balance=v_balance
    where pnr=v_nr;
    end upd_balance;
    /

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

    Re: What's wrong with this pl/sql procedure???

    Well, you have not shown the error message which would save me guessing! (Use SHOW ERROR) However, I can see the following errors:

    1) you cannot specify scale and precision values for parameters, i.e. should just say:

    create or replace procedure upd_balance(
    p_pnr number,p_amount number)...

    2) Variable v_nr is not declared - you meant v_pnr!

    BTW, why are you copying the parameters into variables anyway? And why not just update without selecting first? Why not just:

    create or replace procedure upd_balance(
    p_pnr number,p_amount number)
    is
    begin
    update player
    set balance=balance+p_amount
    where pnr=p_pnr;
    end upd_balance;
    /

    Also, beware of NULL values - you may need to code:

    set balance=NVL(balance,0)+NVL(p_amount,0)

  3. #3
    Join Date
    Dec 2002
    Posts
    3

    Lightbulb Re: What's wrong with this pl/sql procedure???

    Thanks, dude!

Posting Permissions

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