MySQL - Stored Procedures / Functions

mysql

http://www.sitepoint.com/stored-procedures-mysql-php
http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/
http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

Stored procedures are simply collections of one or more SQL statements saved for future use.

MySQL provides capabilities for defining and executing stored procedures and functions. A client sends the definition of a procedure or function to the server, which stores it for later use.

Stored procedures and functions have a great deal in common.

To use stored routines, you must have enough access privileges. However, even with those privileges, the server by default will not let you create stored routines if binary logging is enabled except under certain conditions. This is because use of stored routine introduces certain security issues for replication. Also, routines that modify data may make it problematic to use the binary log for data recovery, particularly if you use routines that have non-deterministic behavior.

To disable the restriction on routine creation, start the server with —log-bin-trust-routine-creators, or set the log_bin_trust_routine_creator system variable:

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

Stored procedures and functions offer several benefits:

  1. More flexible SQL syntax. Stored routines can be written using extensions to SQL syntax, such as compound statements and flow-control constructs, that make it easier to express complex logic.
  2. Error handling capabilities. A stored routine can create error handlers to be used when exceptional condition arise. The occurrence of an error need not cause termination of the routine but can be handled appropriately.
  3. Standard compliance. The MySQL implementation of stored routines conforms to standard SQL syntax. Routines written for MySQL should be reasonably portable to other database servers that also follow standard SQL syntax. Although the implementation is based on standard SQL, it is not yet complete, and there are limitations. For example, cursors are read-only and can not be used to modify tables. Cursors also advance through a result set one row at a time; that is, they are not scrollable.
  4. Code packaging and encapsulation. A routine allows the code that performs an operation to be stored once on the server, and access from multiple applications. The code need not be included within multiple applications. This reduces the potential for variation in how different applications perform the operation.
  5. Less re-invention of the wheel. A collection of stored routines acts as a library of solutions to problems. Developers can use them "off the shelf" rather than re-implementing the code from scratch. Stored routines also facilitate sharing of knowledge and experience.
  6. Separation of logic. Factoring out the logic of specific application operations into stored routines reduces the complexity of an application's own logic and makes it easier to understand.
  7. Ease of maintenance. A single copy of a routine is easier to maintain than a copy embedded within each application. Upgrading applications is easier if they all use a routine in common, because it is necessary to upgrade only the routine, not every application that uses it.
  8. Reduction in network bandwidth requirements. Consider a multiple-statement operation performed by a client without the use of a stored routine: Each statement and its result cross the network, even those that calculate intermediate results. If the operation is performed within a stored routine instead, intermediate statements and results are processed entirely on the server side and do not cross the network. This improve performance and results in less contention of resources, particularly for busy or low bandwidth networks. The potential benefit of this factor must be weighed against the number of clients and the amount of client processing that is moved onto the server.
  9. Server upgrades benefit clients. Upgrades to the server host improve the performance of stored routines that execute on that host. This improve performance of stored routines that execute on that host. This improves performance for client applications that use the routines even though the client machines are not upgraded.
  10. Better security. A routine can be written to access sensitive data on the definer's behalf for the invoker, but not return anything that the invoker should not see. A routine can also be used to modify tables in a safe way, without giving user direct access to the tables.

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

  1. A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
  2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
  3. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

Syntax for routine creation differs somewhat for procedures and functions:

  1. Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration differs from that for functions.
  2. Functions return value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller. RETURNS and RETURN do not appear in procedure definitions.

A MySQL extension for stored procedure (not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as the result of a SELECT statement. However, the contents of such result sets cannot be used directly in expression.

Stored routines are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped.

Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.

Stored routine definitions can contains multiple statements, each must be terminated by a semicolon character. If you are defining a stored routine from within a programming interface that does not use the semicolon as a statement terminator, semicolons within stored routine definitions do not present any issues. However, if you are using the mysql client program, semicolons within routine definitions are ambiguous because mysql itself treats semicolons as a statement terminator. To handle this issue, mysql support a delimiter command that enables you to change its statement terminator temporarily while you define a stored routine. By using delimiter, you can cause mysql to pass semicolons to the server along with the rest of the routine definition:

mysql> delimiter //
mysql> CREATE PROCEDURE world_record_count ()
        -> BEGINE
        ->  SELECT 'Country', COUNT(*) FROM Country;
        ->  SELECT 'City', COUNT(*) FROM City;
        ->  SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage;
        -> END;
        -> //
mysql> delimiter ;

Be sure to choose a delimiter that does not contain characters that occur within the definition of the stored routine that you are creating.

To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:

CREATE PROCEDURE proc_name ([parameters])
 [characteristics]
 routine_body
CREATE FUNCTION func_name ([parameters])
 RETURNS data_type
 [characteristics]
 routine_body

The routine name can be unqualified to create routine in the default database, or qualified with a database name to create the routine in a specific database.

The optional characteristics clause contains one or more of the following values, which can appear in any order:

  1. SQL SECURITY {DEFINER | INVOKER}. A stored routine runs either with the privileges of the user who created it, or the user who invoked it.
  2. DETERMINISTIC or NOT DETERMINISTIC. Indicates whether the routine always produces the same result when invoked with a given set of input parameter values. If it does not, the routine is non-deterministic. This characteristic is intended to convey information to the query optimizer, but the optimizer does not yet use it. The default is NOT DETERMINISTIC.
  3. LANGUAGE SQL. Indicates the language in which the routine is written. Currently, the only supported language is SQL.
  4. COMMENT 'string'. Specifies a descriptive string for the routine. The string is displayed by the statements that return information about routine definitions.

The routine_body specifies the body of the procedure or function. The BEGIN … END construct is optional if the body of the routine consist of only one statement.

CREATE FUNCTION circle_area (radius FLOAT)
 RETURNS FLOAT
 RETURN PI() * radius * radius;

RETURNS is not terminated by a semicolon because it is just a clause, not a statement. The sql_mode system variable value in effect when the routine executes is the value that was current when it was defined.

A block can be labeled. Labels are optional, but the label at the end can be present only if the label at the beginning is also present, and the end label must have the same name as the beginning label. An end label is not required but can make the routine easier to understand. Blocks can be nested.

The LEAVE statement transfers control to the end of the labeled block.

BEGIN
 inner_block: BEGIN
  IF DAYNAME(NOW() = 'Wednesday' THEN
   LEAVE inner_block;
  END IF
  SELECT 'Today is not Wednesday';
 END inner_block;
END;

A stored routine definition can include parameter declarations. For procedures, parameters also enable you to pass information back from the procedure to the caller.

CREATE FUNCTION tax (cost DECIMAL(10,2), tax_rate DECIMAL(10,2))
 RETURNS DECIMAL(10,4)
 RETURN cost * tax_rate;

Parameter declarations occurs within the parentheses that follow the routine name in a CREATE PROCEDURE or CREATE FUNCTION statement. If there are multiple parameters, separate them by commas. A parameter declaration includes the parameter name and data type. Parameter names are not case sensitive.

For procedures (but not functions), parameter name may be preceded by one of the following keywords to indicate the direction in which information flows through the parameter:

  1. IN indicates an input parameter. The parameter value is passed in from the caller to the procedure. The procedure can assign a different value to the parameter, but the change is visible only within the procedure, not to the caller.
  2. OUT indicates an output parameter. The caller passes a variable as the parameter. Any value the parameter has when it is passed is ignored by the procedure, and its initial value within the procedure is NULL. The procedure sets its value, and after the procedure terminates, the parameter value is passed back from the procedure to the caller.
  3. INOUT indicates a two-way parameter that can be used both for input and for output. The value passed by the caller is the parameter's initial value within the procedure. If the procedure changes the parameter value, that value is seen by the caller after the procedure terminates.

If no keyword is given before a parameter name, it is an IN parameter by default.

Parameters for stored functions are not preceded by IN, OUT, or INOUT. All function parameters are treated as IN parameters.

Parameters to stored routines need not be passed as user variables. They can be given as constants or expression as well. However, for OUT or INOUT procedure parameters, if you do not pass a variable, the value passed back from the procedure will not be accessible.

The DECLARE statement is used for declaring local variables, conditions (warnings or exceptions), handlers for conditions, cursors for accessing result sets row by row.

The DECLARE statements can only be used within a BEGIN … END block and must appear in the block before any other statements. If used to declare several types of items, the DECLARE statements must appear in a particular order: You must declare variables and conditions first, then cursors, and finally handlers.

Each variable declared within a block must have a different name. This restriction also applies to declarations for conditions and for cursors. However, items of different types within a block can have the same name. Item names are not case sensitive.

Variables, conditions, handler, and cursors created by DECLARE statements are local to the block. That is, they are valid only within the block (or any nested blocks). When a block terminates, any cursors still open are closed, and all items declared within the block go out of scope, and are no longer accessible.

If an inner block contains an item that has the same name as the same kind of item in an outer block, the outer block item cannot be accessed within the inner block.

To declare local variables:

DECLARE var_name [, var_name] ... data_type [DEFAULT value]

Each variable named in the statement has the given data type, and default value (if the DEFAULT clause is present). To declare variables that have different data types or default value, use a separate DECLARE statement.

To avoid name clashes, it is best not to give a local variable the same name as any table columns that you refer to within a routine.

Local routine variable names are not written with a leading @ character. This differs from the @var_name syntax for writing user variables.

A variable may be assigned a value using a SET, SELECT … INTO, or FETCH … INTO statement.

DECLARE var1, var2, var3 INT;
SET var1 = 1, var2 = 2;
SET var3 = var1 + var2;

DECLARE name_var CHAR(52);
DECLARE pop_var INT;
SELECT Name, Population INTO name_var, pop_var FROM Country WHERE Code = 'ESP';

The SELECT statement must select at most a single row. If it selects more than one row, an error occurs. If the SELECT statement selects no rows, the variables following the INTO keywords remain unchanged.

Conditions and Handlers:

CREATE PROCEDURE add_name (name_param CHAR(20))
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    INSERT INTO dup_names (name) VALUES(name_param);
    SELECT 'duplicate key found, inserted into dup_names' AS result;
  END;
  INSERT INTO unique_names (name) VALUES(name_param);
  SELECT 'row inserted successfully into unique_names' AS result;
END;

A handler has a name and a statement to be executed upon occurrence of a given condition.

It is allowable but not necessary to give a name to a condition by declaring it. You might declare a condition to provide a name for it that is more meaningful than the code that it stand for. The following declaration associates the descriptive name dup_key with the SQLSTATE value '23000':

DECLARE dup_key CONDITION FOR SQLSTATE '23000';

A condition type can be an SQLSTATE value, specified as SQLSTATE (or SQLSTATE VALUE) followed by a five-character string literal. MySQL extends this to allow numeric MySQL error codes as well. The following declarations are equivalent:

DECLARE null_not_allowed CONDITION FOR SQLSTATE '23000';
DECLARE null_not_allowed CONDITION FOR 1048;

The DECLARE HANDLER statement creates a handler for one or more conditions and associates them with an SQL statement that will be executed should any of the condition occurs when the routine is run:

DECLARE handler_type HANDLER FOR condition_type [, condition_type] ...
 statement

The handler_type indicates what happens once the handler statement is executed. CONTINUE causes routine execution to continue (the SQL statement that follows the statement in which the condition occurred is the next to be processed). EXIT causes control to transfer to the end of the block in which the handler is declared (the intermediate SQL statements are not processed). Standard SQL defines UNDO handlers as well, but MySQL does not currently support them.

Each condition associated with a handler must be one of the following:

  1. An SQLSTATE value or MySQL error code, specified the same way as in a DECLARE CONDITION statement
  2. A condition name declared previously with a DECLARE CONDITION statement
  3. SQLWARNING which handles conditions for all SQLSTATE values that begin with 01
  4. NOT FOUND, which handles conditions for all SQLSTATE values that begin with 02
  5. SQLEXCEPTION which handles conditions for all SQLSTATE values not handled by SQLWARNING or NOT FOUND

The statement at the end of DECLARE HANDLER specifies the statement to execute when the condition occurs. It can be a simple statement or a compound statement.

To ignore a condition, declare a CONTINUE handler for it and associate with an empty block.

Cursors:

A cursor enables you to access a result set one row at a time. Cursors, as implemented in MySQL, are read-only. They can not be used to modify tables. Cursors are not scrollable (must be access one row at a time).

**To use a cursor in a stored routine, begin by writing a DECLARE CURSOR statement that names the cursor and associates it with a SELECT statement:

DECLARE cursor_name CURSOR FOR select_statement

To open the cursor, name it in an OPEN statement. This executes the SELECT statement associated with the cursor:

OPEN cursor_name

The FETCH statement fetches the next row of an open cursor's result set:

FETCH cursor_name INTO var_name [, var_name] ...

There must be one variable per column in the result set. You can fetch values into local variables or routine parameters.

FETCH often occurs in a loop so that all rows in the result set can be processed. That raises an issue: What happens when you reach the end of the result set? The answer is that a No Data condition occurs (SQLSTATE 02000), which you can detect by declaring a handler for that condition:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' statement;

When you are done with the cursor, close it with CLOSE:

CLOSE cursor_name;

Closing a cursor is optional. Any cursors declared in a block are closed automatically when the block terminates.

BEGIN
  DECLARE row_count INT DEFAULT 0;
  DECLARE code_var CHAR(3);
  DELCARE name_var CHAR(52);
  DECLARE c CURSOR FOR 
    SELECT Code, Name FROM Country WHERE Continent = 'Africa';
  OPEN c;
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
    LOOP
      FETCH c INTO code_var, name_var;
      SET row_count = row_count + 1;
    END LOOP;
  END;
  CLOSE c;
  SELECT 'number of rows fetched = ', row_count;
END;

The preceding example uses a nested block because an EXIT handler terminates the block within which it is declared, not the loop within which the condition occurs. If nested block had not been used, the handler would transfer control to the end of the main block upon reading the end of the result set, and the CLOSE and SELECT statement following the loop would never execute. An alternative approach does not require a nested block: Use a CONTINUE handler that set a loop-termination variable and test the variable value within the loop:

BEGIN
  DECLARE exit_flag INT DEFAULT 0;
  DECLARE row_count INT DEFAULT 0;
  DECLARE code_var CHAR(3);
  DECLARE name_var CHAR(52);
  DECLARE c CURSOR FOR
    SELECT Code, Name FROM Country WHERE Continent = 'Africa';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;
  OPEN c;
  fetch_loop: LOOP
    FETCH c INTO code_var, name_var;
    IF exit_flag THEN LEAVE fetch_loop; END IF;
    SET row_count = row_count + 1;
  END LOOP;
  CLOSE c;
  SELECT 'number of rows fetched =', row_count;
END;

In standard SQL, a stored procedure that use SELECT statements to retrieve records processes those records itself. A MySQL extension to procedures is that SELECT statements can be executed to generate result sets that are returned directly to the client with no intermediate processing. The client retrieves the results as though it had executed the SELECT statement itself. This extension does not apply to stored function.

Flow Control:

**LOOP iterates unconditionally, whereas REPEAT and WHILE include a clause that test whether it should continue or terminate.

IF and CASE used for flow control in procedures and functions have different syntax from the IF() function and CASE expression. Because of the difference in syntax, it is possible to use IF() function and CASE expression within stored routines without ambiguity.

IF expr
  THEN statement_list
  [ELSEIF expr THEN statement_list] ...
  [ELSE statement_list]
END IF

CASE has two forms:

CASE case_expr
  WHEN when_expr THEN statement_list
  [WHEN when_expr THEN statement_list] ...
  [ELSE statement_list]
END CASE

case_expr is evaluated and used to determine which of the following clauses in the rest of the statement to execute. The when_expr in the initial WHEN clause is evaluated and compared to case_expr. If the two are equal, the statement list following THEN is executed. Each comparison is of the form case_expr = when_expr. The significance of this is that the comparison is never true if either operand is NULL, no matter the value of the other operand.

The second CASE syntax:

CASE
  WHEN when_expr THEN statement_list
  [WHEN when_expr THEN statement_list] ...
  [ELSE statement_list]
END CASE

For this syntax, the conditional expression in each WHEN clause is executed until one is found to be true, and then its statement list is executed. If none of them are true, and there is an ELSE clause, the ELSE clause's statement list is executed. This syntax is preferable to the first syntax for certain types of tests (NULL or IS NOT NULL to test for NULL values, or those that use relative value tests such as val < 0 or val >= 100).

MySQL provides three kinds of loops: LOOP, REPEAT, and WHILE. LOOP constructs an unconditional loop with no loop-termination syntax. For this reason, it must contain a statement that explicitly exit the loop. REPEAT and WHILE are conditional. They include a clause that determines whether loop execution continues or terminates.

Standard SQL includes a FOR loop as well, but MySQL does not currently support FOR loops.

Each of the supported loop-construction statements can be labeled.

LOOP
  statement_list
END LOOP

The statement list within the loop executes repeatedly. An exit can be effected with a LEAVE statement or (in a function) a RETURN statement.

DECLARE i INT DEFAULT 0;
my_loop: LOOP
  SET i = i + 1;
  IF i >= 10 THEN
    LEAVE my_loop;
  END IF;
END LOOP my_loop;

The REPEAT statement creates a conditional loop:

REPEAT
  statement_list
UNTIL expr
END REPEAT

Note that there is no semicolon between the expression and END REPEAT.

WHILE expr DO
  statement_list
END WHILE

Loops can be nested. In such cases, loop labels are useful if it is necessary to exit more than one level at once.

LEAVE label
ITERATE label

LEAVE transfers control to the end of the named construct and can be used with blocks and loops: BEGIN/END, LOOP, REPEAT, and WHILE.

ITERATE transfers control to the beginning of the named construct. It can be used only within loops: LOOP, REPEAT, or WHILE. It cannot be used to restart a BEGIN/END block.

LEAVE and ITERATE must appear within the labeled construct.

CREATE FUNCTION countryname(code_param CHAR(3))
RETURNS CHAR(52)
BEGIN
  DECLARE name_var CHAR(52);
  SELECT Name INTO name_var FROM Country WHERE Code=code_param;
  RETURN name_var;
END;

The ALTER PROCEDURE or ALTER FUNCTION statement can be used to alter some of the characteristics of a stored routine.

ALTER PROCEDURE proc_name [characteristics]
ALTER FUNCTION func_name [characteristics]

The allowable characteristics for these statements are SQL SECURITY and COMMENT:

ALTER FUNCTION f SQL SECURITY INVOKER COMMENT 'this function has invoker security';

The characteristics can be listed in any order. These statements can not be used to alter other aspects of routine definitions, such as parameter declaration or the body. To do that, you must drop the routine and re-create it.

DROP PROCEDURE [IF EXISTS] proc_name
DROP FUNCTION [IF EXISTS] func_name

To invoke a procedure, use a CALL statement. This is a separate statement. **A procedure cannot be invoked as part of an expression.

To invoke a function, invoke it in an expression. It returns a single value that is used in evaluating the expression.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'world_record_count'
  AND ROUTINE_SCHEMA = 'world'\G
SHOW PROCEDURE STATUS LIKE 'w%';
SHOW FUNCTION STATUS

The SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS include a LIKE clause. If it is present, the statements display information about the routines that have a name that matches the pattern.

To display the definition of an individual procedure or function, use the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION:

SHOW CREATE PROCEDURE world_record_count;
SHOW CREATE FUNCTION add3;

To create a routine, you must have the CREATE ROUTINE privilege.

To execute a routine, you must have the EXECUTE privilege for it.

To drop a routine, or alter its definition, you must have ALTER ROUTINE privilege for it.

To grant privileges for a routine, you must have the GRANT OPTION privilege for it.

When you create a stored routine, MySQL automatically gives you the EXECUTE and ALTER ROUTINE privileges for it. You can verify these privileges using SHOW GRANTS statement.

A GRANT statement that grants the ALL privileges specifier at the global or database level includes all stored routine privileges except GRANT OPTION. To give the GRANT OPTION privilege, include WITH GRANT OPTION at the end of the GRANT statement.

GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world.world_record_count TO 'magellan'@'localhost';
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License