14 存储过程

1. 什么是存储过程

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

PS：存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事出发后自动调用。

 

2. 有哪些特性

有输入输出参数，可以声明变量，有IF/ELSE、CASE、WHILE等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

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

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

 

3. 存储过程语法

--存储过程
--创建存储过程
DELIMITER $                --声明存储过程的结束符
CREATE PROCEDURE 存储过程名（参数模式 参数名 参数类型）
BEGIN
        存储过程体            --一组合法的SQL语句，如果存储过程体仅仅只有一句话，BEGIN END可以省略，存储过程体中的每条语句后面必须要加分号
END $                       --结束符


--执行存储过程
CALL 存储过程名（参数模式 参数名 参数类型）


--参数模式
--IN：表示输入参数，可以携带数据带入存储过程中
--OUT：表示输出参数，可以从存储过程中返回结果
--INOUT：表示输入输出参数，两者结合，该参数既可以作为输入又可以作为输出

 

案例：员工表和部门表

DROP TABLE IF EXISTS users;

CREATE TABLE users(
ID BIGINT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
USERNAME VARCHAR(20) DEFAULT NULL,
SEX INT(2) DEFAULT NULL,
DEPT BIGINT(10) DEFAULT NULL,
SALARY DECIMAL(10,2) DEFAULT NULL
)

INSERT INTO users VALUES(1,'张三',1,3,1000.00);
INSERT INTO users VALUES(2,'李四',0,2,2000.00);
INSERT INTO users VALUES(3,'王五',1,3,3000.00);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept(
ID BIGINT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
DEPT_NAME VARCHAR(32) DEFAULT NULL
)

INSERT INTO dept VALUES(1,'IT部门');
INSERT INTO dept VALUES(2,'财务部');
INSERT INTO dept VALUES(3,'人力资源部');

 

带有输入参数的存储过程：

--存储过程
--需求：传入一个用户ID，查询该用户的信息
--带有参数输入的存储过程
DELIMITER $
CREATE PROCEDURE user_findById(IN uid int)
BEGIN 
SELECT * FROM users WHERE id=uid;
END $

--调用存储过程
CALL user_findById(1);

 

带有输出参数的存储过程：

--带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE user_out(OUT str VARCHAR(20))
BEGIN
set str='这是一个输出参数';        --给参数赋值
END $

--调用存储过程
--定义了一个会话变量接收存储过程输出的参数
CALL user_out(@name);
select @name;

--删除存储过程
DROP PROCEDURE user_out;

 

输入输出参数的存储过程：

DELIMITER $                              --注意运行的时候，要把DELIMITER $和存储过程体一起运行，否则会报错
CREATE PROCEDURE user_inout(inout n int)
BEGIN
select n;
set n=500;
END $
SET @n=10;

CALL user_inout(@n);
select @n;

 

带条件判断的存储过程：

--带条件判断的存储过程
--要求输入一个正整数，如果是1，返回“星期一”，如果是2，返回“星期二”

DELIMITER $
CREATE PROCEDURE user_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
set str='星期一';
ELSEIF num=2 THEN
set str='星期二';
ELSEIF num=3 THEN 
set str='星期三';
ELSE
set str='输入错误';
END IF;
END $

--执行存储过程
CALL user_testIf(2,@str)
SELECT @str;

 

带循环功能的存储过程：

--带循环功能的存储过程
--输入一个整数，求和。比如：输入100，统计1-100的和

DELIMITER $
CREATE PROCEDURE user_testWhile(IN num int,OUT result int)
BEGIN
--定义两个局部变量
DECLARE i int DEFAULT 1;
DECLARE vsum int DEFAULT 0;
WHILE i<=num DO
set vsum=vsum+i;
set i=i+1;
END WHILE;
set result=vsum;
END $

CALL user_testWhile(100,@result);
select @result;
DROP PROCEDURE user_testWhile;

 

使用查询的结果赋值给变量（into）：

--使用查询的结果赋值给变量（into）

DELIMITER $
CREATE PROCEDURE user_findById(IN uid int,OUT vname VARCHAR(20))
BEGIN 
SELECT username INTO vname FROM users WHERE id=uid;
END $

CALL user_findById(1,@vname);
select @vname;
DROP PROCEDURE user_findById;

 

编写一个存储过程：

--练习：编写一个存储过程
--如果员工的工资平均数<=7000，输出“一般”
--如果员工的工资平均数>7000，且<=9000，输出“良好”
--如果员工的工资平均数>9000，输出“优秀”

DELIMITER $
CREATE PROCEDURE user_testAvg(OUT str VARCHAR(20))
BEGIN
DECLARE savg DOUBLE;
SELECT AVG(salary) INTO savg FROM users;
IF savg<=7000 THEN
set str='一般'；
ELSEIF savg>7000 AND savg <=9000 THEN 
set str='良好';
ELSE set str='优秀';
END IF;
END $

CALL user_testAvg(@str);
select @str;