Mysql 简明教程
MySQL - Stored Functions
MySQL Stored Functions
Stored Function 是一组执行特定操作然后返回单个值SQL语句。与MySQL中的内置函数类似，可以在任何MySQL语句中调用存储函数。MySQL CREATE FUNCTION 语句用于创建存储函数和用户定义的函数。
A Stored Function is a set of SQL statements that perform a specific operation and then return a single value. Similar to built-in functions in MySQL, a stored function can be called from within any MySQL statement. The MySQL CREATE FUNCTION statement is used to create both stored functions and user-defined functions.
默认情况下，存储函数与默认数据库关联。为了使用CREATE FUNCTION语句，用户必须具有CREATE ROUTINE数据库特权。
By default, a stored function is associated with the default database. In order to use the CREATE FUNCTION statement, the user must have the CREATE ROUTINE database privilege.
Syntax
以下是创建新存储函数的语法 -
Following is the syntax for creating a new stored function −
CREATE FUNCTION function_name(
parameters...
)
RETURN datatype [characteristics]
func_body;其中，
where,
-
function_name: It is the name of the function that we are creating. The name must not be same as the MySQL built-in function names.
-
parameters: These are the list of all parameters for the function. All the parameters are IN parameters by default. We cannot specify the IN, OUT or INOUT modifiers to the parameters.
-
datatype: This is the datatype of the value returned by the function.
-
characteristics: The CREATE FUNCTION statement will only be accepted if at least one of the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are specified in it’s declaration.
-
fun_body: This contains set of MySQL statements that defines the behaviour of the function between the BEGIN and END commands.
Example
首先，我们使用以下查询创建一个名为 CUSTOMERS 的表 -
First, let us create a table with the name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY(ID)
);在这里，我们将行插入CUSTOMERS表 -
Here, we are inserting rows into the CUSTOMERS table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);表格显示为 −
The table is displayed as −
Creating a Function −
Creating a Function −
使用以下 CREATE FUNCTION 查询，我们创建了一个函数，该函数根据客户的年龄返回其出生年份 -
With the following CREATE FUNCTION query, we are creating a function that returns the year of birth of the customers based on their AGE −
DELIMITER $$
CREATE FUNCTION DATE_OF_BIRTH(AGE INT) RETURNS INT DETERMINISTIC
BEGIN
DECLARE currentdate DATE;
SELECT CURDATE() INTO currentdate;
RETURN year(currentdate)-AGE;
END $$
DELIMITER ;现在，我们使用以下查询调用DATE_OF_BIRTH函数 -
Now, we are calling the DATE_OF_BIRTH function using the following query −
SELECT ID, NAME, DATE_OF_BIRTH(AGE)
AS 'YEAR_OF_BIRTH'
FROM CUSTOMERS;Calling Stored Function From Stored Procedure
在MySQL中，我们可以从存储过程中调用存储函数。以下语句创建一个名为 StudentDetails() 的存储过程，该过程调用 DATE_OF_BIRTH() 存储函数。
In MySQL, we can call a stored function from a stored procedure. The following statement creates a stored procedure with the name StudentDetails() that calls the DATE_OF_BIRTH() stored function.
DELIMITER $$
CREATE PROCEDURE CustomerDetails()
BEGIN
SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS 'YEAR_OF_BIRTH'
FROM CUSTOMERS;
END $$
DELIMITER ;此处，我们使用 CALL 关键字调用 CustomerDetails() 存储过程：
Here, we are calling the CustomerDetails() stored procedure using CALL keyword −
CALL CustomerDetails();