Mysql 存储过程、存储函数与递归查询

深碍√TFBOYSˉ_ 2023-05-31 12:02 5阅读 0赞

**目录**

存储过程概述 与 创建

存储过程参数

存储过程变量

流程控制语句

select into 查询并赋值

存储函数 VS 存储过程

自定义函数实现递归查询

递归查询·数据准备

自定义函数·向下递归查询

自定义函数实现·向上递归查询

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

数据准备：

DROP TABLE IF EXISTS `book_2`;
    CREATE TABLE `book_2` (
      `id` int(11) PRIMARY KEY auto_increment,
      `title` varchar(128) NOT NULL,
      `price` float(8,2) DEFAULT NULL COMMENT '价格',
      `publish` date DEFAULT NULL COMMENT '发布时间',
      `info` varchar(128) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of book_2
    -- ----------------------------
    INSERT INTO `book_2`(title,price,publish,info) VALUES ('三国演义', '88.68', '2019-09-08', '三国混战');
    INSERT INTO `book_2`(title,price,publish,info) VALUES ('红楼梦', '48.35', '2019-03-08', '一个家族的兴衰');

# 存储过程概述 与 创建 #

1、MySQL 5.0 版本开始支持存储过程，存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

3、存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

**存储过程创建**

1、创建的存储过程会保存在数据库的数据字典中。

<table> 
 <tbody> 
  <tr> 
   <td> <p>-- <strong><span style="color:#f33b45;">创建存储过程</span>基本语法</strong>，没有参数也必须有括号</p> <p>delimiter $$<br> create procedure 存储过程名(in | out | inout 参数1 数据类型,in | out | inout 参数2 数据类型,…)<br> begin<br> &nbsp;&nbsp; declare 变量名&nbsp; 类型;<br> &nbsp;&nbsp; 执行语句;<br> end $$</p> <p>delimiter ;&nbsp; #将语句的结束符号恢复为分号</p> </td> 
  </tr> 
  <tr> 
   <td>call 存储过程名称[(传参)]; -- <span style="color:#f33b45;">调用存储过程</span></td> 
  </tr> 
  <tr> 
   <td> <p>DROP PROCEDURE 存储过程名称;&nbsp; -- MySQL <span style="color:#f33b45;">删除存储过程</span>：</p> </td> 
  </tr> 
  <tr> 
   <td>drop procedure if exists 存储过程名称; # <span style="color:#f33b45;">如果存在存储过程，则删除，否则不做处理</span></td> 
  </tr> 
  <tr> 
   <td>select * from information_schema.routines; #<span style="color:#f33b45;">查看所有数据库下的所有存储过程以及函数</span>，可以根据 routine_schema 字段（数据库名称）过虑<br> select * from mysql.proc ; # <span style="color:#f33b45;">查看所有数据库下的所有存储过程以及函数,</span>可以根据 db 字段（数据库名称）过虑<br> SHOW PROCEDURE STATUS; #<span style="color:#f33b45;">查询所有数据库下的所有存储过程</span>，可以根据 db 字段（数据库名称）过虑<br> SHOW FUNCTION STATUS; #<span style="color:#f33b45;">查询所有数据库下的所有存储函数</span>，可以根据 db 字段（数据库名称）过虑</td> 
  </tr> 
  <tr> 
   <td> <p><span style="color:#f33b45;">查询存储过程创建 sql</span>：SHOW CREATE PROCEDURE 数据库名.存储过程名; #数据库名可以省略</p> <p>SHOW CREATE PROCEDURE test.param1; #查询 test 数据库下的 param1 存储过程的创建 sql<br> SHOW CREATE PROCEDURE param2; # 直接查询某个存储过程的创建 sql</p> <p><span style="color:#f33b45;">查询存储函数创建 sql：</span>SHOW CREATE FUNCTION 函数名称;</p> </td> 
  </tr> 
 </tbody> 
</table>

![202009061412348.gif][]

2、**MYSQL 存储过程中的关键语法**与入门示例：

<table> 
 <thead> 
  <tr> 
   <th>用途</th> 
   <th>语法格式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>声明语句结束符（可以自定义）</td> 
   <td>DELIMITER $$ 或 DELIMITER //</td> 
  </tr> 
  <tr> 
   <td>声明存储过程</td> 
   <td>create procedure 存储过程名(参数)，<span style="color:#f33b45;">即使没有参数，也必须有括号</span></td> 
  </tr> 
  <tr> 
   <td>声明存储函数</td> 
   <td>create function 存储函数名(参数)</td> 
  </tr> 
  <tr> 
   <td>存储过程开始和结束符号</td> 
   <td>BEGIN .... END</td> 
  </tr> 
  <tr> 
   <td>变量定义</td> 
   <td>DECLARE l_int int unsigned default 4000000;</td> 
  </tr> 
  <tr> 
   <td>变量赋值</td> 
   <td>SET @p_in=1</td> 
  </tr> 
 </tbody> 
</table>

drop procedure if exists hello_world; -- 如果存在指定的存储过程，则删除
    #将sql语句的结束符由默认的分号";"临时改为"$$"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理
    delimiter $$
    create procedure hello_world(in id int) # 声明存储过程
    begin  # 开始
    	SELECT id from dual; -- SELECT 语句会把结果查询出来
    end $$ # 结束
    delimiter ;  #将语句的结束符号恢复为分号
    
    call hello_world(1112); -- 调用存储过程

![20200906135253980.gif][]

3、**存储过程体**：存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl 语句，if-then-else 和 while-do 语句、声明变量的 declare 语句等。过程体格式：以 begin 开始，以 end 结束(可嵌套)

begin
    　　begin
    　　　　begin
    　　　　　　statements; 
    　　　　end
    　　end
    end

每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的 begin-end 块(又叫做复合语句compound statement)，则不需要分号。

# 存储过程参数 #

1、MySQL存储过程的参数共有三种参数类型 IN,OUT,INOUT，形式如：CREATE PROCEDURE 存储过程名(\[\[IN |OUT |INOUT \] 参数名 数据类形...\])

<table> 
 <tbody> 
  <tr> 
   <td>IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）<br> OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）<br> INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）</td> 
  </tr> 
 </tbody> 
</table>

**IN 输入参数举例**

# 如果存在存储过程 param1 则删除
    drop procedure if exists param1;
    
    #创建一个存储过程设置不同类型的传入参数。将语句结束符临时修改为 //
    delimiter //
    create procedure param1(in id int,in name varchar(16),in birthday date,in price float(6,2))
    begin  # 开始
    set id = 33; # 可以设置传入变量的值
    select id,name,birthday,price from dual;
    end // #结束
    delimiter ;
    
    call param1(1010,'张三','2019/08/08',88.98); # 调用存储过程，传入参数

**OUT 输出参数举例**

# 如果存在存储过程 param2 则删除
    drop procedure if exists param2;
    
    # 创建一个存储过程定义输入、输出参数，将语句结束符临时修改为 //
    delimiter //
    create procedure param2(in sal float(11,2),out yearly_salary float(11,2))
    begin  # 开始
    set yearly_salary = sal * 12; #改变了变量的值
    select yearly_salary from dual;
    end // #结束
    delimiter ;
    
    # 调用存储过程：输出参数也需要传值，且必须是变量，不能是常量
    set @yearly_salary = 0; #定义一个全局变量
    call param2(8500,@yearly_salary); # 调用存储过程，输出参数使用变量传入
    
    select @yearly_salary as '年薪'; # 也可以直接获取全局变量的值

**inout 输入输出参数举例**

# 如果存在存储过程 param3 则删除
    drop procedure if exists param3;
    
    #创建一个存储过程，定义输入输出参数，既能当输入参数，也能当输出参数。将语句结束符临时修改为 //
    delimiter //
    create procedure param3(inout sal float(11,2))
    begin  # 开始
    set sal = sal * 12; #改变了变量的值
    select sal as '年薪' from dual;
    end // #结束
    delimiter ;
    
    # 调用存储过程，同理参数也必须是变量，不能是常量
    set @sal = 8500; #定义一个全局变量 @sal
    call param3(@sal); # 调用存储过程

**注意事项**

<table> 
 <tbody> 
  <tr> 
   <td>1）<span style="color:#f33b45;">即使存储过程没有参数，也必须在过程名后面写上小括号，否则会报错</span><br> 2）确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理<br> 3）输入值使用 in 参数；返回值使用 out 参数；inout 参数尽量少用</td> 
  </tr> 
 </tbody> 
</table>

# 存储过程变量 #

1、**局部变量**声明一定要放在存储过程体的开始：

<table> 
 <tbody> 
  <tr> 
   <td> <p>格式：DECLARE variable_name [,variable_name...] <span style="color:#f33b45;">datatype </span>[DEFAULT value];</p> <p><span style="color:#f33b45;">datatype </span>为 MySQL 的数据类型，如: int, float, date,varchar(length) 等</p> </td> 
  </tr> 
 </tbody> 
</table>

declare v_int int unsigned default 4000000; # 变量名称建议以 'v_' 开头  
    declare v_numeric number(8,2) default 9.95;  
    declare v_date date default '1999-12-31';  
    declare v_datetime datetime default '1999-12-31 23:59:59';  
    declare v_varchar varchar(255) default 'this will not be padded';

2、**变量赋值**：SET 变量名 = 表达式值 \[,variable\_name = expression ...\]

4、局部变量在其作用域范围内享有更高的优先权，当执行到 end 时，内部变量消失，此时已经在其作用域外，变量不再可见了。可以通过 out 参数或者将其值指派给会话变量来保存其值。

drop procedure if exists variableAccess; -- 如果已经存在此存储过程，则删除
    
    -- 临时改变语句接收符
    delimiter //  
    create procedure variableAccess()   -- 创建存储过程,即使没有参数，也必须有括号
    begin
        declare x varchar(8) default 'outer'; -- 定义局部变量，设置默认值
      begin -- begin-end 可以嵌套，变量有作用访问，类似 js
            declare x varchar(8) default 'inner';
            set x = '里面';
            select x from dual;
      end;
        select x from dual;
    end //
    delimiter ;
    
    call variableAccess(); #调用存储过程，第一个结果为：'里面'、第二个结果为：'outer'

![20200906135712274.gif][]

5、**用户变量：**在 MySQL 客户端使用用户变量，如：

-- 如下所示都是用户变量
    set @x = '中华';
    select '名族' into @y;
    set @z = 3.14159;
    set @m = null;
    set @n = '';
    
    -- 会话结束后，这些变量也就不存在了。比如关掉客户端工具，重连之后再取值就没有了
    select @x as x,@y as y,@z as z,@m as m,@n as n from dual;

**用户变量举例**

set @name = '张无忌'; # 定义用户变量
    drop procedure if exists  userVariables; -- 如果已经存在此存储过程，则删除
    
    delimiter //
    create procedure userVariables(in id int)
    begin
    set @name = concat(concat(id,'_'),@name); #为变量赋值，存储过程中可以直接调用用户变量
    select id,@name as '姓名' from dual;
    end // 
    delimiter ;
    
    call userVariables(1001); # 调用存储过程

![20200906140152132.gif][]

6、注意事项:

<table> 
 <tbody> 
  <tr> 
   <td>1、用户变量名虽然可以自定义，但是推荐以 @ 开头<br> 2、滥用用户变量会导致程序难以理解及管理，需要慎用</td> 
  </tr> 
 </tbody> 
</table>

# 流程控制语句 #

** if-then-else 条件语句**

<table> 
 <tbody> 
  <tr> 
   <td>简单的条件判断: if – then<br> 二重条件分支: if—then—else<br> 多重条件分支：if—then—elsif—else</td> 
  </tr> 
 </tbody> 
</table>

drop procedure if exists param6;
    
    delimiter //
    create procedure param6(in flag int)
    begin
    
    if flag < 18 then
    select '未成年' as result from dual;
    else
    select '已成年' as result from dual;
    end if;
    
    if flag < 20 then
    select '少年' as result from dual;
    elseif flag < 50 then
    select '壮年' as result from dual;
    else
    select '老年' as result from dual;
    end if;
    
    end //
    delimiter ;
    
    call param6(17); # 输出 '未成年'、'少年'
    call param6(48);  # 输出 '已成年'、'壮年'

**when - case 流程语句**

drop procedure if exists param7;
    
    delimiter //
    create procedure param7(in flag varchar(16))
    begin
    
    case flag
    when 'add' then
     select '新增' as flag from dual;
    when 'update' then
     select '修改' as flag from dual;
    when 'delete' then
     select '删除' as flag from dual;
    else
     select '查询'  as flag from dual;
    end case;
    end //
    delimiter ;
    
    call param7('add');
    call param7('delete');
    call param7('xx');

**while - do 循环语句**：格式：while 条件 do 循环体 end while;

drop procedure if exists param8;
    
    delimiter //
    create procedure param8()
    begin
    declare v_count int default 10;
    while v_count > 0 do
        insert into book_2(title,price,publish,info) 
        values(concat('仙剑奇侠传',v_count),88.45,'2018/08/08','新书上市');
        set v_count = v_count -1;
    end while;
    end //
    delimiter ;
    
    call param8(); -- 调用存储过程

**loop 循环语句：**loop 循环不需要初始条件，使用 leave 关键字离开循环。

drop procedure if exists param9;
    
    delimiter //
    create procedure param9()
    begin
    declare v_count int;
    set v_count = 5;
    loop_label:loop
        insert into book_2(title,price,publish,info) 
        values(concat('速度与激情',v_count),108.42,'2019/08/08','新书上市');
        set v_count = v_count -1;
        if v_count <=0 then
            leave loop_label;
        end if;
    end loop;
    end //
    delimiter ;
    
    call param9(); -- 调用存储过程

# select into 查询并赋值 #

1、select . into . 查询并赋值数据只能对单个查询结果进行处理，如果 select 查询的结果是多条，则可以借助游标进行遍历。

2、如下所示就是最简单的将单个查询结果，赋值给变量。

drop procedure if exists hello_world2; -- 如果存在指定的存储过程，则删除
    #将sql语句的结束符由默认的分号";"临时改为"$$"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理
    delimiter $$
    create procedure hello_world2(in p_deptno int) # 声明存储过程
    begin  # 开始
    	DECLARE v_names text DEFAULT '-1'; -- 定义局部变量
    	DECLARE v_sal text DEFAULT '-1'; -- 定义局部变量
    	SELECT ename,sal INTO v_names,v_sal from emp t where t.deptno = p_deptno ORDER BY sal DESC LIMIT 1; -- 查询并赋值
    	SELECT v_names,v_sal from dual; -- SELECT 语句会把结果查询出来
    end $$ # 结束
    delimiter ;  #将语句的结束符号恢复为分号
    
    call hello_world2(2); -- 调用存储过程

**2、特别注意：select c1,c2... into v1,v2... where ... 为变量赋值时，如果 where 条件没有查询到值，则变量仍然是自己的默认值或者是上一次的被赋的旧值，而并不会 为null，所以千万不能用这里赋值的变量来充当循环的条件，否则很容易死循环。**

![20200906144441233.gif][]

# 存储函数 VS 存储过程 #

1、存储函数也可以叫自定义函数，比如 mysql 会自带一些函数，如 sun、abs、concat 等等，同时用户也可以自定义函数。

<table> 
 <tbody> 
  <tr> 
   <td>-- <strong><span style="color:#f33b45;">创建存储函数</span>基本语法</strong>，没有参数也必须有括号 <p>delimiter $$<br> create <strong>function </strong>存储函数名(参数1 数据类型,参数2 数据类型,…)&nbsp; returns type<br> begin<br> &nbsp;&nbsp; declare 变量名&nbsp; 类型;<br> &nbsp;&nbsp; 执行语句;<br> end $$</p> <p>delimiter ;&nbsp; #将语句的结束符号恢复为分号</p> </td> 
  </tr> 
 </tbody> 
</table>

drop function if exists fun_v1; -- 如果已经存储此函数，则删除
    
    delimiter $$ -- 临时改变 sql 语句结束符
    create function fun_v1(p_title VARCHAR(56)) returns VARCHAR(56) -- 创建存储函数，必须指定返回值
    begin
    	 DECLARE v_titles VARCHAR(56) DEFAULT ''; -- 定义局部变量
    	  -- 查询赋值，因为只是演示，所以只做简单的查询，结果不能多余条，所以用 limit 1 限制最多取一条
       SELECT title into v_titles FROM book_2 where title = p_title LIMIT 1;
    	 return v_titles; -- 必须返回结果，且只能是变量，不能是常量，比如 return '111'，则报错
    end $$ -- 结束
    delimiter ;  #将语句的结束符号恢复为分号
    
    SELECT fun_v1('三国演义'),count(1) from dual; -- 调出存储函数，就如同调用 mysql 自身的函数一样

![20200906152811647.gif][]

2、整体来说存储过程与存储函数使用起来基本差不多，其不同的主要如下：

<table> 
 <tbody> 
  <tr> 
   <td>1、名称关键字不同：存储过程是 <strong>procedure </strong>关键字<strong>；</strong>存储函数是 <strong>function </strong>关键字<strong>;</strong></td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>2、参数定义不同：存储过程可以通过 in、out、inout 关键字指定输入输出参数；存储函数的参数默认为输入参数，不用且不能用 in 关键字。</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td> <p>3、返回值不同：存储函数必须有返回值，开头使用 returns type 指明返回的数据类型，函数体中用 return 返回具体的值；存储过程可以没有返回值，也可以通过输出参数返回结果(out,inout)。</p> </td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>4、调用方式不同：存储函数可以在 sql 语句中直接调用；存储过程中无法在&nbsp;sql 中直接调用，需要通过 call 调用。</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td> <p>5、存储过程中可以调用存储函数，存储函数中无法调用存储过程。</p> </td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

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

# 自定义函数实现递归查询 #

1、[递归查询·需求分析][Link 1]

2、[Oracle 有自带的函数 start with 可以轻松实现递归查询，][Oracle _ start with]而  Mysql 则需要特殊处理才能实现相同的效果，比如借助存储过程或者存储函数，本文使用存储函数，因为 存储函数可以在 sql 语句中直接调用，而且 存储函数可以使用 return 指定返回值。

![20200905153819533.gif][]

## 递归查询·数据准备 ##

--  Table structure for region
    DROP TABLE IF EXISTS `region`;
    CREATE TABLE `region` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `name` varchar(56) COLLATE utf8_bin NOT NULL COMMENT '显示名称',
      `pId` int(11) NOT NULL COMMENT '父节点id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    -- 添加1级菜单
    insert into region(id, name, pId) values(1, '湖南省',0);-- 顶级菜单没有父菜单，所以 pId 为 0
    insert into region(id, name, pId) values(2, '广东省',0);
     
    -- 添加2级菜单
    insert into region(id, name, pId) values(3, '长沙市',1);
    insert into region(id, name, pId) values(4, '娄底市',1);
    insert into region(id, name, pId) values(5, '深圳市',2);
     
    -- 添加3级菜单
    insert into region(id, name, pId) values(6, '芙蓉区',3);
    insert into region(id, name, pId) values(7, '天心区',3);
    insert into region(id, name, pId) values(8, '岳麓区',3);
    insert into region(id, name, pId) values(9, '开福区',3);
    insert into region(id, name, pId) values(10, '雨花区',3);
    insert into region(id, name, pId) values(11, '望城区',3);
     
    insert into region(id, name, pId) values(12, '娄星区',4);   
    insert into region(id, name, pId) values(13, '冷水江市',4); 
    insert into region(id, name, pId) values(14, '涟源市',4); 
    insert into region(id, name, pId) values(15, '双峰县',4);
    insert into region(id, name, pId) values(16, '新化县',4);
     
    insert into region(id, name, pId) values(17, '罗湖区',5);      
    insert into region(id, name, pId) values(18, '福田区',5);
    insert into region(id, name, pId) values(19, '南山区',5);
    insert into region(id, name, pId) values(20, '宝安区',5);
    insert into region(id, name, pId) values(21, '坪山区',5);
    insert into region(id, name, pId) values(22, '龙岗区',5);  
     
    -- 添加4级菜单 
    insert into region(id, name, pId) values(23, '白溪镇',16);  
    insert into region(id, name, pId) values(24, '洋溪镇',16);  
    insert into region(id, name, pId) values(25, '吉庆镇',16);  
    insert into region(id, name, pId) values(26, '曹家镇',16);

## 自定义函数·向下递归查询 ##

1、这里需要用到 Mysql 的几个函数：[find\_in\_set 查询函数、][find_in_set][CONCAT & CONCAT\_WS 连接函数、][CONCAT _ CONCAT_WS][group\_concat 分组排序连接][group_concat]

2、存储函数可以在 sql 语句中直接调用，而且可以使用 return 指定返回值，所以如下所示新建一个 存储函数：通过传入根节点id，查询它本身及其下的的所有子孙节点。

-- 将sql语句的结束符由默认的分号";"临时改为"$$"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理
    delimiter $$ 
    -- 如果已经存在，则先删除掉
    -- 注意这里需要用 当前自定义的结束符 $$ 来结束并执行语句，因为本句需要和下边的函数体区分开来执行
    drop function if exists recursionDown $$ 
    -- 创建存储函数,声明参数,同时指定 返回值参数类型
    create function recursionDown(in_id INT) returns text 
    -- begin 和 end 中间包围的是函数体，用来写具体的逻辑
    begin 
    	-- declare 用于声明变量，可以用 default 设置默认值
    	-- 为防止数据量大，拼接的字符串太长，所以使用 text 大文本数据类型
    	declare return_ids text default ''; 	-- 最终返回的字符串
    	declare temp_ids text; -- 每次循环临时遍历的结果字符串
    	declare total INT DEFAULT 1; -- 每次查询到数据个数
     
     set temp_ids = in_id;  -- set 用于为变量赋值 
     while total > 0  do  -- -- 查询有结果时，才继续循环
    	-- 用 CONCAT_WS 函数把最终结果 和 临时生成的值 用逗号拼接起来
    	-- 设置拼接的 id 列表字符串，因为第一次时 return_ids 值为空，所以开头会有一个 ","，后期去掉即可
      set return_ids = CONCAT_WS(',', return_ids, temp_ids);
      -- 查询数据库并赋值
      -- 以 FIND_IN_SET(pid,tempids)>0 为条件，遍历在 temp_ids 中的所有 pid ，寻找以此为父节点的所有子节点 id
      -- 通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来，并覆盖更新 temp_ids 
      select GROUP_CONCAT(id),count(*) into temp_ids,total from region where FIND_IN_SET(pId, temp_ids);  
     end while; 
    -- 对返回的id字符串从第2位开始截取，舍弃开头的 "," 符号, LENGTH 函数的索引从 1 开始
    set return_ids = SUBSTR(return_ids, 2);
    -- 返回最终的数据
     return return_ids; 
    end  
    $$ 
    delimiter ;

2、调用存储函数，查询指定节点及其下面所有子孙节点数据：

-- 可以简单的测试一下存储函数是否正常,输出值：4,12,13,14,15,16,23,24,25,26
    SELECT recursionDown(4) FROM DUAL; 
    -- 查询 id 为 4 的区域及其所有子孙节点
    SELECT * from region t where FIND_IN_SET(id,recursionDown(4));

![20200906113952882.gif][]

## 自定义函数实现·向上递归查询 ##

1、相对于向下递归来说，向上递归比较简单，因为向下递归时，每一层递归一个父节点都对应多个子节点，而向上递归时，每一层递归一个子节点只对应一个父节点，关系比较单一。

2、自定义存储函数如下，实现向上递归查询，通过传入节点id，查询它本身及其上面的所有父节点。：

-- 将sql语句的结束符由默认的分号";"临时改为"$$"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理
    delimiter $$ 
    -- 如果已经存在，则先删除掉
    -- 注意这里需要用 当前自定义的结束符 $$ 来结束并执行语句，因为本句需要和下边的函数体区分开来执行
    drop function if exists recursionUp $$ 
    -- 创建存储函数,声明参数,同时指定 返回值参数类型
    create function recursionUp(in_id INT) returns text 
    -- begin 和 end 中间包围的是函数体，用来写具体的逻辑
    begin 
    -- declare 用于声明变量，可以用 default 设置默认值
    -- 为防止数据量大，拼接的字符串太长，所以使用 text 大文本数据类型
    	declare return_ids text default ''; -- 最终返回的字符串
    	declare temp_id VARCHAR(56);  -- 每次循环临时遍历的结果字符串
    	declare total INT DEFAULT 1; -- 每次查询到数据个数
    	set temp_id = in_id;  -- set 用于为变量赋值 
    	while total > 0 do  -- 查询有结果时，才继续循环
    		-- 用 CONCAT_WS 函数把最终结果 和 临时生成的值 用逗号拼接起来
    		-- 设置拼接的 id 列表字符串，因为第一次时 return_ids 值为空，所以末尾会有一个 ","，后期去掉即可
    		set return_ids = CONCAT_WS(',', temp_id, return_ids);
    		-- 查询数据库并赋值，因为每个节点都只有一个父节点，所以就说普通的 SELECT INTO 即可
    		select pId,count(*) into temp_id,total from region where id = temp_id ;  
    	end while; 
    -- 对返回的id字符串从第1位开始截取，舍弃末尾的 "," 符号, LENGTH 函数的索引从 1 开始
    	set return_ids = SUBSTR(return_ids, 1, LENGTH(return_ids) - 1);
    -- 返回最终的数据
    	return return_ids; 
    end  
    $$ 
    delimiter ;

2、调用存储函数，查询指定节点及其上面所有父节点数据：

SELECT recursionUp(26) from dual; -- 0,1,4,16,26
    SELECT * from region t where FIND_IN_SET(id,recursionUp(26));

![20200906110914761.gif][]

[202009061412348.gif]: /images/20230531/70031b8b14f4459f9f0a9b3dffad782c.png
[20200906135253980.gif]: /images/20230531/05352aaa98384abd8ae262ad3684107e.png
[20200906135712274.gif]: /images/20230531/30d87740739d41e9b050fbedc94a8b12.png
[20200906140152132.gif]: /images/20230531/0c88cf56255b4466b29bff6ab01be888.png
[20200906144441233.gif]: /images/20230531/e1bf82307a9e4933973a7de14f64fefa.png
[20200906152811647.gif]: /images/20230531/441028663d694332ac03b0469557ef98.png
[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/95479263#%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%C2%B7%E9%9C%80%E6%B1%82%E5%88%86%E6%9E%90
[Oracle _ start with]: https://wangmaoxiong.blog.csdn.net/article/details/95479263
[20200905153819533.gif]: /images/20230531/ce3c79dc7a014f08b794d33d5ec1c88c.png
[find_in_set]: https://wangmaoxiong.blog.csdn.net/article/details/108420395#find_in_set%20%E6%9F%A5%E8%AF%A2%E5%87%BD%E6%95%B0
[CONCAT _ CONCAT_WS]: https://wangmaoxiong.blog.csdn.net/article/details/108420395#CONCAT%20%26%20CONCAT_WS%20%E8%BF%9E%E6%8E%A5%E5%87%BD%E6%95%B0
[group_concat]: https://wangmaoxiong.blog.csdn.net/article/details/108420395#group_concat%20%E5%88%86%E7%BB%84%E6%8E%92%E5%BA%8F%E8%BF%9E%E6%8E%A5
[20200906113952882.gif]: /images/20230531/4f65e2215f0648c09557e668adc18d03.png
[20200906110914761.gif]: /images/20230531/b43f620a1aaf4ec9964efc2d2a246d1e.png