mysql级联查询存储过程实现

MySQL过程函数详解

最新推荐文章于 2022-11-06 11:36:25 发布

原创最新推荐文章于 2022-11-06 11:36:25 发布 · 124 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据库专栏收录该内容

13 篇文章

订阅专栏

本文介绍了三个MySQL中的过程函数：获取父进程ID、获取子进程列表及查找父进程ID的方法。这些函数通过递归查询和字符串操作实现了对进程间关系的有效管理。

1.获取父id

DROP FUNCTION IF EXISTS `getProParentId`;

CREATE DEFINER = `root`@`localhost` FUNCTION `getProParentId`(`proId` int,`ipNum` text)
RETURNS int(10)
BEGIN
   DECLARE sTemp int;
   DECLARE sTempPar int;
DECLARE puid int;
set sTemp=-1;
   SET sTempPar =proId;
set puid=-1;

   WHILE sTempPar !=-1 DO

       SELECT ppid ,uid into sTempPar, puid FROM process where   pid = sTempPar and ip = ipNum LIMIT 1;
       IF 0=puid THEN
           SET sTemp = sTempPar;
    end if;
   END WHILE;

RETURN sTemp;
END;

2.获取子id

DROP FUNCTION IF EXISTS `getProChildList`;

CREATE DEFINER = `root`@`localhost` FUNCTION `getProChildList`(ipNum text,proId INT)
RETURNS varchar(1000)
BEGIN
   DECLARE
       temp,
       tempChild,num VARCHAR (1000) ;
   SET temp = '$' ;
set num=ipNum;
SET tempChild =cast(proId as CHAR);
    WHILE tempChild is not null DO
        SET temp = concat(temp,',',tempChild);
        SELECT group_concat(pid) INTO tempChild FROM process where FIND_IN_SET(ppid,tempChild) > 0 and ip = ipNum;
    END WHILE;
    RETURN temp;
   END;

---------------------------------

DROP FUNCTION IF EXISTS `getParent`;

CREATE DEFINER = `root`@`localhost` FUNCTION `getParent`(`proId` int)
RETURNS int(11)
BEGIN

DECLARE var_id ,var_ppid,var_uid ,return_id int;
DECLARE var_ip VARCHAR(50);
DECLARE var_start DateTime;
set return_id=-1;
set var_id=-1;

select ppid,uid,started,ip INTO var_ppid,var_uid ,var_start,var_ip from process where id=proId;

   WHILE var_uid is not null and var_uid !=0 DO
    select id,ppid,uid,started INTO var_id,var_ppid,var_uid ,var_start from process where ip=var_ip AND
           started <=var_start   and endt >=var_start and pid=var_ppid ;
           set return_id=var_id;
   end while;
   if return_id is null then
       set return_id=-1;
    end if;
   RETURN return_id;
END;

-------------

3. 创建函数

delimiter //
-- 如果数据库 test 里的存在函数 formatDate，就删除这个函数
ROP FUNCTION IF EXISTS `getProChildList`;

CREATE DEFINER = `root`@`localhost` FUNCTION `getProChildList`(ipNum CHAR,proId INT)
RETURNS varchar(1000)
BEGIN
   DECLARE
       temp,
       tempChild VARCHAR (1000) ;
   SET temp = '$' ;
SET tempChild =cast(proId as CHAR);
    WHILE tempChild is not null DO
        SET temp = concat(temp,',',tempChild);
        SELECT group_concat(pid) INTO tempChild FROM process where FIND_IN_SET(ppid,tempChild) > 0 and ip=ipNum;
    END WHILE;
    RETURN temp;
   END;
//

delimiter ;