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));

张兴康

fteair

张兴康

fteair 发表于 2017-9-1 14:44

fteair

张兴康 发表于 2017-9-4 15:47

caixuqad