CREATE PROCEDURE proc_name (
    [[ IN | OUT | INOUT ] param type[,...]]
)
    COMMENT 'string'
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  BEGIN
       routine_body
  END

  

Where:

• parameters are defined as:
  • IN: may be passed into a procedure but its value is not changed
  • OUT: procedure assigns new value to the parameter
  • INOUT: may have a value passed in and changed by the procedure
• param is the name of a parameter. A function may accept many parameters.
• type is the SQL data type of the associated parameter
• DETERMINISTIC states that the function always returns the same value for the same parameters;
  NOT DETERMINISTIC states that the function contains code that inserts, updates, or deletes data in one or more tables
• One of { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } must be provided, and they are self-explanatory
• BEGIN and END wrap around the actual code to be executed

          
CALL procedure([param[,...]]);

A procedure is called using CALL statement. Tuples returned by the procedure, if any, are listed by the database client.

          
CREATE PROCEDURE `pOverlap`(
  IN `term` CHAR(6),
  IN `id` CHAR(9),
  IN `time` TIME,
  IN `day` CHAR(1)
)
NOT DETERMINISTIC
READS SQL DATA
COMMENT 'Returns overlapping lab times'
BEGIN
  SELECT * FROM vIaLab
    WHERE termKey = term
    AND studentId = id
    AND sectionDays =  day
    AND time BETWEEN sectionBeginTime AND sectionEndTime;
END

  

Lab Instructional Assistants (IAs) may apply for many scheduled lab times, but can only actually work in one lab at a time. This procedure returns all tuples from the view vIaLab where a particular IA has applied for overlapping times. An EXISTS clause can be used on this result to make sure that no IA is hired to do multiple simultaneous labs.

This procedure is defined with:

• Four input-only parameters of various kinds.
• NOT DETERMINISTIC states that the tuples generated by the procedure may vary from call to call
• READS SQL DATA states that the procedure accesses a table or view
• Uses a SELECT to query the vIaLab table
• All executable lines must be terminated with ;
• This procedure is executed with:
  

  
  CALL pOverlap('202409', '123456789', '14:30', 'W');
  
  

          
CREATE FUNCTION func_name ([param[,...]])
    RETURNS type
    COMMENT 'string'
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  BEGIN
       routine_body
  END
  
        

Where:

• param is the name of a parameter. A function may accept many parameters.
• type is the SQL data type of the return value
• DETERMINISTIC states that the function always returns the same value for the same parameters;
  NOT DETERMINISTIC states that the function contains code that inserts, updates, or deletes data in one or more tables
• One of { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } must be provided, and they are self-explanatory
• BEGIN and END wrap around the actual code to be executed

          
SELECT ..., function([param[,...]]) AS alias, ... [FROM ...];

SET @var = function([param[,...]]);

A function is called as part of a SELECT statement, or as part of a SET statement in order to assign the function result to an alias or a variable.

A 'term' code at Laurier consists of a 6-digit number. The first four digits are the year of the term, and last two digits represent the month that determine the term. They are:

• '01' - January (Winter)
• '05' - May (Spring)
• '09' - September (Fall)

These three months are the only valid month values allowed in the term code. Given any term code then, this function returns the term description. Thus, '202409' is 'Fall 2024', '202501' is 'Winter 2025', etc.

          
CREATE FUNCTION `fTermName`(
  `termKey` CHAR(6)
)
RETURNS varchar(11)
DETERMINISTIC
NO SQL
COMMENT 'Returns the term name given a term key.'
BEGIN
	DECLARE termName VARCHAR(11);
	DECLARE YEAR CHAR(4);
	DECLARE MONTH CHAR(2);

	SET YEAR = LEFT(termKey, 4);
	SET MONTH = RIGHT(termKey, 2);

	IF MONTH = '01' THEN
	  SET termName = CONCAT('Winter ', YEAR);
	ELSEIF MONTH = '05' THEN
	  SET termName = CONCAT('Spring ', YEAR);
	ELSEIF MONTH = '09' THEN
	  SET termName = CONCAT('Fall ', YEAR);
	ELSE
	    SIGNAL SQLSTATE '45000'
	    SET MESSAGE_TEXT = 'Month code must be one of 01, 05, or 09';
	END IF;

	RETURN termName;
END

  

This function is defined with:

• The parameter is the 6-character termKey
• RETURNS varchar(11) states that the function returns a variable-length string of maximum length 11 characters
• DETERMINISTIC states that the function always returns the same result for the same input
• NO SQL states that the function does not access a table, i.e. no SELECT or other DML commands
• DECLARE name type is required to declare variables used in the function
• SET name = value assigns values to a variable
• LEFT, RIGHT, and CONCAT are built-in MySQL string functions
• IF ... ELSEIF ... ELSE ... END IF is MySQL's decision structure

• 
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Month code must be one of 01, 05, or 09';
  
              

  is MySQL code for throwing an error and providing an error message to the MySQL console - the error code and message can, in some cases, be processed by the program accessing the database and calling the function
• RETURN termName returns the calculated value
• All executable lines must be terminated with ;
• This function is executed with:
  

  
  SELECT fTermName('202401') AS termName;
  SELECT termId, fTermName(termId) as termName FROM term WHERE ...;
  
  

          
CREATE FUNCTION `addItem`(
  `@desc` VARCHAR(50)
)
RETURNS int
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
COMMENT 'Adds '
BEGIN
  INSERT INTO item (`desc`) VALUES (`@desc`);
  RETURN LAST_INSERT_ID();
END  
  

This function clearly uses SQL and is not deterministic since it uses the INSERT command that modifies SQL data.

LAST_INSERT_ID() is a built-in MySQL function that returns the most recent ID number assigned to an auto-increment column. The item table has a primary key column itemId that is defined as AUTO_INCREMENT - it is the value assigned to this column by the database when a new tuple is added to the table that is returned. Note that it is the last ID assigned for the current client session so that there is no confusion with other clients accessing the same table.

This function is executed with:

SELECT addItem('Some Widget') AS widgetId;