关于mysql循环遍历和递归调用

关于mysql循环遍历和递归调用

需求背景 ：有两张表，报表和部门表，现在要根据报表中的部门id在部门表里查出所属大区id，写入报表里的相关字段，这个需求用Java写递归的话很容易，但是因为我平时mysql的函数用的少，这次想用mysql的自定义函数来玩玩。

下面是简化的表关系

-- 部门表
CREATE TABLE `net_org` (
`org_id`  varchar(32) NOT NULL COMMENT '部门id' ,
`org_type`  varchar(32) NULL COMMENT '部门类别' ,
`parent_id`  varchar(32) NULL COMMENT '上级id' ,
PRIMARY KEY (`org_id`)
);
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('001', '1', '-1');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('002', '2', '001');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('003', '3', '002');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('004', '2', '001');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('005', '3', '004');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('006', '1', '-1');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('007', '3', '004');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('008', '4', '007');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('009', '4', '003');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('010', '4', '005');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('011', '4', '003');
INSERT INTO `ssm_test`.`net_org` (`org_id`, `org_type`, `parent_id`) VALUES ('012', '4', '007');
-- 报表
CREATE TABLE `net_cost` (
`order_id`  varchar(32) NOT NULL COMMENT '订单id' ,
`receive_id`  varchar(32) NULL COMMENT '出发部门' ,
`send_id`  varchar(32) NULL COMMENT '到达部门' ,
`area_id`  varchar(32) NULL COMMENT '大区id' ,
PRIMARY KEY (`order_id`)
);
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0002', '003', '005', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0003', '003', '004', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0004', '011', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0005', '012', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0006', '010', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0007', '008', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0008', '009', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0009', '009', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0010', '001', '008', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0011', '002', '007', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0012', '003', '009', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0013', '004', '005', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0014', '005', '003', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0015', '006', '005', '');
INSERT INTO `ssm_test`.`net_cost` (`order_id`, `receive_id`, `send_id`, `area_id`) VALUES ('0016', '007', '010', '');

其中大区id是新添加的字段，部门表中 org_type是2的就是大区。我的思路是根据报表中的出发部门id，在部门表中查找对应的org_type和上级部门parent_id。然后再set进报表里。

-- 根据部门id查出大区id
DROP FUNCTION IF EXISTS query_pid;
DELIMITER //;		#这个是一种声明，不加就会报错，我在百度上找了好久才找到了这个原因
CREATE FUNCTION query_pid(oid VARCHAR(20))		#这里定义函数名，参数和返回值，可以根据实际情况改变
RETURNS VARCHAR(20)
BEGIN
 #暂时保存当前部门id
 DECLARE ooid VARCHAR(20);
 #当前部门的父级id
 DECLARE pid VARCHAR(20);
 #当前部门的机构类型
 DECLARE otype char(6);
 #根据当前org_id查出org_type和 parent_org_id
 SELECT org_type,parent_id,org_id INTO otype,pid,ooid FROM net_org WHERE org_id = oid;
 WHILE oid IS NOT NULL DO
  IF pid = '-1' THEN				#如果当前部门id对应的上级id是-1 ，就是当前部门已经是顶级部门 但是不属于大区 返回空字符串
   SET ooid = '';
   RETURN ooid;
  ELSEIF otype = '2' THEN 	#当前部门的类型是2，就是大区，直接返回当前部门的id
   RETURN ooid; 
  END IF;
  # 如果都不满足，就继续循环
  SELECT org_type,parent_id,org_id INTO otype,pid,ooid FROM net_org WHERE org_id = pid;
 END WHILE;
 RETURN ooid;
END ;

这是mysql的递归查询指定的上一级，我看网上都是查询所有上级，我应该是第一个写指定某一级的

-- 创建存储过程 修改报表对应大区
DROP PROCEDURE IF EXISTS find_tran;
CREATE PROCEDURE find_tran()
BEGIN
 DECLARE flag INT DEFAULT 0; #用来标识是否还有数据需遍历
 #创建临时变量存值
 DECLARE id VARCHAR(32);
 #查询出需要遍历的数据集合
 DECLARE rec_idList CURSOR FOR (-- 查询运输报表中大区id为空的所有出发部门id
SELECT receive_id FROM net_cost WHERE area_id = '' GROUP BY receive_id);
 #查询是否有下一个数据 没有将标识设为1 相当于hasNext
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
 #打开游标
 OPEN rec_idList;
  #取值设置到临时遍历中
  FETCH rec_idList INTO id;
  #游标不等于1 就一直循环
  WHILE flag <> 1 DO 
  	#普通的update语句   
   	UPDATE net_cost 
  	SET area_id = query_pid(id)
  	WHERE
  	receive_id = id;
   #不要忘记游标往后移一位
   FETCH rec_idList INTO id;
  END WHILE;
 CLOSE rec_idList;
END;

#执行存储过程
CALL find_tran();

基本就是这样，因为平时这种逻辑操作都是直接在Java里写，所以对mysql的函数比较陌生，为了这个需求在网上看了很多博客，结合起来才写完了这个需求，所以决定写篇博客记录一下，方便将来再用到的时候忘记了的话可以再看看，也希望能帮到和我一样的初学者。

重点： DELIMITER //; 不加这个的时候会报语法错误。