Oracle开发常用SQL语句

1 游标使用

---------------------------隐示FOR循环使用示例---------------------------
BEGIN
   FOR CUR IN (SELECT T.OWNER, T.TABLE_NAME
                 FROM ALL_TABLES T
                WHERE T.TABLE_NAME LIKE 'CM_%'
                  AND T.OWNER IN ('EA_CTMS', 'EA_CTMS_HIST')) LOOP
       EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || CUR.OWNER || '.' || CUR.TABLE_NAME;
   END LOOP;
END;

2 日期函数

-- 本季度第一天
SELECT to_char(TRUNC(SYSDATE, 'Q'), 'YYYY-MM-DD') FROM dual;


-- 上个季度最后一天（可以用本季度第一天减去1得到）
SELECT to_char(TRUNC(SYSDATE, 'Q') - 1, 'YYYY-MM-DD') FROM dual;


-- 上个季度第一天(本季度第一天 减 3个月)
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), -3), 'YYYY-MM-DD')
 FROM dual;


-- 上个季度第一天(本季度减一个月之后的月份的最后一天)
select to_char(last_day(add_months(trunc(sysdate, 'Q'), -1)), 'yyyy-mm-dd')
 from dual
 
--№1: 取得当前日期是本月的第几周  
select to_char(sysdate,'YYYYMMDD    W    HH24:MI:SS')   from   dual;
select to_char(sysdate, 'W') from dual;

--№2:取得当前日期是一个星期中的第几天,注意星期日是第一天
select sysdate, to_char(sysdate, 'D') from dual;
--类似:
select to_char(sysdate, 'yyyy') from dual; --当前年
select to_char(sysdate, 'Q' ) from dual; --当前季度
select to_char(sysdate, 'mm') from dual; --当前月
select to_char(sysdate, 'dd') from dual; --当前日
--№3:取当前日期是星期几的中文显示:
select   to_char(sysdate, 'day')   from   dual;
--№4:如果一个表在一个date类型的字段上面建立了索引，如何使用
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
--№5: 得到当前的日期
select sysdate from dual;

--№6: 得到当天凌晨0点0分0秒的日期
select to_char(trunc(sysdate), 'yyyymmdd hh:mi:ss' ) from dual;

-- 得到这天的最后一秒
select trunc(sysdate) + 0.99999 from dual;

-- 得到小时的具体数值
select trunc(sysdate) + 1 / 24 from dual;
select trunc(sysdate) + 7 / 24 from dual;

--№7:得到明天凌晨0点0分0秒的日期
select trunc(sysdate + 1) from dual;
select trunc(sysdate) + 1 from dual;

--№8: 本月一日的日期
select trunc(sysdate, 'mm') from dual;

--№9:得到下月一日的日期
select add_months(trunc(sysdate, 'mm'), 1) from dual;
select trunc(add_months(sysdate, 1), 'mm') from dual;

-- №10:返回当前月的最后一天?
select last_day(sysdate) from dual;
select last_day(trunc(sysdate)) from dual;
select trunc(last_day(sysdate)) from dual;
select trunc(add_months(sysdate, 1), 'mm') - 1 from dual;

--№11: 得到一年的第一天
select trunc(sysdate, 'y') from dual

--№11: 得到一年的每一天
select trunc(sysdate, 'yyyy') + rn - 1 date0
 from (select rownum rn from all_objects where rownum < 366) t;

--№12:今天是今年的第N天
SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

--№13:如何在给现有的日期加上2年
select add_months(sysdate, 24) from dual;

--№14:判断某一日子所在年分是否为润年
select last_day(trunc(sysdate, 'yyyy') + 31) from dual   -- 通过一年的第一天加上31天到2月,然后取2月最后一天
select decode(to_char(last_day(trunc(sysdate, 'y') + 31), 'dd'),
             '29',
             '闰年',
             '平年')
 from dual;

--№15:判断两年后是否为润年
select decode(to_char(last_day(trunc(add_months(sysdate, 24), 'y') + 31), 'dd'),
             '29',
             '闰年',
             '平年')
 from dual;

--№16:得到日期的季度
select trunc(sysdate, 'mi') from dual ;
select to_number(to_char(sysdate,'mm')) from dual;
select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;
select to_char(sysdate, 'Q') from dual;

--No17:返回某个月的最后一天
select to_char(last_day(sysdate), 'dd') from dual
--本周的第一天和最后一天，考虑到中国人习惯是周一为每周的第一天（国际惯例是周日为第一天，oracle也是这样）
select trunc(sysdate,'d')+1,trunc(sysdate,'d')+7 from dual where to_char(sysdate,'day')<>'星期日'
union all
select trunc(sysdate,'d')+1-7,trunc(sysdate,'d')+7-7 from dual where to_char(sysdate,'day')='星期日'
--当前季度的第一天
select to_char(trunc(sysdate,'Q'),'yyyy-mm-dd') from dual;
--当前季度的最后一天
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), +3)-1, 'YYYY-MM-DD')
 FROM dual;
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') "本月第一天",
      to_char(last_day(sysdate), 'yyyy-mm-dd') "本月最后一天" FROM dual;
      
--获取某时间段内的年份、月份、日期列表
SELECT TO_CHAR(TO_DATE('2014-01-01', 'YYYY-MM-DD') + ROWNUM - 1, 'YYYYMMDD') AS DAYLIST
 FROM DUAL
CONNECT BY ROWNUM < TRUNC(TO_DATE('2018-01-01', 'YYYY-MM-DD') -
                         TO_DATE('2014-01-01', 'YYYY-MM-DD')) + 1;

3 找出低效率的SQL

SELECT EXECUTIONS,
       DISK_READS,
       BUFFER_GETS,
       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) HIT_RADIO,
       ROUND(DISK_READS / EXECUTIONS, 2) READS_PER_RUN,
       SQL_TEXT
  FROM V$SQLAREA
WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
ORDER BY 4 DESC;

4 行转列

--分两列显示两种汇总结果，并排序，保证对应关系
select DETAIL_ID,COMMENT_TIME,COMMENT_BODY from (
select
DETAIL_ID,
 WMSYS.WM_CONCAT(to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss'))
         OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_TIME,
  WMSYS.WM_CONCAT('{'||COMMENT_BODY||'}')
         OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_BODY,
     row_number() OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) rs
 from BBSCOMMENT) where rs=1;

--多行合并为一行，要求排序（最新的评论在前面）
select DETAIL_ID, max(r)
from (select DETAIL_ID, wmsys.wm_concat(COMMENT_BODY||'('||to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss')||')')
   OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) r from BBSCOMMENT)
group by DETAIL_ID;

--将上述SQL语句与主表做一个连接查询
select bd.DETAIL_ID,TITLE,bcm.COMMENT_INFO
from BBSDETAIL bd,(select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}') as COMMENT_INFO from BBSCOMMENT group by DETAIL_ID) bcm
where bd.DETAIL_ID=bcm.DETAIL_ID(+);

--多行合并为一行，不要求排序
select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}')
from BBSCOMMENT
group by DETAIL_ID;

5 列转行

SELECT WMSYS.WM_CONCAT(T.DBNAME) FROM APPLICATIONCONFIG T WHERE T.APPCODE != 'Main';

6 更新表字段顺序

--第一步，从数据字典视图查询出表的id
SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OWNER = 'EA_CTMS'
  AND OBJECT_NAME = 'MID_CAMCIL_RESULT'; --53114
--第二步，通过id查出该表中所有字段的顺序
SELECT OBJ#, COL#, NAME FROM SYS.COL$ WHERE OBJ# = 53114;
--第三步，更新字段的顺序
UPDATE SYS.COL$ SET COL# = 188 WHERE OBJ# = 53114 AND NAME = 'BUSI_MON_ID';

7 查询某个数字存在某张表的某个字段中

DECLARE
   AC_SQL  VARCHAR2(1000);
   AC_FLAG NUMBER;
BEGIN
   FOR CUR IN (SELECT T.OWNER, T.TABLE_NAME FROM ALL_TABLES T WHERE T.OWNER = 'CWXT') LOOP
       FOR CUR2 IN (SELECT T.COLUMN_NAME
                      FROM ALL_TAB_COLS T
                     WHERE T.OWNER = CUR.OWNER
                       AND T.TABLE_NAME = CUR.TABLE_NAME
                       AND T.DATA_TYPE NOT IN ('BLOB','LONG')) LOOP
           AC_SQL := 'SELECT COUNT(1) FROM {:OWNER}.{:TABLE_NAME} WHERE {:COLUMN_NAME} LIKE ''%1002010101%'' AND ROWNUM = 1';
           AC_SQL := REPLACE(REPLACE(REPLACE(AC_SQL, '{:OWNER}', CUR.OWNER),
                                     '{:TABLE_NAME}',
                                     CUR.TABLE_NAME),
                             '{:COLUMN_NAME}',
                             CUR2.COLUMN_NAME);
           DELETE FROM CWXT.TEST;
           INSERT INTO CWXT.TEST (TST1) VALUES (AC_SQL);
           COMMIT;
           EXECUTE IMMEDIATE AC_SQL
               INTO AC_FLAG;
           IF AC_FLAG = 1 THEN
               DBMS_OUTPUT.PUT_LINE(CUR.OWNER || '.' || CUR.TABLE_NAME || '.' ||
                                    CUR2.COLUMN_NAME);
           END IF;
       END LOOP;
   END LOOP;
END;

8 查询重复的记录

SELECT T.*, T.ROWID
 FROM EA_CTMS.BS_OUT_SENT_OBJ_RELA_FILE T
WHERE EXISTS
(SELECT 1
         FROM (SELECT T.OUT_SENT_OBJ_CODE, T.FILE_CODE, T.OUT_SENT_OBJ_TYPE
                 FROM EA_CTMS.BS_OUT_SENT_OBJ_RELA_FILE T
                GROUP BY T.OUT_SENT_OBJ_CODE, T.FILE_CODE, T.OUT_SENT_OBJ_TYPE
               HAVING COUNT(T.OUT_SENT_OBJ_CODE) > 1) A
        WHERE T.OUT_SENT_OBJ_CODE = A.OUT_SENT_OBJ_CODE
          AND T.FILE_CODE = A.FILE_CODE
          AND T.OUT_SENT_OBJ_TYPE = A.OUT_SENT_OBJ_TYPE)
  AND ROWID IN (SELECT MAX(ROWID)
                      FROM EA_CTMS.BS_OUT_SENT_OBJ_RELA_FILE T
                     GROUP BY T.OUT_SENT_OBJ_CODE, T.FILE_CODE, T.OUT_SENT_OBJ_TYPE
                    HAVING COUNT(T.OUT_SENT_OBJ_CODE) > 1);

9 取字符串中的数字

SELECT MAX(REPLACE(SYS_CONNECT_BY_PATH(CH, '/'), '/')) NUM
 FROM (SELECT CH, RN - MIN(RN) OVER(PARTITION BY RN - ROWNUM) RN, RN - ROWNUM GRP
         FROM (SELECT SUBSTR('@311a10', ROWNUM, 1) CH, ROWNUM RN
                 FROM DUAL
               CONNECT BY ROWNUM <= LENGTH('@311a10'))
        WHERE CH BETWEEN '0' AND '9')
START WITH RN = 0
CONNECT BY RN - 1 = PRIOR RN
      AND GRP = PRIOR GRP
GROUP BY GRP
ORDER BY GRP
SELECT REGEXP_REPLACE('#31110', '[^0-9]+', '') FROM DUAL;

10 分组排序用法

SELECT *
 FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY FILE_CODE ORDER BY ORDER_NO) AS ROW_NUM
         FROM EA_CTMS.BS_INTF_FILE_PRETREAT_REGULAR T)
WHERE ROW_NUM = 1;

11 递归查询

SELECT *
 FROM EA_CTMS.BS_INTF_FILE_CL_TYPE T
START WITH PARENT_FILE_CFG_TYPE = '0'
CONNECT BY PRIOR FILE_CFG_TYPE = PARENT_FILE_CFG_TYPE
ORDER SIBLINGS BY FILE_CFG_TYPE

12 查询表结构

SELECT B.COMMENTS 名称,
      A.COLUMN_NAME 代码,
      A.DATA_TYPE || '(' || DECODE(UPPER(A.DATA_TYPE),
                                   'NUMBER',
                                   DECODE(TO_CHAR(A.DATA_SCALE),
                                          TO_CHAR(0),
                                          TO_CHAR(A.DATA_PRECISION),
                                          A.DATA_PRECISION || ',' || A.DATA_SCALE),
                                   A.DATA_LENGTH) || ')' 数据类型,
      A.NULLABLE 必须,
      B.COMMENTS 描述
 FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B
WHERE A.COLUMN_NAME = B.COLUMN_NAME
  AND A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.TABLE_NAME
  AND A.OWNER = 'EI_CTMSDATA'
  AND A.TABLE_NAME = 'STD_KS_BANK_ACC'
ORDER BY A.COLUMN_ID;

13 根据已知表的主键查询所有引用这个表主键的外键的表

SELECT T.OWNER,
      T.TABLE_NAME,
      T.CONSTRAINT_NAME,
      S.COLUMN_NAME,
      T.R_OWNER,
      T.R_CONSTRAINT_NAME
 FROM SYS.ALL_CONSTRAINTS T, SYS.ALL_CONS_COLUMNS S
WHERE T.CONSTRAINT_NAME = S.CONSTRAINT_NAME
  AND EXISTS (SELECT 1
         FROM ALL_CONSTRAINTS A, SYS.ALL_CONS_COLUMNS B
        WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
          AND B.OWNER = '&OWNER'
          AND B.TABLE_NAME = '&TABLE_NAME'
          AND A.CONSTRAINT_TYPE = '&CONSTRAINT_TYPE'
          AND B.COLUMN_NAME = '&COLUMN_NAME'
          AND T.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME);

14 判断字段是否为数字

--TRANSLATE：提取替换字符串
SELECT NVL2(REPLACE(TRANSLATE('222', '.0123456789', '000000000000'), '0', ''), '否', '是')
 FROM DUAL;

15 启禁用所有外键约束

--------------------------------禁用所有外键约束--------------------------------
SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';'
 FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
--------------------------------启用所有外键约束--------------------------------
SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' enable constraint ' || CONSTRAINT_NAME || ';'
 FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'

16 ORACLE转义符的使用

SELECT *
 FROM MYDATA.FABM_CJDR T
WHERE T.DATA_TYPE = '表'
  AND T.COLL_DATA_SRC NOT LIKE '%X%%' ESCAPE 'X';