常用Oracle語句，oracle語句

常用Oracle語句，oracle語句

查看數據庫版本：

1）select * from PRODUCT_COMPONENT_VERSION;

2）select * from v$version;

根據子節點查詢所有的父節點：

SELECT X_LEVEL, X_ID
  FROM TAB_X
 START WITH X_ID = '子節點編號'
CONNECT BY NOCYCLE PRIOR PARENTID = X_ID;

根據父節點查詢所有子節點：

SELECT X_LEVEL, X_ID
  FROM TAB_X
 START WITH X_ID = '父節點編號'
CONNECT BY NOCYCLE PRIOR X_ID = PARENTID;

 純Oracle實現的日歷

SELECT MONTH 年月,"星期日", "星期一", "星期二",
"星期三", "星期四", "星期五", "星期六"
FROM (
SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,
decode(to_char(dt,'ww'),52,decode(TO_CHAR(dt+1,'iw'),1,53,52),53,decode(TO_CHAR(dt+1,'iw'),1,53,52),TO_CHAR(dt+1,'iw')) week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期日",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期一",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期二",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期三",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期四",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期五",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "星期六"
FROM (SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), 
         decode(to_char(dt,'ww'),52,decode(TO_CHAR(dt+1,'iw'),1,53,52),53,decode(TO_CHAR(dt+1,'iw'),1,53,52),TO_CHAR(dt+1,'iw'))
         )
ORDER BY TO_DATE( MONTH, 'Month YYYY'), 
         TO_NUMBER(week)

 如何分辨用戶是從哪台機器登錄的用戶

SELECT * FROM V$SESSION T