mysql 存储过程总结

xushiyu1996818

已于 2022-05-17 16:05:02 修改

阅读量1.3k

点赞数

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

于 2020-06-16 15:01:30 首次发布

本文链接：https://blog.csdn.net/xushiyu1996818/article/details/106786694

版权

mysql 专栏收录该内容

52 篇文章 1 订阅

订阅专栏

存储过程简介

迄今为止，我们学过的大多数SQL语句都是针对一个或多个表的单条语句。但是并不是所有的操作都是可以用一条语句来完的，经常有一些操作是需要多条语句配合才能完成。我们引入的存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为增强版的批处理文件。　　

使用存储过程的好处：

1 通过把处理封装在一个易用的单元中，可以简化复杂的操作。

2 不需要反复建立一系列的处理步骤，因而保证了数据的一致性。

3 简化了对变动的管理，这一点的延伸就是安全性。

4 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。

使用存储过程的缺点：

1.架构不清晰，不够⾯向对象

存储过程不太适合⾯向对象的设计，⽆法采⽤⾯向对象的⽅式将业务逻辑进⾏封装，业务逻辑在储层实现，增加了业务和存储的耦合，代码的可读性也会降低，

2.开发和维护要求⽐较⾼

存储过程的编写直接依赖于开发⼈员，如果业务逻辑改动较多，需要频繁直接操作数据库，⼤量业务降维到数据库，很多异常不能在代码中捕获，出现问题较难排查，需要数据库管理⼈员的帮助。

3.可移植性差

过多的使⽤存储过程会降低系统的移植性。在对存储进⾏相关扩展时，可能会增加⼀些额外的⼯作

存储过程的使用场景

1、增强SQL语句的功能和灵活性；

2、减少网络流量，从而可降低网络负载；

3、提高数据库的访问效率；

4、提高数据库的安全性和数据的完整性；

5、使数据独立，达到解耦的效果。

普通的项⽬开发中，不建议⼤量使⽤存储过程，对⽐SQL语句，存储过程适⽤于业务逻辑复杂，⽐较耗时，同时请求量较少的操作，例如后台⼤批量查询、定期更新等。

(1)当⼀个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应⽤存储过程

(2)在⼀个事务的完成需要很复杂的商业逻辑时可以考虑应⽤存储过程

(3)⽐较复杂的统计和汇总可以考虑应⽤后台存储过程

存储过程与java的对比

我们在报表开发中经常会使用存储过程准备数据，存储过程支持分步计算，可以实现非常复杂的计算逻辑，为报表开发带来便利。所以，报表开发中这样的存储过程并不少见：

存储过程，会给报表开发带来什么不好的影响？

1. 编辑调试性存储过程难以编辑调试，这样几千行存储过程的开发周期往往要以周或月计，这样会严重影响报表的开发效率，而业务提的报表需求似乎都“很急”。

2. 维护性相对开发的一次性，维护的工作可能要经常做。实际业务中报表经常会修改，这种现象叫做报表业务的稳定性差。报表的数据准备逻辑变化，修改上千行的存储过程对绝大多数报表开发人员来说都是噩梦。

有时这样的报表会分两拨人来做，DBA 或专业程序员负责编写存储过程给前端报表开发人员做报表，这样就避免了报表开发人员写存储过程。但这样报表修改的流程会变长，修改一张报表涉及多个人员之间沟通(还包括业务人员)，如果负责报表前后端的两拨人隶属不同的团队就更麻烦了。

3. 知识传承从维护性可以直接引出另一个“知识传承”的问题。还是拿上面的报表为例，如果一个新人要改上面的报表，你觉得他要多久能看懂存储过程，改完报表？

当然，这个问题还涉及很多管理方面的手段，单纯从技术本身来看，这样的报表想要很好地传承知识是很难的。

4. 安全性对存储过程的修改需要较高的数据库权限，而报表经常要改就要经常操作数据库，这对数据库安全也是一个隐患，同样需要强管理机制才能保障一二。

5. 移植性现在绝大多数规定禁止使用存储过程的原因，首当其冲的就是存储过程没有移植性。如果未来数据库发生变化需要迁移，不管将来是更换数据库类型，还是系统扩展(分表分库)，大量无法移植的存储过程绝对是最头疼的问题。

当然，“换库”这件事情即使在今天仍然不会频繁发生，但是只要发生一次就够受了(有国产化或系统扩展预期的就要注意了)。

6. 耦合性从维护性、安全性和移植性看来，存储过程会导致报表应用(前端)和数据库(后端)紧耦合。紧耦合除了会导致前面的三个问题外，还会让数据库编的臃肿，影响数据库性能。

重要的事情说好多遍，报表的业务不稳定，报表除了经常增加和修改，有时还会删除(不用了)，而为这个报表准备的存储过程还在数据库里，这时想要删掉这个存储过程就比较难了。

为什么？

因为你不知道是不是还有其他程序在共用这个存储过程，删除会不会对其他程序产生影响。结果就是数据库的存储过程越积越多导致数据库臃肿，而有的存储过程还会涉及自动运行，虽然存储过程可能不再使用，但仍然在消耗数据库资源，长此以往数据库性能下降就成为必然了。

7. 多源支持存储过程运行在封闭的数据库内，无法进行跨多数据源混合计算。关于多源问题，几年前在报表开发还不显著，那时大家都用关系库；但现在不一样了，同一个报表的数据可能来自多个不同类型的数据源(RDB/NoSQL/TxT/Excel/Hadoop/ES 等等)，这时存储过程就无能为力了。

如何搞定这些问题？有没有办法解决存储过程带来的这些问题呢？

当然有！

没有什么是硬编码解决不了的！用 JAVA 替代存储过程，脱离数据库运行来解决上面的问题(自行搜索 SOA 和微服务理念)。存储过程一个显示的好处是可以分步实现报表数据准备逻辑，这个优点 JAVA 也有，甚至比存储过程更彻底，说句文绉绉的话：JAVA 的离散性更好。

只是 JAVA 写起来比较麻烦，对于报表开发人员来讲太难了，如果还要加一个修饰词那就是太 XX 难了。存储过程使用的 SQL 语言非常适合做集合运算，分组汇总一句 group by 就写出来了，反观 JAVA 就不具备这个优点了，分组汇总可能要写上几十上百行才行(类库缺失会让开发复杂度急剧上升，想想你为什么不用汇编写程序而要用 JAVA？)。

JAVA 还有一些其他的问题也不容忽视。

不支持热切换JAVA 还有一个非常致命的缺点，就是不支持热切换。报表经常要改(又来一遍)，修改报表数据源以后还要重新编译、重启应用才能生效，对绝大多数业务系统都是不能接受的。报表讲究的不仅是查询立等可取，修改也要实时生效才行。

报表与应用紧耦合与使用存储过程会导致报表与数据库紧耦合类似，用 JAVA 准备报表数据源会导致报表模块和应用的其他业务模块紧耦合不宜维护。

我们知道，报表大多数情况都是作为一个模块集成到应用系统提供报表查询服务，集成的方式可以是 API(jar 包)方式紧集成；也可以将报表单独发布成服务，通过服务调用的方式松集成，这样报表服务器产生的任何压力或问题都不会影响应用系统(高可用)。*API 紧集成后，由于报表数据源是 JAVA 写的，这样就要和主应用的代码一起打包，无法作为独立的模块维护，而未来想要拆分也基本不可能了；* 服务松集成则完全无法实现。

所以，用 JAVA 写报表数据源虽然可行，但也不是特别理想。

总结一下需要的点。

创建语法

delimiter

delimiter //

一般情况下MYSQL以；结尾表示确认输入并执行语句，但在存储过程中；不是表示结束，因此可以用该命令将；号改为//表示确认输入并执行。

所以delimiter与后面的符号要空一格！！！

创建存储过程

create procedure 存储过程名称()
 (

      [in|out|inout] 参数 datatype

  )
   begin

       MySQL 语句;

   end;

MySQL存储过程参数如果不显示指定"in"、“out”、“inout”,则默认为“in”，习惯上，对于是“in”的参数，我们都不会显式指定。

DROP PROCEDURE IF EXISTS wap;
	DELIMITER //
	create procedure wap(in vdate varchar(10) )  ## 创建存储过程
	begin 
	     ## 声明变量，设置变量值
		declare v_date varchar(10);
		declare v_counts int(10);
		declare v_ratio decimal(10,2);  ## 小数不用float，用decimal
		
		set v_date=12;
		
		 ## 查询，查询入字段
		select count(*) into v_counts from users;
		
		##select into两个字段
		select count(*) ,avg(num)  into v_counts ,v_ratio from users;
		select v_counts;
		
		 ## 插入变量
		INSERT INTO `staff` (`member`, `counts`, `CREATE_TIME`) 
		VALUES ('abc',v_counts, now());

		 ## if语句
		IF v_counts=0 THEN
	      SET v_counts=1;
	    ELSE
	      SET v_counts=2 * v_counts;
	    END IF ;
	    
	     ## while循环
		WHILE v_counts< 10 DO
		   select v_counts;
		   SET v_counts= v_counts+ 1;
		END WHILE;
	    
	end; // 
	DELIMITER ; 
	call wap('$vdate');  
	call wap('adf');

drop procedure if exists pr_add;

-- 计算两个数之和

create procedure pr_add (

     a int,   

     b int

) begin   

     declare c int;

    if a is null then      

         set a = 0;   

    end if;

   if b is null then     

        set b = 0;  

   end if;

set c = a + b;

select c as sum; /*    return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。

调用存储过程

示例：
　　

call pr_add(10, 20); 执行 MySQL 存储过程，存储过程参数为 MySQL 用户变量。
set @a = 10; set @b = 20;
call pr_add(@a, @b);

说明：

CALL Avg_Price(); 执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后需要有 () 符号(即使不传递参数也需要)。

删除存储过程

说明：

请注意只给出了存储过程名，并没有书写()。

区块定义

区块定义，常用
begin
......
end;
也可以给区块起别名，如：
lable:begin
...........
end lable;
可以用leave lable;跳出区块，执行区块以后的代码

条件语句

if else

if 条件 then

  statement

else

  statement

end if;

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc2(IN parameter int)  
         -> begin 
         -> declare var int;  
         -> set var=parameter+1;  
         -> if var=0 then 
         -> insert into t values(17);  
         -> end if;  
         -> if parameter=0 then 
         -> update t set s1=s1+1;  
         -> else 
         -> update t set s1=s1+2;  
         -> end if;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

case

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc3 (in parameter int)  
         -> begin 
         -> declare var int;  
         -> set var=parameter+1;  
         -> case var  
         -> when 0 then   
         -> insert into t values(17);  
         -> when 1 then   
         -> insert into t values(18);  
         -> else   
         -> insert into t values(19);  
         -> end case;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

循环语句

while

[label:] WHILE expression DO
 
 statements
 
END WHILE [label] ;

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc4()  
         -> begin 
         -> declare var int;  
         -> set var=0;  
         -> while var<6 do  
         -> insert into t values(var);  
         -> set var=var+1;  
         -> end while;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

loop

[label:] LOOP
 
 statements
 
END LOOP [label];

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc6 ()  
         -> begin 
         -> declare v int;  
         -> set v=0;  
         -> LOOP_LABLE:loop  
         -> insert into t values(v);  
         -> set v=v+1;  
         -> if v >=5 then 
         -> leave LOOP_LABLE;  
         -> end if;  
         -> end loop;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

repeat

[label:] REPEAT
 
 statements
 
 UNTIL expression
 
 END REPEAT [label] ;

它在执行操作后检查结果，而while则是执行前进行检查。

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc5 ()  
         -> begin   
         -> declare v int;  
         -> set v=0;  
         -> repeat  
         -> insert into t values(v);  
         -> set v=v+1;  
         -> until v>=5  
         -> end repeat;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

LABLES 标号

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

ITERATE迭代

ITERATE

通过引用复合语句的标号,来从新开始复合语句

mysql > DELIMITER //  
        mysql > CREATE PROCEDURE proc10 ()  
             -> begin 
             -> declare v int;  
             -> set v=0;  
             -> LOOP_LABLE:loop  
             -> if v=3 then   
             -> set v=v+1;  
             -> ITERATE LOOP_LABLE;  
             -> end if;  
             -> insert into t values(v);  
             -> set v=v+1;  
             -> if v>=5 then 
             -> leave LOOP_LABLE;  
             -> end if;  
             -> end loop;  
             -> end;  
             -> //  
        mysql > DELIMITER ;

MySQL需要注意的问题

1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”

2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。

create procedure pr_add ( @a int,- 错误 b int - 正确 )

3. MySQL 存储过程的参数不能指定默认值。

4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。

create procedure pr_add (
        a int,   
        b int 
) as  - 错误，MySQL 不需要 “as” 
begin    
       mysql statement ...;
end;

5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。

create procedure pr_add (    

     a int,   

     b int 

) begin    

            mysql statement 1 ...;   

            mysql statement 2 ...;
end;

6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”

...

declare c int;

if a is null 

     then set a = 0;   

end if;

... 

end;

7. MySQL 存储过程中的注释。

/*      这是个      

        多行 MySQL 注释。 

    /

 declare c int;    - 这是单行 MySQL 注释 （注意- 后至少要有一个空格）

 if a is null then 这也是个单行 MySQL 注释      

        set a = 0;    

 end if;

  ... end;

8. 不能在 MySQL 存储过程中使用 “return” 关键字。

set c = a + b;

 select c as sum;

     /*    return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。 /

 end;

10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。

call pr_add(10, null);

11. 设置的变量名前面最好加一个v_ ，免得变量名与表的字段名相同，引起冲突

xushiyu1996818

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 存储过程总结

目录存储过程简介创建语法delimiter创建存储过程调用存储过程删除存储过程区块定义条件语句if elsecase循环语句whilelooprepeatLABLES标号ITERATE迭代ITERATEMySQL需要注意的问题存储过程简介迄今为止，我们学过的大多数SQL语句都是针对一个或多个表的单条语句。但是并不是所有的操作都是可以用一条语句来完的，经常有一些操作是需要多条语句配合才能完成。我们引入的存储过程（Stored...
复制链接

扫一扫

专栏目录