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 procedurep_get_avg()begin...end;
//"delimiter //"的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号";",
//为了避免与存储过程中sql语句的结束符相冲突,需要使用delimiter改变存储过程的结束符,并
//"end//"结束存储过程。"delimiter ;"则是恢复默认的";"结束符。
mysql> delimiter //mysql> create procedurep_get_avg()-> begin
-> select * fromtest;-> end//Query OK,0 rows affected (0.02sec)
mysql> delimiter ;
2、存储函数
1》创建存储函数:create function
//指定in、out、inout只对procedure是合法的,在function中总是默认为in类型
//如果return返回的类型不同于returns指定的类型,返回值将会被强制转换为恰当的类型。
create function func_name([in | out | inout]param_name type)returnstype[characteristic ...] routine_body
mysql> delimiter //mysql> create functionf_get_avg()-> returns char(25)-> begin
-> return (select name from test where id=1);-> end//Query OK,0 rows affected (0.00sec)
mysql> delimiter ;
mysql> delimiter //mysql> drop function if existsf_get_avg;-> CREATE FUNCTIONf_get_avg()-> RETURNS int
-> begin
-> declare i int default 0;-> select 666 into i fromdual;-> returni;-> end//Query OK,0 rows affected (0.02sec)
Query OK,0 rows affected (0.02sec)
mysql>delimiter ;
mysql> selectf_get_avg();+-------------+
| f_get_avg() |
+-------------+
| 666 |
+-------------+
1 row in set (0.00sec)
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;*/
returni;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;returni;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_valuedeclare command_not_allowed condition for sqlstate '42000';//方法二:使用mysql_error_codedeclare 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| notfound|sqlexception|sqlwarning
举例:
//方法一:捕获sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';//方法二:捕获mysql_error_codeDECLARE 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';//方法四:使用SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五:使用NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';//方法六:使用SQLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
mysql> DELIMITER //mysql> CREATE PROCEDUREhandlerdemo ()-> 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.02sec)
mysql>DELIMITER ;
mysql>CALL handlerdemo();
Query OK,0 rows affected (0.02sec)
mysql> select @x;+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00sec)
mysql> select @x2;+------+
| @x2 |
+------+
| 1 |
+------+
1 row in set (0.00sec)
mysql> select * fromt;+----+
| s1 |
+----+
| 1 |
+----+
1 row in set (0.00sec)
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 procedurep3()begin
declare id int;declare name varchar(15);declare flag int default 0;--声明游标
declare mc cursor for select * fromclass1;declare continue handler for not found set flag = 1;--打开游标
openmc;--获取结果
loop_label_0:loopfetch mc intoid,name;if flag=1 then --当无法fetch会触发handler continue
leave loop_label_0;end if;--这里是为了显示获取结果
insert into class2 values(id,name);--关闭游标
endloop;closemc;end;
call p3();--不报错
select * from class2;
6、流程控制使用
MySQL中用于流程控制的语句有:if、case、loop、leave、iterate、repeat、while语句。每个流程可能包含一个单独语句,或者使用begin...end构造的符合语句,构造可以被嵌套。
1》if语句
//statement_list可以包含一个或者多个语句if expr_condition thenstatement_list[elseif expr_condition then statement_list ...]...[else statement_list]
end if
2》case语句。与“控制流程函数”中的case是不同的。
//第一种方式://匹配表达式的值casecase_exprwhen when_value thenstatement_list[when when_value then statement_list]...[else statement_list] //不能有else null子句
end case
//第二种方式://逐个表达式执行,直到有一个表达式为true被执行case
when expr_condition thenstatement_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 PROCEDUREdoiterate()BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOPSET p1= p1 + 1;IF p1 < 10 THENITERATE my_loop;
ELSEIF p1> 20 THENLEAVE my_loop;END IF;SELECT 'p1 is between 10 and 20';ENDLOOP my_loop;END
6》repeat语句
创建一个带条件判断的循环过程,每次语句执行完毕,会对条件表达式进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句。相当于do...while语句。
[repeat_label:]repeat
statement_list
until expr_conditionend repeat [repeat_label]
7》while语句
[while_label:]whileexpr_condition do
statement_listend 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.00sec)
mysql> select @name;+-------+
| @name |
+-------+
| Lucy |
+-------+
1 row in set (0.00sec)
mysql>
2》调用存储函数:与MySQL内部定义的函数一样的使用方法。
CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_avg`() RETURNS char(50) CHARSET utf8begin
return (select avg(salary) fromtest);end
--------------------------------------
mysql> selectf_get_avg();+-------------+
| f_get_avg() |
+-------------+
| 1180 |
+-------------+
1 row in set (0.00sec)
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:PROCEDUREDefiner: root@localhostModified:2019-09-05 00:28:04Created:2019-09-05 00:28:04Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: utf8_general_ci1 row in set (0.00sec)
mysql>
2》show create
show create {procedure | function} sp_name
mysql> show create functionf_get_avg \G*************************** 1. row ***************************
Function: f_get_avg
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TIONCreate Function: CREATE DEFINER=`root`@`localhost` FUNCTION`f_get_avg`() R
ETURNSchar(50) CHARSET utf8begin
return (select avg(salary) fromtest);endcharacter_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: utf8_general_ci1 row in set (0.00sec)
mysql>
3》从系统的information_schema数据库查询
MySQL中存储过程和存储函数的信息存储在information_schema数据库下的Routines表中。
select * frominformation_schema.Routineswhere 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:PROCEDUREDATA_TYPE:
CHARACTER_MAXIMUM_LENGTH:NULLCHARACTER_OCTET_LENGTH:NULLNUMERIC_PRECISION:NULLNUMERIC_SCALE:NULLDATETIME_PRECISION:NULLCHARACTER_SET_NAME:NULLCOLLATION_NAME:NULLDTD_IDENTIFIER:NULLROUTINE_BODY: SQL
ROUTINE_DEFINITION:begin
select name into o_name from test t where t.id=i_id;endEXTERNAL_NAME:NULLEXTERNAL_LANGUAGE:NULLPARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS:CONTAINSSQL
SQL_PATH:NULLSECURITY_TYPE: DEFINER
CREATED:2019-09-05 00:28:04LAST_ALTERED:2019-09-05 00:28:04SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBS
TITUTION
ROUTINE_COMMENT:
DEFINER: root@localhostCHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci1 row in set (0.00sec)
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