MySQL stored procedures and functions

A SQL language that is compiled beforehand and stored in a database.

First, create a function

To create a format:

CREATE FUNCTION sp_name ([func_parameter[,...])

RETURNS type

[Characteristic ...] Routine_body

Func_parameter:

Param_name type

Summary: The parameters of the function can not be modified such as, in,out (unlike stored procedures), call can be called directly, such as the general use of system functions.

Mysql> DELIMITER $$

Mysql> CREATE FUNCTION func_emsal (empno INT)

-RETURNS DOUBLE (10,2)

COMMENT ' inquire about the wages of an employee '

BEGIN

--RETURN (

SELECT Sal

-From employee

WHERE employee.empno=empno);

-END $$

Call mode: SELECT func_emsal (empno);

Second, the stored procedure

To create a format:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[Characteristic ...] Routine_body

Proc_parameter:

[In | Out | INOUT] Param_name Type

In is used to describe the parameters that are passed in, the out is the parameter that returns the result (that is, there is no value at the beginning, there is a value after the call), andINOUT acts as both.

Summary: In,out,inout is used to specify the type of parameter, the assignment of an out parameter uses the set operator (similar to a local variable assignment), and the call uses the Call,out parameter with an @ modifier.

Example:

Mysql> DELIMITER $$

Mysql> CREATE PROCEDURE proce_emsal ()

-COMMENT ' inquire about wages of all employees '

BEGIN

SELECT Sal

from employee;

end$$

Calling Mode: Call Proce_emsal ();

MySQL stored procedures and functions