当前位置:   article > 正文

MySQL之存储过程_mysql存储过程怎么写

mysql存储过程怎么写

我的小站:我的博客

1.1 存储过程简介

存储过程是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数的值

MySQL 5.0 版本开始支持存储过程。

存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。

1.2 存储过程的优缺点

优点:

1) 封装性

存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。

2) 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

3) 可减少网络流量

由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。

4) 高性能

存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。

5) 提高数据库的安全性和数据的完整性

使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。

此数据来自知乎大佬 —— Mysql存储过程的详细讲解 - 知乎 (zhihu.com)

缺点:

  • 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
  • 存储过程的性能调校与撰写,受限于各种数据库系统。

1.3 使用存储过程

1. 创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程。语法格式如下

  1.  CREATE PROCEDURE 过程名(过程参数)
  2.  [特性]
  3.  存储过程体

说明

常用特性有:

  1. LANGUAGE SQL:存储过程语言,默认值为 SQL
  2. CONTAINS SQL:表示子程序不包含读或写数据的语句
  3. NOT SQL:表示子程序不包含 SQL 语句
  4. READDS SQL DATA:表示子程序包含读数据的语句,但不包含写的语句
  5. MODIFIES SQL DATA:表示子程序包含写数据的语句

如果 2~5 这些特征没有明确指定,默认值为:CONTAINS SQL

  1. SQL SECURITY { DEFINER | INVOKER }:用来指定存储过程的执行权限,默认值为 DEFINER

    1. DEFINER:使用创建者的权限执行
    2. INVOKER:使用执行者的权限执行

下面结合具体实例讲解存储过程的创建过程:

  1.  DELIMITER //
  2.  CREATE PROCEDURE student(过程参数)
  3.  BEGIN
  4.     SELECT * FROM student;
  5.  END
  6.  DELIMITER ;

说明

1)声明语句分隔符

 DELIMITER $$ 或者 DELIMITER //

需注意的是,如果没有声明语句分隔符的话,编译器就会把存储过程当成 SQL 语句处理,这样编译过程就会报错。

2)定义存储过程的参数

在 MySQL 中,存储过程的参数包括 3 种类型

  • IN:输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
  • OUT: 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量);
  • INOUT:输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)。

3)过程提的标识

在定义存储过程的过程体时,需要标识开始和结束。语法格式如下:

 BEGIN …… END 分隔符

2. 调用存储过程

 CALL 存储过程名(参数列表);

3.存储过程中的变量

 DECLARE 变量名 数据类型 [DEFAULT 值]

1.4 查看存储过程

1.查看存储过程的状态

类似查看数据库中的数据表信息

 SHOW PROCEDURE STATUS;

2.查看存储过程的创建代码

 SHOW CREATE PROCEDURE 存储过程名;

1.5 修改存储过程

在MySQL中,使用 ALTER PROCEDURE语句可以修改已创建的存储过程,但是仅仅能够修改存储过程时定义的特性。语法格式如下:

  1. ALTER PROCEDURE 存储过程名 [特性 ……]

1.6 删除存储过程

类似删除数据库中的数据表,MySQL用户可以使用 DROP PROCEDURE语句删除已创建的存储过程。语法格式如下:

DROP PROCEDURE 存储过程名;

2. 存储过程的控制语句

2.1 条件语句

1.IF-ELSEIF-ELSE条件语句

  1. IF 条件 THEN 语句列表
  2. [ELSEIF 条件 THEN 语句列表]
  3. [ELSE 语句列表]
  4. END IF;

2.CASE条件语句

在 MySQL 中的 CASE 条件语句有两种写法。

第一种语法格式如下:

  1. CASE
  2. WHEN 条件 THEN 语句列表
  3. [WHEN 条件 THEN 语句列表]
  4. [ELSE 语句列表]
  5. END CASE;

在 CASE 语句中,若条件为真,则相应的 SQL 语句列表来执行。

若没有条件匹配,则在 ELSE 子句里的语句列表被执行。

另外,CASE 语句只返回第一个符合条件的值,剩下的部分将会被自动忽略。

实例:

定义存储过程,输入一个整数,使用 case 语句判断是正数还是负数,代码如下所示:

  1. -- 创建过程
  2. create procedure pro_demo(in num int)
  3. begin
  4. case -- 条件开始
  5. when num < 0 then select '负数';
  6. when num = 0 then select '不是正数也不是负数';
  7. else select '正数';
  8. end case; -- 条件结束
  9. end;
  10. -- 调用过程
  11. call pro_demo(1);

运行结果:

  1. 正数
  2. 正数

第二种语法格式如下:

  1. CASE 列名
  2. WHEN 条件 THEN 语句列表
  3. [WHEN 条件 THEN 语句列表]
  4. [ELSE 语句列表]
  5. END CASE;

实例:

定义存储过程,输入一个整数,使用 case 语句判断是 1 还是 2,代码如下所示:

  1. -- 创建过程
  2. create procedure pro_demo(in num int)
  3. begin
  4. case num -- 条件开始
  5. when 1 then select '数值是 1';
  6. when 2 then select '数值是 2';
  7. else select '不是 1 也不是 2';
  8. end case; -- 条件结束
  9. end;
  10. -- 调用过程
  11. call pro_demo(3);

运行结果:

  1. 不是 1 也不是 2
  2. 不是 1 也不是 2

此处实例来自 (398条消息) MySQL---存储过程详解ʜғ的博客-CSDN博客mysql存储过程详解

2.2 循环语句

1)WHILE 循环语句

WHILE 循环语句时最普遍使用的循环语句,它首先判断条件是否成立,若成立,则执行循环体。语法如下:

  1. while 条件 DO
  2. 循环体
  3. end while;

实例:

  1. delimiter //
  2. -- 创建过程 使用 while 循环输出 110 的累加和
  3. create procedure pro_demoWhile(in i int)
  4. begin
  5. declare num int default 0;
  6. declare sum int default 0;
  7. while num < 10 do -- 循环开始
  8. set num = num + i;
  9. set sum = sum + num;
  10. end while; -- 循环结束
  11. select sum;
  12. end;
  13. -- 调用过程
  14. call pro_demoWhile(1);

运行结果:55

2)LOOP 循环语句

LOOP循环结构不需要判断初始条件,直接执行循环体,知道遇到 LEAVE 语句才退出循环。语法格式如下:

  1. loop_name: LOOP
  2. IF 条件 THEN -- 满足条件时离开循环
  3. LEAVE loop_name; -- 和 break 差不多都是结束循环
  4. END IF
  5. END LOOP;

实例:

  1. delimiter //
  2. -- 创建过程 使用 loop 循环输出 110 的累加和
  3. create procedure pro_demoLoop(i int)
  4. begin
  5. declare num int default 0;
  6. declare sum int default 0;
  7. #loop 循环语法:
  8. loop_name:loop
  9. set num = num + i;
  10. set sum = sum + num;
  11. if num >= 10 THEN -- 满足条件时离开循环
  12. leave loop_name; -- 和 break 差不多都是结束循环
  13. end if;
  14. end loop;
  15. select sum;
  16. end;
  17. -- 调用过程
  18. call pro_demoLoop(1);

运行结果:55

3)REPEAT 循环语句

REPEAT 循环与 LOOP 循环类似,不需要初始条件就可以直接进入循环体。

但是LOOP 循环不同的是,它有退出条件。因此,REPEAT 是执行一次操作后检查条件是否成立。若成立,则结束循环;若不成立,则继续执行下一次循环操作。

这种先执行循环操作再判断循环条件的循环与 Java 中的 do-while 循环类似。语法格式如下:

  1. repeat
  2. 循环体
  3. until 条件 end repeat;

实例:

  1. delimiter //
  2. -- 创建过程 使用 while 循环输出 110 的累加和
  3. create procedure pro_demoRepeat(i int)
  4. begin
  5. declare num int default 0;
  6. declare sum int default 0;
  7. # repeat 循环语法
  8. repeat
  9. set num = num + i;
  10. set sum = sum + num;
  11. until num >= 10 end repeat;
  12. select sum;
  13. end;
  14. -- 调用过程
  15. call pro_demoRepeat(1);

运行结果:55

3. 总结

  1. 存储过程是一组为了完成特定功能的 SQL 语句集合,MySQL 从5.0版本之后开始支持存储过程。
  2. 存储过程具有安全性高、减少网络流量等优势,在实际项目开发过程中具有广泛的应用。
  3. 创建存储过程使用 CREATE PROCEDURE 结构,调用存储过程使用 CALL 关键字。
  4. MySQL 存储过程支持多种控制语句,包括条件语句( IF和 CASE )、循环语句(WHILE、LOOP、REPEAT)。
声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号