标题: ORACLE 高级函数应用三 [打印本页] 作者: 张兴康 时间: 2020-7-1 14:11 标题: ORACLE 高级函数应用三 三、树查询相关的函数
--1、ORDER SIBLINGS BY 先按上级的compid排序，再按下级的compid排序。
--2、level与rpad\lpad组合应用，构造可通的层级
--3、CONNECT_BY_ISLEAF 判断当前结点是否为叶子结点，0表示为非叶子结点，1则表示为叶子结点
--（如果不存在下级节点就是路子节点）
--4、CONNECT_BY_ROOT 列出树的根结点对应的字段值，需与字段值组合使用
--5、SYS_CONNECT_BY_PATH 函数就是从start with 开始的地方开始遍历，并记下其遍历到的节点，
-- start with 开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的
-- 字符串。
SELECT LEVEL,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,
CONNECT_BY_ROOT COMPNAME ROOT_COMPNAME,CONNECT_BY_ISCYCLE "CYCLE",
SYS_CONNECT_BY_PATH(COMPID,'/') FORMAT_COMPID,
SYS_CONNECT_BY_PATH(COMPNAME,'->') FORMAT_COMPNAME,
A.*
FROM COMPANYINFO A
START WITH A.COMPID = '0000'
CONNECT BY NOCYCLE PRIOR A.COMPID = A.PCOMPID
ORDER SIBLINGS BY COMPID;
SELECT * FROM COMPANYINFO WHERE COMPID='0000' FOR UPDATE;
--with关键字与树查询组合应用例子：
--查询与compid=’0001‘的同级节点
WITH COMPANYINFO_TMP AS
(SELECT LEVEL LEV,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,
A.*
FROM COMPANYINFO A
START WITH A.COMPID = '0000'
CONNECT BY PRIOR A.COMPID = A.PCOMPID
)
SELECT * FROM COMPANYINFO_TMP
WHERE LEV=(SELECT LEV FROM COMPANYINFO_TMP
WHERE COMPID='0001') ORDER BY COMPID;
四、其它：
NVL(a,b); --若a为null，则返回b,否则返回a(即不转换)
NVL2(a,b,c); --不管a是不是Null都要转换，如果为空，返回c,否则返回b.
NULLIF(a,b); --判断a与b是否相等，若相等则返回null,不支持类型自动转换。
COALESC(a,b,...,n); --从左到右返回第1个非null值，若所有的列表元素都为null,则返回null.它有短路计算功能，
--比如，a为null,b非null,则返回b的值，不再计算下一个值。
DECODE\SIGN;
--应用例子：
WITH T AS
(SELECT LEVEL ID
FROM DUAL
CONNECT BY LEVEL<10
)
SELECT ID,
DECODE(-1,SIGN(ID-5),'low',
SIGN(ID-8),'mid',
'high') RESULTS
FROM T;
--decode的常用应用之一是实现固定行转列：
--下面的一decode的综合应用：查询借货不平衡的数据
CALL VPD_PKG.SET_CONTEXT_COMPID('-1') ;
SELECT BILLID,SUM(DECODE(BZJD,1,TMONEYF,0)) AS DFJE,SUM(DECODE(BZJD,1,0,TMONEYF)) AS JFJE
FROM (SELECT A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD) AS BZJD,SUM(DECODE(A.CXBZ,1,-1*A.TMONEYF,A.TMONEYF)) AS TMONEYF
FROM XTBILLMX2010 A,XTBILL2010 B
WHERE B.BILLID=A.BILLID AND B.BZWC=1
GROUP BY A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD))
GROUP BY BILLID
HAVING SUM(DECODE(BZJD,1,TMONEYF,0))<>SUM(DECODE(BZJD,1,0,TMONEYF)); 作者: 张兴康 时间: 2020-7-1 14:11 作者: fteair 时间: 2020-7-1 14:44 作者: 张兴康 时间: 2020-7-4 15:47
