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 表示数值时无限值。