Untitled
unknown
sql
a month ago
12 kB
3
Indexable
-- Customers Table Creation (Simulation)
CREATE OR REPLACE PROCEDURE simulate_dummy_customers AS
TYPE name_array IS TABLE OF VARCHAR2(50);
first_names name_array := name_array
('James', 'Mary', 'John', 'Patricia', 'Robert', 'Jennifer', 'James', 'Mary', 'John', 'Patricia',
'Robert', 'Jennifer', 'Michael', 'Linda', 'William', 'Elizabeth',
'David', 'Barbara', 'Richard', 'Susan', 'Joseph', 'Jessica', 'Thomas', 'Sarah', 'Charles', 'Karen',
'Christopher', 'Nancy', 'Daniel', 'Lisa', 'Matthew', 'Margaret', 'Anthony', 'Betty', 'Mark', 'Sandra',
'Donald', 'Ashley', 'Steven', 'Dorothy', 'Paul', 'Kimberly', 'Andrew', 'Emily', 'Joshua', 'Donna',
'Kenneth', 'Michelle', 'Kevin', 'Carol', 'Brian', 'Amanda', 'George', 'Melissa', 'Edward', 'Deborah',
'Ronald', 'Stephanie', 'Timothy', 'Rebecca', 'Jason', 'Laura', 'Jeffrey', 'Sharon', 'Ryan', 'Cynthia',
'Jacob', 'Kathleen', 'Gary', 'Helen', 'Nicholas', 'Amy', 'Eric', 'Shirley', 'Jonathan', 'Angela',
'Stephen', 'Anna', 'Larry', 'Brenda', 'Justin', 'Pamela', 'Scott', 'Nicole', 'Brandon', 'Ruth',
'Benjamin', 'Katherine', 'Samuel', 'Samantha', 'Gregory', 'Christine', 'Frank', 'Emma', 'Alexander', 'Catherine',
'Raymond', 'Debra', 'Patrick', 'Virginia', 'Jack', 'Rachel', 'Dennis', 'Carolyn', 'Jerry', 'Janet',
'Tyler', 'Alice', 'Aaron', 'Diane', 'Henry', 'Julie', 'Douglas', 'Heather', 'Peter', 'Teresa',
'Walter', 'Doris', 'Harold', 'Gloria', 'Kyle', 'Evelyn', 'Carl', 'Jean', 'Arthur', 'Martha',
'Gerald', 'Jacqueline', 'Roger', 'Kathryn', 'Keith', 'Frances', 'Lawrence', 'Ann', 'Terry', 'Alice',
'Sean', 'Diana', 'Christian', 'Natalie', 'Austin', 'Lori', 'Joe', 'Tina', 'Jesse', 'Sara',
'Willie', 'Janice', 'Billy', 'Julia', 'Jordan', 'Theresa', 'Albert', 'Marie', 'Dylan', 'Madison',
'Bruce', 'Grace', 'Gabriel', 'Cheryl', 'Logan', 'Annette', 'Randy', 'Hannah', 'Roy', 'Darlene',
'Eugene', 'Diana', 'Russell', 'Judy', 'Bobby', 'Megan', 'Victor', 'Kathy', 'Martin', 'Jessica',
'Eugene', 'Vicki', 'Russell', 'Judy', 'Bobby', 'Megan', 'Victor', 'Kathy', 'Martin', 'Jessica',
'Ernest', 'Katherine', 'Philip', 'Christine', 'Todd', 'Debbie', 'Jared', 'Charlotte', 'Craig', 'Mildred',
'Dale', 'Kathy', 'Dustin', 'Tiffany', 'Javier', 'Kristen', 'Lance', 'Kerry', 'Erik', 'Tiffany',
'Alfred', 'Kristen', 'Lance', 'Kerry', 'Damon', 'Renee', 'Erik', 'Tiffany', 'Alfred', 'Kristen',
'Lance', 'Kerry', 'Damon', 'Renee', 'Erik', 'Tiffany', 'Alfred', 'Kristen', 'Lance', 'Kerry',
'Derek', 'Joan', 'Dustin', 'Ethel', 'Jorge', 'Marlene', 'Gabriel', 'Lydia', 'Carl', 'Sally',
'Jesse', 'Martha', 'Dale', 'Kathy', 'Erik', 'Tiffany', 'Alfred', 'Kristen', 'Lance', 'Kerry',
'Javier', 'Martha', 'Dale', 'Megan', 'Leonard', 'Kathy', 'Wesley', 'Diana', 'Rafael', 'Lillian',
'Dustin', 'Samantha', 'Javier', 'Victoria', 'Ernest', 'Natalie', 'Bradley', 'Diana', 'Derrick', 'Megan',
'Damon', 'Misty', 'Erik', 'Tiffany', 'Alfred', 'Kristen', 'Lance', 'Kerry', 'Damon', 'Renee');
last_names name_array := name_array
('Smith', 'Johnson', 'Williams', 'Jones', 'Brown', 'Davis','Smith', 'Johnson', 'Williams', 'Jones', 'Brown', 'Davis', 'Miller', 'Wilson', 'Moore', 'Taylor',
'Anderson', 'Thomas', 'Jackson', 'White', 'Harris', 'Martin', 'Thompson', 'Garcia', 'Martinez', 'Robinson',
'Clark', 'Rodriguez', 'Lewis', 'Lee', 'Walker', 'Hall', 'Allen', 'Young', 'Hernandez', 'King',
'Wright', 'Lopez', 'Hill', 'Scott', 'Green', 'Adams', 'Baker', 'Gonzalez', 'Nelson', 'Carter',
'Mitchell', 'Perez', 'Roberts', 'Turner', 'Phillips', 'Campbell', 'Parker', 'Evans', 'Edwards', 'Collins',
'Stewart', 'Sanchez', 'Morris', 'Rogers', 'Reed', 'Cook', 'Morgan', 'Bell', 'Murphy', 'Bailey',
'Rivera', 'Cooper', 'Richardson', 'Cox', 'Howard', 'Ward', 'Torres', 'Peterson', 'Gray', 'Ramirez',
'James', 'Watson', 'Brooks', 'Kelly', 'Sanders', 'Price', 'Bennett', 'Wood', 'Barnes', 'Ross',
'Henderson', 'Coleman', 'Jenkins', 'Perry', 'Powell', 'Long', 'Patterson', 'Hughes', 'Flores', 'Washington',
'Butler', 'Simmons', 'Foster', 'Gonzales', 'Bryant', 'Alexander', 'Russell', 'Griffin', 'Diaz', 'Hayes',
'Myers', 'Ford', 'Hamilton', 'Graham', 'Sullivan', 'Wallace', 'Woods', 'Cole', 'West', 'Jordan',
'Owens', 'Reynolds', 'Fisher', 'Ellis', 'Harrison', 'Gibson', 'McDonald', 'Cruz', 'Marshall', 'Ortiz',
'Gomez', 'Murray', 'Freeman', 'Wells', 'Webb', 'Simpson', 'Stevens', 'Tucker', 'Porter', 'Hunter',
'Hicks', 'Crawford', 'Henry', 'Boyd', 'Mason', 'Morales', 'Kennedy', 'Warren', 'Dixon', 'Ramos',
'Reyes', 'Burns', 'Gordon', 'Shaw', 'Holmes', 'Rice', 'Robertson', 'Hunt', 'Black', 'Daniels',
'Palmer', 'Mills', 'Nichols', 'Grant', 'Knight', 'Ferguson', 'Rose', 'Stone', 'Hawkins', 'Dunn',
'Perkins', 'Hudson', 'Spencer', 'Gardner', 'Stephens', 'Payne', 'Pierce', 'Berry', 'Matthews',
'Arnold', 'Wagner', 'Willis', 'Ray', 'Watkins', 'Olson', 'Carroll', 'Duncan', 'Snyder', 'Hart',
'Cunningham', 'Bradley', 'Lane', 'Andrews', 'Ruiz', 'Harper', 'Fox', 'Riley', 'Armstrong', 'Carpenter',
'Weaver', 'Greene', 'Lawrence', 'Ester', 'Vasquez', 'Higgins', 'Caldwell', 'Soto', 'Schmidt',
'Hartman', 'Dawson', 'Mendez', 'Bush', 'Franco', 'Vaughn', 'Pope', 'Murray', 'Hodge', 'Hodge');
streets name_array := name_array('Main Street', 'Oak Avenue', 'Maple Drive', 'Cedar Lane', 'Pine Street', 'Elm Road', 'Washington Avenue',
'Park Place', 'Lake View', 'Highland Avenue', 'Sunset Drive', 'Forest Lane', 'River Road', 'Mountain View',
'Meadow Lane', 'Valley Drive', 'Spring Street', 'Mill Road', 'Church Street', 'Bridge Road',
'North Street', 'South Avenue', 'East Road', 'West Boulevard', 'Willow Lane', 'Madison Street',
'Jefferson Avenue', 'Adams Road', 'Lincoln Street', 'Franklin Avenue', 'Wilson Road', 'Jackson Street',
'Grant Avenue', 'Central Avenue', 'Pleasant Street', 'Colonial Drive', 'Prospect Street', 'Broadway',
'Market Street', 'Front Street', 'Second Avenue', 'Third Street', 'Fourth Road', 'Fifth Avenue', 'Sixth Street',
'Seventh Avenue', 'Eighth Street', 'Ninth Road', 'Tenth Avenue', 'Eleventh Street', 'Twelfth Avenue',
'Thirteenth Street', 'Fourteenth Avenue', 'Fifteenth Street', 'Sixteenth Avenue', 'Seventeenth Street',
'Eighteenth Avenue', 'Nineteenth Street', 'Twentieth Avenue', 'Twenty-First Street', 'Twenty-Second Avenue',
'Twenty-Third Street', 'Twenty-Fourth Avenue', 'Twenty-Fifth Street', 'Twenty-Sixth Avenue', 'Twenty-Seventh Street',
'Twenty-Eighth Avenue', 'Twenty-Ninth Street', 'Thirtieth Avenue', 'Thirty-First Street', 'Thirty-Second Avenue',
'Thirty-Third Street', 'Thirty-Fourth Avenue', 'Thirty-Fifth Street', 'Thirty-Sixth Avenue', 'Thirty-Seventh Street');
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');
states name_array := name_array('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'FL', 'GA',
'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD',
'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ',
'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC',
'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY',
'DC', 'PR', 'GU', 'VI', 'MP', 'AS', 'FM', 'MH', 'PW', 'AK',
'AA', 'AE', 'AP', 'AE', 'AP', 'AA', 'AE', 'AP', 'AA', 'AE',
'AP', 'AA', 'AE', 'AP', 'AA', 'AE', 'AP', 'AA', 'AE');
domains name_array := name_array('gmail.com', 'yahoo.com', 'hotmail.com', 'outlook.com', 'aol.com',
'icloud.com', 'mail.com', 'protonmail.com', 'zoho.com', 'yandex.com');
-- Variables
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
v_email VARCHAR2(100);
v_phone VARCHAR2(20);
v_birth_date DATE;
v_address VARCHAR2(200);
v_loyalty_points NUMBER;
v_batch_size INT := 10;
-- Timing variables
start_time NUMBER;
end_time NUMBER;
elapsed_time NUMBER;
BEGIN
-- Record the start time
start_time := DBMS_UTILITY.GET_TIME;
-- Loop to simulate 1,000,000 customers
FOR batch IN 1..10000 LOOP
-- Generate v_batch_size records
FOR i IN 1..v_batch_size LOOP
-- Generate random data
v_first_name := first_names(TRUNC(DBMS_RANDOM.VALUE(1, first_names.COUNT + 1)));
v_last_name := last_names(TRUNC(DBMS_RANDOM.VALUE(1, last_names.COUNT + 1)));
v_email := LOWER(v_first_name || '.' || v_last_name || '@' || domains(TRUNC(DBMS_RANDOM.VALUE(1, domains.COUNT + 1))));
v_phone := '(' || TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(100, 999))) || ') ' || TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(100, 999))) || '-' || TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1000, 9999)));
v_birth_date := TRUNC(SYSDATE) - TRUNC(DBMS_RANDOM.VALUE(18 * 365, 90 * 365));
v_address := TRUNC(DBMS_RANDOM.VALUE(1, 9999)) || ' ' || streets(TRUNC(DBMS_RANDOM.VALUE(1, streets.COUNT + 1))) || ', ' || cities(TRUNC(DBMS_RANDOM.VALUE(1, cities.COUNT + 1))) || ', ' || states(TRUNC(DBMS_RANDOM.VALUE(1, states.COUNT + 1)));
v_loyalty_points := TRUNC(DBMS_RANDOM.VALUE(0, 10000));
END LOOP;
-- Simulate progress report
DBMS_OUTPUT.PUT_LINE('Simulated batch ' || batch || ' of 10 (' || (batch * v_batch_size) || ' records)');
-- Commented out actual bulk insert
/*
FORALL i IN l_customer_ids.FIRST..l_customer_ids.LAST
INSERT INTO Customers (
CustomerID,
FullName,
Email,
PhoneNumber,
DateOfBirth,
Address,
LoyaltyPoints
) VALUES (
l_customer_ids(i),
l_full_names(i),
l_emails(i),
l_phone_numbers(i),
l_birth_dates(i),
l_addresses(i),
l_loyalty_points(i)
);
-- Commit each batch
COMMIT;
*/
END LOOP;
-- Record the end time
end_time := DBMS_UTILITY.GET_TIME;
-- Calculate elapsed time in seconds
elapsed_time := (end_time - start_time) / 100;
DBMS_OUTPUT.PUT_LINE('Execution Time (Simulation): ' || elapsed_time || ' seconds');
END;
/
-- Execute the procedure
BEGIN
simulate_dummy_customers();
END;
/Editor is loading...
Leave a Comment