MySQL存储过程

最新推荐文章于 2022-07-16 16:19:01 发布

外包猿

最新推荐文章于 2022-07-16 16:19:01 发布

阅读量229

点赞数

分类专栏：数据库设计文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43540973/article/details/119306562

版权

数据库设计专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL存储过程精讲

工作之后才发现，不仅仅是DBA，而且是开发，运维，在日常工作中存储过程的编写成为了一项必备技能，之前在学习存储过程的过程中自我感觉存储过程在日常开发中并没有太大的作用，基本上用不到，总的来说还是格局小了哈！确实在经历过一些事情，自己一些事情有了跟家深刻的看法，不管是技术上面，还是生活上对需要不断的经历，接触新事物，才能有更加深刻的理解，下面是我在系统的学习存储过程时的一些笔记，希望能帮助到需要的小伙伴！结尾有两个存储过程的题目，有点深度，推荐大家在不看答案的基础上自己先做一遍，这样子对于初学者或者复习有更加深刻的帮助！

简介：

存储过程定义：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

能干什么？

封装sql语句集，用来完成一些比较复杂的业务逻辑，并且有入参出参，创建时会预先编译后保存，用户后续调用不需要再次编译

优缺点：

优点：

在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑，不需要重启服务器
执行熟读快，存储过程经过编译后比一条一条语句的执行要快（创建时会编译保存）
方便优化

缺点：

过程化编程，复杂业务逻辑处理的维护成本高
调试不方便
不同的数据库之间可移植性差

存储过程的使用

基础用法

创建语法：

 delimiter  $$   -- 声明存储过程结束符号   
create procedure 存储过程名（参数类型1 参数名1 数据类型1，参数类型2 参数名2 数 据类型2） begin  存储过程体... end $$

运行存储过程：

call pro_user() ;  -- 有参数则传入参数即可，使用方法与java中调用方法一致

删除：

drop procedure 存储过程名

变量声明：

1、局部变量

 delimiter  $$  
 create procedure emp()
 begin
declare var_name varchar(32) ;   --也可以在后面添加默认值  defualt '诸葛亮'
select var_name ;
 set var_name = '张三';  -- 为变量赋值用set
	select var_name;
 end $$
-- 执行存储过程
 call emp();
 前面输出为空，后面为‘张三’

2、用户变量：当前会话（连接）有效类比java中的成员变量，用户的全局变量

不需要创建，直接在赋值时创建，赋值需要使用‘@’标注为全局变量

 delimiter  $$  
 create procedure emp()
 begin

 set @var_name = 'zhangsan';

 end $$ 
 --在存储过程外面进行访问用户变量，这里需要注意，存储过程创建以后一定要执行一次
 --在创建的时候并没有执行，只是进行编译保存
call emp();  
	select @var_name;

into变量赋值

**特点：**查询出来的结果然后赋值给变量，变量在后，结果在前

 delimiter  $$  
 create procedure emp()
 begin

select name into @var_name from employee where id = '190';

 end $$ 
 
call emp();
	select @var_name;

参数类型（所有的参数与变量都不要跟列名重复）：

in: 输入

 delimiter  $$  
 create procedure emp( in name varchar(32))
 begin
  set @var_name  = name ;
  select @var_name; 
 end $$ 
 
call emp('扎根三');

out:输出

-- 根据id查出姓名：
 delimiter  $$  
 create procedure emp( in emp_id varchar(10)  , out var_name varchar(32) )
 begin
   select name into var_name from employee where id = emp_id;
    select var_name;
 end $$ 
 
call emp('190',@vars_name);

inout:输入输出（就是输入时可以进行赋值，进行操作后放回结果）

 delimiter  $$  
 create procedure emp( in emp_id varchar(10)  , inout var_name varchar(32) )
 begin
 declare e_name varchar(32);
 set e_name = var_name;
   select name into var_name from employee where id = emp_id;
    select concat(var_name , '--',e_name);
 end $$ 
 
 set @vars_name = 'Hello';
call emp('190',@vars_name);  -- 输入参数
	  select @vars_name;  -- 打印输出参数

流程控制–判断：

if语法：满足if条件执行then后的语句

  if p_info101 = '0' then
    m_info101_word := '推进中';
  elsif p_info101 = '1' then
    m_info101_word := '已销号';
  elsif p_info101 = '2' then
    m_info101_word := '已取消';
  end if;

case用法：

CASE 
WHEN price IS NULL THEN 'Unpriced' 
WHEN price < 10 THEN 'Bargain' 
WHEN price BETWEEN 10 and 20 THEN 'Average' 
ELSE 'Gift to impress relatives' 
END

流程控制–循环：

loop（死循环必须写leave进行跳出相当于java中break）：输出1-6的数字

create procedure emp1()
 begin
declare e_index int default 1;
e_loop: loop    -- 开始循环
select e_index;
if  e_index >5     -- 满足条件跳出循环
then leave e_loop;
end if ;
set e_index = e_index + 1;    -- 不满足变量+1
end
loop e_loop;    -- 结束循环
 end 

call emp1();

repeat（满足条件跳出循环，一定会执行一次代码）

 create procedure emp2()
 begin
declare e_index int default 1;
e_repeat: repeat      -- 开始循环
set e_index = e_index + 1;    -- 变量+1
 until e_index >5  end   repeat e_repeat;   -- 满足条件结束循环
 select e_index;
 end 
call emp1();

注意until语句后面没有分号结束，until与end一起为结束语句共一个分号

while循环（与java中的while类似）

-- 循环输出5
 create procedure emp3()
 begin
declare e_index int default 1;
while  e_index <5 do    -- 开始循环，满足条件执行代码块
set e_index = e_index + 1;
  end  while;       -- 结束循环
 select e_index;
 end 
call emp3();

流程控制–退出与继续循环(用与loop循环)

leave(loop循环中必须带，结束循环)
iterate(loop循环可带可不带，跳出本次循环)

游标

用游标得到每一个结果集，对每一行进行数据处理（类比jsbc中的ResultSet）

使用方法：

-- 声明语法
DECL ARE cursor_ name CURSOR FOR select_ stat ement
-- 打开语法
OPEN cursor_ name
-- 取值语法
FETCH cursor_ name INTO var_ name [ ，var_ name] . 。.
--  关闭语法
CLOSE cursor_ name

例子：根据dept_ name去查询员工的id，姓名，工资(本例子推荐先自己做一遍)


delimiter $$ 
create procedure sp_ show emp (in dept_ name varchar (32) )
begin
-- 创建用与存储员工信息的变量
declare e_ no int;
declare e_ name varchar (32) ;
declare e_ sal decimal(7,2) ;
declare 1p_ flag boolean true;   -- 定义变量判断是否要继续在游标中获取值
declare emp_cursor  cursor for    -- 声明游标
select e . empno, e .ename,e.sal
from emp e, dept d where e .deptno = d.deptno and d.dname = dept_ name ;
-- handler句柄 ，当存储过程包1329（not datas）报错时，改变1p_ flag值，结果集中没有数据了
-- continue 表示句柄检测到报错后会执行后面代码，exit不会执行后面代码
declare continue handler for 1329 set 1p_ flag = false;
open emp_cursor;   -- 打开游标
emp_ 1oop :1oop     -- 开始循环从结果集中取数据
fetch emp_ cursor into e_ no,e_ name,e_ sal;
if 1p_ flag then   -- 根据变量判断结果集中是否还有数据
select e_ no,e_ name,e sal ;
else
leave emp_ loop;  -- 结果集中没有数据了，跳出循环
end if;
end 1oop emp_ 1oop;  -- 关闭循环
close emp_ cursor ;   -- 关闭游标
set @end_ falg = 'end' ;  -- 判断游标检测到报错后，还会不会执行后面的代码
end$$
delimiter ;
ca11 sp_ show emp ( ' ACCOUNTING' ) ; 

-- 结果输出：正常输出员工信息  + @end_ falg = 'end'

-- 改变continue  成exit
declare exit handler for 1329 set 1p_ flag = false;
set @end_ falg = 'exit_ flag' ;
-- 结果输出：正常输出员工信息  + @end_ falg = 'end' 
-- 结果是end，说明报错后没有执行后面代码，存储过程值关闭

特别注意:

在语法中，量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

MYSQL存储过程函数:

-- 在现在的日期往后面推两个月
select  Date_add(now(),interval 2 month);

-- LAST_DAY(DATE)  获取日期本月的最后一天
select   Last_DAY(Date_add(now(),interval 2 month));
-- year(date)   获取日期中的年
-- month(date)  获取日期中的月
-- DAYOFMONTH(date)  获取日期中的日
-- 时间差函数：
-- timestampdiff（根据年|月|日来算时间差，旧时间，现在时间）

模拟训练:

创建下个月的每天对应的表comp_ 2020_ 04_ 01、comp- 2020_ 04_ 02、…(推荐自己先看需求做一遍)

(模拟)需求描述:

我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天- -张表，存当天的统计数据，就要求提前生产这些表- -每月月底创建下一个月每天的表!

create procedure SP_ create_ table ()
begin
-- 本月年月日
declare next_year int;
declare next_month int;
declare next_month day int;
-- 下个月月日
declare next_ month_ str char(2) ;
declare next month_ day_ str char(2) ;
-- 处理每天的表名
declare table_ name_ str char(10) ;
-- 循环条件
declare t_ index int default 1;
-- 获取下个月的年份
set next_ year = year (date_ add (now() , INTERVAL 1 month) ) ;
-- 获取下个月是几月
set next month = month (date_ add (now() , INTERVAL 1 month) ) ;
-- 下个月最后一天是几号
set next_ month_ day = dayofmonth (LAST DAY (date_ add (now() , INTERVAL 1 month) )) ;
-- 当月份大于10的时候在月份前面拼接一个0
if next_month < 10
then set next_ month_ str = concat('0' ,next_ month) ;
else
set next_ month_ str = concat('' ,next_ month) ;
end if;
while t_ index <= next_ month_ day do
if (t_ index < 10)
then set next_ month_ day_ str = concat('O',t_ index) ;
else
set next_ month_ day_ str = concat('',t_ _index) ;
end if;
-- 进行字符串拼接得到表名
set table_ name_ str = concat (next_ year, ' ' ,next month_ str, ' ' , next_ month_ day_ str) ;
--拼接create sq1语句
set @create table sql = concat (
'create table comp_ '，
table_ name_ str,
' ( 'id' int(11) not null PRIMARY KEY  , 'name' varchar(20) not null , 'grade' INT(11) NULL,'losal' INT (11) NULL, 'hisal' INT (11) NULL) )'
-- FROM后面不能使用局部变量! 一定要使用用户变量
prepare create_ table_ stmt FROM @create_ _table_ sql;
execute crealte_ table_ stmt;
DEALLOCATE prepare create_ table_ stmt;
set  tindex=tindex+1;
end while;
end