ORACLE经典语句汇总

-- 字符串左填充和右填充，默认填充空格
SELECT   LPAD('TEST',8,'LLLL') ,RPAD('TEST',8,'RRRR') FROM DUAL;

-- 产生1~99行数据，少于一位则补0
SELECT LPAD(ROWNUM,2,'0') FROM DUAL CONNECT BY ROWNUM <=99;

-- 刪除相同行
DELETE FROM TEST T WHERE T.ROWID <> (SELECT MIN(T1.ROWID) FROM TEST T1 WHERE T1.A1 = T.A1)

-- 随机数
SELECT DBMS_RANDOM.VALUE, DBMS_RANDOM.VALUE(55, 100) FROM DUAL;

-- 产生业务流水号
create sequence test_seq1 minvalue 1 maxvalue 100000000 start with 2 increment by 1 nocache noorder nocycle; --仅执行一次
select '530101'||to_char(sysdate,'yyyymmdd')||lpad(test_seq1.nextval,6,'0') from dual

-- 查询某张表中有哪些字段
select COLUMN_NAME
from user_col_comments
where upper(TABLE_NAME) = upper('content')
order by COLUMN_NAME;

-- 自循环表中 由叶子节点查父节点
SELECT T.*
FROM COLUMNINFO T
CONNECT BY PRIOR T.COLSUPID = T.ID
START WITH T.ID = 754

-- 查子节点
SELECT T.*
FROM COLUMNINFO T
CONNECT BY PRIOR T.ID = T.COLSUPID
START WITH T.ID = 7

-- 查叶子节点
SELECT A.*
   FROM COLUMNINFO A
WHERE NOT EXISTS (SELECT 1 FROM COLUMNINFO B WHERE B.COLSUPID = A.ID)
START WITH A.ID = 7
CONNECT BY PRIOR A.ID = A.COLSUPID

-- 根据父ID查叶子节点和路径
SELECT COL.ID,
       COL.COLSUPID,
       COL.COLNAME,
       CONNECT_BY_ISLEAF "ISLEAF",
       SYS_CONNECT_BY_PATH(ID, '/') "PATH"
FROM COLUMNINFO COL
WHERE CONNECT_BY_ISLEAF = 1
START WITH COL.ID = 7
CONNECT BY PRIOR COL.ID = COL.COLSUPID

-- 根据叶子节点ID查路径
SELECT CONNECT_BY_ISLEAF "ISLEAF",
       '/' || REVERSE(SYS_CONNECT_BY_PATH(ID, '/')) "PATH"
FROM COLUMNINFO COL
WHERE CONNECT_BY_ISLEAF = 1
START WITH COL.ID = 754
CONNECT BY PRIOR COL.COLSUPID = COL.ID

-- 以父栏目分组，按栏目ID排序，取出汇总每个栏目下的子栏目，并以“,”符号作为分隔符
SELECT COL.COLSUPID,
       COL2.COLNAME,
       LTRIM(SYS_CONNECT_BY_PATH(COL.COLNAME, ','), ',') SCBP
FROM (SELECT C.COLSUPID,
               C.COLNAME,
               ROW_NUMBER() OVER(PARTITION BY COLSUPID ORDER BY COLNAME) RN,
               COUNT(*) OVER(PARTITION BY COLSUPID) CNT
          FROM COLUMNINFO C) COL,
       COLUMNINFO COL2
WHERE COL2.ID = COL.COLSUPID
   AND RN = CNT
START WITH RN = 1
CONNECT BY PRIOR ID = ID
       AND PRIOR RN = RN - 1
ORDER BY COL.COLSUPID