MySQL操作之存储过程
序号 | 类型 | 地址 |
---|---|---|
1 | MySQL | MySQL操作之概念、SQL约束(一) |
2 | MySQL | MySQL操作之数据定义语言(DDL)(二) |
3 | MySQL | MySQL操作之数据操作语言(DML)(三) |
4 | MySQL | MySQL操作之数据查询语言:(DQL)(四-1)(单表操作) |
5 | MySQL | MySQL操作之数据查询语言:(DQL)(四-2)(多表查询) |
6 | MySQL | MySQL操作之数据控制语言:(DC)(五) |
7 | MySQL | MySQL操作之数据库函数 |
8 | MySQL | MySQL管理之数据类型 |
9 | MySQL | MySQL管理之索引 |
10 | MySQL | MySQL管理之事务管理 |
11 | MySQL | MySQL管理之存储过程 |
12 | MySQL | MySQL管理之视图 |
13 | MySQL | MySQL管理之数据备份与还原 |
14 | MySQL | Linux(centos 7.5)服务器安装MySQL |
15 | MyBatis | MyBatis从入门到多表关联 |
16 | MyBatis | MyBatis常用方法 |
17 | MyBatis | Mybatis逆向工程的使用(附文件地址) |
18 | MyBatis | spring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle) |
19 | MyBatis-Plus | Mybatis-Plus使用案例(包括初始化以及常用插件) |
20 | MyBatis-Plus | mybatis-plus代码生成器 |
21 | MyBatis-Plus | 自定义SQL |
22 | MyBatis-Plus | Mybatis-Plus(连接Hive) |
23 | MyBatis-Plus | MyBatis-plus配置自定义SQL(执行用户传入SQL) |
24 | MyBatis-Plus | Mybatis-Plus(Service CRUD 接口) |
文章目录
一、概念
在开发过程中,经常会用到某一功能重复使用,为此MySQL引入了存储过程。
是一条或者多条的SQL语句的集合,存储过程就这些SQL封装成一个代码块,以便重复使用。
二、存储过程的创建
2.1创建存储过程
使用create PROCEDURE
语句创建存储过程。
CREATE PROCEDURE sp_name ([proc_parameter])
[characters ...]routine_body
- CREATE PROCEDURE:创建存储过程的关键字。
- sp_name:为存储过程的名称。
- proc_parameter:存储过程的参数列表。
- **characters:**用于指定存储过程的特性。
routine_body
:是SQL代码的内容。也可以只是用begin ...end
来表示SQL代码的开始和结束。
proc_parameter
参数列表:
[IN|OUT|INOUT]param_name type
- IN: 表示输入参数。
- OUT: 输出参数
- INOUT: 既可表示输入,也可表示输出参数。
- param_name: 表示参数名称。
- type: 表示参数的类型(可以是MySQL中任意一种类型)。
characters
参数列表:
Language SQL
:说明routine_body部分是由SQL语句组成的,当前系统支持语句为SQL,SQL是language的唯一值。[Not]Deterministic
: 指明存储过程执行的结果是否确定。Not Deterministic
不确定,为默认值。{CONTAINS SQL|NO SQL |READS sql data|modifies sql data}
: 指明自语句使用SQL语句的限制。表示自语句中含有sql。SQL security{definer|invoker}
: 指明谁有权限来执行。definer:只有定义者才能执行。invoker
:表示拥有权限的调用者可以执行。默认为:definer
。COMMENT'String
:注释信息。
案例:
CREATE PROCEDURE Proc ()
BEGIN
SELECT * FROM student;
END
2.2 变量的使用
在编写存储过程中,会使用变量保存数据处理过程中的值。MySQL中,变量可以在子程序中声明并使用,变量的作用范围是在BEGIN...END
程序中。
想要在存储过程中使用变量,首先需要定义变量。使用declare
语句定义变量。语法如下:
DECLARE var_name [,varname]...date_type[DEFAULT value]
var_name
:为局部变量的名称。DEFAULT value
:子句给变量提供一个默认值。该值除了可以被声明为一个常数之外,还可以被指定为一个表达式。- 如果没有Default子句,变量的初始值为null。
案例:
DECLARE myvariable INT DEFAULT 100;
更改变量值SET
:
SET var_name = expr[,var_name =expr]...;
案例:
DECLARE var1,var2,var3 INT ;
SET var1=10,var2=20;
SET var3=var1+var3;
还可以通过SELECT ... INTO
为一个或多个变量赋值。
SELECT col_name[...] INTO var_name [...]table_expr;
col_name
:表示字段名称。var_name
:表示定义的变量名称。table_expr
:表示查询条件表达式,包括表名称和WHERE子句。
案例:
DECLARE s_grade FLOAT;
DECLARE s_grader CHAR(2);
SELECT grade,gender INTO s_grade,s_gender FROM student WHERE name='rose';
2.3 定义条件和处理程序
定义条件是实现定义程序执行过程中遇到的问题,处理程序定义了在遇到问题时应当采取的处理方式,并且保证存储过程中遇到警告或错误时能继续执行下去。
1、定义条件
在编写存储过程中,用DECLARE
语句。
DECLARE condition_name CONDITION FOR [condition_type];
// conditoin_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
:为数值类型的错误代码。
案例:
//方式一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE `42000`;
//方式二:使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148;
2、定义处理程序
定义完条件以后,还需要定义针对此条件的处理程序。MySQL中使用DECLARE
语句处理程序。
DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement
handler_type: CONTINUE|EXIT|UNDO
condition_value:
|condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code
handler_type
:为错误处理方式:参数取三个值CONTINUE
、EXIT
、UNDO
。
CONTINUE
:表示遇到错误不处理,继续执行。
EXIT
:表示遇到错误马上退出。
UNDO
:表示遇到错误后撤回之前的操作,MySQL不支持这样的操作。
sp_statement
:参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程。condition_value
:表示错误类型。可以有
SQLSTATE[VALUE]sqlstate_value
:包含5个字符的字符串错误值。
condition_name
:表示DECLARE CONTINUE
定义的错误条件名称。
SQLWARNING
:匹配所有以01开头的SQLSTATE
错误代码。
NOT FOUND
:匹配所有以02开头的SQLSTATE
错误代码。
SQLEXCEPTION
:匹配所有没有被SQLWARING
和NOT FOUND
捕获的SQLSTATE
错误代码。
mysql_error_code
:匹配数值类型错误代码。
案例:
//方法一:捕获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 CONTINUE FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';
//方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info ='NO_SUCH_FOUND';
//方法六:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
2.4 光标的使用
在编写存储过程时,查询语句可能返回多条记录,如果数据量过大,则需要使用光标来逐条读取查询结果集中的记录。
在使用光标前需要先声明光标。光标必须声明在声明变量、条件之后、声明处理程序之前。
1、光标的声明
MySQL中使用DECLARE
关键字来声明光标。
DECLARE cursor_name CURSOR FOR select_statement
cursor_name
:表示光标的名称。select_statement
:表示select语句的内容,返回一个创建光标的结果集。
案例(声明cursor_student的光标):
DECLARE cursor_name CURSOR FOR select s_name,s_gender FROM student;
2、光标的使用
OPEN cursor_name
FETCH cursor_name INTO var_name[,var_name]...
cursor_name
:表示参数的名称。var_name
:表示将光标中SELECT
语句查询出来的信息存入该参数中。var_name
:必须在声明光标之前就定义好。
案例:
FETCH cursor_student INTO s_name ,s_gender;
3、光标的关闭
使用完光标后,要将其关闭。
CLOSE cursor_name
2.5、流程控制的使用
将多个SQL语句划分或者组合成符合业务逻辑的代码块。
包括:If
语句、case
语句、loop
语句、while
语句、leave
语句、iterate
语句、repeat
语句。
1、IF语句
if
语句是满足某个条件,则执行某个操作。
IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]
[ELSE statement_list]
END IF
expr_condition
:表示判断条件。statement_list
:表示SQL语句列表,可以包括一个或多个语句。
案例:
IF val IS NULL
THEN SELECT 'val is NULL‘;
ELSE SELECT 'val is NOT NULL';
END IF;
判断val值是否为空,假如val为空,输出字段’val is NULL’;否则,输出"val is NOT NULL"。
2、CASE语句
格式一:
CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END IF
案例:
CASE val
WHEN 1 THEN SELECT ’val is 1‘
WHEN 2 THEN SELECT 'val is 2'
ELSE SELECT ’val is not 1 or 2'
END IF
格式二:
CASE
WHEN expr_codition THEN statement_list
[WHEN expr_codition THEN statement_list]
[ELSE statement_list]
END IF
3、LOOP语句
LOOP
循环语句用来重复执行某些语句,与if
和case
语句相比,loop
只是创建一个循环操作的过程,并不进行条件判断。
LOOP
内的语句一直重复执行,直到跳出循环语句。
[loop_label:]LOOP
statement_list
END LOOP [loop_label]
loop_label
:表示LOOP语句的标注名称,该参数可以省略。statement_list
:表示需要循环执行的语句。
案例:
DECLARE id INT DEFAULT 0:
add_loop:LOOP
SET id=id +1;
IF id>=10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
循环执行了id+1的操作。当id的值小于10时,循环重复执行;当id的值大于或等于10时,使用LEAVE
语句退出循环。
4、LEAVE语句
当不满足循环条件时,需要使用LEAVE
语句退出循环。
LEAVE label
5、ITERATE语句
ITERATE
是再次循环,用于将执行顺序顺序转到语句段的开头处。
ITERATE lable
lable
:表示循环的标志。ITERATE
语句只可以出现在LOOP
、REPEAT
和while
语句内。
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
p1的初始值为0,如果p1的值小于10时,重复执行p1+1的操作;当p1大于或等于10并且小于20时,打印内容"p1 is between 10 and 20";当p1大于20时,退出循环。
6、REPEAT语句
用于创建一个带有条件判断的循环过程,每次语句执行完毕后,会对条件表达式进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句。
[repeat_lable:]REPEAT
statement_list
UNTIL expr_condition
END REPEAT [repeat_lable]
repeat_lable
:为REPEAT
语句的标注名称,该参数是可选的。REPEAT
语句内的语句或语句群被重复,知道expr_condition
为真。
DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10;
END REPEAT;
7、WHILE 语句
创建一个带条件判断的循环过程,与REPEAT
不同的是,while
在语句执行时,先对指定的表达式进行判断,如果为真,则执行循环内的语句,否则退出循环。
[while_lable:]WHILE expr_condition DO
statement_list
END WHILE [while_lable]
while_lable
:为while
语句的标注名称。expr_condition
:为进行判断的表达式,如果表达式为真,WHILE
语句内的语句或语句群将被执行,直至expr-condition
为假,退出循环。
DECLARE i INT DEFALULT 0;
WHILE i< 10 DO
SET i=i+1;
END WHILE;
三、存储过程的使用
3.1、调用存储过程
存储过程有多种调用方法。必须使用CALL
语句调用,并且存储过程和数据库相关。如果要执行其他数据库中的存储过程,需要指定数据库的名称。
CALL sp_name([parameter],...)
sp_name
:为存储过程的名称。parameter
:为存储过程的参数。
案例:
CALL countProcl("女",@num);
3.2、查看存储过程
用户可以使用SHOW STATUS
语句、SHOW CREATEE
语句和从系统中的information_schema
数据库中查询。
1、SHOW STATUS语句查看存储过程的状态
SHOW{PROCEDURE|FUNCTION} STATUS [LIKE'pattern']
2、SHOW CREATE语句查看存储过程的状态
SHOW CREATE{PROCEDURE|FUNCTION} sp_name
返回一个可以用来创建已命名子程序的确切字符串。
SHOW CREATE PROCEDURE chapter06.CountProc1
3、从information_schema.Routines表中查看存储过程的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE NAME='CountProc1' AND ROUTINE_TYPE='PROCEDURE'
3.3、修改存储过程
MySQL可以使用ALTER
语句修改存储过程的特性。
ALTER{PROCEDURE|FUNCTION}sp_name [characteristic...]
sp_name
:表示存储过程或者函数的名称。characteristic
:表示要修改存储过程的哪个部分。
CONTAINS SQL
:表示子程序包含SQL语句,但不包含读或写数据的语句。
NO SQL
:表示子程序中不包含SQL语句。
READS SQL DATA
:表示子程序中包含读数据的语句。
MODIFIES SQL DATA
:表示子程序中包含写数据的语句。
SQL SECURITY{DEFEINER|INVOKER}
:指明谁有权限来执行。
DEFINER
:表示只有定义者自己才能够执行。
INVOKER
:表示调用者可以执行。
COMMENT
:表示注释信息。
ALTER PROCEDURE ContProc1
MODIFIES SQL DATA
SQL SECURITY INVOKER;
目前,MySQL还不提供对已存在的数据存储的代码修改,如果一定要修改存储过程代码,必须要先将存储过程删除以后,再重新编写代码,或者创建一个新的存储过程。
3.4、删除存储过程
MySQL可以使用DROP
语句删除存储过程。
DROP{PROCEDURE|FUNCTION}[IF EXISTS] sp_name
sp_name
:表示要移除的存储过程的名称IF EXISTS
:表示如果程序不存在,它可以避免发生错误,产生一个警告。该警告可以使用SHOW WARNINGS
进行查询。
案例:
DROP PROCEDURE Countroc1;
四、综合案例
4.1、创建数据库
CREATE TABLE stu(id INT,name CARCHAR(50),class varchar(50));
插入数据:
INSERT INTO stu VALUE(1,’Lucy','class1'),(2,'Tom','class1'),(3,"Rose",'class2')
4.2、创建存储过程
CREATE PROCEDURE addcount(out count INT)
BEGIN
DECLARE itmp INT;
DECLARE cur_id CURSOR FOR SELECT id FROM stu;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT count(*) INTO count FROM stu;
SET @sum=0;
OPEN cur_id;
REPEAT
FETCH cur_id INTO itmp;
IF itmp <10
THEN SET @sum=@sum + itmp;
END IF;
UNTIL 0 END REPEAT;
CLOSE cur_id;
END;