标签归档:oracle技巧

Oracle 如何获取当天时间、本月第一天、本周第一天、本季度第一天呢?


摘要:
下文讲述oracle中获取当前日期的一些方法分享,如下所示:


SELECT TRUNC (SYSDATE,'DD'), –当天
     TRUNC (SYSDATE,'MM'),   --本月第一天
     TRUNC (SYSDATE,'yyyy'), --本年第一天
     TRUNC (SYSDATE,'day'),  --本周第一天
     TRUNC (SYSDATE,'q')      --本季度第一天

oracle数据库中查询表主键及外键信息的方法分享


摘要:
下文讲述oracle数据库中查询表主键及外键信息的方法分享,如下所示:

select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName,
       decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
              4, 'R', 5, 'V', 6, 'O', 7,'C', '?') as constraintType,
       col.name AS columnName
     
from sys.con$ oc, sys.con$ rc,
     sys.obj$ ro,sys.obj$ o, sys.obj$ oi,
     sys.cdef$ c,
     sys.col$ col, sys.ccol$ cc, sys.attrcol$ ac
where oc.con# = c.con#
  and c.obj# = o.obj#
  and c.rcon# = rc.con#(+)
  and c.enabled = oi.obj#(+)
  and c.robj# = ro.obj#(+)
  and c.type# != 8
  and c.type# != 12       /* don't include log groups */
  and c.con# = cc.con#
  and cc.obj# = col.obj#
  and cc.intcol# = col.intcol#
  and cc.obj# = o.obj#
  and col.obj# = ac.obj#(+)
  and col.intcol# = ac.intcol#(+)
  and o.name = '数据表名称'