Untitled
unknown
sql
19 days ago
6.5 kB
4
Indexable
-- Accommodations Table Creation CREATE OR REPLACE PROCEDURE generate_dummy_accommodations AS -- Variables v_batch_size INT := 1000; -- Collections for bulk insert TYPE t_accommodation_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_hotel_names IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER; TYPE t_cities IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER; TYPE t_stars IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE t_prices IS TABLE OF DECIMAL(10,2) INDEX BY BINARY_INTEGER; TYPE t_destination_ids IS TABLE OF INT INDEX BY BINARY_INTEGER; l_accommodation_ids t_accommodation_ids; l_hotel_names t_hotel_names; l_cities t_cities; l_stars t_stars; l_prices t_prices; l_destination_ids t_destination_ids; -- Preloaded DestinationIDs destination_ids t_destination_ids; -- Array for hotel names and cities TYPE name_array IS TABLE OF VARCHAR2(255); hotel_names name_array := name_array( 'Grand Hotel', 'Ocean View Resort', 'Mountain Lodge', 'City Center Inn', 'Luxury Suites', 'Budget Inn', 'Cozy Retreat', 'Elegant Hotel', 'Charming B&B', 'Modern Apartments', 'Seaside Escape', 'Countryside Inn', 'Urban Oasis', 'Historic Hotel', 'Family Friendly Resort', 'Romantic Getaway', 'Business Class Hotel', 'Pet Friendly Lodge', 'All-Inclusive Resort', 'Boutique Inn', 'Spa and Wellness Retreat', 'Adventure Hotel', 'Cultural Experience Lodge', 'Gourmet Dining Hotel', 'Eco-Friendly Lodge', 'Luxury Villa', 'Private Beach Resort', 'Mountain Cabin', 'Chalet Retreat', 'Rustic Lodge', 'City View Hotel', 'Skyline Suites', 'Waterfront Inn', 'Garden View Hotel', 'Countryside Retreat', 'Heritage Inn', 'Art Deco Hotel', 'Contemporary Suites', 'Classic Hotel', 'Vintage Inn', 'Modern Lodge', 'Chic Hotel', 'Trendy Apartments', 'Luxury Retreat', 'Coastal Inn', 'Nature Lodge', 'Scenic View Hotel', 'Relaxation Retreat', 'Wellness Hotel', 'Adventure Suites', 'Cultural Lodge', 'Gastronomic Hotel', 'Eco-Conscious Hotel', 'Luxury Cabin', 'Private Resort', 'Mountain View Lodge', 'Countryside Hotel', 'City Escape', 'Skyline Retreat', 'Waterfront Resort', 'Garden Lodge', 'Countryside B&B', 'Heritage Hotel', 'Artistic Retreat', 'Contemporary Inn', 'Classic Lodge', 'Vintage Hotel', 'Budget Stay', 'Comfort Inn', 'Royal Palace', 'Sunset Villas', 'Paradise Hotel', 'Heritage House', 'Modern Loft', 'Charming Cottage', 'Elegant Retreat', 'Cozy Cabin', 'Business Hotel', 'Family Resort', 'Boutique Hotel', 'Eco Lodge', 'Spa Retreat', 'Adventure Lodge', 'Historic Inn', 'Artistic Hotel', 'Cultural Center', 'Gourmet Hotel' ); cities name_array := name_array( 'New York', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix', 'Philadelphia', 'San Antonio', 'San Diego', 'Dallas', 'San Jose', 'Austin', 'Jacksonville', 'Fort Worth', 'Columbus', 'Charlotte', 'San Francisco', 'Indianapolis', 'Seattle', 'Denver', 'Washington', 'Boston', 'El Paso', 'Nashville', 'Detroit', 'Portland', 'Memphis', 'Oklahoma City', 'Las Vegas', 'Louisville', 'Baltimore', 'Milwaukee', 'Albuquerque', 'Tucson', 'Fresno', 'Sacramento', 'Kansas City', 'Long Beach', 'Mesa', 'Atlanta', 'Colorado Springs', 'Virginia Beach', 'Omaha', 'Raleigh', 'Miami', 'Cleveland', 'Tulsa', 'Oakland', 'Minneapolis', 'Wichita', 'New Orleans', 'Arlington', 'Bakersfield', 'Tampa', 'Honolulu', 'Anaheim', 'Aurora', 'Santa Ana', 'St. Louis', 'Riverside', 'Corpus Christi', 'Pittsburgh', 'Lexington', 'Anchorage', 'Stockton', 'Cincinnati', 'St. Paul', 'Greensboro', 'Lincoln', 'Buffalo', 'Plano', 'Henderson', 'Fort Wayne', 'Jersey City', 'Chula Vista', 'Orlando', 'St. Petersburg', 'Norfolk', 'Chandler', 'Laredo', 'Madison', 'Durham', 'Garland', 'Gilbert', 'Glendale', 'Hialeah', 'Reno', 'Chesapeake', 'Irving', 'Scottsdale', 'North Las Vegas', 'Fremont', 'Boise', 'Richmond', 'Baton Rouge', 'San Bernardino', 'Spokane', 'Des Moines', 'Modesto', 'Birmingham', 'Tacoma', 'Fontana', 'Oxnard', 'Moreno Valley', 'Fayetteville', 'Huntington Beach', 'Yonkers', 'Montgomery', 'Augusta', 'Aurora', 'Akron', 'Little Rock', 'Columbus', 'Grand Rapids', 'Salt Lake City', 'Huntsville', 'Tallmadge', 'Knoxville', 'Worcester' ); BEGIN -- Preload all DestinationIDs into memory SELECT DestinationID BULK COLLECT INTO destination_ids FROM Destinations; -- Check if data exists IF destination_ids.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No DestinationIDs found in the Destinations table.'); RETURN; END IF; -- Loop to create 3,000 Accommodations FOR batch IN 1..3 LOOP -- Generate v_batch_size records FOR i IN 1..v_batch_size LOOP -- Generate random data l_accommodation_ids(i) := accommodation_id_seq.NEXTVAL; l_hotel_names(i) := hotel_names(TRUNC(DBMS_RANDOM.VALUE(1, hotel_names.COUNT + 1))); l_cities(i) := cities(TRUNC(DBMS_RANDOM.VALUE(1, cities.COUNT + 1))); l_stars(i) := TRUNC(DBMS_RANDOM.VALUE(1, 6)); -- Random stars between 1 and 5 l_prices(i) := TRUNC(DBMS_RANDOM.VALUE(50, 500), 2); -- Random price between 50 and 500 l_destination_ids(i) := destination_ids(TRUNC(DBMS_RANDOM.VALUE(1, destination_ids.COUNT + 1))); END LOOP; -- Bulk insert data FORALL i IN l_accommodation_ids.FIRST..l_accommodation_ids.LAST INSERT INTO Accommodations ( AccommodationID, HotelName, City, Stars, PricePerNight, DestinationID ) VALUES ( l_accommodation_ids(i), l_hotel_names(i), l_cities(i), l_stars(i), l_prices(i), l_destination_ids(i) ); -- Commit each batch COMMIT; -- Progress report DBMS_OUTPUT.PUT_LINE('Completed batch ' || batch || ' of 3 (' || (batch * v_batch_size) || ' records)'); END LOOP; DBMS_OUTPUT.PUT_LINE('Successfully generated 3,000 Accommodations records.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); ROLLBACK; END generate_dummy_accommodations; / -- Execute the procedure BEGIN generate_dummy_accommodations(); END; /
Editor is loading...
Leave a Comment