数据库编程-MYSQL-创建存储过程和方法 -实现递归方法

原创
2019/03/08 16:57
阅读数 22

说明

在实际业务环境中需要找到一位职工的直属上级。为了使技术策略可以更好的和sql进行配合。所以采取在MYSQL中创建自定义方法getUserLead(user_id)的技术路线。以期能达到如下的技术效果。

select getUserLead(id) FROM user;

下文中结合源码进行说明。

代码示例:

-- 创建存储过程和方法 获取用户的上级主管ID

DROP PROCEDURE IF EXISTS getUserLead;

DELIMITER //
CREATE PROCEDURE getUserLead(IN userid VARCHAR(255), IN d_id VARCHAR(255), OUT res VARCHAR(255))
#设置退出标签并且用LEAVE指令退出
proc_Exit:BEGIN
DECLARE d_lead VARCHAR(255);
DECLARE d_pid VARCHAR(255);
#设置超级管理员ID
DECLARE admin VARCHAR(255) DEFAULT 44;
#设置递归层数
SET max_sp_recursion_depth = 255;
#如果未找到用户则返回超级管理员
IF userid IS NULL OR userid = '' OR userid = 0 OR userid = admin THEN
SET res = admin;
LEAVE proc_Exit;
END IF;
#得到用户的部门ID
IF d_id IS NULL OR d_id = '' OR d_id = 0  THEN
SET d_id = (SELECT department_id FROM user WHERE id = userid);
END IF;
#如果用户没有部门则返回超级管理员
IF d_id IS NULL OR d_id = '' OR d_id = 0 THEN
SET res = admin;
LEAVE proc_Exit;
END IF;
#如果部门没有负责人则返回超级管理员
SET d_lead = (SELECT lead FROM department WHERE id = d_id);
IF d_lead IS null OR d_lead = '' OR d_lead = 0 THEN
SET res = admin;
LEAVE proc_Exit;
END IF;
#如果部门负责人不等于当前用户则可用
IF d_lead != userid THEN
SET res = d_lead;
LEAVE proc_Exit;
END IF;
#如果当前部门为顶级部门则返回超级管理员
SET d_pid = (SELECT pid FROM department WHERE id = d_id);
IF d_pid IS NULL OR d_pid = '' OR d_pid = 0 THEN
SET res = admin;
LEAVE proc_Exit;
END IF;
#前面所有条件都不满足则进行递归
CALL getUserLead(userid, d_pid, res);
END //
DELIMITER ;

DROP FUNCTION IF EXISTS getUserLead;

DELIMITER //
CREATE FUNCTION getUserLead(userid VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE res VARCHAR(255);
CALL getUserLead(userid, 0, res);
RETURN res;
END //
DELIMITER ;

SELECT getUserLead(42);

知识点解析

  1. 声明上的不同

    依照上文中的代码,我们可以发现主要的区别在于传入参数的声明以及返回值的写法上。所以主要在这两方面进行介绍。

  • 参数声明

    创建方法的参数声明

    	CREATE FUNCTION getUserLead(userid VARCHAR(255))
    

    创建存储过程的参数声明

    	CREATE PROCEDURE getUserLead(IN userid VARCHAR(255))
    

    很明显的发现创建存储过程中在参数名之前多了一个关键词IN。我在这主要对这多出来的关键词进行详细说明:

      IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
      OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
      INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
    
  • 返回值与中止

    在上文代码中我们可以很清楚的在创建方法的代码部分中看到RETURNS VARCHAR(255)以及RETURN res;这样和返回值相关的内容。但是在存储过程中就没有看到。所以在此主要说明存储过程的返回值与中止。

    在参数声明一节中我们对存储过程参数声明中参数名之前多的关键词进行了介绍,其中OUTINOUT达到的效果就像其他编程语言中的静态参数一样,可以参照这段代码理解

    DECLARE res VARCHAR(255);
    CALL getUserLead(userid, 0, res);
    RETURN res;
    

    知道了原理,我们回到存储过程过程体中,可以看到这样的赋值操作SET res = admin;我们对res的改变会作用于传入的变量本身,就达到了返回值获取的目的。

    那为什么谈到中止了,是因为RETURN对外是返回值,对内还有一个功能是跳出方法,不执行下面的代码。很显然我们一个SET还做不到这点,那么如何实现了,在源码中我们可以看到这样一个组合

    proc_Exit:BEGIN
    、、、
    LEAVE proc_Exit;
    

    BEGIN上设置一个标签,然后用LEAVE 中止他。

  1. 调用方式不同

    存储过程不可以在SQL中嵌套调用,调用形式为

    	CALL 存储过程;
    

    方法可以在SQL中嵌套调用

    	SELECT 方法;
    
  2. 是否支持递归

    存储过程支持递归,方法不支持递归。存储过程支持递归需要设置

    	SET max_sp_recursion_depth = 255;
    

    最大递归层数。

  3. 实现技巧

    由于我们的技术目标需要实现方法的递归,但方法不支持递归,存储过程不可以在SQL中嵌套使用,所以采取一种间接的方式来实现。用方法调用存储过程即可。

  4. 其他小知识点

    方法/存储过程主体内变量的声明,在上文源码中变量的声明无论在存储过程中还是在方法中都需要紧跟在BEGIN之后。变量声明的语法都为

    	DECLARE admin VARCHAR(255) DEFAULT 44; (设置默认值的)
    

    或者

    	DECLARE admin VARCHAR(255); (不设置默认值的)
    

应用技术难点 - 未解决!

  1. 源码中对于 department 表 条件相同的情况下查询了多次。这肯定是不必要的开销。 原因是我只知道给单一变量赋值的方法。至少有以下几点是我不清楚的。需要攻克!
  • <1>是否可以将变量声明为数据集。然后将查询集合赋予它,如果存在如何从数据集变量中进行取值。
  • <2>除去SET这种还有一种
    	SELECT name INTO 变量 FROM user where id = 1;
    
    是可以给变量赋值的,以此衍生我觉得可以这么写
    	SELECT name INTO 变量,  age INTO 变量 FROM user WHRER id = 1;
    
    但很可惜不行。

本文长期维护。欢迎留言、交流、指导!

展开阅读全文
打赏
0
0 收藏
分享

作者的其它热门文章

加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部