1、存储过程
1》创建存储过程：create procedure
create procedure sp_name ([in | out | inout] param_name type) [characteristics ...] routine_body
characteristics指定存储过程的特性：
1>language sql：说明routine_body部分是由sql语句组成的，当前系统支持的语言为sql，sql是language特性的唯一值。
2>[not] deterministic：指明存储过程执行的结果是否确定。deterministic表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为not deterministic。
3>｛contains sql | no sql | reads sql data | modifies sql data｝：指明子程序使用sql语句的限制。contains sql表明子程序包含sql语句，但是不包含读写数据的语句；no sql表明子程序不包含sql语句；reads sql data说明子程序包含读数据的语句；modifies sql data表明子程序包含写数据的语句。默认情况下，系统会指定为contains sql。
4>sql security{definer | invoker}：指明谁有权限来执行。definer表示只有定义者才能执行。invoker表示拥有权限的调用者可以执行。默认情况下，系统指定为definer。
5>comment 'string'：注释信息，可以用来描述存储过程或函数。
routine_body：sql代码的内容，可以用begin...end;来表示sql代码的开始和结束。
//最简单的存储过程
create procedure p_get_avg() begin ... end;
//"delimiter //"的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号";"，
//为了避免与存储过程中sql语句的结束符相冲突，需要使用delimiter改变存储过程的结束符，并
//"end//"结束存储过程。"delimiter ;"则是恢复默认的";"结束符。
mysql> delimiter // mysql> create procedure p_get_avg() -> begin -> select * from test; -> end// Query OK, 0 rows affected (0.02 sec) mysql> delimiter ;
2、存储函数
1》创建存储函数：create function
//指定in、out、inout只对procedure是合法的，在function中总是默认为in类型
//如果return返回的类型不同于returns指定的类型，返回值将会被强制转换为恰当的类型。
create function func_name([in | out | inout] param_name type) returns type [characteristic ...] routine_body
mysql> delimiter // mysql> create function f_get_avg() -> returns char(25) -> begin -> return (select name from test where id=1); -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;
mysql> delimiter // mysql> drop function if exists f_get_avg; -> CREATE FUNCTION f_get_avg() -> RETURNS int -> begin -> declare i int default 0; -> select 666 into i from dual; -> return i; -> end// Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> select f_get_avg(); +-------------+ | f_get_avg() | +-------------+ | 666 | +-------------+ 1 row in set (0.00 sec) mysql>
注：trigger和function都需要写成 select ...into 这种句式，否者会报1415错误？
CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_avg`() RETURNS int(11) begin declare i int default 0; /*select 666 into i from dual;*/ return i; end
CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_avg`() RETURNS int(11) begin declare i int default 0; /*select 666 into i from dual;*/ set i=999; return i; end
3、变量的使用
1》在存储过程中使用declare语句定义变量：
declare var_name [,var_name]... data_type [default value];
即：
declare var1,var2,var3 int;
2》为变量赋值：定义变量后，MySQL使用set语句为变量赋值
set var_name = expr [,var_name=expr] ...;
或者
select col_name[,...] into var_name[,...] tb_expr;
即：
set var1=10,var2=20;
4、定义条件和处理程序：都是使用declare关键字
1》定义条件
//codition_name：表示条件的名称；condition_type参数表示条件的类型；
//sqlstste_value和mysql_error_code：都可以表示MySQL的错误
//sqlstate_value：为长度为5的字符串类型错误代码。
//mysql_error_code：为数值类型错误代码。
//例如：error 1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值为1142。
declare condition_name condition for [condition_type] 其中[condition_type]:sqlstate [value] sqlstate_value
| mysql_error_code
即：
declare condition_name condition for sqlstate [value] sqlstate_value
或者
declare condition_name condition for mysql_error_code
举例：
//定义"error 1148(42000)"错误，名称为command_not_allowed
//方法一：使用sqlstate_value declare command_not_allowed condition for sqlstate '42000';
//方法二：使用mysql_error_code declare command_not_allowed condition for 1148;
2》定义处理程序
declare handler_type handler for condition_value[，...] sp_statement
sp_statement：程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数
//错误处理方式
//continue：表示遇到错误不处理。继续执行；
//exit：遇到错误马上退出；
//undo：表示遇到错误后撤回之前的操作，MySQL暂时不支持这样的操作。 handler_type:continue | exit | undo
//错误类型
//sqlstate [value] sqlstate_value：包含5个字符的字符串错误值
//condition_name：表示declare condition定义的错误条件名称
//mysql_error_code：匹配数值类型错误代码
//sqlwarning：匹配所有以01开头的sqlstate错误代码
//not found：匹配所有以02开头的sqlstate错误代码
//sqlexception：匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码
condition_value: sqlstate [value] sqlstate_value | condition_name | mysql_error_code | not found | sqlexception | sqlwarning
举例：
//方法一：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE'; //方法二：捕获mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @info=' NO_SUCH_TABLE '; //方法三：先定义条件，然后调用 DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=' NO_SUCH_TABLE '; //方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; //方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info=' NO_SUCH_TABLE '; //方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
mysql> DELIMITER // mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO t VALUES (1); -> SET @x = 2; -> INSERT INTO t VALUES (1); -> SET @x = 3; -> END; -> // Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ; mysql> CALL handlerdemo(); Query OK, 0 rows affected (0.02 sec) mysql> select @x; +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> select @x2; +------+ | @x2 | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select * from t; +----+ | s1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql>
@x是一个用户变量，执行结果@x等于3，说明存储过程被执行到了最后面一句。如果省去异常处理那一句，第2个insert因为主键约束强制失败之后，存储过程可能已经采取默认（exit）路径，此时select @x返回的结果可能是2。
"@var_name"表示用户变量，使用set语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。
当客户端连接退出时，该客户端连接的所有变量将自动释放。
5、光标的使用：MySQL中的光标只能在存储过程、存储函数中使用
1》声明光标
declare cursor_name cursor for select_statement;
2》打开关标
open cursor_name;
3》使用光标
fetch cursor_name into var_name[，var_name] ...;
4》关闭光标
close cursor_name;
举个例子：
create procedure p3() begin declare id int; declare name varchar(15); declare flag int default 0; -- 声明游标 declare mc cursor for select * from class1; declare continue handler for not found set flag = 1; -- 打开游标 open mc; -- 获取结果 loop_label_0:loop fetch mc into id,name; if flag=1 then -- 当无法fetch会触发handler continue leave loop_label_0; end if; -- 这里是为了显示获取结果 insert into class2 values(id,name); -- 关闭游标 end loop;
close mc; end; call p3();-- 不报错 select * from class2;
6、流程控制使用
MySQL中用于流程控制的语句有：if、case、loop、leave、iterate、repeat、while语句。每个流程可能包含一个单独语句，或者使用begin...end构造的符合语句，构造可以被嵌套。
1》if语句
//statement_list可以包含一个或者多个语句 if expr_condition then
statement_list [elseif expr_condition then statement_list ...] ... [else statement_list] end if
2》case语句。与“控制流程函数”中的case是不同的。
//第一种方式： //匹配表达式的值 case case_expr when when_value then statement_list [when when_value then statement_list] ... [else statement_list] //不能有else null子句 end case //第二种方式： //逐个表达式执行，直到有一个表达式为true被执行 case when expr_condition then statement_list [when expr_condition then statement_list] ... [else statement_list] //不能有else null子句 end case
3》loop语句
[loop_label:] loop statement_list
//如果要跳出循环,这时候label是必须的
if expr_condition then leave loop_label
end if; end loop [loop_label]
4》leave语句
//用来退出任何被标注的流程控制构造，包括begin...end和循环体。
leave label
5》iterate语句
//将执行顺序转到语句段开头处
//iterate只可以出现在loop、repeat、while语句内，iterate表示再次循环，label参数表示循环的标志。
//iterate语句必须跟在循环标志前面。 iterate label
CREATE PROCEDURE doiterate() BEGIN DECLARE p1 INT DEFAULT 0; my_loop: LOOP SET p1= p1 + 1; IF p1 < 10 THEN ITERATE my_loop; ELSEIF p1 > 20 THEN LEAVE my_loop; END IF; SELECT 'p1 is between 10 and 20'; END LOOP my_loop; END
6》repeat语句
创建一个带条件判断的循环过程，每次语句执行完毕，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。相当于do...while语句。
[repeat_label:]repeat statement_list until expr_condition end repeat [repeat_label]
7》while语句
[while_label:]while expr_condition do statement_list end while [while_label]
7、调用存储过程、存储函数
存储过程的调用必须使用call语句，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称。
存储函数的调用与MySQL中定义的函数的调用方式相同。
1》调用存储过程
call sp_name([parameter[,...]])
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_get_name`(in i_id int,out o_name varchar(25))
begin
select name into o_name from test t where t.id=i_id;
end
-----------------------------------
mysql> call p_get_name(1,@name); Query OK, 1 row affected (0.00 sec) mysql> select @name; +-------+ | @name | +-------+ | Lucy | +-------+ 1 row in set (0.00 sec) mysql>
2》调用存储函数：与MySQL内部定义的函数一样的使用方法。
CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_avg`() RETURNS char(50) CHARSET utf8 begin return (select avg(salary) from test); end -------------------------------------- mysql> select f_get_avg(); +-------------+ | f_get_avg() | +-------------+ | 1180 | +-------------+ 1 row in set (0.00 sec) mysql>
8、查看存储过程和函数，方法有3种：
1》show status
//[like 'pattern']指匹配存储过程或函数的名称
show ｛procedure | function｝ status [like 'pattern']
mysql> show procedure status like 'p_get_name%' G *************************** 1. row *************************** Db: mybatis Name: p_get_name Type: PROCEDURE Definer: root@localhost Modified: 2019-09-05 00:28:04 Created: 2019-09-05 00:28:04 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) mysql>
2》show create
show create ｛procedure | function｝ sp_name
mysql> show create function f_get_avg G *************************** 1. row *************************** Function: f_get_avg sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU TION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_avg`() R ETURNS char(50) CHARSET utf8 begin return (select avg(salary) from test); end character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) mysql>
3》从系统的information_schema数据库查询
MySQL中存储过程和存储函数的信息存储在information_schema数据库下的Routines表中。
select * from information_schema.Routines where routine_name=' sp_name ' and routine_type='procedure | function';
mysql> select * from information_schema.routines where routine_name='p_get_name' and routine_type='procedure' G *************************** 1. row *************************** SPECIFIC_NAME: p_get_name ROUTINE_CATALOG: def ROUTINE_SCHEMA: mybatis ROUTINE_NAME: p_get_name ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin select name into o_name from test t where t.id=i_id; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2019-09-05 00:28:04 LAST_ALTERED: 2019-09-05 00:28:04 SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBS TITUTION ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.00 sec) mysql>
9、修改存储过程、函数（并不是修改其中的代码）
alter {procedure | function} sp_name [characteristic ...]
characteristic指定存储过程的特性，可能的取值有：
contains sql：表示子程序包含sql语句，但不包含读或写数据的语句。
no sql：表示子程序中不包含sql语句。
reads sql data：表示子程序中包含读数据的语句。
modifies sql data：表示子程序中包含写数据的语句。
sql security {definer | invoker}：指明谁有权限来执行。
definer：表示只有定义者自己才能够执行。
invoker：表示调用者可以执行。
comment 'string'：表示注释信息。
10、删除存储过程、函数
drop {procedure | function} [if exists] sp_name