MySQL存储过程用法–含练习题

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

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

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

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

下面的例子都是用emp表进行演示的，emp表的数据可以在这里自取 https://blog.csdn.net/wuyomhchang/article/details/124000402?spm=1001.2014.3001.5501

-- 变量的基本用法
SELECT  MAX(sal) into @maxsal FROM emp;
SELECT @maxsal;
SELECT * FROM emp WHERE sal=@maxsal;

一、存储过程的语法

-- 存储过程
DROP PROCEDURE IF EXISTS get_maxsal; --（自定义存储过程的名字）
DELIMITER//
CREATE PROCEDURE get_maxsal()
 BEGIN
   SELECT max(sal) FROM emp;    --（begin-end写要执行的代码）
  END//
	
CALL get_maxsal();  --调用存储过程

二、在存储过程中声明并使用变量

-- 在存储过程中声明并使用变量
DROP PROCEDURE IF EXISTS get_sal();
DELIMITER//
CREATE PROCEDURE get_sal()
 BEGIN
   DECLARE avgsal INT;
	 SELECT AVG(sal) into avgsal FROM emp;
	 SELECT ename,sal FROM emp WHERE sal > avgsal;
 END
 
CALL get_sal();

三、带参数的存储过程语法

IN/OUT/INOUT param_name param_type(param_size)

-- IN OUT INOUT为参数类型

IN 输入参数:表示该参数的值必须在调用存储过程时指定赋值，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时必须指定具体值，并且可在存储过程体重被改变和在调用完毕后可被返回

看个小栗子吧！

DROP PROCEDURE IF EXISTS get_sal();
DELIMITER//
CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT empsal INT)
 BEGIN
   SELECT sal INTO empsal from emp WHERE ename=empname;
 END//
 
 -- 在存储过程中定义的参数，会在mysql中自动生成以下划线命名的参数
 CALL get_sal('BLACK',@_empsal);
 SELECT @_empsal;

四、流程控制函数（类似于Java、python里的）

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

1、if判断

 -- if判断
DROP PROCEDURE IF EXISTS get_sal;
DELIMITER//
CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT sallevel VARCHAR(10))
BEGIN
  DECLARE empsal INT;
	SELECT sal INTO empsal FROM emp WHERE ename=empname;
	IF empsal>3000 THEN
	 SET sallevel='high';
	ELSEIF empsal<=3000 AND empsal>2000 THEN
	 SET sallevel='middle';
  ELSE
	 SET sallevel='low';
  END if;
END//

CALL get_sal('BLACK',@sallevel);
SELECT @sallevel;

2、case语法

作用

if 的高级版，类似Java 里面的 switch … case
通过条件表达式匹配 case 对应的值，然后执行对应的操作

语法

— 只有一个条件表达式

CASE
    WHEN <条件1> THEN <操作>
    WHEN <条件2> THEN <操作>
    …
    ELSE <操作>
END;

将 <表达式> 的值逐一和每个 when 跟的 <值> 进行比较
如果跟某个<值>想等，则执行它后面的 <操作> ，如果所有 when 的值都不匹配，则执行 else 的操作
如果 when 的值都不匹配，且没写 else，则会报错

看个栗子吧！

-- case语法
CREATE PROCEDURE p()
  BEGIN
	 DECLARE v INT DEFAULT 1;
	 case v 
	  WHEN 2 THEN SELECT v;
		WHEN 3 THEN SELECT 0;
	 ELSE
	  BEGIN
		 SELECT 'hello';
	  END;
	END case;
	
END

CALL p();

3、循环 REPEAT 语法

-- 基本语法 固定搭配
REPEAT
	statement_list
UNTIL search_condition END REPEAT; -- UNTIL后为结束循环的条件

看个栗子吧！（打印指定开始和结束数字的和）

-- 打印指定开始和结束 数字的和 
DROP PROCEDURE if EXISTS dorepeat;
CREATE PROCEDURE dorepeat(in b INT,in e INT)
 BEGIN
   DECLARE total INT DEFAULT 0;
	 DECLARE temp INT DEFAULT b;
	 REPEAT
	   set total=total+temp;
		 set temp=temp+1;
UNTIL temp>e END REPEAT;
        SELECT total;
END

CALL dorepeat(1,10);

-- 例子2  应输出5 4 3 2 1
CREATE PROCEDURE dowhile()
BEGIN
   DECLARE num int DEFAULT 5;
	 WHILE num>0 DO
	   SELECT num;
		 SET num=num-1;
		END WHILE;
END 

CALL dowhile();

再来一道题！！编写一个存储过程，该存储过程接受一个部门编号作为参数，可以求出该部门的员工总数。

DELIMITER//
CREATE PROCEDURE get_total (IN bianhao INT,OUT _total INT) 
 BEGIN
   SELECT count(*) INTO _total FROM emp WHERE deptno=bianhao; 
  END
	
CALL get_total(10,@emptotal);
SELECT @emptotal;

今天分享到此结束，因为最近学校事情多，所以更新会有点慢！感谢支持♥

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/114923.html