MySQL 存储过程 - 小白必看（四）

存储过程

什么是存储过程？

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

2，简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

3，存储过就是数据库 SQL 语言层面的代码封装与重用。

有哪些特性?

1，有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

2，函数的普遍特性：模块化，封装，代码复用；

3，速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;

-- 自定义 在begin/end块中有效
DELIMITER $$
CREATE PROCEDURE proce02()
BEGIN 
DECLARE var_name VARCHAR(10) DEFAULT '张晶晶';
SELECT emp_id,emp_name FROM emp WHERE 
emp_name=var_name;
END $$
DELIMITER;
CALL proce02();

-- 系统变量-全局变量
-- 1,如何查看全局变量
SHOW GLOBAL VARIABLES ;
-- 2,查看某个全局变量
SELECT @@global.avoid_temporal_upgrade;
-- 3,修改全局变量的值
SET GLOBAL sort_buffer_size = 40000; 
SET @@global.sort_buffer_size = 40000;


-- 系统变量-会话变量
-- 查看会话变量
SHOW SESSION VARIABLES;
-- 查看某会话变量 
SELECT @@session.auto_increment_increment;
-- 修改会话变量的值
SET SESSION sort_buffer_size = 50000; 
SET @@session.sort_buffer_size = 50000 ;

IN :输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回

OUT 输出参数:该值可在存储过程内部被改变，并向外输出

INOUT 输入输出参数，既能输入一个值又能传出来一个值

-- 存储过程传参 -in
DELIMITER $$
CREATE PROCEDURE proce03(IN emp_empno INT)
BEGIN 
SELECT * FROM emp WHERE emp_id=emp_empno;
END $$
DELIMITER;
CALL proce03(1);

-- in 表示传入的参数，可以传入数值或者变量，同时即使传入变量，并不会更改变量的值，可以内部更改，只作用于函数范围


-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
DELIMITER $$
CREATE PROCEDURE proce04(IN e_departtment VARCHAR(10),IN e_salary DECIMAL(10,2))
BEGIN 
SELECT * FROM emp WHERE department=e_departtment 
AND salary>ALL(SELECT e_salary FROM emp WHERE department=e_departtment);
END $$
DELIMITER;
CALL proce04("销售部",5000);


-- 传出参数 -out
DELIMITER $$
CREATE PROCEDURE proce05(IN emp_empno INT , OUT out_ename VARCHAR(10) )
BEGIN 
SELECT emp_name INTO out_ename FROM emp WHERE emp_id=emp_empno;
END $$
DELIMITER;

CALL proce05(4,@out_ename);

SELECT @out_ename;


-- 传入员工编号，返回员工名字和薪资
DELIMITER $$
CREATE PROCEDURE proce06(IN e_id INT , OUT out_name VARCHAR(10),OUT out_salary DECIMAL(10,2))
BEGIN
SELECT emp_name,salary INTO out_name ,out_salary FROM emp WHERE emp_id=e_id;
END $$
DELIMITER;

CALL proce06(2,@out_name,@out_salary);

SELECT @out_name,@out_salary;


-- 传入员工名，拼接部门号，传入薪资，求出年薪
DELIMITER $$
CREATE PROCEDURE proce07(INOUT e_name VARCHAR(10), INOUT e_salary DECIMAL(10,2))
BEGIN

SELECT CONCAT(e_name,"_",department) INTO e_name FROM emp WHERE emp_name=e_name;
SET e_salary=e_salary*12;
END $$
DELIMITER;

SET @e_name='张晶晶';
SET @e_salary=5000;
CALL proce07(@e_name,@e_salary);

SELECT @e_name;
SELECT @e_salary;



-- if判断
-- 输入学生的成绩，来判断成绩的级别：
/*
  score < 60 :不及格
  score >= 60  , score <80 :及格
    score >= 80 , score < 90 :良好
    score >= 90 , score <= 100 :优秀
    score > 100 :成绩错误
*/
DELIMITER $$
CREATE PROCEDURE proce08 (IN score INT) 
BEGIN
  IF score < 60 
  THEN 
  SELECT 
    '不及格' ;
  ELSEIF score < 80 
  THEN 
  SELECT 
    '及格' ;
  ELSEIF score < 90 
  THEN 
  SELECT 
    '良好' ;
  ELSEIF score <= 100 
  THEN 
  SELECT 
    '优秀' ;
  ELSE 
  SELECT 
    '成绩错误' ;
  END IF ;
END $$
DELIMITER;

CALL  proce08(100);

-- 输入员工名字，判断工资情况
DELIMITER $$
CREATE PROCEDURE proce09 (IN e_name VARCHAR (10)) 
BEGIN
  DECLARE result VARCHAR (20) ;
  DECLARE e_salary DECIMAL (10, 2) ;
  
  SELECT salary INTO e_salary 
  FROM emp 
  WHERE emp_name = e_name ;
  
  IF e_salary < 5000 
  THEN 
  SET result = '初级' ;
  
  ELSEIF e_salary < 6000 
  THEN
  SET result = '中级' ;
  
  ELSEIF e_salary < 8000 
  THEN 
  SET result = '高级' ;
  ELSE 
  SET result ='你落后了' ;
  END IF ;
  SELECT result;
END $$
DELIMITER;

CALL  proce09('张晶晶');


-- 流程控制 case 类似于switch
-- 语法一：
DELIMITER $$
CREATE PROCEDURE proce10 (IN t_zy INT) 
BEGIN
  CASE t_zy 
    WHEN 1 THEN SELECT '微信支付' ;
    WHEN 2 THEN SELECT '支付宝支付' ;
    WHEN 3 THEN SELECT '银行卡支付' ;
    ELSE SELECT '其他支付' ;
  END CASE ;
END $$
DELIMITER;
CALL proce10(1);

-- 语法二：
DELIMITER $$
CREATE PROCEDURE proce11( IN t_zy INT )
BEGIN
  CASE 
    WHEN t_zy=1 THEN SELECT '微信支付';
    WHEN t_zy=2 THEN SELECT '支付宝支付';
    WHEN t_zy=3 THEN SELECT '银行卡支付';
    ELSE SELECT '其他支付';
  END CASE;
END $$
DELIMITER;
CALL proce11(1);

-- 流程控制 循环 while 
DELIMITER $$
CREATE PROCEDURE proce12( IN t_zy INT )
  BEGIN
  DECLARE i INT DEFAULT 11;
  label:WHILE i<=t_zy DO
  INSERT INTO emp(emp_id,emp_name,salary,department)
  VALUES (i,CONCAT('user-',i),'5000','好人部');
  SET i=i+1;
  END WHILE label;
  END $$
DELIMITER;
CALL proce12(15);

游标：

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

DELIMITER $$
CREATE PROCEDURE proce13( IN t_zy VARCHAR(20))
  BEGIN
  -- 定义局部变量
  DECLARE yb_emp_id INT;
  DECLARE yb_emp_name VARCHAR(50);
  DECLARE yb_salary  DECIMAL(7,2);
  DECLARE yb_department VARCHAR(20);
  
  -- 声明游标
  DECLARE yb_c CURSOR FOR
  SELECT emp_id,emp_name,salary,department FROM emp
  WHERE department=t_zy;
  
  -- 打开游标
  OPEN yb_c;
  
  -- 获取游标的每一行数据
  label:LOOP
  FETCH yb_c INTO yb_emp_id,yb_emp_name,yb_salary,yb_department;
  SELECT yb_emp_id,yb_emp_name,yb_salary,yb_department;
  END LOOP label;
  
  -- 关闭游标
  CLOSE yb_c;
  
  END $$
DELIMITER;
CALL proce13('销售部');