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(Service CRUD 接口)
21	MyBatis-Plus	Mybatis-plus 4种条件构造器方式
22	MyBatis-Plus	Mybatis-Plus 执行自定义SQL
23	MyBatis-Plus	MyBatis-plus配置自定义SQL(执行用户传入SQL)
24	MyBatis-Plus	Mybatis-Plus(连接Hive)
25	MyBatis-Plus	Mybatis-Plus 代码生成器

一、概念

在开发过程中，经常会用到某一功能重复使用，为此MySQL引入了存储过程。

是一条或者多条的SQL语句的集合，存储过程就这些SQL封装成一个代码块，以便重复使用。

二、存储过程的创建

2.1、创建存储过程

使用create PROCEDURE语句创建存储过程。

代码语言：javascript复制

CREATE PROCEDURE sp_name ([proc_parameter])
[characters ...]routine_body

• CREATE PROCEDURE：创建存储过程的关键字。
• sp_name：为存储过程的名称。
• proc_parameter：存储过程的参数列表。
• **characters：**用于指定存储过程的特性。
• routine_body：是SQL代码的内容。也可以只是用begin ...end来表示SQL代码的开始和结束。

proc_parameter参数列表：

代码语言：javascript复制

[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：注释信息。

案例：

代码语言：javascript复制

CREATE PROCEDURE Proc ()
BEGIN
SELECT * FROM student;
END

2.2、变量的使用

在编写存储过程中，会使用变量保存数据处理过程中的值。MySQL中，变量可以在子程序中声明并使用，变量的作用范围是在BEGIN...END程序中。

想要在存储过程中使用变量，首先需要定义变量。使用declare语句定义变量。语法如下：

代码语言：javascript复制

DECLARE var_name [,varname]...date_type[DEFAULT value]

• var_name：为局部变量的名称。
• DEFAULT value：子句给变量提供一个默认值。该值除了可以被声明为一个常数之外，还可以被指定为一个表达式。
• 如果没有Default子句，变量的初始值为null。

案例：

代码语言：javascript复制

DECLARE myvariable INT DEFAULT 100;

更改变量值SET：

代码语言：javascript复制

SET var_name = expr[,var_name =expr]...;

案例：

代码语言：javascript复制

DECLARE var1,var2,var3 INT ;
SET var1=10,var2=20;
SET var3=var1 var3;

还可以通过SELECT ... INTO为一个或多个变量赋值。

代码语言：javascript复制

SELECT col_name[...] INTO var_name [...]table_expr;

• col_name：表示字段名称。
• var_name：表示定义的变量名称。
• table_expr：表示查询条件表达式，包括表名称和WHERE子句。

案例：

代码语言：javascript复制

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语句。

代码语言：javascript复制

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：为数值类型的错误代码。

案例：

代码语言：javascript复制

//方式一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE `42000`;
//方式二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148;

2、定义处理程序

定义完条件以后，还需要定义针对此条件的处理程序。MySQL中使用DECLARE语句处理程序。

代码语言：javascript复制

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：匹配数值类型错误代码。

案例：

代码语言：javascript复制

//方法一：捕获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关键字来声明光标。

代码语言：javascript复制

DECLARE cursor_name CURSOR FOR select_statement

• cursor_name：表示光标的名称。
• select_statement：表示select语句的内容，返回一个创建光标的结果集。

案例(声明cursor_student的光标)：

代码语言：javascript复制

DECLARE cursor_name CURSOR FOR select s_name,s_gender FROM student;

2、光标的使用

代码语言：javascript复制

OPEN cursor_name
FETCH cursor_name INTO var_name[，var_name]...

• cursor_name：表示参数的名称。
• var_name：表示将光标中SELECT语句查询出来的信息存入该参数中。
• var_name：必须在声明光标之前就定义好。

案例：

代码语言：javascript复制

FETCH cursor_student INTO s_name ,s_gender;

3、光标的关闭

使用完光标后，要将其关闭。

代码语言：javascript复制

CLOSE cursor_name

2.5、流程控制的使用

将多个SQL语句划分或者组合成符合业务逻辑的代码块。

包括：If语句、case语句、loop语句、while语句、leave语句、iterate语句、repeat语句。

1、IF语句

if语句是满足某个条件，则执行某个操作。

代码语言：javascript复制

IF expr_condition THEN statement_list
	[ELSEIF expr_condition THEN statement_list]
	[ELSE statement_list]
END IF

• expr_condition：表示判断条件。
• statement_list：表示SQL语句列表，可以包括一个或多个语句。

案例：

代码语言：javascript复制

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语句

格式一：

代码语言：javascript复制

CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END IF

案例：

代码语言：javascript复制

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

代码语言：javascript复制

SELECT
	last_name,job_id,salary,
CASE job_id 
			WHEN 'IT_PROG'  THEN 1.10 * salary 
			WHEN 'ST_CLERK' THEN 1.15 * salary 
			WHEN 'SA_REP'   THEN 1.20 * salary 
			ELSE salary 	END  "REVISED_SALARY" 
FROM employees;

格式二：

代码语言：javascript复制

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内的语句一直重复执行，直到跳出循环语句。

代码语言：javascript复制

[loop_label:]LOOP
	statement_list
END LOOP [loop_label]

• loop_label：表示LOOP语句的标注名称，该参数可以省略。
• statement_list：表示需要循环执行的语句。

案例：

代码语言：javascript复制

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语句退出循环。

代码语言：javascript复制

LEAVE label

5、ITERATE语句

ITERATE是再次循环，用于将执行顺序顺序转到语句段的开头处。

代码语言：javascript复制

ITERATE lable

• lable：表示循环的标志。
• ITERATE语句只可以出现在LOOP、REPEAT和while语句内。

代码语言：javascript复制

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语句

用于创建一个带有条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

代码语言：javascript复制

[repeat_lable:]REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_lable]

• repeat_lable：为REPEAT语句的标注名称，该参数是可选的。
• REPEAT语句内的语句或语句群被重复，知道expr_condition为真。

代码语言：javascript复制

DECLARE id INT DEFAULT 0;
REPEAT
SET id=id 1;
UNTIL id>=10;
END REPEAT;

7、WHILE 语句

创建一个带条件判断的循环过程，与REPEAT不同的是，while在语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。

代码语言：javascript复制

[while_lable:]WHILE expr_condition DO
	statement_list
END WHILE [while_lable]

• while_lable：为while语句的标注名称。
• expr_condition：为进行判断的表达式，如果表达式为真，WHILE语句内的语句或语句群将被执行，直至expr-condition为假，退出循环。

代码语言：javascript复制

DECLARE i INT DEFALULT 0;
WHILE i< 10 DO
SET i=i 1;
END WHILE;

三、存储过程的使用

3.1、调用存储过程

存储过程有多种调用方法。必须使用CALL语句调用，并且存储过程和数据库相关。如果要执行其他数据库中的存储过程，需要指定数据库的名称。

代码语言：javascript复制

CALL sp_name([parameter],...)

• sp_name：为存储过程的名称。
• parameter：为存储过程的参数。

案例：

代码语言：javascript复制

CALL countProcl("女",@num);

3.2、查看存储过程

用户可以使用SHOW STATUS语句、SHOW CREATEE语句和从系统中的information_schema数据库中查询。

1、SHOW STATUS语句查看存储过程的状态

代码语言：javascript复制

SHOW{PROCEDURE|FUNCTION} STATUS [LIKE'pattern']

2、SHOW CREATE语句查看存储过程的状态

代码语言：javascript复制

SHOW CREATE{PROCEDURE|FUNCTION} sp_name

返回一个可以用来创建已命名子程序的确切字符串。

代码语言：javascript复制

SHOW CREATE PROCEDURE chapter06.CountProc1

3、从information_schema.Routines表中查看存储过程的信息

代码语言：javascript复制

SELECT * FROM information_schema.Routines
WHERE ROUTINE NAME='CountProc1' AND ROUTINE_TYPE='PROCEDURE'

3.3、修改存储过程

MySQL可以使用ALTER语句修改存储过程的特性。

代码语言：javascript复制

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：表示注释信息。

代码语言：javascript复制

ALTER PROCEDURE ContProc1
MODIFIES SQL DATA
SQL SECURITY INVOKER;

目前，MySQL还不提供对已存在的数据存储的代码修改，如果一定要修改存储过程代码，必须要先将存储过程删除以后，再重新编写代码，或者创建一个新的存储过程。

3.4、删除存储过程

MySQL可以使用DROP语句删除存储过程。

代码语言：javascript复制

DROP{PROCEDURE|FUNCTION}[IF EXISTS] sp_name

• sp_name：表示要移除的存储过程的名称
• IF EXISTS：表示如果程序不存在，它可以避免发生错误，产生一个警告。该警告可以使用SHOW WARNINGS进行查询。

案例：

代码语言：javascript复制

DROP PROCEDURE Countroc1;

四、综合案例

4.1、创建数据库

代码语言：javascript复制

CREATE TABLE stu(id INT,name CARCHAR(50),class varchar(50));

插入数据：

代码语言：javascript复制

INSERT INTO stu VALUE(1,’Lucy','class1'),(2,'Tom','class1'),(3,"Rose",'class2')

4.2、创建存储过程

代码语言：javascript复制

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;

数据库 mysql 变量 存储过程 数据

0 人点赞