laika222
12/2/2016 - 3:09 AM

You can save a block of multiple statements in a PROCEDURE. First, change the DELIMITER to something other than ';' so the block doesn't exe

You can save a block of multiple statements in a PROCEDURE. First, change the DELIMITER to something other than ';' so the block doesn't execute while you're writing it out. Line 3 CREATEs PROCEDURE called show_times. Line 4 tells it to begin the procedure. Lines 5-6 contain the block of multiple statements. Line 7 ENDs the procedure, and then the following '$' executes it. Then line 8 changes the DELIMITER back to ';'. Line 11 CALLs the procedure, which executes the entire block of statements in lines 4-7 again. Line 14 shows all procedures that have already been stored in your database. Line 17 shows how to see the code or definition that created a specific procedure.

CREATE PROCEDURE, CALL, SHOW PROCEDURE STATUS, SHOW CREATE PROCEDURE MS.mysql
content_copyfile_download
-- CREATING A PROCEDURE OF MULTIPLE STATEMENTS THAT CAN BE CALLED LATER
DELIMITER $
CREATE PROCEDURE show_times()
BEGIN
  SELECT 'Local time is:', CURRENT_TIMESTAMP;
  SELECT 'UTC time is:',UTC_TIMESTAMP;
END $
DELIMITER ;

-- CALL OR EXECUTE THE STORED PROCEDURE AGAIN SOMETIME IN THE FUTURE
CALL show_times;

-- FIND OUT WHICH STORED PROCEDURES ALREADY EXIST IN YOUR MYSQL DATABASE
SHOW PROCEDURE STATUS;

-- FIND OUT THE CODE USED TO CREATE A SPECIFIC PROCEDURE
SHOW CREATE PROCEDURE show_times;