MENU


PROCEDURE and FUNCTION

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE routine ([proc_parameter[,...]])
[characteristic ...] routine_body CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION routine ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body proc_parameter:
[ IN | OUT | INOUT ] param_name type func_parameter:
param_name type type:
Any valid MySQL data type characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
An IN parameter passes a value into a procedure. Any modification to it within the procedure is not visible outside the procedure. An OUT parameter passes a value from the procedure out to the caller’s variable.An INOUT parameter passes a value into a procedure, and any modification to it is visible outside the procedure. For a FUNCTION, parameters are always regarded as IN parameters.
A stored FUNCTION cannot return a result set.
LANGUAGE SQL is ignored currently as only SQL routines are supported.
A routine is DETERMINISTIC if it always produces the same result for the same input parameters. The optimizer executes statements faster with this choice. The default is NOT DETERMINISTIC. A routine containing NOW() or RAND() is nondeterministic.
CONTAINS SQL, the default, indicates the routine does not contain statements that read or write data. NO SQL indicates that the routine contains no SQL statements. READS SQL DATA indicates that the routine contains statements that read data (SELECT), but not statements that write data. MODIFIES SQL DATA indicates that the routine contains statements that writes data (INSERT, DELETE).
SQL SECURITY specifies whether the routine uses the privileges of the account in the DEFINER clause, or the user who invokes it.
The following statements are not permitted in stored routines:
  • LOCK TABLES and UNLOCK TABLES
  • ALTER VIEW
  • LOAD DATA and LOAD TABLE
  • Statements not permitted in SQL prepared statements (except SIGNAL, RESIGNAL, and GET DIAGNOSTICS)
To begin a transaction within a stored program, use START TRANSACTION.

The following statements are not permitted in stored functions:
  • Commit or rollback statements
  • Statements returning a result set
  • FLUSH statements
  • Recursive functions
  • Statements that modify a table that is already being used by the statement that invoked the function
ALTER {PROCEDURE|FUNCTION routine [characteristic ...] characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
DROP {PROCEDURE | FUNCTION} [IF EXISTS] routine

Invoked with the CALL keyword, the procedure generates a table of 0-8 random integers in the specified range. The function returns the average of the integers in the table. Notice the use of DELIMITER to temporarily change the delimiter character for the outer statements. 
DROP PROCEDURE IF EXISTS initialize_tbl;
DROP FUNCTION IF EXISTS tAverage;

DELIMITER //
CREATE PROCEDURE initialize_tbl 
       (IN min INT, IN max INT, OUT cnt INT)
       NOT DETERMINISTIC
       MODIFIES SQL DATA
   BEGIN
      DROP TABLE IF EXISTS tbl;
      CREATE TABLE tbl(num INT, INDEX (num));
      INSERT INTO tbl VALUES
	        (rand()*100),(rand()*100),(rand()*100),
                      (rand()*100),(rand()*100),(rand()*100),
                      (rand()*100),(rand()*100);
      DELETE FROM tbl WHERE num<min OR num>max;
      SET cnt = (SELECT count(num) FROM tbl);
   END //

CREATE FUNCTION tAverage()
       RETURNS INT
       DETERMINISTIC
       READS SQL DATA
   BEGIN 
      RETURN (SELECT AVG(num) FROM tbl);
   END //
DELIMITER ;

CALL initialize_tbl(30,70,@C);
SELECT @C,tAverage(),num FROM tbl;


      

        @C
        tAverage()
        num
      

      

        3
        47
        30
      

      

        3
        47
        53
      

      

        3
        47
        59