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.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|||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!