当前位置:主页 > 热门技术 >

常用Oracle语句,oracle语句

作者:凯发K发表时间:2018-11-24

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

 

http://www.bkjia.com/oracle/1124891.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/1124891.htmlTechArticle常用Oracle语句,oracle语句 查看数据库版本: 1)select * from PRODUCT_COMPONENT_VERSION; 2)select * from v$version; 根据子节点查询所有的父节点: SELE...

本文源自: 环亚娱乐

热门技术
联系我们