laika222

4/6/2017 - 7:13 PM

Procedure that has user input an OrderID number, and then it checks the OrderDate and calculates the time that has elapsed since that order date in seconds, minutes, days, months, and years. If the OrderDate does not exist, it returns a message of 'OrderID Does Not Exist' (it determines if the OrderID exists by using an IF ELSE statement which checks if a certain variable IS NULL).

```
DELIMITER $
DROP PROCEDURE IF EXISTS time_since_order $
CREATE PROCEDURE time_since_order(p_OrderID INT)
BEGIN
DECLARE seconds_since INT;
DECLARE minutes_since INT;
DECLARE hours_since INT;
DECLARE days_since INT;
DECLARE years_since INT;
SET seconds_since = (SELECT (TIMESTAMPDIFF(SECOND,NOW(),(SELECT OrderDate FROM Orders2 WHERE OrderID = p_OrderID))));
SET minutes_since = (seconds_since / 60) * -1;
SET minutes_since = (seconds_since / 60) * -1;
SET hours_since = ((seconds_since / 60) / 60) * -1;
SET days_since = (((seconds_since / 60) / 60) / 24) * -1;
SET years_since = ((((seconds_since / 60) / 60) / 24) / 365) * -1;
IF seconds_since IS NULL THEN SELECT 'OrderID Does Not Exist' AS 'Error';
ELSE SELECT (seconds_since * -1) AS 'Seconds Since Order'
, minutes_since AS 'Minutes Since Order'
, hours_since AS 'Hours Since Order'
, days_since AS 'Days Since Order'
, years_since AS 'Years Since Order';
END IF;
END $
CALL time_since_order(2) $
-- BEGIN DETAIL!!!
-- Sets delimiter as $
DELIMITER $
-- DROP PROCEUDRE time_since_order if it already exists -used for creating the procedure and testing
DROP PROCEDURE IF EXISTS time_since_order $
-- CREATE PROCEDURE time_since_order with one parameter, p_OrderID, which is an integer
CREATE PROCEDURE time_since_order(p_OrderID INT)
-- BEGINs procedure
BEGIN
-- Declares all variables as integers (second_since, minutes_since, hours_since, days_since, years_since)
DECLARE seconds_since INT;
DECLARE minutes_since INT;
DECLARE hours_since INT;
DECLARE days_since INT;
DECLARE years_since INT;
-- SETs variable seconds_since to the value of this SELECT statement. TIMESTAMPDIFF function chooses difference between the two dates; SECOND tells it to return the date in seconds, but you could also use MINUTE, HOUR, DAY, YEAR. The value is returned as an integer. After defining the time, you define the two dates - in this case, the first date uses the NOW() function to pull the current timestamp, and the second date is a SELECT statement pulling the OrderDate from the Orders2 table where the OrderID equals the parameter value for p_OrderID that the user inputs when calling the procedure.
SET seconds_since = (SELECT (TIMESTAMPDIFF(SECOND,NOW(),(SELECT OrderDate FROM Orders2 WHERE OrderID = p_OrderID))));
-- SETs variable minutes_since as value of seconds_since divided by 60 (to convert to minutes), which will return a negative number since the current date is first and a date in the past is second. Multiplying by -1 will change that to a positive value.
SET minutes_since = (seconds_since / 60) * -1;
-- SETs variable hours_since as value of seconds_since divided by 60 and then divided by 60 (to convert to hours), which will return a negative number since the current date is first and a date in the past is second. Multiplying by -1 will change that to a positive value.
SET hours_since = ((seconds_since / 60) / 60) * -1;
-- SETs variable days_since as value of seconds_since divided by 60 and then divided by 60 and then divided by 24 (to convert to days), which will return a negative number since the current date is first and a date in the past is second. Multiplying by -1 will change that to a positive value.
SET days_since = (((seconds_since / 60) / 60) / 24) * -1;
-- SETs variable years_since as value of seconds_since divided by 60 and then divided by 60 and then divided by 24 and then by 365 (to convert to years), which will return a negative number since the current date is first and a date in the past is second. Multiplying by -1 will change that to a positive value.
SET years_since = ((((seconds_since / 60) / 60) / 24) / 365) * -1;
-- Now that the variables have been populated with values, an IF ELSE statement pulls the information that the user will see. First, the IF statment checks seconds_since against being IS NULL, and if it's NULL, THEN it SELECTS 'OrderID does Not Exist' as a way of telling the user that the OrderID he/she plugged in for p_OrderID does not exist (the value will be filled in as NULL if the user puts in an OrderID that doesn't exist).
IF seconds_since IS NULL THEN SELECT 'OrderID Does Not Exist' AS 'Error';
-- IF the value of seconds_since isn't null (if it does hold a value), then the ELSE statment excecutes. This statment selects seconds_since and multiples by -1 to give a postivie number, and names it 'Seconds Since Order'. It then pulles minutes_since as 'Minutes Since Order' - no * -1 is done since we already did that above when setting value of this variable. It then pulls hours_since, days_since, and years_since.
ELSE SELECT (seconds_since * -1) AS 'Seconds Since Order'
, minutes_since AS 'Minutes Since Order'
, hours_since AS 'Hours Since Order'
, days_since AS 'Days Since Order'
, years_since AS 'Years Since Order';
-- END IF stops the ELSE IF statment.
END IF;
-- END ends the procedure
END $
-- CALLs the procedure, plugging in an OrderID value of 2 for the p_OrderID parameter.
CALL time_since_order(2) $
```