基础
- 使用存储过程有3个主要的好处,即简单、安全、高性能。
# 第一个存储过程
# 根据用户 id 查询用户其他信息
# 方法看着很奇葩,但是展示了多个输出参数
DELIMITER ;;
CREATE PROCEDURE `select_user_by_id`(
IN userId BIGINT,
OUT userName VARCHAR(50),
OUT userPassword VARCHAR(50),
OUT userEmail VARCHAR(50),
OUT userInfo TEXT,
OUT headImg BLOB,
OUT createTime DATETIME)
BEGIN
# 根据用户 id 查询其他数据
select user_name,user_password,user_email,user_info,head_img,create_time
INTO userName,userPassword,userEmail,userInfo,headImg,createTime
from sys_user
WHERE id = userId;
END
;;
DELIMITER ;
# 第二个存储过程
# 简单的根据用户名和分页参数进行查询,返回总数和分页数据
DELIMITER ;;
CREATE PROCEDURE `select_user_page`(
IN userName VARCHAR(50),
IN _offset BIGINT,
IN _limit BIGINT,
OUT total BIGINT)
BEGIN
# 查询数据总数
select count(*) INTO total
from sys_user
where user_name like concat('%', userName, '%');
# 分页查询数据
select *
from sys_user
where user_name like concat('%', userName, '%')
limit _offset, _limit;
END
;;
DELIMITER ;
# 第三个存储过程
# 保存用户信息和角色关联信息
DELIMITER ;;
CREATE PROCEDURE `insert_user_and_roles`(
OUT userId BIGINT,
IN userName VARCHAR(50),
IN userPassword VARCHAR(50),
IN userEmail VARCHAR(50),
IN userInfo TEXT,
IN headImg BLOB,
OUT createTime DATETIME,
IN roleIds VARCHAR(200)
)
BEGIN
# 设置当前时间
SET createTime = NOW();
# 插入数据
INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
VALUES (userName, userPassword, userEmail, userInfo, headImg, createTime);
# 获取自增主键
SELECT LAST_INSERT_ID() INTO userId;
# 保存用户和角色关系数据
SET roleIds = CONCAT(',',roleIds,',');
INSERT INTO sys_user_role(user_id, role_id)
select userId, id from sys_role
where INSTR(roleIds, CONCAT(',',id,',')) > 0;
END
;;
DELIMITER ;
# 第四个存储过程
# 删除用户信息和角色关联信息
DELIMITER ;;
CREATE PROCEDURE `delete_user_by_id`(IN userId BIGINT)
BEGIN
DELETE FROM sys_user_role where user_id = userId;
DELETE FROM sys_user where id = userId;
END
;;
DELIMITER ;
- BEGIN和END语句用来限定存储过程体。
- 除\符号外,任何字符都可以用作语句分隔符。
- DELIMITER 定义语句的结束符号。
- IN:在调用是需要传递一个参数。
- OUT:在调用是需要传递一个供以后调用的变量名,必须以@开始。
- 为了得到另一个订单的合计显示,需要再次调用存储过程,然后重新显示变量:
CALL ordertotal(20009, @total);
SELECT @total;
- 检查存储过程:
SHOW CREATE PROCEDURE ordertotal;
程序逻辑
- BOOLEAN值指定为1表示真,指定为0表示假(实际上,非零值都考虑为真,只有0被视为假)。
- 条件语句IF-THEN-ELSE:
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc(IN parameter int)
BEGIN
DECLARE var int;
SET var=parameter+1;
IF var=0 THEN
SELECT 0;
END IF ;
IF parameter=0 THEN
select "=0";
ELSE
select ">0";
END IF ;
END ;
;;
DELIMITER ;
call proc(-1);
- CASE-WHEN-THEN-ELSE语句
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc (IN param INT)
BEGIN
CASE param
WHEN 0 THEN
SELECT 0;
WHEN 1 THEN
SELECT 1;
ELSE
SELECT 1;
END CASE ;
END ;
;;
DELIMITER ;
CALL proc(1)
- 循环语句WHILE-DO…END-WHILE
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc()
BEGIN
DECLARE var INT;
SET var=0;
WHILE var<6 DO
SELECT var;
SET var=var+1;
END WHILE ;
END;
;;
DELIMITER ;
call proc();
- REPEAT…END REPEAT(此语句的特点是执行操作后检查结果)
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc ()
BEGIN
DECLARE v INT;
SET v=0;
REPEAT
SELECT v;
SET v=v+1;
UNTIL v>=5
END REPEAT;
END;
;;
DELIMITER ;
call proc();
- LOOP…END LOOP
DROP PROCEDURE IF EXISTS proc;
DELIMITER ;;
CREATE PROCEDURE proc ()
BEGIN
DECLARE v INT;
SET v=0;
LOOP_LABLE:LOOP
SELECT v;
SET v=v+1;
IF v >=5 THEN
LEAVE LOOP_LABLE;
END IF;
END LOOP;
END;
;;
DELIMITER ;
call proc();
应用(还没看)
游标遍历select结果
DROP TABLE IF EXISTS `tbl_job`;
CREATE TABLE `tbl_job` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`JOB_NAME` varchar(32) NOT NULL COMMENT '职位名称',
`OCCUPANT_ID` int(11) NOT NULL COMMENT '任职者',
`AGE` int(11) NOT NULL COMMENT '年龄',
PRIMARY KEY (`ID`)
);
-- ----------------------------
-- Records of tbl_job
-- ----------------------------
INSERT INTO `tbl_job` VALUES ('1', '经理', '1', '21'),
('2', '董事长', '2', '21'),
('3', '项目组长', '3', '22'),
('4', 'SE', '4', '24'),
('5', 'MDE', '5', '24');
DROP PROCEDURE IF EXISTS proc_tbl_job;
DELIMITER ;;
CREATE PROCEDURE proc_tbl_job(IN inId INT)
BEGIN
DECLARE id INT;
DECLARE jobName VARCHAR(32);
DECLARE occupantId INT;
DECLARE age INT;
DECLARE done INT DEFAULT FALSE;
DECLARE curJob CURSOR FOR ( -- 定义
SELECT ID,JOB_NAME,OCCUPANT_ID,AGE FROM tbl_job -- WHERE ID = inId
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器
OPEN curJob; -- 打开游标
label:LOOP
FETCH curJob INTO id,jobName,occupantId,age;
IF done THEN LEAVE label;END IF;
SELECT id,jobName,occupantId,age;
END LOOP label;
CLOSE curJob; -- 关闭游标
END ;;
delimiter;
CALL proc_tbl_job(2)
复杂嵌套
DROP PROCEDURE IF EXISTS proc_syn_single_blacklist;
DELIMITER ;;
CREATE PROCEDURE proc_syn_single_blacklist(IN var_license VARCHAR(10))
BEGIN
DECLARE var_calc_amount_owed INT DEFAULT 0;
DECLARE var_calc_paid_in_money INT DEFAULT 0;
DECLARE var_calc_arrears_count INT DEFAULT 0;
SELECT License ,IFNULL(SUM(ReceivablesMoney),0),IFNULL(SUM(PaidInMoney),0),IFNULL(COUNT(*),0)
into var_license, var_calc_amount_owed,var_calc_paid_in_money,var_calc_arrears_count
FROM urpcs_evasion_arrears WHERE license=var_license;
SELECT var_license,var_calc_amount_owed,var_calc_paid_in_money,var_calc_arrears_count;
END ;;
DELIMITER;
DROP PROCEDURE IF EXISTS proc_syn_blacklist;
DELIMITER ;;
CREATE PROCEDURE proc_syn_blacklist()
BEGIN
DECLARE var_license VARCHAR(10);
DECLARE done INT DEFAULT FALSE;
DECLARE curJob CURSOR FOR ( -- 定义
SELECT DISTINCT license FROM urpcs_evasion_arrears ORDER BY EvasionID LIMIT 2
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 监听器
OPEN curJob; -- 打开游标
label:LOOP
FETCH curJob INTO var_license;
IF done THEN LEAVE label;END IF;
call proc_syn_single_blacklist(var_license);
END LOOP label;
CLOSE curJob; -- 关闭游标
END ;;
DELIMITER;
CALL proc_syn_blacklist();
参考转载链接:
https://www.jianshu.com/p/4f728a575cce