![cover](https://img-blog.csdnimg.cn/img_convert/8e189b0ff78086cb4eccae1d21aec569.png)
MySQL存储过程
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。
·
MySQL存储过程
一、存储过程的简介
存储过程指事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程及函数可以简化开发人员的很多工作,数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
二、存储过程的操作
2.1 存储过程的创建
create procedure 存储过程名([存储过程的参数]) begin -- SQL语句 end;
示例:
delimiter $ create procedure pro_test1() begin select 'Hello,MySQL!'; end$ delimiter ;
知识小贴士:
delimiter: 该关键字用来声明SQL语句的分隔符,告诉MySQL解析器,该段命令是否已经结束,mysql是否可以执行了。默认情况下,delimiter是英文的分号。在MySQL命令行客户端中,如果有一行命令以分号结尾,那么回车后,mysql将执行该命令。
2.2 存储过程的调用
- 语法: call 存储过程名(); - 示例: call pro_test1();
2.3 查看已创建的存储过程
- 方式一 : 查询数据库中的所有存储过程 SELECT NAME FROM mysql.proc WHERE db = '数据库'; - 方式二: 查询存储过程的状态信息 show procedure status; - 查询某个存储过程的定义(查看存储过程的编写脚本) show create procedure 存储过程名;
2.4 删除存储过程
- 语法: drop procedure [if exists] 存储过程名; - 示例: drop procedure if exists pro_test1;
三、语法约定
* 引言
存储过程是可以编程的,意味着可以使用变量、表达式、控制结构来完成比较复杂的功能。
3.1 变量
-
declare:通过declare可以定义一个局部变量,该变量的作用范围只能在begin...end块中。
- 语法: declare 变量名 变量类型 [default value]; - 示例: delimiter $ create procedure pro_test1() begin declare num int default 5; select num + 10; end$ delimiter ;
-
set: 如果要对变量进行赋值可以使用set关键字
- 语法: set 变量名 = 值; - 示例1: delimiter $ create procedure pro_test1() begin declare name varchar(30); set name = 'MySQL'; select name; end$ delimiter ; - 示例2: delimiter $ create procedure pro_test2() begin declare name varchar(30); declare age int; set name = 'MySQL'; set age = 52; select concat('姓名为:',name,' 年龄为:',age); end$ delimiter ;
-
select ... into 方式进行赋值
- 示例: DELIMITER $ CREATE PROCEDURE pro_test3() BEGIN DECLARE num INT; SELECT COUNT(1) INTO num FROM student; #将学生表中的记录总数赋值给变量num SELECT CONCAT('num的值为:',num); END$ DELIMITER ;
3.2 if 条件语句
- 语法: if 条件1 then 执行语句1 elseif 条件2 then 执行语句2 else 执行语句3 end if; #结束条件语句
# 需求:根据定义的身高变量,判断当前身高的所属身材类型 180 及以上 --------------> 身材高挑 170 - 180 -------------> 标准身材 170 以下 -------------> 一般身材 # 关键代码 DELIMITER $ CREATE PROCEDURE pro_test() BEGIN DECLARE height INT DEFAULT 190; DECLARE description VARCHAR(50) DEFAULT ''; IF height >= 180 THEN SET descriptionv = '身材高挑'; ELSEIF height >= 170 AND height < 180 THEN SET description = '身材标准'; ELSE SET description = '一般身材'; END IF; SELECT CONCAT('身材为:',description); END$ DELIMITER ;
3.3 输入参数
- 语法: delimiter $ create procedure 存储过程名(in 参数名 参数类型) begin --业务代码 end$ delimiter ; 备注: 关键字 in 指代输入参数
# 需求:根据输入的参数身高变量,判断当前身高的所属身材类型 180 及以上 --------------> 身材高挑 170 - 180 -------------> 标准身材 170 以下 -------------> 一般身材 # 定义存储过程关键代码 DELIMITER $ CREATE PROCEDURE pro_test(in height int) # height为输入参数 BEGIN DECLARE description VARCHAR(50) DEFAULT ''; IF height >= 180 THEN SET description = '身材高挑'; ELSEIF height >= 170 AND height < 180 THEN SET description = '身材标准'; ELSE SET description = '一般身材'; END IF; SELECT CONCAT('身材为:',description); END$ DELIMITER ; # 调用存储过程关键代码 call pro_test(190); #调用存储过程时需要传入参数
3.4 输出参数
- 语法: delimiter $ create procedure 存储过程名(out 参数名 参数类型) begin --业务代码 end$ delimiter ; 备注: 关键字 out 指代输出参数
# 需求:根据输入的参数身高变量,判断当前身高的所属身材类型 180 及以上 --------------> 身材高挑 170 - 180 -------------> 标准身材 170 以下 -------------> 一般身材 # 定义存储过程关键代码 DELIMITER $ CREATE PROCEDURE pro_test(IN height INT,OUT description VARCHAR(50)) BEGIN IF height >= 180 THEN SET description = '身材高挑'; ELSEIF height >= 170 AND height < 180 THEN SET description = '身材标准'; ELSE SET description = '一般身材'; END IF; END$ DELIMITER ; # 调用存储过程关键代码 CALL pro_test(199,@hello); SELECT CONCAT('身材为:',@hello); 备注:@hello 这种变量叫做会话变量,代表整个会话过程都是有效的,类似于Java中的全局变量。
3.5 case结构
- 语法: case when 条件1 then 执行语句1 when 条件2 then 执行语句2 when 条件3 then 执行语句3 else 执行语句4 end case;
# 需求:根据传入的参数月份,判断属于一年中的第几季度 # 定义存储过程的关键代码 DELIMITER $ CREATE PROCEDURE pro_test(IN mon INT) BEGIN DECLARE result NVARCHAR(50); CASE WHEN mon >= 1 AND mon <= 3 THEN SET result = '第一季度'; WHEN mon >= 4 AND mon <= 6 THEN SET result = '第二季度'; WHEN mon >= 7 AND mon <= 9 THEN SET result = '第三季度'; ELSE SET result = '第四季度'; END CASE; SELECT CONCAT('对应的季度为:',result); END$ DELIMITER ; #调用存储过程的关键代码 CALL pro_test(9);
3.6 while循环
- 语法: while 循环条件 do 执行的语句 end while; - 需求:计算 1+2+3+4...+100 的总和。 # 定义存储过程的关键代码 DELIMITER $ CREATE PROCEDURE pro_test() BEGIN DECLARE result INT DEFAULT 0; #总和 DECLARE a INT DEFAULT 1; #从1开始 WHILE a <= 100 DO SET result = result + a; SET a = a +1; END WHILE; SELECT CONCAT('1~100以内的数总和为:',result); END$ DELIMITER ;
四、触发器
1.什么是触发器
-
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。
2.触发器的特性
-
什么条件会触发:I、D、U
-
什么时候触发:在增删改前或者后
-
触发频率:针对每一行执行
-
触发器定义在表上
备注:触发器尽量少的使用,因为不管如何,它还是很消耗资源,如果使用的话要谨慎的使用,确定它是非常高效的:触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。
3.创建触发器的语法
#语法: create trigger 触发器名 after [insert|update|delete] on 表1 for each row 执行语句; #例: CREATE TRIGGER triger1 AFTER INSERT ON A FOR EACH ROW INSERT INTO B VALUES (NOW());
4.查看触发器
#语法: show triggers;
5.删除触发器
#语法: drop trigger if exists 触发器名;
五、需求
需求1: 使用输入参数实现下面需求 * 根据输入顾客购物的金额,来确定获得的奖励,并打印输出 * 如果购物金额满500,则奖励5公斤面粉一袋 * 如果购物金额满300,则奖励500ml可乐一瓶 * 如果购物金额满200,则奖励250ml雪碧一瓶 * 否则,没有奖励
需求2: 使用输入参数实现下面需求 * 根据输入顾客购物的金额,来确定折扣,并打印输出折扣后商品的价格 * 如果购物金额满1500,打5折 * 如果购物金额满1000,打7折 * 如果购物金额满500,打9折 * 否则,没有任何折扣
需求3: 打印输出1~50之间所有奇数的和
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)