Untitled

-- 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;
/