SQL 基础（三）

发表于   |   分类于

自定义函数

自定义函数(user-defined function, UDF) 是对 MySQL 扩展的途径，其用法与内置函数相同

自定义函数的两个必要条件：

  • 参数：可以有零个或多个
  • 返回值：只能有一个

函数可以返回任意类型的值，同样可以接收这些类型的参数。
参数数量不能超过 1024 个。

1
2
3
CREATE FUNCTION 函数名
RETURNS {STRING | INTEGER | REAL | DECIMAL}
函数体

函数体的一些限制：

  • 由合法的 SQL 语句构成
  • 可以是简单的 SELECT 或 INSERT 语句
  • 如果为复合结构，则使用 BEGIN…END 语句
  • 复合结构可以包含声明、循环和控制结构等

不带参数的自定义函数

例如：

1
2
3
4
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点：%i分：%s秒');

SELECT f1()

带参数的自定义函数

例如：

1
2
3
4
5
6
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10, 2) UNSIGNED
RETURN (num1+num2)/2


select f2(2,3)

具有复合结构函数体的自定义函数

例如：

1
2
3
4
5
6
7
8
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT users(username) VALUES (username);
RETURN LAST_INSERT_ID();
END

select adduser('Alice')

删除自定义函数

1
DROP FUNCTION [IF EXISTS] 函数名

存储过程

当我们执行一个 SQL 语句时，发生了以下过程：

SQL 命令，MySQL 引擎分析语法正确，编译成 MySQL 引擎可识别的命令，执行结果后返回客户端。

存储过程可以将上述过程简化，当非第一次调用时，可以省去其中语法分析和编译。

存储过程是 SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程的优点：

  • 增强 SQL 语句的功能和灵活性
  • 实现较快的执行速度，当客户端第一次调用时，会进行语法分析和编译。当再次调用时，直接从内存执行
  • 减少网络流量，提交给服务器的数据量小

参数：零个或多个
返回值：多个

创建存储过程

1
2
3
4
CREATE [DEFINER = {user | CURRENT_USER}]
PROCEDURE 存储过程名称([参数 [,...]])
[特性 ...]
过程体

参数：
[IN | OUT | INOUT] 参数名 参数类型

  • IN 表示该参数的值必须在调用过程时指定
  • OUT 表示该参数的值可以被存储过程改变，并且可以返回
  • INOUT 表示该参数的值在调用时指定，并且可以被改变和返回

特性： 

1
2
COMMENT 'string' 
| {CONTAIN SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY { DEFINER | INVOKER}
  • COMMENT：注释
  • CONTAINS SQL：包含 SQL 语句，但不包含读或写数据的语句
  • NO SQL：不包含 SQL 语句
  • READS SQL DATA：包含读数据的语句
  • MODIFIEDS SQL DATA：包含写数据的语句
  • SQL SECURITY {DEFINER | INVOKER}：指明谁有权限来执行

过程体：

  • 由合法的 SQL 语句构成
  • 可以是任意SQL 语句（增删改查）
  • 如果为复合结构，则使用 BEGIN…END 语句
  • 复合结构可以包含声明、循环和控制结构等

此外，DEFINER 表示创建者，如果使用 user，则为当前用户。PROCEDURE 表明了存储过程的名称

调用存储过程

1
2
3
4
5
// 带参数
CALL 存储过程名称([参数 [,...]])

// 不带参数
CALL 存储过程名称[()]

创建不带参数的存储过程

例如：

1
CREATE PROCEDURE sp1() SELECT VERSION()

创建带有 IN 类型参数的存储过程

例如：

1
2
3
4
5
6
7
8
9
DELIMITER $$
CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END;
$$
DELIMITER ;

CALL removeUserById(3)

注：DELIMITER 为定界符

创建带有 IN 和 OUT 类型参数的存储过程

例如：

1
2
3
4
5
6
7
CREATE PROCEDURE removeUserAndReturnUserNums(ID p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums
END;

CALL removeUserAndReturnUserNums(27, @nums)

注：

  • INTO userNums 将 SELECT 的结果赋予变量 userNums
  • DECLARE 声明的变量为局部变量，只在 BEGIN…END 之间有效，并且 DECLARE 只能在第一行。 SET @var 声明的变量为用户变量，与客户端绑定。

创建带有多个 OUT 类型参数的存储过程

例如：

1
2
3
4
5
6
7
8
SELECT PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALL UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(ID) FROM users INTO userCounts;
END

CALL removeUserByAgeAndReturnInfos(23, @a, @b)

注：其中 ROW_COUNT() 返回插入、删除以及更新的行数。

修改存储过程

修改存储过程不能修改 过程体等内容。
如果想修改过程体，只能删除后重建。

1
2
3
ALTER PROCEDURE 过程名 [特性 ...]
COMMENT 'string' 
| {CONTAIN SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY { DEFINER | INVOKER}

删除存储过程

1
ALTER PROCEDURE [IF EXISTS] 过程名

存储过程与自定义函数

  • 存储过程实现的功能要复杂一些，而函数的针对性更强
  • 存储过程可以返回多个值，函数只能有一个返回值
  • 存储过程一般独立的来执行，而函数可以作为其他 SQL 语句的组成部分来出现