惯用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

惯用Oracle语句

相关推荐