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

    Question Unanswered: A new PL/SQL nut...

    I'm using Oracle, BTW...

    Now...: This one is working, thanks to andrewst.

    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;
    /

    But, I'd like this procedure to do one more thing for me:

    insert into trans values(v_next,p_amount,[todays date],p_ttype,p_pnr,'Y');

    ...given that the "p_ttype" attribute is sent with the exec command, and
    ..."v_next" is "select max(tnr)+1 from trans;", and
    ...[todays date] is SYSDATE

    Here's the output of "desc trans;"
    Name Null? Type
    ----------------------------------------- -------- ------------
    TNR NOT NULL NUMBER(5)
    AMOUNT NUMBER(7,2)
    TDATE DATE
    TTNR NUMBER(2)
    PNR NUMBER(2)
    OK CHAR(1)

    CAN SOMEONE MAKE THIS HAPPEN??? I'd be ever so grateful.

    BR, hermasito.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Just insert the record

    Hello hermasito,

    what do you think about

    create or replace procedure upd_balance (p_pnr number,p_amount number, p_next number, p_ttype number, p_today DATE)
    is
    v_next NUMBER := NULL;
    v_ttype NUMBER := NULL;
    begin

    update player
    set balance=balance+p_amount
    where pnr=p_pnr;

    select max(tnr) + 1 INTO v_next from trans;",

    IF v_next = p_next AND TO_CHAR(p_today, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD') THEN
    v_ttype := p_ttype;
    END IF;

    insert into trans values(p_next, p_amount, SYSDATE, v_ttype, p_pnt, 'Y');

    end upd_balance;

    Hope that helps a little bit ?

    Best regards
    Manfred
    (Alligator Company GmbH)
    http://www.alligatorsql.com

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

    Re: A new PL/SQL nut...

    Well, the insert statement is simple enough - but you MUST specify the columns like this:

    INSERT INTO trans (tnr, amount, tdate, ttnr, pnr, ok)
    VALUES( v_next, p_amount, SYSDATE, p_ttype, p_pnr, 'Y' );

    ... otherwise your code may fail some time in the future (e.g. if a new column is added to table TRANS).

    (Note: SYSDATE includes time. If you want JUST the date use TRUNC(SYSDATE))

    Now, how to get the v_next value. You should be aware that this MAX(trn)+1 solution is not a good idea at all unless your application is single user, because it locks out other users from inserting any data into TRANS until the first user commits. In a multi-user system, all your users will experience potentially long waits to get the next tnr value for their inserts. The correct solution is to use a SEQUENCE:

    CREATE SEQUENCE tnr_seq;

    Then in your trigger:

    INSERT INTO trans (tnr, amount, tdate, ttnr, pnr, ok)
    VALUES( tnr_seq.NEXTVAL, p_amount, SYSDATE, p_ttype, p_pnr, 'Y' );

    You will have to accept when you use sequences that there WILL be gaps in the numbers, they CANNOT be guaranteed to be sequential. You have to accept that this doesn't matter (it really doesn't matter -an ID is just an ID - but when people are used to gap-free numbers they THINK it matters!) It is this "no gap-free guarantee" feature that makes sequences work efficiently for multi-user systems.

    If after reading all the above you still want to use MAX(tnr)+1, then all you have to do is:

    SELECT MAX(trn)+1
    INTO v_next
    FROM trans;

    INSERT INTO trans( tnr,...) VALUES (v_next,...);

    But don't say I didn't warn you!

Posting Permissions

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