MySQL-存储过程

转x33g5p2x  于2021-09-24 转载在 Mysql  
字(3.8k)|赞(0)|评价(0)|浏览(930)

前言

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

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

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

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

优点

  • 存储过程可封装，并隐藏复杂的商业逻辑。
  • 存储过程可以回传值，并可以接受参数。
  • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  • 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

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

第一个例子

我们通过一个简单的例子来学习存储过程的基本语法
基本语法：

  1. CREATE PROCEDURE produce_name(参数列表)
  2. beign -- 表示过程体开始 --
  3.    -- 这里就是你的业务逻辑代码了 --
  4. end -- 表示过程体结束 --

案例：
先说几个问题，我这里用的是Navicat。Navicat中默认的语句结束符号是“;” 但是在存储过程中我们可能会有多条语句，每条语句的结束都是“；”，为了防止创建存储过程中Navicat就使用“；”作为结束符号，我们需要重新申明结束符号：

  1. DELIMITER $$
  2. 或
  3. DELIMITER //

tips : 当然这个符号你可以自己定义（不要太随意就行）。
来吧！看看案例：

  1. -- 申明结束符号 --
  2. DELIMITER $$
  3. CREATE PROCEDURE PROC_SELECT_BOO()
  4. BEGIN -- 开始 --
  5. 	SELECT * FROM book;
  6. END $$ -- 结束 --
  7. -- 在将结束符号申明回去 --
  8. DELIMITER ;

执行之后查看左边的结构视口：

执行存储过程：

  1. CALL PROC_SELECT_BOO;

删除存储过程：

  1. DROP PROCEDURE PROC_SELECT_BOOK

当然我们上面的存储过程仅仅是执行了一条查询语句。
存储过程中可以有业务逻辑，那么就需要基本的流程控制。
所以我们要写出一定质量的存储过程，就需要了解存储过程中的一些基本语法。
tips：只要你学过编程，这些都很简单。
当然，让我们觉得恶心的就是，每个不同的数据库中的存储过程的语法都不一样。这也是前面说过的存储过程最大的缺点。

基本语法

变量

MySQL的变量有：全局变量，回话变量，用户变量，系统变量，局部变量。
我们这里重点研究存储过程的使用，暂时只说局部变量。
局部变量就是申明在BEGIN和END之间的变量，作用域也就是在BEGIN和END之间。
申明变量：使用DECLARE关键字

  1. DECLARE var_name var_type [default_value]

案例：

  1. CREATE PROCEDURE PROC_SELECT_BOOK()
  2. BEGIN -- 开始 --
  3. 	-- 申明变量 v_bookid 并且赋默认值为1 --
  4. 	DECLARE v_bookid int default 1;
  5. 	-- 使用变量作为查询条件 --
  6. 	SELECT * FROM book where bookid = v_bookid;
  7. END $$ -- 结束 --

给变量赋值有两种方式：
方式1：直接set

  1. set v_bookid = 1;

方式2： 使用select into

  1. select count(*) into v_count from book

案例：

  • 局部变量一定要放在存储过程的开头
  1. -- 申明结束符号 --
  2. DELIMITER $$
  3. CREATE PROCEDURE PROC_SELECT_BOOK()
  4. BEGIN -- 开始 --
  5. 	-- 申明变量 v_bookid 并且赋默认值为1 --
  6. 	DECLARE v_bookid int default 0;
  7. 	DECLARE v_count int;
  8. 	-- 使用set给变量赋值 --
  9. 	SET v_bookid = 1;
  10. 	-- 使用select into 给变量赋值 --
  11. 	SELECT count(*) into v_count FROM book where bookid = v_bookid;
  12. END $$ -- 结束 --
  13. -- 在将结束符号申明回去 --
  14. DELIMITER ;

流程控制语句

分支语句

[1] if…then …else
语法

  1. -- 只有if的结构 --
  2. if 条件  then
  3.  执的语句
  4. end if;
  5. --if.. else --
  6. if 条件  then
  7.  	if语句
  8. else
  9. 	else语句
  10. end if;
  11. -- 多重分支 --
  12. if 条件  then
  13.  	if语句
  14. elseif 条件  then
  15. 	语句。。。
  16. 	。。。。。
  17. else
  18. 	else语句
  19. end if;

案例：

  1. DELIMITER $$
  2. CREATE PROCEDURE PROC_DEMO()
  3. BEGIN
  4. 	DECLARE v_num int;
  5. 	set v_num = ROUND(RAND() * 10);
  6. 	IF MOD(v_num,2)=0 THEN
  7. 		select '偶数';
  8. 	elseif MOD(v_num,3) = 0 THEN
  9. 		select '3的倍数';
  10. 	else
  11. 		select '奇数';
  12. 	end if;
  13. END $$
  14. DELIMITER ;

[2]CASE结构
直接上菜吧：

  1. DELIMITER $$
  2. CREATE PROCEDURE PROC_DEMO()
  3. BEGIN
  4. 	DECLARE v_num int;
  5. 	set v_num = ROUND(RAND() * 10);
  6. 	CASE v_num
  7. 	when MOD(v_num,2)=0 THEN
  8. 		select '偶数';
  9. 	when MOD(v_num,3)=0 THEN
  10. 		select '3的倍数';
  11. 	else
  12. 		select '奇数';
  13. 	end case;
  14. END $$
  15. DELIMITER ;
循环语句

[1]while ···· end while

  1. while 条件 do
  2.     --循环体
  3. endwhile
  1. DELIMITER $$
  2. CREATE PROCEDURE PROC_DEMO()
  3. BEGIN
  4. 	DECLARE v_num int default 0;
  5. 	while v_num < 10 do
  6. 	insert into temp values(v_num);
  7. 	set v_num = v_num + 1;
  8. 	end while;
  9. END $$
  10. DELIMITER ;

[2]repeat···· end repeat

  1. repeat
  2.     --循环体
  3. until 循环条件  
  4. end repeat;
  1. DELIMITER $$
  2. CREATE PROCEDURE PROC_DEMO()
  3. BEGIN
  4. 	DECLARE v_num int default 0;
  5. 	repeat
  6. 	insert into temp values(v_num);
  7. 	set v_num = v_num + 1;
  8. 	until v_num >= 10 end repeat;
  9. END $$
  10. DELIMITER ;

[3]loop ·····endloop

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

  1. DELIMITER $$
  2. CREATE PROCEDURE PROC_DEMO()
  3. BEGIN
  4. 	DECLARE v_num int default 0;
  5. 	LOOP_LABLE:loop 
  6. 	insert into temp values(v_num);
  7. 	set v_num = v_num + 1;
  8. 	if v_num >=10 then 
  9. 		leave LOOP_LABLE; 
  10. 	end if;
  11. 	end loop;
  12. END $$
  13. DELIMITER ;

存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

  1. CREAT EPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
  • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
  • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
  • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1、in 输入参数

就简单理解为java方法的参数

  1. delimiter $$
  2. create procedure in_param(in p_in int)
  3. begin
  4. 	select p_in;
  5. 	set p_in=2;
  6. 	select P_in;
  7. end $$
  8. delimiter ;

额~~~~
调用有输入参数的存储过程就需要传入对应的参数

  1. call in_param(100);

2、out输出参数

就简单的理解为java中方法的返回值

  1. delimiter $$
  2. create procedure out_param(out p_out int)
  3. BEGIN
  4. 	select p_out;
  5. 	set p_out = 10000;
  6. 	select p_out;
  7. end $$
  8. delimiter ;

调用有输出参数的存储过程

  1. -- 这里的@p_out 就是一个用户变量 --
  2. set @p_out=1;
  3. call out_param(@p_out);
  4. select @p_out;

3、inout输入参数

额！！！就字面意思。既能输入，也能输出。 其实知道就好，尽量不要使用这种类型的参数

  1. delimiter $$
  2. create procedure inout_param(out p_inout int)
  3. BEGIN
  4. 	select p_inout;
  5. 	set p_inout = 10000;
  6. 	select p_inout;
  7. end $$
  8. delimiter ;

调用

  1. set @p_inout=1;
  2. call inout_param(@p_inout);
  3. select @p_inout;

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/q2780004063/article/details/120459601

内容来源于网络，如有侵权，请联系作者删除！

sqlmysql数据库教案

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com