/* Copyright 2017 Amazon.com Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ create table seat ( sport_location_id NUMBER constraint s_sport_location_fk references sport_location(id), seat_level NUMBER(1), seat_section VARCHAR2(15), seat_row VARCHAR2(10), seat VARCHAR2(10), seat_type VARCHAR2(15) constraint seat_type_fk references seat_type(name), constraint seat_pk primary key (sport_location_id, seat_level, seat_section, seat_row, seat) ); create index seat_sport_location_idx on seat(sport_location_id); create or replace procedure generateSeats as CURSOR loc_cur IS SELECT id, seating_capacity, levels, sections FROM sport_location; TYPE seatTab IS TABLE OF seat%ROWTYPE INDEX BY BINARY_INTEGER; seat_tab seatTab; TYPE seatTypeTab IS TABLE OF seat_type.name%TYPE INDEX BY BINARY_INTEGER; seat_type_tab seatTypeTab; s_ct BINARY_INTEGER := 1; max_rows_per_section INTEGER := 25; min_rows_per_section INTEGER := 15; rows INTEGER; seats INTEGER; s_ref VARCHAR2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; tot_seats NUMBER(10); BEGIN -- load seat type percentage table FOR i IN 1..100 LOOP CASE WHEN i <= 5 THEN seat_type_tab(i) := 'luxury'; -- 5% luxury seats WHEN 5 < i AND i <= 35 THEN seat_type_tab(i) := 'premium'; -- 30% premium seats WHEN 35 < i AND i <= 89 THEN seat_type_tab(i) := 'standard'; -- 54% standard seats WHEN 89 < i AND i <= 99 THEN seat_type_tab(i) := 'sub-standard'; -- 10% sub-standard seats WHEN i = 100 THEN seat_type_tab(i) := 'obstructed'; -- 1% obstructed seats END CASE; END LOOP; FOR lrec in loc_cur LOOP tot_seats := 0; rows := ROUND(dbms_random.value(min_rows_per_section, max_rows_per_section)); seats := TRUNC(lrec.seating_capacity/(lrec.levels*lrec.sections*rows)+1); FOR i IN 1..lrec.levels LOOP FOR j IN 1..lrec.sections LOOP FOR k IN 1..rows LOOP FOR l in 1..seats LOOP tot_seats := tot_seats +1; IF tot_seats <= lrec.seating_capacity THEN seat_tab(s_ct).seat_level := i; seat_tab(s_ct).seat_section := j; seat_tab(s_ct).seat_row := SUBSTR(s_ref,k,1); seat_tab(s_ct).seat := l; seat_tab(s_ct).sport_location_id := lrec.id; seat_tab(s_ct).seat_type := seat_type_tab(ROUND(dbms_random.value(1,100))); s_ct := s_ct +1; IF s_ct >= 1000 THEN FORALL m IN INDICES OF seat_tab SAVE EXCEPTIONS INSERT /*+ APPEND */ INTO seat VALUES seat_tab(m); s_ct := 1; seat_tab.DELETE; END IF; END IF; END LOOP; END LOOP; END LOOP; END LOOP; FORALL m IN INDICES OF seat_tab SAVE EXCEPTIONS INSERT INTO seat VALUES seat_tab(m); seat_tab.DELETE; s_ct := 1; END LOOP; END; /