Java后端笔记 MySQL：存储过程

基础

• 使用存储过程有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