mysql存储过程（递归，交叉表）

最新推荐文章于 2024-07-01 22:08:55 发布

fjfdszj

最新推荐文章于 2024-07-01 22:08:55 发布

阅读量1.9k

点赞数 1

CC 4.0 BY-SA版权

文章标签：存储 mysql recursion sql query null

本文链接：https://blog.csdn.net/fjfdszj/article/details/3915933

本文介绍两个SQL存储过程实例：一是递归查询机构及其下属的所有机构编号；二是创建交叉表实现数据从行列转换的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.递归

DELIMITER $$;

DROP PROCEDURE IF EXISTS `rentmgr`.`tmp`$$

/******************************************

** 参数说明：zjjg_id上级机构编号，result是输出变量

** 函数说明：递归返回本级机构和下级所有机构编号，通过","分隔

******************************************/

CREATE DEFINER=`root`@`localhost` PROCEDURE `tmp`(in ZGJG_ID char(16),out result varchar(1000))
BEGIN
    declare _DONE int default 0;
    declare JG_ID1 char(250);
    /*游标申明*/
    declare Cursor1 cursor for select JG_ID from JGXXB where SJJG_ID=ZGJG_ID;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _DONE = 1;
    SET @@max_sp_recursion_depth = 10;
    /*当为空值时返回本级结点*/
    if result is null then
        set result=ZGJG_ID;
    end if;
    /*打开游标*/
    open Cursor1;

     REPEAT
     fetch Cursor1 into JG_ID1;
     if NOT _DONE then
       /*

注意：如果想用result做为参数传入的话，存储过程不识别认为空值，这样递归就会失败！

       解决方案：采用就建一个临时变量的方式，取得数据后用连接方式。@符即是为临时存储使用不需要申明。
       set result=concat(result,',',JG_ID1);
       call tmp(JG_ID1,result);
      */
       call tmp(JG_ID1,@tmp1);
       set result=concat(result,',',@tmp1);
     end if;
     UNTIL _DONE END REPEAT;
    close Cursor1;
END$$

DELIMITER ;$$

2.交叉表

DELIMITER $$;

DROP PROCEDURE IF EXISTS `rentmgr`.`cross_tab`$$

/*********************************************

**********************************************/

CREATE DEFINER=`root`@`localhost` PROCEDURE `cross_tab`(vSourceTAB Varchar(2000),vGroupbyField Varchar(50),vTransFormCol Varchar(50),vPivotCol Varchar(50))
BEGIN
   declare vFunction Varchar(50)default "sum" ;/*求和函数*/
   declare StrSql Varchar(8000);                        /*求和sql*/
   declare done INT DEFAULT 0;                         /*游标判断*/
   declare StrSum Varchar(3000);                     /*求分组的总和*/
   Declare pCols Varchar(100);
   declare temp_NY varchar(10);

   /*申明游标*/
   declare cur1 Cursor for select distinct NY from query_area order by NY;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   Set StrSql ='';
   Set StrSum='';
   Set pCols='';
   set StrSql="select jg_id";/*select分组字段*/
   open cur1;
    repeat
    fetch cur1 into temp_NY;/*年份*/
   if not done then

          /*求年月份*/
          Set StrSql=concat(StrSql,",sum(case when NY='",temp_NY,"' then mzh else 0 end) as ",temp_NY);
          /*求年月份总和使用*/

          Set StrSum=concat(StrSum,"+case when a.",temp_NY," is null then 0 else a.",temp_NY," end");
        end if;
    UNTIL done END REPEAT;
    close cur1;

    /*内层sql求取各年份的值*/
    set StrSql=concat(StrSql," from query_area group by jg_id");
    set StrSql=concat("(",StrSql,")a");

     /*求年月份总和*/
    set StrSum=concat("(0",StrSum,") as Total");
    set StrSql=concat("select a.*,",StrSum," from ",StrSql);
    set @a=StrSql;

/*拼凑得到的可执行的sql语句*/
select @a;

    /*执行拼凑得到的可执行的sql语句*/
    PREPARE stmt1 FROM @a;
    EXECUTE stmt1 ;
END$$

DELIMITER ;$$