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之间所有奇数的和