Mysql存储过程
说明 :
数据库用户名root,密码rootpwd,连接的是本地windows的mysql数据库
数据库版本, SELECT VERSION() , 8.0.15
数据库的引擎 : SHOW ENGINES
由上图可以看出,只有InnoDB是支持事务的
默认的数据库引擎 : SHOW VARIABLES LIKE 'default_storage_engine'
由上图可以看出,InnoDB是默认的数据引擎。
准备的数据表结构 :
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for trans_order
-- ----------------------------
DROP TABLE IF EXISTS `trans_order`;
CREATE TABLE `trans_order` (
`WAYBILL_NO` int(255) NOT NULL COMMENT '运单号',
`SEND_SITE_ID` int(11) NULL DEFAULT NULL COMMENT '寄件网点ID',
`SEND_SITE_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件网点代码',
`SEND_SITE_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件网点名称',
`SEND_DATE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件时间',
`ORDER_NO` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订单号',
`CUSTOMER_ID` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '客户ID',
`CUSTOMER_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户名称',
`CUSTOMER_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户代码',
`SEND_PHONE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件人联系电话',
`SEND_PROVINCE_CITY_DISTRICT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件人省市区',
`SEND_ADDRESS_DETAIL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件人详细地址',
`SEND_PERSON` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '寄件人',
`DISP_SITE_ID` int(255) NULL DEFAULT NULL COMMENT '目的网点ID',
`DISP_SITE_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '目的网点名称',
`DISP_SITE_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '目的网点代码',
`ACCEPT_PHONE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收件人电话',
`ACCEPT_ADDRESS` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收件人地址',
`ACCEPT_PROVINCE_CITY_DISTRICT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收件人省市区',
`ACCEPT_PERSON` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收件人',
`PRODUCT_ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '产品类型',
`WEIGHT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '重量',
`PAYMENT_TYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '支付类型',
`PAYMENT__DELIVERY_FEE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '到付费用',
`PAYMENT__NOW_FEE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '现付费用',
`INSURANCE_FEE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '保险费',
`GOODS_TYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '货物类型',
`REMARK` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
`PACK_TYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '包装方式',
PRIMARY KEY (`WAYBILL_NO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of trans_order
-- ----------------------------
INSERT INTO `trans_order` VALUES (8001, 1, '755A', '深圳龙岗坂街道', '20201216131522', '741000015819', 666, '阿聪', '888', '13828892565', '广东省|深圳市|龙岗区', '坂田街道禾坪岗', '阿聪', 9, '湖北武汉大学网点', '027C', '15889861946', '武汉大学', '湖北省|武汉市|武昌区', '阿朱', 'T66', '1.80', '现付', '0', '100.00', '10', '日用品', '零食和衣物', '0');
SET FOREIGN_KEY_CHECKS = 1;
参考文档 :
MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
2.1 什么是存储过程
2.1.1 概念
MySQL 5.0 版本开始支持存储过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
2.1.2 优点
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程可以回传值,并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
- 存储过程可以用在数据检验,强制实行商业逻辑等。
- 存储过程执行速度相对快一些,已经预编译好了。
2.1. 3缺点
- 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
- 存储过程的性能调校与撰写,受限于各种数据库系统。
2.2 存储过程的简单例子
2.2.1 创建存储过程和存储函数语句
官方说明 :
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
默认地 ,程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来,可以在创建子程序的时候指定其名字为db_name.sp_name。
如果子程序名和内建的SQL函数名一样,定义子程序时,你需要在这个名字和随后括号中间插入一个空格,否则发生语法错误。当你随后调用子程序的时候也要插入。为此,即使有可能出现这种情况,我们还是建议最好避免给你自己的 存储子程序取与存在的SQL函数一样的名字。
DEFINER
: CREATE PROCEDURE和CREATE FUNCTION需要CREATE ROUTINE特权。 如果存在DEFINER子句,则所需的特权取决于用户值,
括号内的参数列表必须始终存在。 如果没有参数,则应使用()的空参数列表。 参数名称不区分大小写。
每个参数 默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT
注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。(FUNCTION参数总是被认为是IN参数)
RETURNS字句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
routine_body :复合语句
[begin_label:] BEGIN
[statement_list]
END [end_label]
翻译成写能够使用的语法模板 :
delimiter //
CREATE
PROCEDURE sp_name ([proc_parameter[,...]])
BEGIN
[statement_list]
END//
delimiter;
delimiter //
CREATE
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
BEGIN
[statement_list]
END
delimiter;
1. 创建空参存储过程
delimiter //
CREATE
PROCEDURE query_trans_all ()
BEGIN
SELECT * FROM trans_order;
END//
delimiter;
调用存储过程
CALL query_trans_all;
实际上,与执行查询语句一样的结果 : SELECT * FROM trans_order; 但是可以看书查询时间存储过程比,查询语句更短;
复杂的逻辑可以通过多个语句构造
-- 将多个sql语句封装成一个存储过程 --
delimiter //
CREATE
PROCEDURE query_trans_all ()
BEGIN
[创建表结构]
[插入表数据]
[查询表数据]
....
END//
delimiter;
2.3 存储过程中的变量
2.3.1 存储过程入参变量
存储过程的参数类型有:IN,OUT,INOUT,下面分别介绍这个三种类型:
参数IN说明 :
- 传入参数,类型IN表示该入参在被调用的过程中必须传值,如果不显示指定类型,默认类型就是IN类型
- IN类型参数一般只用作传入,在调用过程中,一般不作为修改和返回
- 如果调用存储过程中需要修改和返回值,需要使用OUT类型参
实例 : 根据运单WAYBILL_NO查询,返回运单的ORDER_NO
-- 根据运单WAYBILL_NO查询,返回运单的ORDER_NO
delimiter //
CREATE
PROCEDURE query_trans_by_Id (IN wayNO int)
BEGIN
DECLARE orderno VARCHAR(32) DEFAULT ' ';
SELECT ORDER_NO INTO orderno FROM trans_order WHERE WAYBILL_NO=wayNO;
SELECT orderno;
END//
delimiter;
-- 调用
CALL query_trans_by_Id(8002);
结果 :
参数OUT说明 :
- 传出参数:在调用存储过程中,可以改变其值,并且可以返回
- OUT是传出参数,不能用于传入参数
- 调用存储过程时,out参数也需要指定,但必须是变量,不能是常量;
- 如果既需要传入,同时又需要传出,则可以使用INOUT参数类型
实例 : 根据运单WAYBILL_NO查询,返回运单的ORDER_NO
-- 根据运单WAYBILL_NO查询,返回运单的ORDER_NO
delimiter //
CREATE
PROCEDURE query_trans_by_Id_out (IN wayNO int,out orderno VARCHAR(32))
BEGIN
SELECT ORDER_NO INTO orderno FROM trans_order WHERE WAYBILL_NO=wayNO;
END//
delimiter;
-- 调用
SET @orderno='';
CALL query_trans_by_Id_out(8001,@orderno);
SELECT @orderno as orderno
结果:
参数INOUT说明 :
- 可变变量INOUT,调用时可传入值,在调用过程中,可修改其值,同时也可返回值
- INOUT参数:集合了IN和OUT参数类型的参数共而过
- INOUT调用时传入的值是变量而不是常量
实例 : 根据运单WAYBILL_NO查询,返回运单的ORDER_NO,WAYBILL_NO,SEND_PHONE
-- 根据运单WAYBILL_NO查询,返回运单的ORDER_NO,WAYBILL_NO,SEND_PHONE
delimiter //
CREATE
PROCEDURE query_trans_by_Id_INOUT (INOUT wayNO int,INOUT orderno VARCHAR(32),OUT sendphone VARCHAR(32))
BEGIN
SET orderno = '0000000';
SET sendphone = '12345678';
SELECT WAYBILL_NO,ORDER_NO,SEND_PHONE INTO wayNO,orderno,sendphone FROM trans_order WHERE WAYBILL_NO=wayNO;
END//
delimiter;
-- 调用
SET @wayNO = 8002;
SET @orderno='';
SET @sendphone='';
CALL query_trans_by_Id_INOUT(@wayNO,@orderno,@sendphone);
SELECT @wayNO,@orderno as orderno,@sendphone;
结果:
说明:CALL query_trans_by_Id_INOUT(8001,@orderno,@sendphone); 这种常量的写法调用会报错,此处必须要一个变量;
CALL query_trans_by_Id_INOUT(8001,@orderno,@sendphone)
> 1414 - OUT or INOUT argument 1 for routine ats.query_trans_by_Id_INOUT is not a variable or NEW pseudo-variable in BEFORE trigger
> 时间: 0.001s
2.3.2 存储过程的局部变量
变量的声明
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个DEFAULT子句。值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL。
说明 :
- 变量的声明使用declare,变量必须先声明后使用,位置在BEGIN...END内,BEGIN...END可以嵌套;
- 变量具有数据类型和长度,与mysql的SQL数据类型保持一致,可以设置默认值
- 变量可以通过set来赋值,也可以通过select into的方式赋值;
- 变量需要返回,可以使用select语句,如:select 变量名。
变量的赋值
- set 变量名 = 值
- select 字段1,字段2 into 变量1,变量2 from 表名 where 条件 ...
变量的作用域
作用域的说明
- 存储过程中变量是有作用域的,作用范围是在BEGIN和END块之间,END结束变量的作用范围即结束
- 需要多个块之间传值,可以使用全局变量,即放在所有代码块之前
- 传参变量是全局的,可以在多个块之间起作用
验证局部变量的作用域:
-- 变量的作用域验证
delimiter //
CREATE
PROCEDURE test ()
BEGIN
BEGIN
-- 返回trans_order的总记录数
DECLARE count int DEFAULT 0;
SELECT COUNT(*) INTO count FROM trans_order;
SELECT count;
END;
BEGIN
-- 返回trans_order中CUSTOMER_NAME是阿朱的记录数
DECLARE usercount int DEFAULT 0;
SELECT COUNT(*) INTO usercount FROM trans_order WHERE CUSTOMER_NAME = '阿朱' ;
SELECT usercount;
END;
END//
delimiter;
-- 调用
CALL test
结果 :
如果改一下,就会有问题 :
-- 变量的作用域验证
delimiter //
CREATE
PROCEDURE test2 ()
BEGIN
BEGIN
-- 返回trans_order的总记录数
DECLARE count int DEFAULT 0;
SELECT COUNT(*) INTO count FROM trans_order;
SELECT count;
END;
BEGIN
-- 返回trans_order中CUSTOMER_NAME是阿朱的记录数
DECLARE usercount int DEFAULT 0;
SELECT COUNT(*) INTO usercount FROM trans_order WHERE CUSTOMER_NAME = '阿朱' ;
SELECT usercount,count;
END;
END//
delimiter;
-- 调用
CALL test2()
创建的时候不会报错,调用的时候只有一个结果返回,第二结果没有返回;
将变量声明提前 :
-- 变量的作用域验证
delimiter //
CREATE
PROCEDURE test3 ()
BEGIN
-- 返回trans_order的总记录数
DECLARE count int DEFAULT 0;
BEGIN
SELECT COUNT(*) INTO count FROM trans_order;
SELECT count;
END;
BEGIN
-- 返回trans_order中
DECLARE usercount int DEFAULT 0;
SELECT COUNT(*) INTO usercount FROM trans_order WHERE CUSTOMER_NAME = '阿朱' ;
SELECT usercount,count;
END;
END//
delimiter;
-- 调用
CALL test3()
结果 :
说明 :存储过程中变量的作用域,作用范围在begin和end块之间,end结束变量的作用范围即结束
2.4 存储过程中流程控制
2.4.1 IF语句
IF语句结构
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
简单结构 :
IF (condition) THEN
statement_list
ELSE
statement_list
END IF
多条件判断:
IF (condition) THEN
statement_list
ELSEIF (condition) THEN
statement_list
ELSEIF (condition) THEN
statement_list
ELSE
statement_list
END IF
实例 : 通过ID查询,如果id是偶数返回名字,奇数返回电话号码;
-- 通过ID查询,如果id是偶数返回名字,奇数返回电话号码;
delimiter //
CREATE
PROCEDURE test4 (IN wayNO int)
BEGIN
DECLARE username VARCHAR(32) DEFAULT ' ';
DECLARE phone VARCHAR(32) DEFAULT ' ';
IF(wayNO%2=0) THEN
SELECT CUSTOMER_NAME INTO username FROM trans_order WHERE WAYBILL_NO = wayNO;
SELECT username;
ELSE
SELECT SEND_PHONE INTO phone FROM trans_order WHERE WAYBILL_NO = wayNO;
SELECT phone;
END IF;
END//
delimiter;
执行结果: (数据库表里面就两条数据)
2.4.2 WHILE语句
WHILE语句结构
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
简单结构 :
WHILE (condition) DO
statement_list
END WHILE
实例 : 表中增加n条数据
-- 插入n条记录
delimiter //
CREATE
PROCEDURE test5 (num int)
BEGIN
DECLARE n int DEFAULT 0;
DECLARE wayNO int DEFAULT 0;
WHILE (n < num) DO
SET wayNO = n;
INSERT INTO `trans_order` VALUES (wayNO, 1, '755A', '深圳龙岗坂街道', '20201216131522', '741000015819', 666, '阿聪', '888', '13828892565', '广东省|深圳市|龙岗区', '坂田街道禾坪岗', '阿聪', 9, '湖北武汉大学网点', '027C', '15889861946', '武汉大学', '湖北省|武汉市|武昌区', '阿朱', 'T66', '1.80', '现付', '0', '100.00', '10', '日用品', '零食和衣物', '0');
SET n = n+1;
END WHILE;
SELECT n;
END//
delimiter;
执行结果 :
原来有2条数据,现在有1002条
2.4.3 REPEAT语句
REPEAT语句结构
REPEAT
statement_list
UNTIL search_condition
END REPEAT
search_condition 为真就退出循环
实例 : 循环插入数据,原来的id已经到999了这里从1000开始增加
-- 插入n条记录
delimiter //
CREATE
PROCEDURE test6 (num int)
BEGIN
DECLARE n int DEFAULT 0;
DECLARE wayNO int DEFAULT 1000;
REPEAT
SET wayNO = wayNO + n;
INSERT INTO `trans_order` VALUES (wayNO, 1, '755A', '深圳龙岗坂街道', '20201216131522', '741000015819', 666, '阿聪', '888', '13828892565', '广东省|深圳市|龙岗区', '坂田街道禾坪岗', '阿聪', 9, '湖北武汉大学网点', '027C', '15889861946', '武汉大学', '湖北省|武汉市|武昌区', '阿朱', 'T66', '1.80', '现付', '0', '100.00', '10', '日用品', '零食和衣物', '0');
SET n = n+1;
SET wayNO = 1000;
UNTIL n >= num
END REPEAT;
SELECT n;
END//
delimiter;
CALL test6(2000);
结果 :
增加了2000条数据,id从1000到2999
2.4.4 LOOP语句
LOOP语句结构
label: LOOP
statement_list
IF exit_condition THEN
LEAVE label;
END IF;
END LOOP label;
LEAVE label 表示退出loop循环
实例 : 循环插入1000条数据
delimiter //
CREATE
PROCEDURE test7 ()
BEGIN
DECLARE wayNO int DEFAULT 3000;
label: LOOP
INSERT INTO `trans_order` VALUES (wayNO, 1, '755A', '深圳龙岗坂街道', '20201216131522', '741000015819', 666, '阿聪', '888', '13828892565', '广东省|深圳市|龙岗区', '坂田街道禾坪岗', '阿聪', 9, '湖北武汉大学网点', '027C', '15889861946', '武汉大学', '湖北省|武汉市|武昌区', '阿朱', 'T66', '1.80', '现付', '0', '100.00', '10', '日用品', '零食和衣物', '0');
SET wayNO = wayNO + 1;
IF wayNO >= 3999 THEN
LEAVE label;
END IF;
END LOOP label;
SELECT wayNO;
END//
delimiter;
结果 :
增加了1000条数据;
2.4.5 CASE语句
CASE语法结构
case ...
when ... then....
when.... then....
else ...
end case;
类似其他语言的switch-case语法,这里复制别人写的例子做一个说明,应该很好懂!
create procedure testcate(userid int)
begin
declare my_status int default 0;
select status into my_status from users where id=userid;
case my_status
when 1 then update users set score=10 where id=userid;
when 2 then update users set score=20 where id=userid;
when 3 then update users set score=30 where id=userid;
else update users set score=40 where id=userid;
end case;
end;
2.5 存储过程的操作
2.5.1 修改、删除、查看
修改
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
删除
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存储,它防止发生错误。
查看
SHOW CREATE {PROCEDURE | FUNCTION} sp_name 查看单个
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'] 查看存储过程或函数的创建者,创建时间等属性
2.6 自定义函数
定义函数语法结构
delimiter //
CREATE
FUNCTION sp_name ([func_parameter[,...]]) RETURNS type
[statement_list]
BEGIN
[statement_list]
END
delimiter;
说明 :
- 创建函数必须指定返回值类型,入参数默认都是IN类型
- 函数体放在BEGIN和END之间
- return指定函数的返回值
- 函数的调用使用 select 函数名()
实例 : 通过ID查询姓名
delimiter //
CREATE
FUNCTION getUsername (id int) RETURNS VARCHAR(32)
READS SQL DATA
BEGIN
DECLARE id_username VARCHAR(32) DEFAULT ' ';
SELECT CONCAT(CUSTOMER_NAME,'_',id) INTO id_username FROM trans_order WHERE WAYBILL_NO = id;
RETURN id_username;
END//
delimiter;
结果 :