9.1 创建存储过程和函数

9.1.1 创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法：

CREATE PROCEDURE 存储过程名称 ([proc_parameter])
[characteristics ...] routine_body

备注：proc_parameter：为指定存储过程的参数列表

​ characteristics：是存储过程特性

​ routine_body：是sq代码内容，可以用BEGIN...END表示sql开始结束时间

proc_parameter列表形式如下：

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

备注：IN表示输入参数，OUT表示输出参数，INOUT表示即可输入也可输出

characteristics指定存储过程特性，有以下取值：

LANGUAGE SQL：说明这部分有SQL组成，SQL是唯一值

[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定：确定，相同输入得到相同输出；不确定，相同输入可能得到不同输出，默认不确定

{CONTAINS SQL | NO SQL | READS SQL DATE | MODIFIES SQL DATE}：指明子程序sql使用限制，按照顺序依旧代表如下意思：子程序包含sql，不包含写数据的语句；子程序不包含SQL语句；子程序包含读写数据的语句；子程序包含写数据语句；默认子程序不包含sql

SQL SECURITE {DEFINER | INVOKER}：指明谁有权限来执行，DEFINER表示只有定义者才可以执行，INVOKER表示拥有权限的调用者才可以执行，默认定义者

COMMENT'string'：注释信息，描述存储过程或函数

9.1.2 创建存储函数

创建存储过程，要使用CREATE FUNCTION语句，基本语法格式：

CREATE FUNCTION 存储函数名字([proc_parameter])
RETURN type
[characteristics ...] routine_body

备注：对应的指定内容可以看一下上面的备注

举例：

-- 创建存储函数，名为test，返回select语句的所有结果，返回结果为字符串型
CREATE FUNCTION test()
RETURNS CHAR(50)
RETURN(SELECT name FROM tab WHERE id = 1)

注意：如果RETURN里面返回的类型不同于RETUENS定义的类型，会被强转

注意：指定参数IN、OUT和INOUT只对PROCEDURE是合法的。(FUNCTION中总是默认为IN参数)。RETURNS子句只能对FUNCTION做指定，对于函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

9.1.3 变量的使用

变量可以在子程序中声明并使用，变量作用范围在BETWEEN...END程序中

1. 定义变量

   -- 语法：
   DECLARE 局部变量名字[,varname]... 数据类型 [默认值];
   
   -- 举例
   DECLARE name INT DEFAULT 100;

   注意：默认值不填，初始值为NULL

2. 为变量赋值

   通过SET赋值

   -- 语法
   SET var_name = expr [, var_name = expr]...;
   
   -- 举例
   DECLARE var1, var2, var3 INT;
   SET var1 = 10, var2 = 20;
   SET var3 = var1 + var2;

   通过SELECT...INTO赋值

   -- 语法
   SELECT col_name[,...] INTO var_name[,...] tab_expr;
   
   -- 举例
   DECLARE name CHAR(50);
   DECLARE money DECIMAL(8,2);
   SELECT u.name, u.money INTO name, money
   FROM USER u WHERE u.id = 1;

   备注：col_name表示字段名；

   ​ var_name表示定义的变量名；

   ​ tab_expr表示查询条件表达式，包括表名与where语句

9.1.4 定义条件和处理程序

1. 定义条件

   -- 语法
   DECLARE condition_name CONDITioN FOR [condition_type]
   [condition_type]
   SQLSTART [VALUE] sqlstart_value(长度为5的字符串类型) | mysql_error_code(数值类型错误码)
   
   -- 举例
   DECLARE not_allow CONDITION FRO SQLSTART '42000';
   DECLARE not_allow CONDITION FRO 11480;

   备注：condition_name表示条件名称

   ​ condition_type表示条件类型

   ​ sqlstart_value | mysql_error_code表示MySQL的错误

2. 定义处理程序

   语法：

   DECLARE gandler_type HANDLER FOR condition_value[,...] sp_statement
   handler_type:
       CONDITION | EXIT | UNDO
   
   condition_value:
       SQLSTART[VALUE] sqlstart_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

   备注：handler_type错误处理方式，CONDITION表示不处理，EXIT表示有错退出，UNDO表示有错回滚

   ​ condition_value表示错误类型：

   ​ SQLSTART[VALUE] sqlstart_value表示5个字符的错误值，

   ​ condition_name表示DECLARE_CONDITION定义的错误条件名称，

   ​ SQLWARNING匹配所有01开头的SQLSTART错误代码，

   ​ NOT FOUND匹配所有02开头的SQLSTART错误代码，SQLEXCEPTION匹配没被SQLWARNING、NOT FOUND捕获的SQLSTART错误代码，

   ​ mysql_error_code匹配数值类型错误码

   举例：

   -- sqlstart_value
   DECLARE CONTINUE HANDLER FOR SQLATART '42S02' SET @info='NO_SUCH_TABLE';
   
   -- mysql_error_code
   DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
   
   -- 先定义条件，然后调用 
   DECLARE no_such_table CONDITION FRO 1146;
   DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
   
   -- 使用SQLWARNING
   DECLARE EXIT HANDLER FOR SQLWARNING SET @info='NO_SUCH_TABLE';
   
   -- 使用NOT FOUND
   DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
   
   -- 使用SQLEXCEPTION
   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='NO_SUCH_TABLE';

9.1.5 光标的使用

1. 光标的声明

   -- 语法
   DECLARE cursor_name CURSOR FOR select_statement
   
   -- 例子
   DECLARE name CURSOR FOR SELECT u.name FROM user ;

   备注：cursor_name表示光标名称

   ​ select_statement表示SQL语句内容，用于创建光标结果集

2. 打开光标

   -- 语法
   OPEN 光标名称
   
   -- 举例
   OPEN name

3. 使用光标

   -- 语法
   FETCH 光标名 INTO 光标中的参数 [, 光标中的参数]
   
   -- 举例
   FETCH name INTO u.name

4. 关闭光标

   -- 语法
   CLOSE 光标名
   
   -- 举例
   CLOSE name

注意：光标只能在存储过程和函数中使用

9.1.6 流程控制的使用

1. IF语句(判断)

   -- 语法
   IF 条件表达式 THEN 真时执行的语句
       [ELSEIF 条件 THEN 真时执行的语句]
       [ELSE 执行的语句]
   END IF

   注意：不同于IF()函数

   ​ 结尾要用END IF来结束

2. CASE语句(判断)

   -- 语法1
   CASE 条件表达式
       WHEN 条件表达式可能的值 THEN 真时执行的语句
       [WHEN 条件表达式可能的值 THEN 真时执行的语句]
       [ELSE 真时执行的语句]
   END CASE
   
   -- 语法2
   CASE
       WHEN 条件表达式 THEN 真时执行的语句
       [WHEN 条件表达式 THEN 真时执行的语句]
       [ELSE 真时执行的语句]
   END CASE

3. LOOP语句(循环)

   -- 语法
   [标注名称:] LOOP
       循环执行的语句
   END LOOP [标注名称]

4. LEAVE语句(跳出循环，类似break)

   -- 语法
   LEAVE 循环的标注名称
   
   -- 举例
   add_num: LOOP
       SET @count=@count+1;
       IF @count=50 THEN LEAVE add_num;
   END LOOP add_num;

   备注：例子中，表示每次循环都是count+1，当count等于50时候，终止循环

5. ITERATE语句(跳出循环，类似continue)

   -- 语法
   ITERATE 循环的标注名称

   注意：可以使用在LOOP、REPEAT和WHILE中

6. REPEAT语句(带条件判断的循环)

   -- 语法
   [标注名称:] REPEAT
       循环执行的语句
       UNTIL 限定条件
   END REPEAT[标注名称]

7. WHILE语句

   -- 语法
   [标注名称:] WHILE 判断表达式 DO