MySQL数据库（6）-存储过程

于 2024-12-10 15:10:16 发布
阅读量1k 收藏 8
点赞数 21
分类专栏： MySQL 数据库 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Tttian622/article/details/144363573
版权

1. 什么是存储过程

存储过程（Stored Procedure）：

是一种在数据库中存储的SQL语句集合，它可以通过一个名称来调用。

存储过程可以接受输入参数、输出参数或两者兼有，并且可以返回一个状态值。

特点：1.封装、复用      2.可接受参数      3.减少网络交互，提高效率

2. 创建&调用存储过程

2.1 语法结构

创建：

CREATE PROCEDURE 存储过程名称（[ 参数列表 ]）

BEGIN

        —SQL语句

END;

调用：

CALL 名称（[ 参数 ]）;

2.2 实例

#创建存储过程  #设置sql语句以$$符号结束
delimiter $$   
create procedure p1()
begin
	select count(*) from t_emp;
end$$
delimiter $$;
#调用存储过程
call p1();

3. 查看&删除存储过程

3.1 语法结构

查看：

SHOW CREATE PROCEDURE 存储过程名称;

删除：

DROP PROCEDURE 存储过程名称;

3.2 实例 

#查看存储过程
SHOW CREATE PROCEDURE p1
SELECT * FROM information_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='demo';
#删除存储过程
DROP PROCEDURE IF EXISTS p1;

4. 局部变量

变量分为：系统变量、用户定义变量、局部变量【※】

#创建存储过程，使用局部变量
delimiter $$
CREATE PROCEDURE p2()
BEGIN
	#声明变量
	DECLARE emp_count int DEFAULT 0;
	SELECT count(*) INTO emp_count FROM t_emp;
	SELECT emp_count;
END$$
delimiter;
#调用过程
call p2()

5. IF判断

#if练习
delimiter $$
CREATE PROCEDURE p3()
BEGIN
	#定义变量，用户存放成绩
	DECLARE score int DEFAULT 86;
	#定义变量用户存放返回结果
	DECLARE result VARCHAR(10);
	#判断成绩返回对应文本
	IF score >= 85 THEN
		SET result :='优秀';
	ELSEIF score >= 60 THEN
		SET result :='及格';
	ELSE 
		SET result :='不及格';
	END IF;
	SELECT result;
END$$
delimiter;
#调用过程
call p3()

6. 参数列表

6.1 什么是参数列表

参数列表是指在定义函数、方法或存储过程时，用于指定其参数的一系列声明。

参数列表定义了函数或方法可以接受的输入参数的名称、类型和传递方式。

参数列表在编程中起着至关重要的作用，因为它决定了函数如何接收和处理外部传入的数据。

6.2 参数类型

1、IN(默认)：该类参数作为输入，调用时需要传入值

2、OUT：该类参数作为输出，参数可以作为返回值

3、INOUT：既可以作为输入参数，也可以作为输出参数

6.3 实例1

# 参数列表
delimiter $$
CREATE PROCEDURE p4(IN score INT,OUT result VARCHAR(10))
BEGIN
	IF score >= 85 THEN
		SET result :='优秀';
	ELSEIF score >= 60 THEN
		SET result :='及格';
	ELSE
		SET result :='不及格';
	END IF;
END$$
delimiter;
# 调用过程 
# @:定义用户级变量
call p4(55,@result)
SELECT @result;

6.4 实例2

将传入的200分制的分数转换成百分制输出：

DELIMITER $$
CREATE PROCEDURE p5(INOUT score DOUBLE, OUT result VARCHAR(10))
BEGIN
    SET score = score * 0.5;
    IF score >= 85 THEN
        SET result = '优秀';
    ELSEIF score >= 60 THEN
        SET result = '及格';
    ELSE
        SET result = '不及格';
    END IF;
END$$
DELIMITER ;
-- 创建用户级的自定义变量
SET @score = 165;
CALL p5(@score, @result);
SELECT @score, @result;

7.case多条件

根据传入的月份，判定月份所属的季节(要求采用case结构)：

  • 1-3月份，为第一季度
  • 4-6月份，为第二季度
  • 7-9月份，为第三季度
  • 10-12月份，为第四季度
#多条件判断
DELIMITER $$
CREATE PROCEDURE p6(IN month INT)
BEGIN
	DECLARE result VARCHAR(10);
	CASE 
		WHEN month >= 1 and month <= 3 THEN
			SET result := '第一季度';
		WHEN month >= 4 and month <= 6 THEN
			SET result := '第二季度';
		WHEN month >= 7 and month <= 9 THEN
			SET result := '第三季度';
		WHEN month >= 10 and month <= 12 THEN
			SET result := '第四季度';
		ELSE
			SET result := '非法参数';
	END CASE;
	SELECT CONCAT('您输入的月份为:',month,'所属季度为:',result);
END$$
DELIMITER ;
# 调用过程 
CALL p6(5)

8.循环

计算从1累加到n的值，n为传入的参数值。

定义局部变量，记录累加之后的值;

每循环一次，就会对进行减1，如果n减到0，则退出循环

8.1 while循环

#while循环
DELIMITER $$
CREATE PROCEDURE p7(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	WHILE n>0 DO
		#累加
		SET total := total +n;
		SET n := n-1;
	END WHILE;
	SELECT total;
END$$
DELIMITER ;
# 调用过程 
CALL p7(6)

8.2 repeat循环

#REPEAT循环
DELIMITER $$
CREATE PROCEDURE p8(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	REPEAT
		SET total := total +n;
		SET n := n-1;
	UNTIL n <=0 #返回结果为true时，跳出循环
	END REPEAT;
	SELECT total;
END$$
DELIMITER ;
# 调用过程 
CALL p8(150)

8.3 loop循环

#LOOP循环
DELIMITER $$
CREATE PROCEDURE p9(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	sum:LOOP
		IF n <= 0 THEN
			leave sum;
		END IF;
		SET total := total + n ;
		SET n := n-1;
	END LOOP sum;
	SELECT total;
END$$
DELIMITER ;
# 调用过程 
CALL p9(10)

8.4 练习1

n以内的偶数累加：

DELIMITER $$
CREATE PROCEDURE p10(IN n INT)
BEGIN
	DECLARE total INT DEFAULT 0;
	sum:LOOP
		IF n <= 0 THEN
			leave sum;
		END IF;
		IF n % 2 = 1 THEN
			SET n := n-1;
			ITERATE sum;
		END IF;
		
		SET total := total + n ;
		SET n := n-1;
	END LOOP sum;
	SELECT total;
END$$
DELIMITER ;
# 调用过程 
CALL p10(10)

9.游标

9.1 什么是游标

用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。

游标的使用包括游标的声明、OPEN、FETCH和 CLOE。语法结构如下:

  • 1.声明游标：DECLARE 游标名称 CURSOR FOR 查询语句
  • 2.打开游标：OPEN 游标名称;
  • 3.获取游标记录：FETCH 游标名称 INTO 变量[变量];
  • 4.关闭游标：CLOSE 游标名称;

9.2 游标案例

根据传入的薪资参数，来查询员工表t_emp中，所有底薪大于传入薪资的员工姓名(ename)、岗位(job)、底薪数(sal)。将查询到的结果插入到一张新表中。思路如下：

  • 1.声明游标，存储查询结果集
  • 2.准备:创建表结构
  • 3.开启游标
  • 4.获取游标中的记录
  • 5.插入数据到新表中
  • 6.关闭游标
DELIMITER $$
CREATE PROCEDURE p11(IN esal DECIMAL)
BEGIN
	#声明变量
	DECLARE e_ename VARCHAR(100);
	DECLARE e_job VARCHAR(100);
	DECLARE e_sal VARCHAR(100);
	#声明游标
	DECLARE e_cursor CURSOR FOR SELECT ename,job,sal FROM t_emp WHERE sal > esal;
	#声明处理程序，满足条件时关闭游标
	DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE e_cursor;
	#删除并创建存放数据的表
	DROP TABLE IF EXISTS t_emp_pro;
	CREATE TABLE IF NOT EXISTS t_emp_pro(
		empno INT PRIMARY KEY auto_increment,
		ename VARCHAR(100),
		job VARCHAR(100),
		sal DECIMAL
	);
	#打开游标
	OPEN e_cursor;
	#游标赋值给变量
	WHILE TRUE DO
		FETCH e_cursor INTO e_ename,e_job,e_sal;
		#向t_emp_pro表中添加数据
		INSERT INTO t_emp_pro VALUES(NULL,e_ename,e_job,e_sal);
	END WHILE;
	#关闭游标
	CLOSE e_cursor;
END$$
DELIMITER ;
#调用过程
CALL p11(2000)

10. 条件处理程序

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

10.1 Handler Action

Handler Action 是指当特定条件（如错误或警告）发生时，你希望执行的操作。

MySQL提供了三种类型的处理程序动作：

  • DCONTINUE：继续执行当前程序
  • EXIT：终止执行当前程序

  • UNDO：回滚当前事务（仅在事务性存储引擎中有效）。

10.2 Condition Value

  • SQLSTATE + sqlstate value（状态码，如02000）：表示特定的错误或警告条件；
  • SQLWARNING：所有以01开头的SQLSTATE代码的简写；
  • NOT FOUND：所有以02开头的SQLSTATE代码的简写；
  • SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码简写。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值