MySQL-day05

函数、存储过程和游标

一、基本概念

1. 什么是存储过程(Stored PROCEDURE)？

• 简单的 SQL 语句不过是一些命令，用过即结束，我们尚不能把代码存储起来，像各种语言中的函数一样，在需要的时候随意调用。存储过程和函数使我们能够把软件中的逻辑部分保存起来以便日后使用。

存储过程和函数就是命名的代码块。

2. 存储过程的优势

1. 更快的速度
   • 程序和数据库服务器之间不再需要传递大量的 SQL 代码，只需发送一个存储过程或函数命令即可。
   • 存储过程和函数是编译过的，可直接执行，无需像普通 SQL 那样，每次都需要编译。
2. 减少代码冗余
   • 多个应用程序访问同一个数据库是十分常见的，为雷同的功能编写一个存储过程不仅可以减少代码冗余，而且还能够使相关程序更容易维护。
3. 提高数据库的安全性
   • 在安全性要求较高的领域，客户程序必须使用存储过程进行所有的数据库操作，这样可以对每次数据访问进行监控，并将操作记入日志。

3. 存储过程的缺点

• 存储过程的最大缺点是：移植性差。很难讲存储过程从一个数据库移植到另一个数据库离去， 因为每个数据库的语法和语法扩展都不尽相同。

4. 案例：一个简易的函数案例

1> 定义 shorten() 函数

USE d7111;
DROP FUNCTION IF EXISTS shorten;

DELIMITER $$
-- 简化字符串函数：将一个长字符串缩短
-- 如果长度小于等于 13，直接返回
-- 如果 13 < len <= 20, 截取前 5 个字符，加上 ...
-- 如果 len > 20, 截取前后 5 个字符，中间加上 ...
CREATE FUNCTION shorten(s VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
	DECLARE len INT DEFAULT 0;
	SET s = TRIM(s);
	SET len = LENGTH(s);

	IF ISNULL(s) OR len = 0 THEN
		RETURN '';
	ELSEIF len <= 13 THEN
		RETURN s;
	ELSEIF len <= 20 THEN
		RETURN CONCAT(LEFT(s, 5), '...');
	ELSE
		RETURN CONCAT(LEFT(s, 5), '...', RIGHT(s, 5));
	END IF;
END;$$

DELIMITER ;

说明：DELIMITER $$ 命令将默认终结符改为 $$，因为定义函数时的语句需要使用 ; 作为语句结束符，为了不让环境立即执行该语句，所以需要修改终结符。使用完可以改回去。

2> 调用 shorten() 函数

SELECT shorten('abcdefg');

SELECT shorten('abcdefg hijklmn');

SELECT shorten('abcdefg hijklmn opq rst uvw xyz');

二、存储过程的语法规则

1. 基本规则

1. 语句必须用 ; 分隔，就连分支和循环的控制结构也必须用 ; 结束。
2. BEGIN-END：没有位于 SP 关键字之间(如 THEN 和 END IF 之间)的多条 SQL 语句必须放在关键字 BEGIN 和 END 之间。
3. 变量：供 SP 内部使用的局部变量和局部参数不加 @ 前缀。在 SP 内部使用的普通 SQL 变量必须加 @ 前缀。变量名不要使用特殊字符。
4. 注释：以 -- 开始的是行注释。

2. 变量

1> 变量的分类

MySQL 中的变量分为 3 类：

1. 普通变量：以 @ 开头，在 MySQL 连接被关闭时失去内容
2. 系统变量和服务器变量：这里变量的内容是 MySQL 服务器的工作状态或属性，它们以 @@ 开头，例如：许多系统变量都有两种形式，一个对应当前连接(如@@session.wait_timeout)，另一个对应整个 MySQL 服务器(如@@global.wait_timeout，默认值)
3. 存储过程里的局部变量：在存储过程内部定义，没有特殊标识，但不能和数据表和数据列重名。

2> 变量的声明

变量的声明必须放在 BEGIN-END 语句块里，并且必须在语句块的头部。

1. 语法

   DECLARE a, b, ... datatype [DEFAULT value];
   

2. 必须为所有局部变量声明数据类型
3. 如果没有默认值，则默认值为 NULL。

3> 变量的赋值

-- 语法1：SET
SET a = 20;
-- 语法2：select into
SELECT 3 + 4 INTO v;
SELECT COUNT(0) FROM student INTO a;

SELECT studentName, address
FROM student WHERE studentNo = 3
INTO s_name, s_address;

3. 代码块

一条以上的 SQL 语句构成的函数或过程必须以关键字 BEGIN 开头、以关键字 END 结束。此外，有时在 IF 等结构中也需要使用代码块。

[blockname :]
BEGIN
    DECLARE variables;   -- 变量声明
    DECLARE cursors;     -- 游标声明
    DECLARE conditions;     -- 条件声明
    DECLARE handler;        -- 异常声明
    other sql commands;
END [blockname];

说明： blockname 是代码块的标号，如果起了标号，则结束时必须写做 END blockname。标号的目的是为了使用 LEAVE blockname 命令提前退出这个语句块。

4. 分支

1. IF-THEN-ELSE

   IF score = 100 THEN
       SELECT '厉害';
   ELSEIF score >= 90 THEN
       SELECT '优秀';
   ELSEIF score >= 80 THEN
       SELECT '良好';
   ELSEIF score >= 60 THEN
       SELECT '及格';
   ELSE
       SELECT '不及格';
   END IF;
   

2. CASE

   CASE ming_ci
       WHEN 1 THEN SELECT '第一名';
       WHEN 2 THEN SELECT '第二名';
       WHEN 3 THEN SELECT '第三名';		
       ELSE
           SELECT '无名氏';
   END CASE;
   

5. 循环

1. REPEAT-UNTIL 循环

   [loopname:] REPEAT
       循环体
   UNTIL 条件 END REPEAT [loopname];
   

   说明： 当条件成立时，退出。
2. WHILE 循环

   [loopname:] WHILE 条件 DO
       循环体
   END WHILE [loopname];
   

3. LOOP 循环

   loopname: LOOP
       循环体
       IF 条件 THEN
           LEAVE loopname;
       END IF;
   END LOOP loopname;
   

4. LEAVE 和 ITERATE 语句
   • LEAVE loopname 用于在循环体内跳出循环。
   • ITERATE loopname 用于再执行一遍循环体。

6. 出错处理句柄

• SP 里的 SQL 命令在执行过程中可能出错，所有 SQL 也像其他一些程序设计语言那样向程序员提供了一种利用 出错处理句柄(error handler) 来处理这类错误的机制。

1> 声明出错处理句柄

DECLARE type HANDLER FOR condition1, condition2,... command;

说明：

1. type(类型)：目前只有 CONTINUE 和 EXIT 两种：
2. condition(条件)：这里可列出一个或多个出错条件，他们是出错处理句柄被调用的前提。出错处理条件可以用以下几种方式给出：
   • SQLSTATE ‘errorcode’：单个 SQL 出错代码，errorcode 是错误代码。
   • SQLWARNING：涵盖了 SQLSTATE 编号为 01nnn 的所有错误。
   • NOT FOUND：涵盖了 SQLSTATE 编号不以 01 或 02 开头的错误。
   • mysqlerrorcode：MySQL 出错代码，不是 SQLSTATE 出错代码。MySQL 出错代码的完整清单和对应的 SQLSTATE 值可以在 MySQL 在线文档里查到：https://dev.mysql.com/doc/refman/8.0/en/error-handling.html
   • conditionname：用 DECLARE CONDITION 命令定义出来的出错处理条件，
3. command(命令)：执行出错时将要执行的命令。因为这里只允许放上一条命令，所以它通常是一个变量赋值命令，该变量将在后续的出错处理代码中使用。这里必须给出一条命令。

2> 使用 出错处理句柄

DECLARE myerror VARCHAR(128) DEFAULT 'none';
-- 1. 定义条件，指代对应的错误代码
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
-- 2. 定义错误处理句柄，用来以指定方式处理指定条件，发生错误时，执行命令
DECLARE CONTINUE HANDLER FOR duplicate_key SET myerror = 'dupkey';

SELECT myerror;
INSERT INTO student VALUES (2, 'dddddd', 'abc', '男', 3, '110', '', '1991-1-1', '', '');

SELECT myerror;

7. 游标 Cursor

• 游标是一个指向数据表里一条记录的指针。 通过它，我们可以一条一条的处理记录。

1. 游标的使用步骤

-- 使用游标求平均年龄
DECLARE done INT DEFAULT 0;
DECLARE age DOUBLE DEFAULT 0;
DECLARE age_sum DOUBLE DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
-- 1. 定义游标
DECLARE stu_cursor CURSOR
FOR SELECT YEAR(NOW()) - YEAR(borndate) FROM student;
-- 2. 定义出错处理句柄，处理 NOT FOUND 条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 计算总人数
SELECT COUNT(0) FROM student INTO cnt;

-- 计算年龄之和
-- 3. 打开游标
OPEN stu_cursor;
sumloop: LOOP
    FETCH stu_cursor INTO age;
    -- 4. 如果找不到下一条记录，则出现 NOT FOUND 错误，从而跳出循环
    IF done = 1 THEN LEAVE sumloop; END IF;
    SET age_sum = age_sum + age;
END LOOP sumloop;
CLOSE stu_cursor;	-- 关闭游标
SELECT age_sum;
SELECT cnt;
SELECT age_sum / cnt;

2. 不足与缺陷

MySQL5.0 版 游标有如下限制：

1. 游标是只读的。
2. 游标只能前进。
3. 游标是敏感的，即：使用游标读数据时，数据表不允许发生任何变化。如果非要这么做，MySQL 服务器可能出现难以预料的行为。

三、使用存储过程

1> 如何定义存储过程

-- 创建
CREATE PROCEDURE name ([parameter-list])
[options]
sqlcode
-- 删除
DROP PROCEDURE IF EXISTS name

2> 参数与返回值

1. 过程的参数分为：输入参数(IN)，输出参数(OUT)和输入输出参数(INOUT)。IN 参数必须有初值。
2. 函数的参数没有这些分类，函数只能进行值传递。
3. 过程没有返回值，而函数有返回值。

DROP	PROCEDURE IF EXISTS test_proc;

DELIMITER $$
CREATE PROCEDURE test_proc(IN stu_no INT, OUT stu_name VARCHAR(50), INOUT stu_grade INT)
BEGIN
	SELECT studentName, gradeId FROM student 
	WHERE studentNo = stu_no AND gradeId = stu_grade
	INTO stu_name, stu_grade;	
END;$$

DELIMITER ;

SET @grade = 2;
CALL test_proc(5, @name, @grade);
SELECT @name, @grade;

3> 调用存储过程

只能用 call proc() 来调用存储过程。
如下所示：

SET @grade = 2;
CALL test_proc(5, @name, @grade);
SELECT @name, @grade;

四、使用函数

1> 如何定义函数

-- 创建
CREATE FUNCTION name ([parameter-list]) RETURNS datatype
[options]
sqlcode
-- 删除
DROP FUNCTION IF EXISTS name;

2> 调用函数

函数可嵌入 SQL 语句中，或放在表达式里。

SELECT shorten('abcdefg hijklmn opq rst uvw xyz');

五、存储过程与函数的区别

方面	存储过程	函数
调用方式	只能用CALL命令调用	可嵌入sql命令中
返回值	可返回一个或多个查询结果	只能返回一个标量
参数	可使用值参和引用参数（IN(默认)，OUT,INOUT）	只能用值参
内部可用的命令	所有sql语句	不能使用访问表的sql命令
可调用内容	允许调用其他过程和函数	只能调用函数
语法	CREATE PROCEDURE name([param list]) [option] BEGIN code END	CREATE FUNCTION name([param list]) RETURNS datatype [option] BEGIN code END

六、应用案例

1. 插入一个学生，并返回 id

   DROP	PROCEDURE IF EXISTS insert_stu;
   
   DELIMITER $$
   CREATE PROCEDURE insert_stu(IN stu_pwd VARCHAR(50), IN stu_name VARCHAR(50), IN stu_sex CHAR(2),
   				IN stu_gradeId INT, OUT stu_no INT)
   myproc: BEGIN
   	SELECT studentNo FROM student WHERE studentName = stu_name INTO stu_no;
   	-- 有同名学生，返回
   	IF stu_no > 0 THEN
   		LEAVE myproc;
   	END IF;
   
   	INSERT student (studentNo, loginPwd, studentName, sex, gradeId)
   	VALUES (NULL, stu_pwd, stu_name, stu_sex, stu_gradeId);
   	SELECT LAST_INSERT_ID() INTO stu_no;
   END myproc;$$
   
   DELIMITER ;
   

2. 调用

   CALL insert_stu('ddd', 'lao8', '男', 3, @stu_no);
   SELECT @stu_no;
   
   CALL insert_stu('ddd', '孙悟空', '男', 3, @stu_no);
   SELECT @stu_no; -- 2
   

---