文章目录

• 一、自定义函数
• 1. 创建演示环境
• 1.1 创建数据库
• 1.2 创建数据表
• 1.3 插入示例数据
• 2. 创建示例函数
• 3. 使用自定义函数
• 二、存储过程
• 1. 定义存储过程
• 2. 使用存储过程
• 三、参考资料

一、自定义函数

1. 创建演示环境

1.1 创建数据库

为了更好的演示如何创建和使用MySQL自定义函数，下面先创建一个演示用的数据库db4_demo：

mysql> -- 创建数据库db4_demo
mysql> CREATE DATABASE db4_demo charset=utf8;
Query OK, 1 row affected (0.01 sec)

mysql> use db4_demo;
Database changed

1.2 创建数据表

接着，创建一个数据表products：

mysql> -- 创建数据表products
mysql> CREATE TABLE
    ->     products (prod_id INT NOT NULL AUTO_INCREMENT,
    ->               prod_name VARCHAR(20) NOT NULL,
    ->               prod_cost FLOAT NOT NULL DEFAULT 0.0,                     
    ->               prod_price FLOAT NOT NULL DEFAULT 0.0,
    -> PRIMARY KEY(prod_id)                                                     
    ->               )
    -> ;
Query OK, 0 rows affected (0.01 sec)

1.3 插入示例数据

mysql> INSERT INTO
    ->     products (prod_name, prod_cost, prod_price)
    -> VALUES
    ->     ('Basic Widget', 5.95, 8.35),
    ->     ('Micro Widget', 0.95, 1.35),
    ->     ('Mega Widget', 99.95, 140.00)
    -> ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

2. 创建示例函数

下面创建一个自定义函数calcProfit，该函数接受两个参数：产品成本cost、产品售价price，返回产品利润profit：

mysql> DELIMITER $
mysql> CREATE FUNCTION calcProfit (cost FLOAT, price FLOAT) RETURNS DECIMAL(9, 2)
    -> BEGIN
    ->     DECLARE profit DECIMAL(9, 2);
    ->     SET profit = price - cost;
    ->     RETURN profit;
    -> END 
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

注意：

• 在创建函数CREATE FUNCTION命令前使用DELIMITER是为了将MySQL默认的语句分隔符由;切换为$，否则因为函数体内部会因为包含;而提前结束，从而导致函数创建失败，另外DELIMITER和$之间必须有空格；
• 在完成函数创建后，需要注意将DELIMITER ;命令再次将MySQL默认语句分隔符切换回;，同样DELIMITER和;之间必须有空格；
• 可以：
  • 通过show create function 函数名;查看已自定义的函数；
  • 通过drop function 函数名删除自定义函数。

3. 使用自定义函数

下面可以使用上述自定义函数calcProfit：

mysql> SELECT 
    ->     *, calcProfit(prod_cost, prod_price)
    -> AS
    ->     profit
    -> FROM
    ->     products
    -> ;
+---------+--------------+-----------+------------+--------+
| prod_id | prod_name    | prod_cost | prod_price | profit |
+---------+--------------+-----------+------------+--------+
|       1 | Basic Widget |      5.95 |       8.35 |   2.40 |
|       2 | Micro Widget |      0.95 |       1.35 |   0.40 |
|       3 | Mega Widget  |     99.95 |        140 |  40.05 |
+---------+--------------+-----------+------------+--------+
3 rows in set (0.01 sec)

上述calcProfit函数自动计算出了每个产品的利润。

二、存储过程

MySQL的存储过程和函数很容易混淆，但二者又有很大区别，比如存储过程应该使用CALL语句调用，而函数可直接和SQL表达式一起使用。

1. 定义存储过程

mysql> DELIMITER $
mysql> CREATE PROCEDURE procedureTest()
    -> BEGIN
    ->     SELECt prod_name FROM products;
    -> END
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

2. 使用存储过程

mysql> CALL procedureTest() 
    -> ;
+--------------+
| prod_name    |
+--------------+
| Basic Widget |
| Micro Widget |
| Mega Widget  |
+--------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

三、参考资料

• [1] CREATE PROCEDURE and CREATE FUNCTION Statements
• [2] MySQL Stored Procedure & MySQL Functions Guide