大家好,今天小编来为大家解答以下的问题，关于oracle递归函数，oracle递归nocycle这个很多人还不知道，现在让我们一起来看看吧！
Oracle数据库作为一款功能强大的数据库管理系统，广泛应用于各种企业级应用中。在数据库开发过程中，我们经常会遇到一些具有递归特性的业务场景，这时候，Oracle递归函数就派上了用场。本文将深入解析Oracle递归函数，并分享一些实战应用案例。
一、Oracle递归函数概述
1. 什么是递归函数？
递归函数是一种函数调用自身的函数。在Oracle数据库中，递归函数主要用于解决具有树状结构或层级关系的问题，如组织架构、产品分类等。
2. Oracle递归函数的特点
（1）自调函数：递归函数可以调用自身，实现循环迭代。
（2）无限递归：递归函数可以无限循环，直到满足某个终止条件。
（3）简洁易读：递归函数可以使代码更加简洁，易于理解。
3. Oracle递归函数的语法
“`sql
DECLARE
TYPE
BEGIN
— 初始化
— 递归循环
WHILE
— 递归调用
— 更新变量
END LOOP;
— 返回结果
RETURN
END;
“`
二、Oracle递归函数实战应用
1. 组织架构查询
假设我们有一个组织架构表，包含员工ID、上级ID、姓名等信息。现在我们需要查询某个员工的下级员工信息。
SQL代码：
“`sql
DECLARE
TYPE employee_list IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
employees employee_list;
emp_id VARCHAR2(50);
BEGIN
emp_id := ‘1001’; — 假设查询员工ID为1001的下级员工
employees(1) := emp_id;
WHILE employees.COUNT > 0 LOOP
SELECT subordinates INTO emp_id FROM employee WHERE id = employees(employees.FIRST);
employees(employees.COUNT + 1) := emp_id;
END LOOP;
— 输出结果
FOR i IN 1..employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(‘员工ID：’ || employees(i));
END LOOP;
END;
“`
2. 产品分类查询
假设我们有一个产品分类表，包含产品ID、上级ID、产品名称等信息。现在我们需要查询某个产品的所有下级产品信息。
SQL代码：
“`sql
DECLARE
TYPE product_list IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
products product_list;
prod_id VARCHAR2(50);
BEGIN
prod_id := ‘1001’; — 假设查询产品ID为1001的所有下级产品
products(1) := prod_id;
WHILE products.COUNT > 0 LOOP
SELECT sub_products INTO prod_id FROM product WHERE id = products(1);
products(products.COUNT + 1) := prod_id;
END LOOP;
— 输出结果
FOR i IN 1..products.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(‘产品ID：’ || products(i));
END LOOP;
END;
“`
Oracle递归函数在解决具有递归特性的业务场景中具有重要作用。本文深入解析了Oracle递归函数的概念、特点及语法，并分享了组织架构查询和产品分类查询的实战应用案例。希望本文能帮助您更好地掌握Oracle递归函数的使用方法。
()|::|==> _qerixtFetch
例子:
打印某个特定函数被调用时的线程调用堆栈
我们这里追踪 _kkeAdjSingTabCard设置追踪点
SQL代码
_kkeAdjSingTabCard*
函数名后的*N指定输出的调用个数为无限制
然后获取到你需要追踪的会话的SPID
SQL代码
HELLODBA>select distinct spid from v$mystat m v$session s v$process p where s sid=m sid and s paddr=p addr;
SPID
————
从进程列表中选择ORACLE EXE=>从线程列表中选择TID为的线程=>点击 Trace按钮
在被追踪的会话中解释一条语句
SQL代码
HELLODBA>explain plan for select/*+full(t)*/ count(*) from demo t_test t;
Explained
我们就可以从监控窗口获取到该函数被调用时的整个调用堆栈的情况
SQL代码
[:: ]User call: _kkeAdjSingTabCard(TID:)
Call Stacks():
×(ORACLE EXE!_kkoitbp+)
× c d(ORACLE EXE!_kkoijbad+)
× d b(ORACLE EXE!_kkoCopyPreds+)
× ee a(ORACLE EXE!_kkosta+)
× d f c(ORACLE EXE!__PGOSF __apaRequestBindCapture+)
× d(ORACLE EXE!_apagcp+)
× d c(ORACLE EXE!_apafbr+)
xea(ORACLE EXE!_opitcaNcp+)
× b eb(ORACLE EXE!_kksMinimalTypeCheck+)
× d(ORACLE EXE!_rpidrus+)
× b ce(ORACLE EXE!_kksSetNLSHandle+)
× e(ORACLE EXE!_kxsReleaseRuntimeLock+)
×(ORACLE EXE!_kkscbt+)
× e cf(ORACLE EXE!_kksParseCursor+)
× f b(ORACLE EXE!_kksxscpat+)
× e(ORACLE EXE!_opibrp+)
× cd ed(ORACLE EXE!_kpodrd+)
× cba c(ORACLE EXE!_kpocrs+)
× e(ORACLE EXE!_opirip+)
× feff(oramon dll!_ttcpro+)
× a(ORACLE EXE!_opiodr+)
×(ORACLE EXE!_opiino+)
× e(ORACLE EXE!_opirip+)
× e(ORACLE EXE!_opidcl+)
× a(ORACLE EXE!_ksdwri+)
×(ORACLE EXE!_ssthrnfy+)
×(ORACLE EXE!_opimai_init+)
×(ORACLE EXE!_osnsoiint+)
× c b(KERNEL dll!GetModuleFileNameA+)
[Args()]:
× e d
× e da
× a
×
lishixinzhi/Article/program/Oracle/201311/18739
利用Oracle分析函数实现多行数据合并为一行
demo场景以oracle自带库中的表emp为例
select ename deptno from emp order by deptno;
ENAME DEPTNOCLARK KING MILLER SMITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER WARD
现在想要将同一部门的人给合并成一行记录如何做呢？如下
ENAME DEPTNOCLARK 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 RANKCLARK 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_PATHCLARK CLARK KING CLARK KING MILLER CLARK KING MILLER KING KING MILLER KING MILLER MILLER MILLER
DEPTNO ENAME RANK CURR_LEVEL ENAME_PATHADAMS 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_RANKCLARK KING MILLER CLARK KING KING MILLER CLARK KING MILLER DEPTNO ENAME_PATH ENAME_PATH_RANKADAMS 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))
lishixinzhi/Article/program/Oracle/201311/17343
关于oracle递归函数到此分享完毕，希望能帮助到您。
