oracle递归函数(深入浅出!探讨Oracle中的递归函数应用)
一、oracle 存储过程 递归实现 依赖分析
下面是用oracle数据库解决不用startwith来查询子父数据查询方法，里面主要用到了substr和instr函数（这两个函数，其他数据库也有相对应的函数），游标（其他数据库也有游标）。
-- 1前提：创建表以及插入数据
CREATE TABLE TMP_TEST(MAIN_COLUMN VARCHAR2(10),PARENT_COLUMN VARCHAR2(10));
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('A',NULL);
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('B','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('C','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('D','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('E','B');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('F','C');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('G','E');
-- 2创建存储过程
CREATE OR REPLACE PROCEDURE GET_TREE(IS_PARENT IN NUMBER/**子父查询**/,
SEARCH_ID IN VARCHAR2/**查询条件节点**/,
TREE_RESOUT OUT VARCHAR2/**输出结果集合**/)
AS
V_TEMP VARCHAR2(4000);
V_SEARCH VARCHAR2(4000);
V_INDEX INTEGER;
BEGIN
V_TEMP:=SEARCH_ID||'-';
TREE_RESOUT:='';
WHILE length(V_TEMP)> 0 LOOP
V_INDEX:= instr(V_TEMP,'-');
V_SEARCH:= substr(V_TEMP,0,V_INDEX-1);
V_TEMP:= substr(V_TEMP,V_INDEX+1);
/*DBMS_OUTPUT.put_line('V_INDEX:'|| V_INDEX||'V_TEMP:'||V_TEMP||'V_SEARCH:'|| V_SEARCH);*/
/**查询子节点**/
if(IS_PARENT= 1) THEN
FOR C1 IN(SELECT* FROM TMP_TEST T1 WHERE T1.PARENT_COLUMN= V_SEARCH) LOOP
TREE_RESOUT:= TREE_RESOUT|| C1.MAIN_COLUMN;
V_TEMP:= V_TEMP|| C1.MAIN_COLUMN||'-';
END LOOP;
ELSE
/**查询父节点**/
FOR C1 IN(SELECT* FROM TMP_TEST T1 WHERE T1.MAIN_COLUMN= V_SEARCH) LOOP
TREE_RESOUT:= TREE_RESOUT|| C1.PARENT_COLUMN;
V_TEMP:= V_TEMP|| C1.PARENT_COLUMN||'-';
END LOOP;
END IF;
END LOOP;
/*DBMS_OUTPUT.put_line('TREE_RESOUT:'||TREE_RESOUT);*/
END;
-- 3调用存储过程
declare
TREE_RESULT VARCHAR2(4000);
SEARCH_ID VARCHAR2(4000);
begin
get_tree(1,'A',TREE_RESULT);
dbms_output.put_line('查询子节点:'|| TREE_RESULT);
get_tree(0,'G',TREE_RESULT);
dbms_output.put_line('查询父节点:'|| TREE_RESULT);
end;
二、sql语句实现递归查询所有节点,mysql和oracle都能用的
首先说一下Oracle的递归查询，相信大部分人都知道很简单。无非start with connect by函数。下面是从pId向子节点递归查询的例子，unId是数据库表中的主键。
如果是从子节点递归到父节点查询，就把start with换成unid，prior左右对换
下面再讲MySql的递归查询方式。MySql没有Oracle的强大功能，虽然都是同一个公司的产品。所以只能靠自己写。有很多方法，用sql去循环查询，或者写存储过程，我这里只提供一种。就是新建一个function函数。
表结构不说了，无非就是 Id，pId，其他列。下面是创建一个递归查询子节点的函数
DROP FUNCTION IF EXISTS queryChildrenPowerInfo;
CREATE FUNCTION `queryChildrenPowerInfo`(powerId VARCHAR(2000))
RETURNS VARCHAR(2000)
BEGIN
DECLARE sTemp VARCHAR(2000);
DECLARE sTempChd VARCHAR(2000);
SET sTemp='$';
SET sTempChd= cast(powerId as CHAR);
WHILE sTempChd is not NULL DO
SET sTemp= CONCAT(sTemp,',', sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_discretionary_power where FIND_IN_SET(pId,sTempChd)>0;
END WHILE;
return sTemp;
END
调用的时候：select queryChildrenPowerInfo("fa2528924c7e9168014c9bedfe04039c");该语句会返回Id和父Id等于传入参数powerId的一个字符串，中间有逗号隔开如图
下面这句代码的意思是，查询出 t_discretionary_power表中，t.id等于上面查询出的结果集的数据。FIND_IN_SET(A,B)是MYSQL的函数。意思是查找在B集合中有A的数据。相当于In
select t.* from t_discretionary_power t where FIND_IN_SET(t.id,queryChildrenPowerInfo('fa2528924c7e9168014c9bedfe04039c'))