A stored function in MySQL encapsulates user defined formulas that can be reused among stored programs or SQL statements. It is different from stored procedure because it returns a single value as result. The function parameters are always IN parameters and it uses RETURN statements to return the results. Syntax for creating functions is as below:

DELIMITER //

CREATE FUNCTION functionName(function parameters ?.);

RETURNS type;

[COMMENT 'string' LANGUAGE SQL DETERMINISTIC or NOT DETERMINISTIC 

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA 

SQL SECURITY {DEFINER | INVOKER}];

BEGIN

 

Statements...;

 

END //

DELIMITER ;

Where,

  1. Here, DELIMITER // statement sets the delimiter as '//' instead of ';'. The END // defines to MYSQL that the entire block from CREATE to END // is a stored function that has to be sent to server as a block instead of single statement. The DELIMITER; at the end, defines the delimiter back to ';' instead of '//'.
  2. CREATE FUNCTION functionName()creates a procedure with name 'functionName()'. If there are any parameters to be passed to the function they are put within the '()' of the function.
  3. The parameters within '[   ]' are called deterministic. They are optional parameters.
  4. COMMENT is used for documentation purpose.
  5. LANGUAGE stand for the language used to write the subroutine. Only SQL is supported.
  6. NOT DETERMINISTIC indicates the same result is not produced for the same input parameters. DETERMINISTIC is used for testing.
  7. CONTAINS SQL indicates the routine does not have read and write data.
  8. NO SQL indicates the routine does not have SQL statements.
  9. READS SQL DATA indicates that the routine reads data but does not write data.
  10. MODIFIES SQL DATA indicates that the routine has statements that write data.
  11. SQL SECURITY characteristics can be DEFINER or INVOKER. DEFINER has the all privileges as it defines the user who created the procedure. INVOKER defines the user who calls the procedure.

Example of a stored function is as given below. The function returns the name of the patient from the database 'tblpatients' whose id is equal to the 'id' passed to function.

DELIMITER //

CREATE FUNCTION getName(id INT) RETURNS VARCHAR(10)

   DETERMINISTIC 

   BEGIN

      DECLARE name varchar(10);

      SELECT NameOfPatient INTO name FROM tblpatients WHERE PatientID = id;

      RETURN (name);

   END //

   DELIMITER ;

SELECT getName(1005);

The method above outputs the name of the patient with id 1005.