Procedure to add random customers to the Customers2 table for testing purposes. The first text file shows how to do it all with Local Variables (note there are no @s anywhere). First text file shows how to do this with Local Variable (i) and a host of Session Variables (the stuff with the @ in front of it). I've read that it's best to use Local variables within procedures? Find out if this is true and why.
-- SHOWS HOW TO CREATE A PROCDURE THAT RANDOMLY SELECTS THREE PIECES OF INFORMATION FROM MULTIPLE TABLES AND THEN USES THOSE VALUES TO POPULATE A TABLE uses SESSION variables
DELIMITER $
CREATE PROCEDURE populate_Customers2()
BEGIN
SET @last_name_numeric = (SELECT FLOOR((RAND() * (51-1+1))+1));
SET @first_name_numeric = (SELECT FLOOR((RAND() * (48-1+1))+1));
SET @street_direction_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET @street_type_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET @street_numeric = (SELECT FLOOR((RAND() * (58-1+1))+1));
SET @city_numeric = (SELECT FLOOR((RAND() * (47-1+1))+1));
SET @state_numeric = (SELECT FLOOR((RAND() * (52-1+1))+1));
SET @street_direction_text = (SELECT (Direction)
FROM random_so_street_direction
WHERE DirectionID = @street_direction_numeric);
SET @last_name_text = (SELECT (LastName)
FROM random_so_last_name
WHERE LastNameID = @last_name_numeric);
SET @first_name_text = (SELECT (FirstName)
FROM random_so_first_name
WHERE FirstNameID = @first_name_numeric);
SET @street_type_text = (SELECT (StreetType)
FROM random_so_street_type
WHERE StreetTypeID = @street_type_numeric);
SET @street_text = (SELECT (Street)
FROM random_so_street
WHERE StreetID = @street_numeric);
SET @city_text = (SELECT (City)
FROM random_so_city
WHERE CityID = @city_numeric);
SET @state_text = (SELECT (State)
FROM random_so_state
WHERE StateID = @state_numeric);
SET @full_address = (SELECT CONCAT((SELECT FLOOR((RAND() * (5000-1000+1))+1000)), ' ',
(SELECT @street_direction_text), ' ',
(SELECT @street_text), ' ',
(SELECT @street_type_text)) AS 'Random Address');
INSERT INTO Customers2 (Address, LastName, FirstName, State, City, LifetimeValue)
VALUES (@full_address, @last_name_text, @first_name_text, @state_text, @city_text, (SELECT FLOOR((RAND() * (1000000-1+1))+1)));
END $
DELIMITER ;
-- SHOWS HOW TO USE A WHILE LOOP TO GO THROUGH PROCEDURE 10 TIMES, AND THEREFORE CREATE 10 NEW RANDOM ROWS IN CUSTOMERS2 BY RUNNING THE PROCEDURE ONCE
DELIMITER $
DROP PROCEDURE IF EXISTS populate_Customers2 $
CREATE PROCEDURE populate_Customers2()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i <= 10 DO
SET @last_name_numeric = (SELECT FLOOR((RAND() * (51-1+1))+1));
SET @first_name_numeric = (SELECT FLOOR((RAND() * (48-1+1))+1));
SET @street_direction_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET @street_type_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET @street_numeric = (SELECT FLOOR((RAND() * (58-1+1))+1));
SET @city_numeric = (SELECT FLOOR((RAND() * (47-1+1))+1));
SET @state_numeric = (SELECT FLOOR((RAND() * (52-1+1))+1));
SET @street_direction_text = (SELECT (Direction)
FROM random_so_street_direction
WHERE DirectionID = @street_direction_numeric);
SET @last_name_text = (SELECT (LastName)
FROM random_so_last_name
WHERE LastNameID = @last_name_numeric);
SET @first_name_text = (SELECT (FirstName)
FROM random_so_first_name
WHERE FirstNameID = @first_name_numeric);
SET @street_type_text = (SELECT (StreetType)
FROM random_so_street_type
WHERE StreetTypeID = @street_type_numeric);
SET @street_text = (SELECT (Street)
FROM random_so_street
WHERE StreetID = @street_numeric);
SET @city_text = (SELECT (City)
FROM random_so_city
WHERE CityID = @city_numeric);
SET @state_text = (SELECT (State)
FROM random_so_state
WHERE StateID = @state_numeric);
SET @full_address = (SELECT CONCAT((SELECT FLOOR((RAND() * (5000-1000+1))+1000)), ' ',
(SELECT @street_direction_text), ' ',
(SELECT @street_text), ' ',
(SELECT @street_type_text)) AS 'Random Address');
INSERT INTO Customers2 (Address, LastName, FirstName, State, City, LifetimeValue)
VALUES (@full_address, @last_name_text, @first_name_text, @state_text, @city_text, (SELECT FLOOR((RAND() * (1000000-1+1))+1)));
SET i = i + 1;
END WHILE;
END $
DELIMITER ;
-- Shows how to declare variables using DECLARE statement at the top (local variables). Apparently all DECLARE statments must be at the top and together.
DELIMITER $
DROP PROCEDURE IF EXISTS populate_Customers2 $
CREATE PROCEDURE populate_Customers2()
BEGIN
DECLARE i INT;
DECLARE last_name_numeric INT;
DECLARE first_name_numeric INT;
DECLARE street_direction_numeric INT;
DECLARE street_type_numeric INT;
DECLARE street_numeric INT;
DECLARE city_numeric INT;
DECLARE state_numeric INT;
DECLARE country_numeric INT;
DECLARE street_direction_text varchar(30);
DECLARE last_name_text varchar(20);
DECLARE first_name_text varchar(20);
DECLARE street_type_text varchar(20);
DECLARE street_text varchar(20);
DECLARE city_text varchar(20);
DECLARE state_text varchar(20);
DECLARE country_text varchar(20);
DECLARE full_address varchar(50);
SET i = 1;
WHILE i <= 10 DO
SET last_name_numeric = (SELECT FLOOR((RAND() * (51-1+1))+1));
SET first_name_numeric = (SELECT FLOOR((RAND() * (48-1+1))+1));
SET street_direction_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET street_type_numeric = (SELECT FLOOR((RAND() * (4-1+1))+1));
SET street_numeric = (SELECT FLOOR((RAND() * (58-1+1))+1));
SET city_numeric = (SELECT FLOOR((RAND() * (47-1+1))+1));
SET state_numeric = (SELECT FLOOR((RAND() * (52-1+1))+1));
SET country_numeric = (SELECT FLOOR((RAND() * (27-1+1))+1));
SET street_direction_text = (SELECT (Direction)
FROM random_so_street_direction
WHERE DirectionID = street_direction_numeric);
SET last_name_text = (SELECT (LastName)
FROM random_so_last_name
WHERE LastNameID = last_name_numeric);
SET first_name_text = (SELECT (FirstName)
FROM random_so_first_name
WHERE FirstNameID = first_name_numeric);
SET street_type_text = (SELECT (StreetType)
FROM random_so_street_type
WHERE StreetTypeID = street_type_numeric);
SET street_text = (SELECT (Street)
FROM random_so_street
WHERE StreetID = street_numeric);
SET city_text = (SELECT (City)
FROM random_so_city
WHERE CityID = city_numeric);
SET state_text = (SELECT (State)
FROM random_so_state
WHERE StateID = state_numeric);
SET country_text = (SELECT (Country)
FROM random_so_country
WHERE CountryID = country_numeric);
SET full_address = (SELECT CONCAT((SELECT FLOOR((RAND() * (5000-1000+1))+1000)), ' ',
(SELECT street_direction_text), ' ',
(SELECT street_text), ' ',
(SELECT street_type_text)) AS 'Random Address');
INSERT INTO Customers2 (Address, LastName, FirstName, State, City, Country, LifetimeValue, PostalCode)
VALUES (full_address, last_name_text, first_name_text, state_text, city_text, country_text, (SELECT FLOOR((RAND() * (1000000-1+1))+1)), (SELECT FLOOR((RAND() * (90000-10000+1))+1)));
SET i = i + 1;
END WHILE;
END $
DELIMITER ;