MySQL-Part4：变量 & 存储过程、函数 & 流程控制结构

1. 变量
   主要可以分为两大类：系统变量(全局变量&会话变量) ， 自定义变量(用户变量&局部变量)

   • 1.1 系统变量

     • 作用域
       ① 全局变量作用域：服务器每次启动将为所有的全局变量赋初始值。 针对所有会话(连接有效)，但重启后会失效。(若需要永久修改该变量的值，则需要在配置文件中手动修改)
       ② 会话变量作用域：仅仅针对当前会话(连接)有效

     • 相关语法

       #查看所有/部分 全局/会话系统变量
       show global|[session] variables [like clause]  
       #查看某个指定的系统变量
       select @@global|[@@session].系统变量名; 
       
       #为某系统变量赋值
       set @@global.|[@@session.]系统变量名 = 设定值; 
       # 例如：set global.autocommit = 0;该设定将在所有会话中生效。
       #      set [session.]autocommit = 0;该设定将仅针对此次会话。
       或者
       set global|[session] 变量名 = 设定值;
       

   • 1.2 自定义变量

     • 作用域
       ① 用户变量：同系统变量中的会话变量的作用域相同，仅对当前会话(连接)有效 。标志符号为变量名前置的‘@’
       ② 局部变量：无@标识，且仅仅在定义它的begin — end 中有效(即存储过程体中) , 且必须为第一句。

     • 相关语法

       # 局部变量的赋值，查看与用户变量语法一致。
       
       #创建用户变量并赋值
       set @var_name=值; set @var_name:=值; select @var_name:=值;
       
       #更新/赋值   在定义存储过程中，常用到该方式给out型参数赋值
       select 字段 into @var_name from tab_name [where |group by |having];
       
       #声明局部变量----需在储存体中定义，赋值语法同上(无 ‘@‘)
       declare 变量名 类型 [default default_value];
       
       #查看自定义变量
       select @var_names;
       

2. 存储过程&函数

   • 2.1 定义

     • 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数
       ① 提高了代码的重用性;
       ② 简化了应用开发人员的很多工作 ;
       ③ 减少数据在数据库和应用服务器之间的传输，提高了数据处理的效率。

     • 区别
       ① 函数必须有返回值且仅有一个，而存储过程可以没有;
       ② 存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的(无需标识)。
       ③ 应用场景
       ④ 存储过程，函数，视图三者的区别

     • 前情提要---delimiter

       If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.
       To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

       delimiter //
       CREATE PROCEDURE dorepeat(p1 INT)
       -> BEGIN
       -> SET @x = 0;
       -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
       -> END
       -> //
       #Query OK, 0 rows affected (0.00 sec)
       delimiter ;
       CALL dorepeat(1000);
       #Query OK, 0 rows affected (0.00 sec)
       
       SELECT @x;
       #  +------+
       #  | @x |
       #  +------+
       #  | 1001 |
       #  +------+
       #1 row in set (0.00 sec)
       
       

       You can redefine the delimiter to a string other than //, and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash () character because that is the escape character for MySQL.

       #delimiter 命令仅用于指定当前mysql客户端识别一条语句的标识，当指定 $ 为客户端的分隔标识后，存储过程体中的';'仍然会被服务器端所识别，因为服务器端仍然以分号作为一条sql语句的标识。分号与其他具体的存储过程体是作为一个整体直接传送到服务器端的，不会在客户端进行解析。
       

   • 2.2 存储过程相关语法

     #创建存储过程
     delimiter $  #指定分隔标识符为 $
     create procedure 存储过程名([参数类型 参数名 数据类型][,参数类型 参数名 数据类型])
     #参数类型可以分为：IN，OUT,INOUT
     begin
          #存储过程体：
          #一组合法的SQL语句，每条SQL语句结尾必须添加分号   
     end $     
     #如果存储过程体只有一句话，begin,end 可以省略。
     delimiter ; #将分隔符恢复为日常使用的分号
     
     #调用存储过程
     call 存储过程名(参数类型 参数名 数据类型);
     select @def_name;  #@def_names是存储过程参数列表中标识为out或inout类型的参数
       
     #删除存储过程
     drop procedure [if exists ]pro_name; #不支持同时删除多个procedure
     #查看存储过程的定义信息
     show create procedure pro_name;
     #查看当前数据库中的存储过程信息
     show procedure status [like 'pattern'|where expr];
     

     • example

       delimiter &
       create procedure pc_test (IN v_1 int ,INOUT var_a int ,INOUT var_b int,OUT v_2 int)
       begin
           set var_a=v_1+var_a*2;
           set var_b=v_1+var_b*3;
           set v_2=var_a+var_b;
       end &
       
       delimiter ;
       set @var_temp_a=20;
       set @var_temp_b=25;
       
       call pc_test(100,@var_tmep_a,@var_temp_b,@var_count);
       select @var_temp_a;   ---> 140
       select @var_temp_b;   ---> 175
       select @var_count;    ---->315
       

   • 与存储过程相关的表INFORMATION_SCHEMA.ROUTINES show full columns from information_schema.routines;

   • 2.3 函数相关语法

     #创建函数
     delimiter $  #指定分隔标识符为 $
     create function fun_name([参数名 数据类型, ...]) return 返回类型
     begin
          #函数体：
          #一组合法的SQL语句，每条SQL语句结尾必须添加分号   
          return 值 ;
     end $     
     #如果函数体只有一句话，begin,end 可以省略。
     delimiter ; #将分隔符恢复为日常使用的分号
     
     #调用存储过程
     select fun_name(参数列表);
     
     #删除函数
     drop function [if exists ]fun_name;
     #查看函数的定义信息
     show create function fun_name;
     

     • example

       delimiter #
       create function fun_2(empName varchar(30)) returns double
       begin
       	set @sarl_emp=0;
           select salary into @sarl_emp
           from employees 
           where last_name = empName;
           return @sarl_emp;
       end #
       delimiter ;
       select fun_2('OConnell') as her/his_sarlary;
       

3. 流程控制结构-------主要应用在begin end结构中

   • 分支结构

     • if函数： if(判断表达式,成立时返回的值,不成立时返回的值);

     • if分支结构 (只能应用于begin_end结构中)

       IF 条件1 THEN 语句1; 
         ELSEIF 条件2 THEN 语句2;
         ...
       END IF;
       

       

     • case分支结构

       #判断等值                           #判断各种条件
       case exp                           case 
       when value1 then 返回值1/语句1;      when exp1 then 返回值1/语句1; 
       when value2 then 返回值/语句2;       when exp2 then 返回值/语句2;
       ...                                ...
       else 返回值/语句;                    else 返回值/语句;
       end case;                          end case;
       /*满足when条件则执行完then语句后跳出case结构，若when条件皆不满足，则执行else语句，若皆不满足且else 语句省略 ，则返回NULL值。
       

       注意：在Flow control functions ----流控制函数中的case when 语句 与 存储过程/函数中的case when 语句 结束方式有所区别(前者为END,后者为END CASE ),且后者else 返回值不可为NULL。详细参见手册12.5节 Flow Control Function & 13.6.5.1节 Compound Statement Syntax -----Flow Control Statements----Case Statement
       

   • 循环结构( 用于begin_end结构体中 )

     • 分类：while ,loop , repeat 循环控制：iterate , leave

     • label: while 条件 do  
            loop_list  
       end while label;  #先判断后循环
       
       label: loop 
          loop_list 
       end loop label; #死循环
       
       label: repeat 
          loop_list 
          until 终止条件 
       end repeat label #先循环后判断
       
       #可使用iterate ,leave 来控制当前循环的进行，前者 终止当前循环，进入下一轮的循环，相当于continue的效果；后者直接跳出循环体，相当于break的效果。
       #example:
       if condi_1 then dosth;
       elseif condi_2 then iterate label;
       elseif condi_3 then leave label;  
       end if ;