By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE exhibition
(
exhibition_date DATE
PRIMARY KEY
NOT NULL,
exhibition_name VARCHAR2(50)
NOT NULL,
exhibition_director VARCHAR2(50)
NOT NULL,
exhibition_location VARCHAR2(50)
NOT NULL
);
CREATE TABLE eventtype
(
eventtype_code CHAR(3)
PRIMARY KEY
NOT NULL,
eventtype_desc VARCHAR2(50)
NOT NULL
);
CREATE TABLE event
(
event_id NUMBER(6)
NOT NULL
PRIMARY KEY,
exhibition_date REFERENCES exhibition(exhibition_date)
NOT NULL,
eventtype_code REFERENCES eventtype(eventtype_code)
NOT NULL,
event_starttime DATE
NOT NULL
);
CREATE TABLE "GROUP"
(
group_id NUMBER(3)
NOT NULL
PRIMARY KEY,
group_name VARCHAR2(30)
UNIQUE
NOT NULL,
group_no_members NUMBER(2)
NOT NULL,
event_id REFERENCES event(event_id)
NOT NULL,
char_id NUMBER(3)
);
CREATE SEQUENCE group_seq;
CREATE TABLE entry
(
event_id REFERENCES event(event_id)
NOT NULL,
entry_no NUMBER(5)
PRIMARY KEY
NOT NULL,
entry_starttime DATE,
entry_finishtime DATE,
part_no NUMBER(5)
NOT NULL,
group_id NUMBER(3)
REFERENCES "GROUP"(group_id),
char_id NUMBER(3)
);
CREATE SEQUENCE entry_seq;
CREATE OR REPLACE PROCEDURE event_registration
(new_part_no IN NUMBER,
new_exhibition_date IN DATE,
new_eventtype_desc IN VARCHAR2,
new_group_name IN VARCHAR2,
output OUT VARCHAR2)
AS
v_event_id EVENT.EVENT_ID%TYPE;
v_group_id "GROUP".GROUP_ID%TYPE;
BEGIN
SELECT e.event_id
INTO v_event_id
FROM event e
INNER JOIN eventtype et
ON (e.eventtype_code = et.eventtype_code)
WHERE exhibition_date = new_exhibition_date
AND eventtype_desc = new_eventtype_desc;
IF new_group_name IS NOT NULL THEN
MERGE INTO "GROUP" g
USING DUAL
ON (g.group_name = new_group_name)
WHEN MATCHED THEN
UPDATE
SET group_no_members = group_no_members + 1
WHEN NOT MATCHED THEN
INSERT (
group_id,
group_name,
group_no_members,
event_id,
char_id
) VALUES (
GROUP_SEQ.NEXTVAL,
new_group_name,
1,
SELECT * FROM USER_ERRORS;