PL/SQL Procedure - Ticket system

Question

I'm trying to make a Procedure in PL/SQL for a ticket system for my website. I want a simple procedure that saves some data into the database. It will have name of the user (random VARCHAR2) and a number and the date of today as input.

When this procedure is called it will automatically higher a number in the database (Maybe primary key) and add the input into the database. But I have no idea how to make something like this with the option to have more people access it at once.

Can anyone help please?

For the people that want to know what I have already.

CREATE OR REPLACE PROCEDURE VoegBoeteToe
(v_speler IN number, v_date IN date, v_boete IN number)
IS VoegBoeteToe
DECLARE
v_nextNum NUMBER;

BEGIN
SELECT BETALINGSNR
INTO v_nextNum
FROM BOETES
ORDER BY BETALINGSNR DESC;

v_nextNum := v_nextNum + 1;

INSERT INTO BOETES VALUES(v_nextNum,v_speler,v_date,v_boete);

end;
/

possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) — Noel, Jul 07 '14 at 10:41

Bob Jarvis - Слава Україні · Answer 1 · 2014-07-07T17:03:48.553

The fourth line of your procedure should be IS instead of IS VoegBoeteToe. In addition, remove the fifth line (DECLARE), which is not needed in a procedure. And use a sequence to derive the sequential number safely.

So, when all's said and done, your procedure should look something like:

CREATE SEQUENCE BOETES_SEQ;

CREATE OR REPLACE PROCEDURE VoegBoeteToe
(v_speler IN number, v_date IN date, v_boete IN number)
IS
BEGIN
  INSERT INTO BOETES VALUES(BOETES_SEQ.NEXTVAL,v_speler,v_date,v_boete);  
end;

It would also be a very good idea to include the list of field names in the BOETES table into which you're inserting values. This will make life better for anyone who has to maintain your code, and may save you from making a few errors.

Share and enjoy.

score 0 · Answer 2 · answered Jul 07 '14 at 10:51

0

You need to create SEQUENCE

CREATE SEQUENCE DUMMY_SEQ
/

Then in Your code You can use something like that:

select DUMMY_SEQ.NEXTVAL into myVar from dual;

answered Jul 07 '14 at 10:51

Wojtek Mlodzianowski

398
2
17

Thanks for the comment, but it's not about the interval. It's my procedure that gives an error. A column not allowed here error. – Jos Jul 07 '14 at 10:57
Which line is the error at? Btw. Your select will throw TOO_MANY_ROWS exception if there will be more than one row in the table – Wojtek Mlodzianowski Jul 07 '14 at 11:40

score 0 · Answer 3 · answered Jul 07 '14 at 11:02

First, create a sequence. A sequence generates an ever incrementing number whenever it is invoked. See this for some nice examples. The documentation could be useful too.

CREATE SEQUENCE user_sequence
START WITH 1000                   -- your sequence will start with 1000
INCREMENT BY 1;                   -- each subsequent number will increment by 1

Then,

SELECT user_sequence.NEXTVAL INTO v_nextNum FROM DUAL;

PL/SQL Procedure - Ticket system

3 Answers3