ORACLE一些不常用的指令碼

本篇介紹一下一些不太常用的指令碼，它們往往有“奇能異效”，但是由於不太常用、不常見、或效率方面的原因，往往被大多數人給遺忘在某些角落裡。呵呵，不知道你看見下面這些指令碼，是否覺得眼熟呢？

1: 如果我需要從SCOTT模式下EMP表中查詢職位為CLERK（祕書）、SALESMAN（銷售人員）這兩種員工，你會怎麼寫？是下面哪種寫法呢？

SELECT * FROM SCOTT.EMP WHERE JOB =ANY('CLERK', 'SALESMAN') 
/

SELECT * FROM SCOTT.EMP WHERE JOB IN ('CLERK', 'SALESMAN')

/  

 ANY表示滿足於條件中任何一個，可以吧條件拆分成OR，或等效於IN。這個命令有些不常用，很多人習慣用IN替代.

 

2: 

查詢收入比所有職位為MANAGER都高的員工

SELECT * FROM SCOTT.EMP WHERE JOB != 'MANAGER' AND SAL >(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MANAGER')
/
SELECT * FROM SCOTT.EMP WHERE JOB !='MANAGER' AND SAL > ALL(SELECT SAL FROM SCOTT.EMP WHERE JOB ='MANAGER')

/

ALL表示列表中的所有條件都需要滿足。 可以拆分成AND條件。

 

3: SOME類似於ANY，在用法意義上和ANY意義一樣。表示只要滿足於條件中的一個。 SOME、ANY、ALL這三者在SQL指令碼中的效率一般不及子查詢（多行子查詢或關聯子查詢），建議還是少在指令碼中使用它們。

 

 

4: 假如需要在EMP表中插入一條員工名為MR'WANG的記錄，由於'一般用來標示一段字串，如果要'作為一個字元插入表中，其實可以兩個單引號''來表示

INSERT INTO SCOTT.EMP

VALUES(7903, 'MR''WANG',  'SALESMAN', 7698, TO_DATE('1984-12-25', 'YYYY-MM-DD'), 300, 0, 30) 

 

 大家一般用_ 、%來做模糊查詢匹配，但是假如我需要查詢字串中包含_或%字元的模糊查詢,這個該怎麼做呢？比如需要你查詢資料庫中所有表名包含_字元的表，可以用ESCAPE關鍵字指定一個轉義字元“\"來實現。這樣轉移字元後的下劃線就不再表示匹配關係了。如下所示：

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%\_%' ESCAPE '\' 

 

 

5: IS NAN 和 IS INFINITE

CREATE TABLE TEST
(
       T1    NUMBER           ,
       T2    BINARY_DOUBLE    ,
       T3    BINARY_FLOAT
)

INSERT INTO TEST
VALUES(12, 34.4, 23.45)

SELECT * FROM TEST 

INSERT INTO TEST

VALUES(13, 'NAN', 'NAN')  

 

INSERT INTO TEST

VALUES(14, 'INF', '-INF') 

此時你在PL/SQL裡面查詢SELECT * FROM TEST時，會報下面的錯誤(SELECT T1, TO_CHAR(T2), T0_CHAR(T3) FROM TEST 則不會報ORA-01722的錯誤，這是因為NAN等不能轉化為數字式格式的緣故）還是去SQL/PLUS裡面查詢吧，如圖所示， IS NAN 表示非數值匹配  IS INFINITI 表示數值時無限值。