laika222
12/2/2016 - 10:48 PM

Procedure to add random customers to the Customers2 table for testing purposes. The first text file shows how to do it all with Local Variab

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.

EXAMPLE: CREATE PROCEDUE, and No Procedure Version to INSERT INTO Random Address Local and Session Variables MS.mysql
content_copyfile_download
-- 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 ;
EXAMPLE: CREATE PROCEDUE, and No Procedure Version to INSERT INTO Random Address Local Variables MS.mysql
content_copyfile_download
-- 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 ;