MySQL存储过程

MySQL存储过程

存储过程就是在数据库中定义的一些sql语句的集合，可以直接调用存储过程执行已经定义好的sql语句，从而避免重复编写相同的sql语句的问题。

存储过程

存储过程是一组为了完成特定功能的sql语句的集合，使用存储过程的目的在于将常用的复杂工作预先使用sql语句定义好，并指定一个名称存储起来，数据库管理系统会将存储过程进行编译和优化后存储在数据库服务器中。以后需要使用对应功能时，只需要通过存储过程名称即可直接调用。

• 一个存储过程是一个可编程的函数，在数据库中创建并保存，可以SQL语句和一些特殊控制结构组成

• 常见的sql语句在执行时需要先编译优化，然后执行。但是存储过程已经经过了编译和优化后存储在数据库中

• 在存储过程中可以使用标准sql和数据库管理系统自身提供的控制结构语句

• 存储过程可以用于转换数据、数据迁移、制作报表等功能，一次编译，可以随时调用，完成特定的功能

• 使用存储过程不仅可以提高数据库的访问效率，同时还可以提供数据库使用的安全性

存储过程的特点

• 封装性。

• 增强sql语句的功能和灵活性

• 可以减少网络数据传输

• 高性能

• 提高数据库的安全性和数据的完整性，使数据独立

存储过程

创建存储过程的基本语法：

create procedure 存储过程名称(参数列表,...) 存储过程体 

1、首先配置sql语句命令结束符号为其它符号，不是默认的分号

delimiter $$

2、创建一个简单的存储过程实现访问tb_dept中的所有数据

create procedure p1() begin 
select * from tb_dept; 
end;
$$

3、存储过程提交后将命令结束符恢复为原来的分号

4、调用存储过程 call 存储过程名称();

查看存储过程

可以通过 show procedure status 查看存储过程的状态信息，例如创建时间、修改时间、字符集等

show procedure status like 'p1';
mysql> show procedure status like 'p1' 
\G *************************** 1. row*************************** 
				Db: test Name: p1 
				Type: PROCEDURE
                Definer: root@localhost
                Modified: 2022-05-08 16:01:24
                Created: 2022-05-08 16:01:24 
                Security_type: DEFINER 
                Comment:
                character_set_client: gbk 
                collation_connection: gbk_chinese_ci 
                Database Collation: utf8_general_ci 
1 row in set (0.00 sec)

可以通过 show create procedure 存储过程名 可以存储过程的定义

show create procedure p1;

查找指定名称的存储过程：从系统的数据字典表中查看指定存储过程的信息

select * from information_schema.routines where routine_name='存储过程名';

尽量不要直接操作数据字典表，否则可能会导致数据库系统无法正常使用

修改存储过程

修改存储过程的语法：用于修改存储过程的某些特征，例如执行权限

alter procedure 存储过程名 特征;

修改存储过程的定义，可以先删除原来的存储过程，再使用相同的名称创建新的存储过程

删除存储过程

删除存储过程语法： drop procedure 过程名;

存储过程名称后面没有参数列表，也不需要括号。

存储过程解释

1、存储过程的名称

存储过程默认在当前数据库中创建，如果需要在其它数据库中创建则需要在存储过程名称前面添加一个

数据库的名称，例如 db2.pp1() 。注意尽量不要和系统内置的函数名称相同否则出错

mysql> create procedure p1() begin 
-> select * from tb_emp; 
-> end 
-> $$
ERROR 1304 (42000): PROCEDURE p1 already exists 12345

2、存储过程体

存储过程体包含在以begin开始，以end结束；如果存储过程体中是一条sql语句，则begin/end可以省略。多条语句以分号分割

3、过程参数

在存储过程中允许使用参数。参数的语法为 [in/out/inout] 参数名称 参数类型 ，多个参数以逗号分割。存储过程中可以没有参数，也可以有多个参数

存储过程支持三种类型的参数，就是输入参数in、输出参数out和输入输出参数inout。输入参数只能传递进来一个数据，输出参数用于存储过程返回操作结果，而输入输出参数既可以充当输入参数传入数据也可以充当输出参数返回数据。

一般参数的名称不要和数据表的列名称相同，尽管定义时不会报错，但是执行时sql语句会将参数看作列名称处理传入一个参数为部门编号，获取本部门的所有员工信息

delimiter $$ 
create procedure p2(in deptId bigint) begin 
select * from tb_emp where dept_id=deptId; 
end
$$
delimiter ;

调用 call p2(1);

定义变量

使用 declare 关键字可以用于定义局部变量，定义后则可以给变量进行赋值操作或者进行计算，变量的作用范围为begin…end之间

基础语法： declare 变量名称 数据类型 [default 默认值] ，如果没有default则变量默认值为null

declare age int default 10;

变量赋值

可以使用set直接赋值 set 变量名称=表达式

set age=100;

可以使用select into语句将查询结果赋值给变量

select year(now())- year(birth) into age from tb_emp where id=1;

注意：将查询结构赋值给变量时要求查询结果只能是单行

@符号的用法

测试时可以使用mysql用户变量 @变量名 定义，它的作用域为当前连接。 select @a; 变量名如果不加 @的话，会认为这是一个列名，但是这列不存在，就报错了；

@变量名 : 定义一个用户变量 
= 对该用户变量进行赋值. 

用户变量赋值有两种方式: 一种是直接用"=“号，另一种是用”:=“号。

其区别在于: 
使用set命令对用户变量进行赋值时，两种方式都可以使用； 
用select语句时，只能用”:=“方式，因为select语句中，”="号被看作是比较操作符。

样例：定义一个自定义函数，通过输入学生姓名和课程名称查询该学生所学课程的成绩。该自定义函数使用了一个局部变量result，类型为int，通过select…into命令把查询结果保存到该变量，然后使用return返回变量的值。

条件判断语句if

基础语法：

if 条件表达式1 then 执行语句1；
elseif 条件表达式2 then 执行语句2；
else 默认执行语句；
end if;

使用样例

if age>20 then
    set count=count+1;
else if age=20 
	then set count=count-1;
else
	set count=0; 
end if;

条件分支语句case

case提供了多个条件进行选择，可以实现比if更复杂的条件判断

case语法，类似java中的switch写法较少使用

case
	when 条件1 then 语句1；
    when 条件2 then 语句2；
    else 语句n; 
end case

写法1：

case age
	when 20 then set count=count+1;
    else set count=0;
end case

写法2：

case
	when age=20 then set count=count+1; 
	else set count=0;
end case

循环语句loop

loop无条件循环，一般需要配合跳出循环的语句

基础语法：

loop
	语句语句；
end loop

跳出循环

iterate跳出本次循环，直接进入下次循环，需要有一个循环标识符。类似于continueleave跳出循环体，彻底终止循环，执行循环后的后续语句。类似于break

add_num:
loop
	set count=count+1; 
	if count=100 then 
		leave add_num; 
	else if count<100 then 
		iterate add_num;
       end if 
end loop add_num

repeat循环

类似于do/while

repeatset count=count+1; until count=100 end repeat;

执行循环+1操作，直到count等于100结束循环

while循环

类似于while循环

while count<100 do set count=count+1; end while;

当count小于100时反复执行循环体，直到count>=100为止

小结循环

• loop死循环，所以需要配合if进行跳出

• repeat循环至少执行一次

• while循环满足条件才执行，否则可能一次都不执行