当前位置:   article > 正文

MySQL存储过程_mysql 存储过程

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之间所有奇数的和
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/代码探险家/article/detail/785559
推荐阅读
相关标签
  

闽ICP备14008679号