MySQL学习Day09——存储过程和函数

一、MySQL的存储过程

MySQL5.0起开始支持存储过程。简单地说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。存储过程就是数据库SQL语言层面的代码封装与重用

二、存储过程的优点:

1.简化操作，提高了SQL语句的重用性，减少了开发的压力

2.减少操作过程中的事务，提高效率

3.减少网络传输量

4.减少SQL语句暴露在网上的风险，提高了数据查询的安全性

三、存储过程的特性:

1.有输入输出参数，可以声明变量，有if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能

2.函数的普遍特性:模块化，封装，代码复用

3.速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤。

四、存储过程的分类:

1.没有参数(无参数无返回)

2.仅仅带IN类型(有参数无返回)

3.仅仅带有OUT类型(无参数有返回)

4.既带有IN又带有OUT(有参数有返回)

5.带有INOUT(有参数有返回)

格式:

DELIMITER 自定义结束符号
CREATE PROCEDURE 存储名([in,out,inout] 参数名 数据类型...)
BEGIN
    SQL语句
END 自定义的结束符号
DELIMITER;
调用存储过程:
CALL 存储过程名称(参数表);
自定义结束符号一般为$$

五、变量

在MySQL的存储过程和函数中，可以使用变量来存储查询或计算中间结果数据，或者输出最终的结果数据。

1.变量定义:

(1)局部变量:用户自定义，在BEGIN/END块中使用DECLARE定义，并且只在该代码块内有效。DECLARE声明的局部变量必须声明在BEGIN中的首行位置。定义格式如下:

DECLARE var_name type [DEFAULT var_value]

局部变量赋值:

方式一:SET var_name = value;
方式二:SELECT col_name [...] INTO var_name[,...] from table_name where condition
其中:
col_name参数表示查询的字段名称
var_name参数表示变量的名称
table_name表示表的名称
condition参数表示查询的条件
注意:当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列

获得局部变量的值:

SELECT var_name

(2)用户变量:用户自定义，当前会话有效，类比java的成员变量,不需要提前声明，可以之间赋值使用，使用即声明.用户变量可以在存储过程外使用。用户变量的声明格式如下:

@var_name

用户变量赋值和获得用户变量的值:

SET @var_name = value
SELECT ... INTO @var_name FROM ... WHERE ...
SELECT @var_name

用户变量和局部变量的对比: 

	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END语句块	BEGIN END中的第一句话	一般不用加@，需要指定类型

(3)系统变量：系统变量又分为全局变量和会话变量。全局系统变量(global)在MySQL启动时由服务器自动将它们初始化为默认值，这些默认值可通过更改my.ini这个文件来更改。会话变量(session)在每次建立一个新的连接的时候由MySQL来初始化，MySQL会将当前所有全局变量的值复制一份来作为会话变量，也就是说，如果在建立会话之后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。全局变量与会话变量的区别在于对全局变量的修改会影响到整个服务器，但是对会话变量的修改只会影响到当前的会话(当前的数据库连接)。一个会话对全局系统变量的修改会导致另外一个会话中的相应的全局系统变量改变。有些系统变量的值是可以利用语句来动态的修改的，但是有些系统变量的值是只读的，对于那些可以更改的系统变量，可以利用set语句进行更改。

全局系统变量:由系统提供，在整个数据库中有效

@@global.var_name
查看全局变量:
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'pattern';
查看某全局变量
:SELECT @@global.auto_increment_increment;
修改全局变量的值:
SET global sort_buffer_size = 40000;/set @@global.sort_buffer_size = 40000;

会话系统变量:由系统提供，当前会话有效

@@session.var_name
查看会话变量的值:
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'pattern';
查看某个会话变量的值:
SELECT @@session.var_name
修改某个会话变量的值:
SET @@session.var_name  = value;

六、存储过程中的传参:

(1)存储过程传参IN:IN表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。参数的名字可以和查询表的列名一致，此时需要在条件中使用 表名.列名  的格式。

(2)存储过程传参OUT:OUT表示从存储过程内部传值给调用者.存储过程内部需要使用select  ... into将值赋给out类型的变量。创建存储过程在参数列表声明变量时不需要加@，存储过程内部使用out类型的变量时不需要加@。只有在存储过程外赋值、调用存储过程传递的实际参数以及获取OUT类型的变量时才需要加@符号。

call procedure_name(@out_var_name);
select @out_var_name

(3)存储过程传参INOUT:INOUT表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值(即使函数执行完毕),使用方法同in和out类型的变量。

set @inout_var_name = value;
call procedure_name(@inout_var_name);
select @inout_var_name

七、存储过程中的流程控制:

(1)判断:IF语句中包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if,else if,else语法类似，其语法格式为:

IF search_condition_1 THEN statement_list_1;
    [ELSEIF search_condition_2 THEN statement_list_2];
    ...
    [ELSE statement_list_n];
END IF;

CASE语句是另一个条件判断的语句，类似于编程语言中的switch

格式一:
CASE case_value
    WHEN when_value1 THEN statement_list1;
    WHEN when_value2 THEN statement_list2;
    ...
    ELSE statement_listn;
END CASE;
格式二:
CASE 
    WHEN search_condition1 THEN statement_list1;
    WHEN search_condition2 THEN statement_list2;
    ...
    ELSE statement_listn;
END CASE;
    
    

(2)循环:循环是一段在程序中只出现一次但可能会连续运行多次的代码，循环中的代码会运行特定的次数，或者是运行到特定的条件成立时结束循环。循环分类包括while-do,repeat-until以及loop。

循环控制:leave类似于break，跳出结束当前所在的循环，iterate类似于continue，结束本次循环继续下次循环。LEAVE可以用在循环语句内或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。ITERATE只能用在循环语句内，表示重新开始循环，将执行顺序转到语句段开头处。使用LEAVE和ITERATE时必须定义循环语句标签。

循环结构四个要素:1.初始化条件、2.循环条件、3.循环体以及4.迭代条件

WHILE-DO:

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

REPEAT-UNTIL:

[标签:]REPEAT
    循环体;
UNTIL 条件表达式
END REPEAT[标签];
until 条件表达式后面不加分号
直到满足条件表达式后才会跳出repeat循环

LOOP:

[标签:] LOOP
    循环体;
    IF 条件表达式 THEN
        LEAVE [标签];
    END IF;
END LOOP;
loop循环必须加if表达式，否则就是一个死循环

八、游标CURSOR:游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理，游标的使用包括声明、OPEN、FETCH和CLOSE。游标提供了一种灵活的操作方式，能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构，让MySQL拥有面向过程开发的能力。

声明语法:
DECLARE cursor_name CURSOR FOR select_statement
打开语法:
OPEN cursor_name
取值语法:
FETCH cursor_name INTO var_name [,var_name....]
游标结果集中的字段数目必须和INTO后面的变量名数目一致，否则在存储过程的执行中MySQL就会提示错误
关闭语法:每次fetch取值时只能取出结果中的一行数据，此时游标会向下移动一行，直到到达结果集末尾为止。
CLOSE cursor_name
游标会占用系统资源，如果不及时关闭游标，游标会一直保持到存储过程结束，影响系统运行的效率

九、定义条件与处理程序

定义条件是实现定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或者错误时能够继续执行，这样可以增强存储程序处理问题的能力，避免程序异常停止运行。定义条件和处理程序在存储过程和存储函数中都是支持的。

(1)定义条件:定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰，它将一个错误名字和指定的错误条件关联起来，这个名字可以随后被用在定义处理程序的DECLARE HANDLER中。

DECLARE 错误名称 CONDITION FOR 错误码(或者错误条件)

(2)错误码的说明:MySQL_error_code和sqlstate_value都可以表示MySQL的错误，其中MySQL_error_code是数值类型错误代码而sqlstate_value是长度为5的字符串类型错误代码。

(3).定义异常处理HANDLER句柄:MySQL存储过程提供了对异常处理的功能，通过定义HANDLER来完成异常声明的实现。

DECLARE handler_action HANDLER
    FOR condition_value[,condition_value]...
    statement
handler_action:{
    CONTINUE|EXIT|UNDO
}
condition_value:{
    mysql_error_code|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION
}
handler_action：异常处理完毕后程序该如何执行
    continue:继续执行剩余代码
    exit:直接终止程序
    undo:目前不支持
condition_value:异常触发条件
    条件码:
    条件名:
        SQLSTATE '字符串错误码':表示长度为5的sqlstate_value类型的错误代码
        MySQL_error_code:匹配数值类型的错误代码
        错误名称:declare ... condition for ...中定义的错误条件名称
        SQLWARNING:匹配所有以01开头的SQLSTATE错误代码
        NOT FOUND:匹配所有以02开头的SQLSTATE错误代码
        SQLEXCEPTION:匹配没有被SQLWARNING和NOT FOUND捕获的SQLSTATE错误代码
statement:异常触发后执行什么代码，可以是set赋值语句或者是用begin...end包围的语句块

在语法中，声明变量、游标、handler必须按照先后顺序书写，否则创建存储过程将会出错

十、存储函数:在MySQL中，创建存储函数使用CREATE FUNCTION关键字,存储函数作为查询的一部分来进行调用

CREATE FUNCTION function_name ([param_name type[,...]])
RETURNS 返回值类型
[characteristic ...]
begin
    routine_body
    RETURN res;
end;
func_name:
存储函数的名称
param_name type:
可选项，表示存储函数的参数，type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中支持的全部类型。存储函数中全部参数都是IN类型的
returns type:
指定返回值的类型
characteristic:
可选项，指定存储函数的特性
routine_body:
SQL代码内容
调用存储函数:
SELECT function_name(参数表)

(1)存储函数和存储过程的对比:

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	有0个或者多个	用于更新
存储函数	FUNCTION	SELECT 存储函数()	只能是一个	用于查询结果为一个值并返回时

(2)查看存储过程和函数:

1.使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE {PROCEDURE|FUNCTION} 存储过程或存储函数名;

 2.使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'];
返回子程序的特征，如数据库、名字、类型、创建者和修改日期
[LIKE 'pattern']匹配存储过程或函数的名称，可以省略，当省略不写时，
会列出MySQL数据库中存在的所有存储过程或函数信息

3.从information_schema.Routines表中查看存储过程和函数信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程或存储函数名称' AND ROUTINE_TYPE = {'FUNCTION|PROCEDURE'}

(3)修改存储过程和函数：不修改存储过程或函数的功能，而是修改其相关的特性，使用ALTER语句实现

ALTER {PROCEDURE|FUNCTION} 存储过程或存储函数名称 [characteristic ...]

(4).删除存储过程和存储函数

DROP {PROCEDURE|FUNCTION} IF EXISTS 存储过程或存储函数名称;

(5).存储函数和存储过程的优点:

1.存储过程可以一次编译多次使用

2.可以减少开发工作量

3.存储过程的安全性强

4.可以减少网络传输量

5.良好的封装性

(6).存储函数和存储过程的缺点:

1.可移植性差

2.调试困难

3.存储过程的版本管理困难

4.不适合高并发的场景