oracle递归函数，oracle 存储过程 递归实现 依赖分析

其实oracle递归函数的问题并不复杂，但是又很多的朋友都不太了解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;

ORACLE select 递归查询

START WITH定义数据行查询的初始起点；

CONNECT BY prior定义表中的各个行是如何联系的；

connect by后面的"prior"如果缺省，则只能查询到符合条件的起始行，并不进行递归查询；

条件2：col_1= col_2，col_1是父键（它标识父），col_2是子键（它标识子）。

条件3过滤递归前相应节点及其子节点，如果上级节点不满足则下级节点自动过滤掉；

条件4过滤递归后相应的节点或子节点，如果上级节点不满足则下级结点自动提升一级。

系统伪列：

CURRVAL AND NEXTVAL使用序列号的保留字

ROWID记录的唯一标识

ROWNUM限制查询结果集的数量

LEVEL显示层次树中特定行的层次或级别

CONNECT_BY_ROOT返回当前层的根节点(当前行数据所对应的最高等级节点的内容)

SYS_CONNECT_BY_PATH(<column>,<char>)函数实现将从父节点到当前行内容以"path"或者层次元素列表的形式显示出来

CONNECT_BY_ISCYCLE须带参数NOCYCLE，当前行中引用了某个父亲节点的内容并在树中出现了循环，如果循环显示"1"，否则就显示"0"。

CONNECT_BY_ISLEAF判断当前行是不是叶子。如果是叶子显示"1"，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示"0"

而在 Oracle 10g中，只要指定"NOCYCLE"就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示"1"，否则就显示"0"。

【实例】

--创建测试表，增加测试数据

create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));

insert into test values('0','1','A1');

insert into test values('0','2','A2');

insert into test values('1','11','A11');

insert into test values('1','12','A12');

insert into test values('2','21','A21');

insert into test values('2','22','A22');

insert into test values('11','111','A111');

insert into test values('11','112','A112');

insert into test values('12','121','A121');

insert into test values('12','122','A122');

insert into test values('21','211','A211');

insert into test values('21','212','A212');

insert into test values('22','221','A221');

insert into test values('22','222','A222');

commit;

--层次查询示例

select level||'级' jc,lpad('',(level-1)*4)||id id,mc

from test

start with superid='0' connect by prior id=superid;

select level||'级' jc,connect_by_isleaf mxf,lpad('',(level-1)*4)||id id,mc

from test

start with superid='0' connect by prior id=superid;

--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with... connect by...

--功能：实现按照superid分组,把id用";"连接起来

--实现：以下两个例子都是通过构造2个伪列来实现connect by连接的。

SQL递归获取所有父节点的函数

其实这个只是一个循环的问题，我说下思路。

1：建立一个临时表，这个是用来存储“包括自己在内的所有父节点ID”的。

2：那就是循环查询并复制给一个变量，并且把这个值插入到临时表里(注意中间需要判断父节点是否为NULL，如果为NULL，则插入退出，但是不要忘记把这个值插入到临时表里)。

利用Oracle分析函数实现多行数据合并为一行

demo场景以oracle自带库中的表emp为例

select ename deptno from emp order by deptno;

ENAME DEPTNO CLARK KING MILLER SMITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER WARD

现在想要将同一部门的人给合并成一行记录如何做呢？如下

ENAME DEPTNO CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD

通常我们都是自己写函数或在程序中处理这里我们利用oracle自带的分析函数row_number()和sys_connect_by_path来进行sql语句层面的多行到单行的合并并且效率会非常高

基本思路

对deptno进行row_number()按ename排位并打上排位号

select deptno ename row_number() over(partition by deptno order by deptno ename) rank

from emp order by deptno ename;

DEPTNO ENAME RANK CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD可看出经过row_number()后部门人已经按部门和人名进行了排序并打上了一个位置字段rank

利用oracle的递归查询connect by进行表内递归并通过sys_connect_by_path进行父子数据追溯串的构造这里要针对ename字段进行构造使之合并在一个字段内（数据很多只截取部分）

select deptno ename rank level as curr_level

ltrim(sys_connect_by_path(ename)) ename_path from(

select deptno ename row_number() over(partition by deptno order by deptno ename) rank

from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank;

各部门递归后的数据量都是(+n)/* n即 deptno=数据量(+)/*=;

deptno=数据量(+)/*=; deptno=数据量(+)/*=;

DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH CLARK CLARK KING CLARK KING MILLER CLARK KING MILLER KING KING MILLER KING MILLER MILLER MILLER

DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH ADAMS ADAMS FORD ADAMS FORD JONES ADAMS FORD JONES SCOTT ADAMS FORD JONES SCOTT SMITH ADAMS FORD JONES SCOTT SMITH FORD FORD JONES FORD JONES SCOTT FORD JONES SCOTT SMITH FORD JONES SCOTT SMITH JONES JONES SCOTT JONES SCOTT SMITH JONES SCOTT SMITH SCOTT SCOTT SMITH SCOTT SMITH SMITH SMITH

这里我们仅列出deptno=的至此我们应该能否发现一些线索了即每个部门中 curr_level最高的那行有我们所需要的数据那后面该怎么办取出那个数据？对了继续用row_number()进行排位标记然后再按排位标记取出即可

对deptno继续进行row_number()按curr_level排位

select deptno ename_path row_number() over(partition by deptno order by deptno curr_level desc) ename_path_rank from(select deptno ename rank level as curr_level

ltrim(sys_connect_by_path(ename)) ename_path from(

select deptno ename row_number() over(partition by deptno order by deptno ename) rank

from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank);

DEPTNO ENAME_PATH ENAME_PATH_RANK CLARK KING MILLER CLARK KING KING MILLER CLARK KING MILLER DEPTNO ENAME_PATH ENAME_PATH_RANK ADAMS FORD JONES SCOTT SMITH ADAMS FORD JONES SCOTT FORD JONES SCOTT SMITH ADAMS FORD JONES FORD JONES SCOTT JONES SCOTT SMITH ADAMS FORD FORD JONES SCOTT SMITH JONES SCOTT ADAMS JONES SMITH SCOTT FORD这里还是仅列出deptno为的至此应该很明了了在进行一次查询取ename_path_rank为的即可获得我们想要的结果

获取想要排位的数据即得部门下所有人多行到单行的合并

select deptno ename_path from(select deptno ename_path

row_number() over(partition by deptno order by deptno curr_level desc) ename_path_rank

from(select deptno ename rank level as curr_level

ltrim(sys_connect_by_path(ename)) ename_path from(

select deptno ename row_number() over(partition by deptno order by deptno ename) rank

from emp order by deptno ename) connect by deptno= prior deptno and rank= prior rank))

关于oracle递归函数到此分享完毕，希望能帮助到您。