MySQL存储过程

最新推荐文章于 2022-06-09 20:03:21 发布

weixin_43576422

最新推荐文章于 2022-06-09 20:03:21 发布

阅读量601

点赞数

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

本文链接：https://blog.csdn.net/weixin_43576422/article/details/108253772

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

简介

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

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

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

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

优点

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

缺点

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

编写第一个MySQL存储过程

下面是一个名为 GetAllProducts()的存储过程，从products表中选择所有产品。
启动 mysql 客户端工具并键入以下命令：

DELIMITER $$
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END $$
DELIMITER ;

让我们来详细地说明上述存储过程：
第一个命令是DELIMITER $$，它与存储过程语法无关。作用是声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

DELIMITER语句将标准分隔符 - 分号(;)更改为：$$。在这种情况下，分隔符从分号(;)更改为$$。在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。最后一个命令(DELIMITER;)将分隔符更改回分号(; )。

使用CREATE PROCEDURE语句创建一个新的存储过程。在CREATE PROCEDURE语句之后指定存储过程的名称。在这个示例中，存储过程的名称为：GetAllProducts，并把括号放在存储过程的名字之后，括号里面可以带参数，如果接触过编程语言的话，会发现这和函数很像。

BEGIN和END之间的部分称为存储过程的主体。将声明性SQL语句放在主体中以处理业务逻辑。在这个存储过程中，我们使用一个简单的SELECT语句来查询products表中的数据。

大多数数据库客户端工具可以让我们方便的创建存储过程：下面是Navicat的截图：
在这里插入图片描述

在这里插入图片描述

调用存储过程要调用存储过程，可以使用以下SQL命令：

CALL STORED_PROCEDURE_NAME[(参数)];

使用CALL语句调用存储过程，例如调用GetAllProducts()存储过程，则使用以下语句：

由于没有参数，去掉括号（）也是可以的。

CALL GetAllProducts();

MySQL存储过程的变量

变量是一个命名数据对象，变量的值可以在存储过程执行期间更改。我们通常使用存储过程中的变量来保存直接/间接结果。这些变量是存储过程的本地变量。

注意：变量必须先声明后，才能使用它。

声明变量

要在存储过程中声明一个变量，可以使用DECLARE语句，如下所示：

DECLARE variable_name datatype(size) DEFAULT default_value;

下面来更详细地解释上面的语句：

首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
其次，指定变量的数据类型及其大小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。
第三，当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。

例如，可以声明一个名为total_sale的变量，数据类型为INT，默认值为0，如下所示：

DECLARE total_sale INT DEFAULT 0;

其他类型例如:

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

MySQL允许您使用单个DECLARE语句声明共享相同数据类型的两个或多个变量，如下所示：

DECLARE x, y INT DEFAULT 0;

我们声明了两个整数变量x和y，并将其默认值设置为0。

分配变量值

当声明了一个变量后，就可以开始使用它了。
1）要为变量分配一个值，可以使用SET语句，例如：

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;

上面语句中，分配total_count变量的值为10。

2）除了SET语句之外，还可以使用SELECT INTO语句将查询的结果分配给一个变量。请参阅以下示例：

DECLARE total_products INT DEFAULT 0;

SELECT COUNT(*) INTO total_products FROM products;

在上面的例子中：

首先，声明一个名为total_products的变量，并将其值初始化为0。
然后，使用SELECT INTO语句来分配值给total_products变量，从默认数据库（或者指定数据库）中的products表中选择的产品数量。

变量范围(作用域)

一个变量有自己的范围(作用域)，它用来定义它的生命周期。如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。
如果您在BEGIN END块内声明一个变量，那么如果达到END，它将超出范围。

可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。

以@符号开头的变量是会话变量（用户变量）。直到会话结束前它可用和可访问。

例如在MySQL客户端使用用户变量:

 select 'hello world!' into @x;
 select @x;

在这里插入图片描述
在存储过程中使用用户变量:

set @greeting='hello';
create procedure HelloWorld() select concat(@greeting,'world') as message;
call HelloWorld();

在这里插入图片描述

删除存储过程

和删除表类似:

drop procedure procedure_name;

在这里插入图片描述

存储过程的参数

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

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数（默认模式，值传递）：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

如果有多个参数，则用 , 隔开

MySQL存储过程参数示例

1、IN参数示例

假设有这样一个student表：
在这里插入图片描述

以下示例说明如何使用Get_Stu_Name存储过程中的IN参数来查询选择特定学生的分数。

desc student; --查看要查询的字段的数据类型

-- 查询sid对应的学生姓名
delimiter $$
-- 使用名为score的数据库
use score $$
create procedure get_stu_name(in ssid VARCHAR(10))
begin 
select sname from student where ssid=sid;
END $$
delimiter ;

ssid是存储过程的IN参数。
假设我们想要查询‘01’学号的学生姓名，我们只需要将一个值(‘01’)传递给存储过程，如下所示：

call get_stu_name('01');

在这里插入图片描述

2、out输出参数

下面存储过程查询student表的所有人数：

delimiter $$
create procedure get_stu_nums(out num)
begin
select num;
select count(*) into num from student;
select num;
end $$
delimiter ;

在这里插入图片描述
可以看到：
第一个输出是NULL，因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_num为null。

由于已经调用了get_stu_nums存储过程，输出参数，改变了p_num变量的值。

inout应该不难理解，建议自己尝试代码。

MySQL存储过程返回多个值

MySQL存储函数只返回一个值。要开发返回多个值的存储过程，需要使用带有INOUT或OUT参数的存储过程。
就是利用多个参数当作返回值。
现在有这样一个分数表：
在这里插入图片描述

课程号：
在这里插入图片描述

我们传入一个学号，返回三科的成绩（如果有的话）：

delimiter $$
create procedure get_id_score(
	in p_id varchar(10),
	out p_eng int,
	out p_ch int,
	out p_math int)
begin
select score into p_eng from sc where sid=p_id and cid='03';
select score into p_ch from sc where sid=p_id and cid='01';
select score into p_math from sc where sid=p_id and cid='02';
end $$
delimiter ;

接着可以传递学生id和三个用户定义的变量来获取输出值。
执行存储过程后，使用SELECT语句输出变量值。

set @id='01';
call get_id_score(@id,@eng,@ch,@math);
select @id,@eng,@ch,@math;

在这里插入图片描述
当某个学生某科没有分数：

MySQL存储过程条件语句

1）if-then-else语句

语法
最简单的单个if语句：

IF expression THEN 
   statements;
END IF;

当然也可以使用多层嵌套：

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

输入id，根据学生平均成绩输出对应信息：

drop procedure if EXISTS get_degree;
delimiter $$
CREATE PROCEDURE get_degree (IN p_id VARCHAR ( 10 )) 
BEGIN
	DECLARE	v_score INT;
	DECLARE	degree CHAR ( 4 );
	SELECT
		avg(score) INTO v_score FROM sc WHERE sid = p_id GROUP BY sid;
	IF
		v_score >= 60 THEN		
			SET degree = 'pass';
		
		ELSEIF v_score < 60 THEN	
			SET degree = 'NO';
		ELSE 
			SET degree = 'NULL';
	END IF;
		
	SELECT p_id, (select sname from student where sid=p_id) as name, v_score, degree;
		
	END $$
	delimiter ;

在这里插入图片描述

2）case语句

语法

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

循环语句

1. while ···· end while

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 ;

while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

它在执行操作后检查结果，而 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 ;

repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

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 ;

LABLES 标号：

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

MySQL游标

使用MySQL游标可以遍历SELECT语句返回的结果集。
游标允许您迭代查询返回的一组行，并相应地处理每行。
MySQL游标为只读，不可滚动和敏感。

使用游标

使用MySQL游标首先，必须使用DECLARE语句声明游标：

DECLARE cursor_name CURSOR FOR SELECT_statement;

游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。

游标必须始终与SELECT语句相关联。

接下来，使用OPEN语句打开游标。OPEN语句初始化游标的结果集，因此您必须在从结果集中提取行之前调用OPEN语句。

OPEN cursor_name;

然后，使用FETCH语句来检索光标指向的下一行，并将光标移动到结果集中的下一行。

FETCH cursor_name INTO variables list;

之后，可以检查是否有任何行记录可用，然后再提取它。
最后，调用CLOSE语句来停用光标并释放与之关联的内存，如下所示：

CLOSE cursor_name;

当光标不再使用时，应该关闭它。

当使用MySQL游标时，还必须声明一个NOT FOUND处理程序来处理当游标找不到任何行时的情况。因为每次调用FETCH语句时，游标会尝试读取结果集中的下一行。当光标到达结果集的末尾时，它将无法获得数据，并且会产生一个条件。处理程序用于处理这种情况。
要声明一个NOT FOUND处理程序，参考以下语法：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

finished是一个变量，指示光标到达结果集的结尾。请注意，处理程序声明必须出现在存储过程中的变量和游标声明之后。
下图说明了MySQL游标如何工作。
在这里插入图片描述
下面这个例子取出所有学生的姓名并以；分隔

-- drop procedure if exists get_all_stu_name;
delimiter $$
CREATE PROCEDURE get_all_stu_name (inout name_list VARCHAR ( 400 )) 
BEGIN
	DECLARE	v_finished integer DEFAULT 0;
	DECLARE	v_name VARCHAR (20) DEFAULT "";
	-- declare cursor for stu name
	declare name_cursor cursor for
	select sname from student;
	
	-- declare not found handler
	declare continue handler
	for NOT found set v_finished=1;
	
	-- 打开游标
	open name_cursor;
	get_name: loop
	
	fetch name_cursor into v_name;
	
	if v_finished = 1 then
	leave get_name; -- 离开循环
	end if;
	
	-- build name list
	set name_list = CONCAT(v_name,",",name_list);
	end loop get_name;
	close name_cursor;
END $$
delimiter ;

要查看结果，可以这样调用：

set @name_list='';
call get_all_stu_name(@name_list);
select @name_list;

在这里插入图片描述

查看MySQL数据库存储过程

显示存储过程字符要显示存储过程的字符，请使用SHOW PROCEDURE STATUS语句如下：

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

可以使用LIKE或WHERE子句根据各种标准过滤出存储过程。

比如在特定数据库中显示存储过程：
在这里插入图片描述
如果要显示具有特定模式的存储过程，例如，名称为get字符开头的，则可以使用LIKE操作符，如以下命令：

SHOW PROCEDURE STATUS WHERE name LIKE 'get%'

在这里插入图片描述

显示存储过程的源代码

要显示特定存储过程的源代码，请使用SHOW CREATE PROCEDURE语句如下：

SHOW CREATE PROCEDURE stored_procedure_name

在SHOW CREATE PROCEDURE关键字之后指定存储过程的名称。

参考

参考：https://www.yiibai.com/mysql/variables-in-stored-procedures.html

weixin_43576422

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

MySQL存储过程

文章目录

简介

优点

缺点

编写第一个MySQL存储过程

调用存储过程要调用存储过程，可以使用以下SQL命令：

MySQL存储过程的变量

声明变量

分配变量值

变量范围(作用域)

删除存储过程

存储过程的参数

MySQL存储过程参数示例

1、IN参数示例

2、out输出参数

MySQL存储过程返回多个值

MySQL存储过程条件语句

1）if-then-else语句

2）case语句

循环语句

1. while ···· end while

2. repeat···· end repeat

3. loop ·····endloop

MySQL游标

使用游标

查看MySQL数据库存储过程

显示存储过程的源代码

参考