什么是存储过程
- MySQL 5.0版本开始支持存储过程。
- 简单的说,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似JAVA语言中的方法
- 存储过程就是数据库SQL语言层面的代码封装与重用
特性 - 有输入输出参数,可以声明比那里,有if/else,case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
- 函数的普遍特性:模块化,封装,代码复用
- 速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤
入门案例
方法:
delimiter 自定义结束符号
CREATE PROCEDURE 存储名([in , out, inout] 参数名 数据类型……)
BEGIN
SQL语句
END 自定义的结束符号
delimiter ;
例如:
CREATE DATABASE mysql7_procedure;
use mysql7_procedure;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int NOT NULL,
`dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`loc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, '教研部', '北京');
INSERT INTO `dept` VALUES (20, '学工部', '上海');
INSERT INTO `dept` VALUES (30, '销售部', '广州');
INSERT INTO `dept` VALUES (40, '财务部', '武汉');
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int NOT NULL,
`ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`job` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`mgr` int NULL DEFAULT NULL,
`hiredate` date NULL DEFAULT NULL,
`sal` decimal(7, 2) NULL DEFAULT NULL,
`COMM` decimal(7, 2) NULL DEFAULT NULL,
`deptno` int NULL DEFAULT NULL,
PRIMARY KEY (`empno`) USING BTREE,
INDEX `fk_emp`(`mgr`) USING BTREE,
CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int NOT NULL,
`losal` int NULL DEFAULT NULL,
`hisal` int NULL DEFAULT NULL,
PRIMARY KEY (`grade`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES (1, 7000, 12000);
INSERT INTO `salgrade` VALUES (2, 12010, 14000);
INSERT INTO `salgrade` VALUES (3, 14010, 20000);
INSERT INTO `salgrade` VALUES (4, 20010, 30000);
INSERT INTO `salgrade` VALUES (5, 30010, 99990);
SET FOREIGN_KEY_CHECKS = 1;
DELIMITER $$
CREATE PROCEDURE proc01()
BEGIN
SELECT empno,ename FROM emp;
END $$
DELIMITER ;
-- 调用存储过程
CALL proc01();
变量定义
局部变量
用户自定义
用户自定义,在BEGIN/END块中有效
方法:
声明变量 DECLARE var_name TYPE [DEFAULT var_value];
例如:
DELIMITER $$
CREATE PROCEDURE proc02()
BEGIN
DECLARE var_name01 VARCHAR(20) DEFAULT 'aaa'; -- 声明/定义变量
SET var_name01='zhangsan'; -- 给变量赋值
SELECT var_name01; -- 输出变量的值
END $$
DELIMITER ;
CALL proc02();
MySQL中还可以使用SELECT…INTO语句为变量赋值。
方法:
SELECT col_name[…] into var_name[,…]
from table_name where condition
col_name 参数表示查询的字段名称
var_name参数时变量的名称
table_name 参数指表的名称
condition 参数指查询条件
注:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列。
例如:
DELIMITER $$
CREATE PROCEDURE proc03()
BEGIN
DECLARE my_ename VARCHAR(20) ; -- 声明/定义变量
SELECT ename INTO my_ename FROM emp WHERE empno=1001; -- 给变量赋值
SELECT my_ename; -- 输出变量的值
END $$
DELIMITER ;
CALL proc03();
用户变量
概念:用户自定义,当前会话(连接)有效。类比JAVA的成员变量
方法:
@var_name
不需要提前声明变量,使用即声明
例如:
DELIMITER $$
CREATE PROCEDURE proc04()
BEGIN
SET @var_name01='beijing';
SELECT @var_name01;
END $$
DELIMITER;
CALL proc04()
SELECT @var_name01;
系统变量
概念:
- 系统变量又分为全局变量与会话变量
- 全局变量在MySQL启动的时候由服务器自动将他们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改。
- 会话变量在每次建立一个新的连接的时候,由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份。来作为会话变量。
- 也就是说,如果在建立会话以后,没有手动更改过会话变量于全局变量的值,那所有这些变量的值都是一样的。
- 全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)。
- 有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统比那里的值却是只读的,对于那些可以更改的系统变量,我们可以利用set语句进行更改。
系统变量-全局变量
由系统提供,在整个数据库有效。
方法:
@@global.var_name
例如:
-- 查询全局变量
SHOW GLOBAL VARIABLES;
-- 查看某全局变量
SELECT @@GLOBAL.sort_buffer_size;
-- 修改全局变量的值
SET GLOBAL sort_buffer_size=300000;
SET GLOBAL sort_buffer_size=262144;
-- 查看会话变量
SHOW SESSION VARIABLES;
-- 查看某会话变量
SELECT @@SESSION.auto_increment_increment;
-- 修改会话变量的值
SET SESSION sort_buffer_size=50000;
SET SESSION @@sort_buffer_size=60000;
参数传递
in
概念:in表示传入的参数,可以传入数值或者变量,即使传入变量,并不会更改变量的值,可以内部更改,仅仅作用在函数范围内。
例如:
DELIMITER $$
CREATE PROCEDURE proc06(IN empno INT)
BEGIN
SELECT * FROM emp WHERE emp.empno = empno;
END $$
DELIMITER;
CALL proc06(1001);
-- 封装有参数的存储过程,可以通过传入部门名和薪资,查询指定部门,并且薪资大于指定的员工信息
DELIMITER $$
CREATE PROCEDURE proc07(IN param_dname VARCHAR(50),IN param_sal DECIMAL(7,2))
BEGIN
SELECT * FROM dept a,emp b WHERE a.deptno = b.deptno AND a.dname=param_dname AND b.sal >param_sal;
END $$
DELIMITER ;
CALL proc07('学工部',20000);
out
概念:out表示从存储过程内部传值给调用者
例如:
-- 封装有参数的存储过程,传入员工编号,返回员工名字
DELIMITER $$
CREATE PROCEDURE proc08(IN in_empno INT,OUT out_ename VARCHAR(50))
BEGIN
SELECT ename INTO out_ename FROM emp WHERE empno = in_empno;
END $$
DELIMITER;
CALL proc08(1001,@o_ename);
SELECT @o_ename;
-- 封装有参数的存储过程,传入员工编号,返回员工名字和薪资
DELIMITER $$
CREATE PROCEDURE proc09(IN in_empno INT,OUT out_ename VARCHAR(50),OUT out_sal DECIMAL(7,2))
BEGIN
SELECT ename,sal INTO out_ename,out_sal FROM emp WHERE empno = in_empno;
END $$
DELIMITER;
CALL proc09(1001,@o_ename,@o_sal);
SELECT @o_ename;
SELECT @o_sal;
inout
概念:inout表示从外部传入的参数经过修改后可以返回的变量,即可以使用传入变量的值也可以修改变量的值(即使函数执行完)
例如:
-- 传入一个数字,传出这个数字的10倍值
DELIMITER $$
CREATE PROCEDURE proc10(INOUT num INT)
BEGIN
SET num=num*10;
END $$
DELIMITER ;
SET @inout_num = 2;
CALL proc10(@inout_num);
SELECT @inout_num;
-- 传入员工名,拼接部门号,传入薪资,求出年薪
DELIMITER $$
CREATE PROCEDURE proc11(INOUT inout_ename VARCHAR(50),INOUT inout_sal INT)
BEGIN
SELECT concat_ws('_',deptno,ename) INTO inout_ename FROM emp WHERE emp.ename=inout_ename;
SET inout_sal = inout_sal *12;
END $$
DELIMITER ;
SET @inout_ename = '关羽';
SET @inout_sal = 3000;
CALL proc11(@inout_ename,@inout_sal);
SELECT @inout_ename;
SELECT @inout_sal;
流程控制
分支语句
if
IF语句包含多个判断条件,根据结果为TRUE、FALSE执行语句,与编程语言中的if、else if、else语法类似
方法:
IF search_condition_1 THEN statement_list_1
[ELSEIF search_condition_2 THEN statement_list_2]…
[ELSE statement_list_n]
END IF
例如:
-- 案例一:
-- 输入学生的成绩,来判断成绩的级别
/*
score < 60:不及格
score >= 60 ,score <8 0 :及格
score >= 80,score < 90 : 良好
score >= 90, score <= 100 :优秀
score > 100 : 成绩错误
*/
use mysql7_procedure;
CREATE PROCEDURE proc_12_if(IN score INT)
BEGIN
IF score < 60
THEN
SELECT '不及格';
ELSEIF score >=60 AND score < 80
THEN
SELECT '及格';
ELSEIF score >=80 AND score < 90
THEN
SELECT '良好';
ELSEIF score >=90 AND score <= 100
THEN
SELECT '优秀';
ELSE
SELECT '成绩错误';
END IF;
END;
SET @score = 55;
CALL proc_12_if(@score);
CALL proc_12_if(100);
-- 输入员工的名字,判断工资的情况。
/*
sal <10000:试用薪资
sal >=10000 and sal < 20000 :转正薪资
sal >= 20000 :元老薪资
*/
CREATE PROCEDURE proc_13_if(IN in_ename VARCHAR(20))
BEGIN
DECLARE var_sal DECIMAL(7,2);
DECLARE result VARCHAR(20);
SELECT sal INTO var_sal FROM emp WHERE ename = in_ename;
IF var_sal < 10000
THEN
SET result = '试用薪资';
ELSEIF var_sal < 20000
THEN
SET result = '转正薪资';
ELSE
SET result = '元老薪资';
END IF;
SELECT result;
END;
-- 调用
CALL proc_13_if('关羽');
CALL proc_13_if('程普');
case
case是另一个条件判断的语句,类似于变成语句中的switch语法
方法:
方法一
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]…
[ELSE statement_list]
END CASE
方法二
CASE
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]…
[ELSE statement_list]
END CASE
例如:
-- 案例一:
-- 输入学生的成绩,来判断成绩的级别
/*
score < 60:不及格
score >= 60 ,score <8 0 :及格
score >= 80,score < 90 : 良好
score >= 90, score <= 100 :优秀
score > 100 : 成绩错误
*/
use mysql7_procedure;
CREATE PROCEDURE proc_12_if(IN score INT)
BEGIN
IF score < 60
THEN
SELECT '不及格';
ELSEIF score >=60 AND score < 80
THEN
SELECT '及格';
ELSEIF score >=80 AND score < 90
THEN
SELECT '良好';
ELSEIF score >=90 AND score <= 100
THEN
SELECT '优秀';
ELSE
SELECT '成绩错误';
END IF;
END;
SET @score = 55;
CALL proc_12_if(@score);
CALL proc_12_if(100);
-- 输入员工的名字,判断工资的情况。
/*
sal <10000:试用薪资
sal >=10000 and sal < 20000 :转正薪资
sal >= 20000 :元老薪资
*/
CREATE PROCEDURE proc_13_if(IN in_ename VARCHAR(20))
BEGIN
DECLARE var_sal DECIMAL(7,2);
DECLARE result VARCHAR(20);
SELECT sal INTO var_sal FROM emp WHERE ename = in_ename;
IF var_sal < 10000
THEN
SET result = '试用薪资';
ELSEIF var_sal < 20000
THEN
SET result = '转正薪资';
ELSE
SET result = '元老薪资';
END IF;
SELECT result;
END;
-- 调用
CALL proc_13_if('关羽');
CALL proc_13_if('程普');
/*
支付方式:
1 微信支付
2 支付宝支付
3 银行卡支付
4 其他支付
*/
-- 格式1
DELIMITER $$
CREATE PROCEDURE proc14_case(IN pay_type INT)
BEGIN
CASE pay_type
WHEN 1 THEN
SELECT '微信支付';
WHEN 2 THEN
SELECT '支付宝支付';
WHEN 3 THEN
SELECT '银行卡支付';
ELSE
SELECT '其他支付';
END CASE;
END $$
delimiter ;
CALL proc14_case(2);
CALL proc14_case(4);
-- 格式2
CREATE PROCEDURE proc_15_case(IN score INT)
BEGIN
CASE
WHEN score < 60
THEN
SELECT '不及格';
WHEN score >=60 AND score < 80
THEN
SELECT '及格';
WHEN score >=80 AND score < 90
THEN
SELECT '良好';
WHEN score >=90 AND score <= 100
THEN
SELECT '优秀';
ELSE
SELECT '成绩错误';
END CASE;
END;
CALL proc_15_case(100);
CALL proc_15_case(120);
循环语句
概念:
- 循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
- 循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环。
循环分类:
- while
- repeat
- loop
循环控制:
- leave 类似于 break,跳出,结束当前所在的循环
- iterate类似于continue,继续,结束本次循环,继续下一次
while
方法:
[标签:]WHILE 循环条件 DO
循环体
END WHILE [标签];
例如:
-- 需求:向表中添加指定条数的数据
-- while
CREATE TABLE user(
uid INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
);
CREATE PROCEDURE proc16_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<= insertCount DO
INSERT INTO user(uid, username,password) VALUES (i,CONCAT('user-',i),'123456');
SET i=i+1;
END WHILE;
END;
CALL proc16_while(10);
-- 存储过程 - while + leave
TRUNCATE TABLE user;
CREATE PROCEDURE proc17_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
label:WHILE i<= insertCount DO
INSERT INTO user(uid,username,password) VALUES (i,CONCAT('user-',i),'123456');
IF i = 5 THEN
LEAVE label;
END IF;
SET i=i+1;
END WHILE label;
END;
CALL proc17_while(10);
-- 存储过程- while+iterate
CREATE TABLE user2(
uid INT ,
username VARCHAR(50),
password VARCHAR(50)
);
CREATE PROCEDURE proc18_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
label:WHILE i< insertCount DO
SET i=i+1;
IF i = 5 THEN
ITERATE label;
END IF;
INSERT INTO user2(uid,username,password) VALUES (i,CONCAT('user-',i),'123456');
END WHILE label;
END;
CALL proc18_while(10);
repeat
方法:
[标签:]repeat
循环体
until 条件表达式
end repeat [标签];
例如:
CREATE PROCEDURE proc18_repeat(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
label:REPEAT
INSERT INTO user(uid, username,password) VALUES (i,CONCAT('user-',i),'123456');
SET i=i+1;
UNTIL i > insertCount
END REPEAT label;
SELECT '循环结束';
END;
CALL proc18_repeat(100);
loop
方法:
[标签:] loop
循环体;
if 条件表达式 then
leave[标签];
end if;
end loop;
例如:
-- loop
TRUNCATE TABLE user;
CREATE PROCEDURE proc19_loop(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
label: LOOP
INSERT INTO user(uid,username,password) VALUES(i,CONCAT('user-',i),'123456');
SET i=i+1;
IF i > insertCount THEN
LEAVE label;
END IF;
END LOOP label;
END
CALL proc19_loop(10);
游标
概念:游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE。
方法:
声明语法
DECLARE cursor_name CURSOR FOR select_statement
打开语法
OPEN cursor_name
取值语法
FETCH curosor_name INTO var_name [,var_name]…
关闭语法
CLOSE cursor
例如:
-- 需求:输入一个部门名,查询该部门员工的编号、员工的名字、薪资
CREATE PROCEDURE proc19_cursor(IN in_dname VARCHAR(50))
BEGIN
-- 定义局部变量
DECLARE var_empno INT;
DECLARE var_ename VARCHAR(50);
DECLARE var_sal DECIMAL(7,2);
-- 声明游标
DECLARE my_cursor CURSOR FOR
SELECT empno,ename,sal
FROM dept a,emp b
WHERE a.deptno=b.deptno AND a.dname=in_dname;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取值
LOOP
FETCH my_cursor INTO var_empno,var_ename,var_sal;
SELECT var_empno,var_ename,var_sal;
END LOOP;
-- 关闭游标
CLOSE my_cursor;
END;
CALL proc19_cursor('销售部');
异常处理——句柄
MySQL存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现。
方法:
DECLARE handler_action HANDLER
FOR condition_value [,condition_value]…
statement
handler_action:{
CPMTOMIE
| EXIT
| UNDO
}
condition_value:{
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
例如:
-- 需求:输入一个部门名,查询该部门员工的编号、员工的名字、薪资,将查询的结果集添加游标。
CREATE PROCEDURE proc21_cursor_handler(IN in_dname VARCHAR(50))
BEGIN
-- 定义局部变量
DECLARE var_empno INT;
DECLARE var_ename VARCHAR(50);
DECLARE var_sal DECIMAL(7,2);
-- 定义标记值
DECLARE flag INT DEFAULT 1;
-- 声明游标
DECLARE my_cursor CURSOR FOR
SELECT empno,ename,sal
FROM dept a,emp b
WHERE a.deptno=b.deptno AND a.dname=in_dname;
-- 定义句柄:定义异常的处理方式
/*
1.异常树立完之后程序应该怎么执行
continue :继续执行剩余代码
exit:直接终止程序
undo:不支持
2.触发条件
条件码:
如:1329、1501等
条件名:
SQLWARNING
NOT FOUND
SQLEXCEPTION
3.异常触发之后执行什么代码
设置flag的值---》 0
*/
DECLARE CONTINUE HANDLER FOR 1329 SET flag=0;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取值
label:LOOP
FETCH my_cursor INTO var_empno,var_ename,var_sal;
-- 判断flag,如果flag的值为1,则执行,否则不执行
IF flag = 1 THEN
SELECT var_empno,var_ename,var_sal;
ELSE
LEAVE label;
END IF;
END LOOP;
-- 关闭游标
CLOSE my_cursor;
END;
CALL proc21_cursor_handler('销售部');
存储函数
方法:
CREATE FUNCTION func_name ([param_name TYPE[,…]])
RETURNS TYPE
[characteristic …]
BEGIN
routine_body
END;
参数说明:
- func_name:存储函数的名称
- param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以使MySQL数据库中所有支持的类型。
- RETURNS type:指定返回值的类型。
- characteristic:可选项,指定存储函数的特性。
- routine_body:SQL代码内容。
例如:
-- 创建一个数据库
CREATE DATABASE mydb9_function;
USE mydb9_function;
-- 允许创建函数权限信任
SET GLOBAL log_bin_trust_function_creators = TRUE ;
-- 创建一个存储函数 - 没有参数
DELIMITER $$
CREATE FUNCTION myfunc1_emp() RETURNS INT
BEGIN
-- 定义局部变量
DECLARE cnt INT DEFAULT 0;
SELECT COUNT(*) INTO cnt FROM emp;
RETURN cnt;
END $$
DELIMITER ;
-- 调用函数
SELECT myfunc1_emp();
-- 创建一个存储函数 - 有参数
-- 需求:传入一个员工的编号,返回员工的名字
DELIMITER $$
CREATE FUNCTION myfunc2_emp(in_empno INT) RETURNS VARCHAR(50)
BEGIN
DECLARE out_ename VARCHAR(50);
SELECT ename INTO out_ename FROM emp WHERE empno = in_empno;
RETURN out_ename;
END $$
DELIMITER ;
-- 调用存储函数
SELECT myfunc2_emp(1008);