一、创建存储过程和函数
什么是创建存储过程和函数?
就是将经常使用的一组SQL语句组合在一起,并将这些SQL语句当做一个整体存储在MYSQL服务器中。
创建存储过程的语句是:CREATE PROCEDURE
创建存储函数的语句是:CREATE FUNCTION
CALL语句负责调用存储过程,只能输出变量返回值。
1、创建存储过程
语法:
CREATE PROCEDURE procedure_name( [ proc_parm[,...... ] ] ) [ characteristic...] routine_body
使用BEGIN......END来标志SQL语句的开始和结束
proc_parm指定存储过程的参数列表,列表形式如下:
[IN|OUT|INOUT] param_name type
其中in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型
参数charateristic指定存储过程的特性,有以下取值:
characteristic:
LANGUAGE SQL|[NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA }| SQL SECURITY { DEFINER |INVOKER }| COMMENT 'string'routine_body:
Valid SQL procedure statement or statements
LANGUAGE SQL :说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC :指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时,相同的输入会得到
相同的输出。
[NOT] DETERMINISTIC 表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为[NOT] DETERMINISTIC
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
SQL SECURITY { DEFINER | INVOKER } :指明谁有权限来执行。DEFINER 表示只有定义者才能执行
INVOKER 表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER
COMMENT 'string' :注释信息,可以用来描述存储过程或函数
注意“:创建存储过程,系统默认值是指定CONTAINS SQL,表示存储过程中使用了SQL语句
创建存储过程举例子:
mysql>DELIMITER $$
mysql> CREATE PROCEDUREproc_id()-> COMMENT '查看ID'
-> BEGIN
-> SELECT id FROMtest1_5;-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>DELIMITER ;mysql>CALL proc_id;+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00sec)
Query OK,0 rows affected (0.00sec)
2、创建存储函数
CREATE FUNCTION func_name ( [func_param[,....] ] ) [characteristic...] routine_body
注意:具体创建函数时,函数名不能和已经存在的函数名重名,推荐使用func_xxxx来命名
func_parameter为存储函数的参数列表,参数列表如下
[IN|OUT|INOUT]PARAM_NAMETYPE
其中,IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;
param_name表示参数名称;type表示参数类型,该类型可以是MYSQL数据库中的任意类型
RETURNS TYPE语句表示函数返回数据的类型;characteristics:指定存储函数的特性,取值与创建存储过程时相同
例子:
mysql>DELIMITER $$
mysql> CREATE FUNCTION func_c_name(id int(4))-> RETURNS char(4)-> COMMENT '查询name'
-> BEGIN
-> RETURN(SELECT name from test1_5 where test1_5.id =id);-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>DELIMITER ;
mysql> select func_c_name(1); #传入参数数字1 ,即id=1+----------------+
| func_c_name(1) |
+----------------+
| chao |
+----------------+
1 row in set (0.00 sec)
3、变量的使用
变量可以在子程序中声明并使用,这些变量的作用范围是在BEGIN...END程序中
1、定义变量
在存储过程中定义变量
DECLARE var_name[,varname]...date_type...[DEFAULT VALUE];
var_name为局部变量的名称。DEFAULT VALUE子句给变量提供一个默认值。值除了可以被声明为一个常数外,还可以被指定为一个表达式。没有DEFAULT的话,变量默认值为NULL
如果没有DEFAULT子句,初始值为NULL
常见的用法是:
DECLARE MYPARAM INT
或者定义并赋值
DECLARE MYPARAM INT DEFAULT 100;
2、为变量赋值
定义变量之后,为变量赋值可以改变变量的默认值,MYSQL中使用SET语句为变量赋值
SET var_name=expr[,var_name=expr]...
比如:
SET MAPARAM = 100
为多个变量赋值
DECLARE var1,var2,var3 INT;SET var1=10,var2=20;SET var3=var1+var2;
注意:MySQL中declare和set都可以定义变量并赋值,但是两者的作用范围不一样,declare定义的是局部变量(只在存储过程中的begin和end之间生效),而set定义的是全局变量,在整个会话中都起作用(比如某个应用的一个连接过程中)
3、通过SELECT....INTO语句为变量赋值
语法:
SELECT col_name[,....] INTO var_name[,.....]
FROM table_name WHERE condition
例如
DECLARE NAME CHAR(50);DECLARE id DECIMAL(8,2);SELECT id,NAME INTO id ,NAME FROM test1_5 WHERE id=1;
4、定义条件和处理程序
特定条件需要特定处理。这些条件可以联系到错误,以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题,
处理程序定义了在遇到这些问题时候应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。
这样可以增强存储程序处理问题的能力,避免程序异常停止运行
1、定义条件
DECLARE condition_name CONDITION FOR[condition_type]
[condition_type]:
SQLSTATE[VALUE] sqlstate_value |mysql_error_code
condition_name:表示条件名称
condition_type:表示条件的类型
sqlstate_value和mysql_error_code都可以表示mysql错误
sqlstate_value为长度5的字符串错误代码
mysql_error_code为数值类型错误代码,例如:ERROR1142(42000)中,sqlstate_value的值是42000,
定义ERROR1148(42000)错误,名称为command_not_allowed。
可以用两种方法定义//方法一:使用sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'
//方法二:使用mysql_error_codeDECLARE command_not_allowed CONDITION FOR SQLSTATE 1148
2、定义处理程序
MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下:
DECLARE handler_type HANDLER FORcondition_value[,...]sq_statement
handler_type:CONTINUE | EXIT |UNDO
condition_value:
SQLSTATE[VALUE] sqlstate_value |condition_name|SQLWARNING| NOT FOUND | SQLEXCEPTION | mysql_error_code
其中,handler_type参数指明错误的处理方式,该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。
CONTINUE表示遇到错误不进行处理,继续向下执行;
EXIT表示遇到错误后马上退出;
UNDO表示遇到错误后撤回之前的操作,MySQL中暂时还不支持这种处理方式。
因此,遇到错误时最好执行EXIT操作。如果事先能够预测错误类型,并且进行相应的处理,那么可以执行CONTINUE操作。
condition_value参数指明错误类型,该参数有6个取值。
sqlstate_value和mysql_error_code与条件定义中的是同一个意思。
condition_name是DECLARE定义的条件名称。
SQLWARNING表示所有以01开头的sqlstate_value值。
NOT FOUND表示所有以02开头的sqlstate_value值。
SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。
sq_statement表示一些存储过程或函数的执行语句,如果遇到定义的错误时,需要执行的存储过程或函数
例子:
//方法一:捕获sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET @info='CAN NOT FIND';//方法二:捕获mysql_error_codeDECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND';//方法三:先定义条件,然后调用DECLARE can_not_find CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR can_not_find SET
@info='CAN NOT FIND';//方法四:使用SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五:使用NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';//方法六:使用SQLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42000,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。
第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1148,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。
第三种方法是先定义条件,然后再调用条件。这里先定义can_not_find条件,遇到1148错误就执行CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。
第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出"CAN NOT FIND"信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息
(1)当没有条件处理时,执行结果的例子:
mysql> select * fromtest1_5;+----+------+
| id | name |
+----+------+
| 1 | chao |
+----+------+
1 row in set (0.00sec)
mysql> desctest1_5;+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | NO | PRI | NULL | |
| name | char(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00sec)
mysql>DELIMITER $$
mysql> CREATE PROCEDUREproc_insert()-> BEGIN
-> SET @x=1;-> INSERT INTO test1_5 (id) values (2);-> SET @x=2;-> INSERT INTO test1_5 (id) values (1);-> SET @x=3;-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>DELIMITER ;
mysql>CALL proc_insert();
ERROR1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select @x;+------+
| @x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
我定义了一个表,id列是主键,然后定义变量x,看看运行到哪一步骤,
从上面的例子中我们可以看出来当插入重复值的时候,mysql退出执行,因此此时的x=2。没有执行到x=3这一步
(2)当对主键重复的异常进行处理时的例子
mysql>DELIMITER $$
mysql> CREATE PROCEDUREproc_insert1()-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;-> SET @x=1;-> INSERT INTO test1_5(id) values(3);-> SET @x=2;-> INSERT INTO test1_5(id) values(3);-> SET @x=3;-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>delimiter ;
mysql>call proc_insert1();
Query OK,0 rows affected (0.00sec)
mysql> select @x,@x2;+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00sec)
调用条件处理的过程,遇到主键重的错误时,会按照定义的处理方式进行处理,我们例子中是CONTINUE操作,所以遇到错误就继续执行下面的语句
5、光标的使用
作用:查询语句可能查询出多条记录,在存储过程和函数中使用光标来逐条读取查询结果的几种记录
1.声明光标
MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下:
DECLARE cursor_name CURSOR FOR select_statement ;
其中,cursor_name参数表示光标的名称;select_statement参数表示SELECT语句的内容,返回一个用于创建光标的结果集
下面声明一个名为cur_employee的光标。代码如下:
DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;
上面的示例中,光标的名称为cur_employee;SELECT语句部分是从employee表中查询出name和age字段的值。
2.打开光标
MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下:
OPEN cursor_name ;
其中,cursor_name参数表示光标的名称。
下面打开一个名为cur_employee的光标,代码如下:
OPEN cur_employee ;
3.使用光标
MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下:
FETCH cur_employee INTO var_name[,var_name…] ;
其中,cursor_name参数表示光标的名称;var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。
下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中,代码如下:
FETCH cur_employee INTO emp_name, emp_age ;
上面的示例中,将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。
4.关闭光标
MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下:
CLOSE cursor_name ;
其中,cursor_name参数表示光标的名称。
【示例14-11】 下面关闭一个名为cur_employee的光标。代码如下:
CLOSE cur_employee ;
上面的示例中,关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。
例子:
mysql>delimiter $$
mysql> CREATE PROCEDUREproc_sun()-> BEGIN
-> DECLARE i_grade tinyint;-> DECLARE cur_grade CURSOR FOR select grade fromtest1_5;-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSEcur_grade;-> set @x1=0;-> set @x2=0;-> opencur_grade;->repeat-> FETCH cur_grade INTOi_grade;-> set @x1 = @x1 +i_grade;-> UNTIL 0 ENDREPEAT;-> closecur_grade;-> END;->$$Query OK,0 rows affected (0.00sec)
mysql>delimiter ;
mysql>call proc_sun();
Query OK,0 rows affected (0.00sec)
mysql> select @x1;+------+
| @x1 |
+------+
| 451 |
+------+
1 row in set (0.00 sec)
这个例子就相当于把表中的grade的列的值都相加起来。
注意:变量、条件、处理程序、光标都是通过DECLARE定义的,他们之间是有先后顺序的变量和条件必须在最前面生命,然后才能使光标的声明,最后才可以是处理程序的声明
6、流程控制语句的使用
存储过程和函数中可以使用流程控制来控制语句的执行。
MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
每个流程中可能包含一个单独语句,或者是使用BEGIN...END构造的复合语句,构造可以被嵌套
1、IF语句
IF语句用来进行条件判断。根据是否满足条件,将执行不同的语句。其语法的基本形式如下:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
其中,search_condition参数表示条件判断语句;statement_list参数表示不同条件的执行语句。
2、CASE语句
CASE语句也用来进行条件判断,其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
其中,case_value参数表示条件判断的变量;
when_value参数表示变量的取值;
statement_list参数表示不同when_value值的执行语句。
代码可以是下面的形式:
CASE
WHEN age=20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE ;
本示例中,如果age值为20,count1的值加1;否则count2的值加1。CASE语句都要使用END CASE结束。
3、LOOP语句
LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。
但是LOOP语句本身没有停止循环的语句,必须是遇到LEAVE语句等才能停止循环。
LOOP语句的语法的基本形式如下:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
其中,begin_label参数和end_label参数分别表示循环开始和结束的标志,这两个标志必须相同,而且都可以省略;
statement_list参数表示需要循环执行的语句。
下面是一个LOOP语句的示例。代码如下:
add_num: LOOP
SET @count=@count+1;
END LOOP add_num ;
该示例循环执行count加1的操作。因为没有跳出循环的语句,这个循环成了一个死循环。
LOOP循环都以END LOOP结束。
4、LEAVE语句
LEAVE语句主要用于跳出循环控制。其语法形式如下:
LEAVE label
其中,label参数表示循环的标志。
下面是一个LEAVE语句的示例。代码如下:
add_num: LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num ;
END LOOP add_num ;
该示例循环执行count加1的操作。当count的值等于100时,则LEAVE语句跳出循环。
1.用户变量:以"@"开始,形式为"@变量名"
用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
例子:
mysql> desctest1_5;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | NO | PRI | NULL | |
| name | char(4) | YES | | NULL | |
| grade | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> create procedureactor_insert()-> BEGIN
-> set @x=0;->ins:LOOP-> set @x = @x +1;-> IF @x = 100 thenleave ins;-> END IF;-> insert into test1_5 values(@x,'haha',100);-> ENDLOOP ins;-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>delimiter ;
mysql>call actor_insert();
Query OK,0 rows affected (0.11sec)
mysql> select count(*) fromtest1_5;+----------+
| count(*) |
+----------+
| 99 |
+----------+
1 row in set (0.00sec)
5、ITERATE语句
ITERATE语句也是用来跳出循环的语句。但是,ITERATE语句是跳出本次循环,然后直接进入下一次循环。
ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。
ITERATE语句的基本语法形式如下:
ITERATE label
6、REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
其中,statement_list参数表示循环的执行语句;search_condition参数表示结束循环的条件,满足该条件时循环结束。
下面是一个REPEAT语句的示例。代码如下:
REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT ;
该示例循环执行count加1的操作,count值为100时结束循环。
REPEAT循环都用END REPEAT结束。
7、WHILE语句
WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。
WHILE语句是当满足条件时,执行循环内的语句。
WHILE语句的基本语法形式如下:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
其中,search_condition参数表示循环执行的条件,满足该条件时循环执行;
statement_list参数表示循环的执行语句。
二、调用存储过程和函数
存储过程和存储函数都是存储在服务器端的SQL语句的集合。存储过程通过CALL语句来调用,存储函数的使用方法与MYSQL内部函数的使用方法时一样的。此外执行存储过程和存储函数需要拥有EXECUTE权限。
1、调用存储过程
CALL proc_name(parameter....)
其中parameter为存储过程的参数。
mysql>delimiter $$
mysql> create procedure proc_select(IN ID INT,OUT name char)-> COMMENT '查询薪水'
-> BEGIN
-> SELECT name from test1_5 where test1_5.id =ID;-> END;->$$
Query OK,0 rows affected (0.00sec)
mysql>delimiter ;mysql> call proc_select(10,@name);
2、调用存储函数
mysql>delimiter $$
mysql> create function proc_select4(ID INT(4)) RETURNS INT(4) BEGIN RETURN(SELECT grade from test1_5 where test1_5.id=ID); END;$$
Query OK,0 rows affected (0.00sec)
mysql>delimiter ;
mysql> select proc_select4(1);+-----------------+
| proc_select4(1) |
+-----------------+
| 100 |
+-----------------+
1 row in set (0.00sec)
mysql> select proc_select4(10);+------------------+
| proc_select4(10) |
+------------------+
| 100 |
+------------------+
1 row in set (0.00 sec)
注意:where子句中的id前面必须带上表名,即where test1_5.id =
3、查看存储过程和函数
SHOW {PROCEDURE|FUNCTION} STATUS {LIKE 'pattern'}
mysql> show procedure status like 'proc_select5'\G*************************** 1. row ***************************Db: test1
Name: proc_select5
Type:PROCEDUREDefiner: root@localhostModified:2018-11-17 19:50:39Created:2018-11-17 19:50:39Security_type: DEFINER
Comment: 查询name
character_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: utf8_general_ci1 row in set (0.00 sec)
查看有哪些存储过程直接:show procedure status\G
4、使用SHOW CREATE语句查看存储过程和函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} proc_name
mysql> show create procedureproc_select6 \G*************************** 1. row ***************************
Procedure: proc_select6
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_select6`(IN ID INT,OUT grade INT)
COMMENT'查询name'
BEGIN SELECT grade from test1_5 where test1_5.id=ID; ENDcharacter_set_client: utf8
collation_connection: utf8_general_ciDatabaseCollation: utf8_general_ci1 row in set (0.00 sec)
5、修改存储过程
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]characteristic:
{CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA }| SQL SECURITY { DEFINER |INVOKER }| COMMENT 'string'
6、删除存储过程和函数
DROP PROCEDURE pro_name
DROP FUNCTION func_name