--external table--查询时从外部文件中读取到定义好的表中，但Oracle并不保存数据，是对sqlldr工具的一个补充--下面的例子，测试用 external table读取oracle日志文件。-- 创建Oracle目录，指向Oracle的日志文件位置CREATE DIRECTORY ORCL_LOG as 'F:\oracle\product\10.2.0\db_1\admin\orcl\bdump';--创建外部表CREATE TABLE ALERT_LOG(LOG_TEXT VARCHAR2(4000))ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY ORCL_LOGACCESS PARAMETERS( RECORDS DELIMITED BY NEWLINE--每一行是一条记录 NOBADFILE NODISCARDFILE NOLOGFILE ) LOCATION('ALERT_ORCL.LOG') ) REJECT LIMIT UNLIMITED;--查询外部表SELECT * FROM ALERT_LOG WHERE ROWNUM <= 10;LOG_TEXT-----------------------------------Dump file f:\oracle\product\10.2.0\db_1/admin/orcl/bdump\alert_orcl.logSun Oct 21 09:39:32 2012ORACLE V10.2.0.1.0 - Production vsnsta=0vsnsql=14 vsnxtr=3Windows NT Version V6.1 Service Pack 1CPU : 4 - type 586Process Affinity : 0x00000000Memory (Avail/Total): Ph:285M/1991MSun Oct 21 09:39:32 2012Starting ORACLE instance (normal)--------------------------------------------------------------MERGE的使用--先来看下Merge的语法/*MERGE INTO TABLEUSING TABLE|SUBQUERYON CONDITIONWHEN MATCHED THEN UPDATE SET COL = EXPRESSION | DEFAULT WHERE_CLAUSE DELETE WHERE_CLAUSEWHEN NOT MATCHED THEN INSERT(COL,COL2) VALUES(EXPR1,EXPR2,|DEFAULT) WHERE_CLAUSEWHERE CONDITION;*/DROP TABLE TAB1;DROP TABLE TAB2;CREATE TABLE TAB1(ID INT, NAME VARCHAR2(10),STATUS VARCHAR2(10));CREATE TABLE TAB2(ID INT, NAME VARCHAR2(10));INSERT INTO TAB1 VALUES(1,'ANKER','N');INSERT INTO TAB1 VALUES(2,'MIN','U');INSERT INTO TAB1 VALUES(3,'MING','D');INSERT INTO TAB1 VALUES(4,'XIAO','U');INSERT INTO TAB2 VALUES(2,'A');INSERT INTO TAB2 VALUES(3,'MING');INSERT INTO TAB2 VALUES(4,'B');COMMIT;SELECT * FROM TAB1; ID NAME STATUS--------- ---------- ---------- 1 ANKER N 2 MIN U 3 MING D 4 XIAO USELECT * FROM TAB2; ID NAME---------- ---------- 2 A 3 MING 4 BMERGE INTO TAB2 USING TAB1 ON (TAB2.ID = TAB1.ID)WHEN MATCHED THEN UPDATE SET NAME = TAB1.NAME DELETE WHERE (TAB1.STATUS = 'D')WHEN NOT MATCHED THEN INSERT(ID,NAME) VALUES(TAB1.ID,TAB1.NAME) WHERE 1=1; SELECT * FROM TAB2;--查看合并之后的结果 ID NAME---------- ---------- 2 A 3 MING 4 B--------------------------------------------------------------------connect by 层次嵌套查询DROP TABLE TT;CREATE TABLE TT(ID INT,PARENT_ID INT,NAME VARCHAR2(10));INSERT INTO TT VALUES(1,NULL,'CEO');INSERT INTO TT VALUES(2,1,'VP');INSERT INTO TT VALUES(3,1,'SVP');INSERT INTO TT VALUES(4,1,'CFO');INSERT INTO TT VALUES(5,2,'DIRECTOR 1');INSERT INTO TT VALUES(6,2,'DIRECTOR 2');INSERT INTO TT VALUES(7,3,'DIRECTOR 3');INSERT INTO TT VALUES(8,3,'DIRECTOR 4');INSERT INTO TT VALUES(9,6,'MANAGER 1');COMMIT;SELECT * FROM TT; ID PARENT_ID NAME---------- ---------- ---------- 1 CEO 2 1 VP 3 1 SVP 4 1 CFO 5 2 DIRECTOR 1 6 2 DIRECTOR 2 7 3 DIRECTOR 3 8 3 DIRECTOR 4 9 6 MANAGER 1SELECT * FROM TT START WITH ID = 2 CONNECT BY PRIOR ID = PARENT_ID; ID PARENT_ID NAME---------- ---------- ---------- 2 1 VP 5 2 DIRECTOR 1 6 2 DIRECTOR 2 9 6 MANAGER 1 --START WITH 标示开始的记录 -- CONNECT BY 表示与当前记录关联的字段关系--PIROR ID = PARENT_ID，我是这样理解的，当前条(也是上一条)的ID等于下一条的PARENT ID。SELECT * FROM TT START WITH ID = 2 CONNECT BY ID = PRIOR PARENT_ID;--连接条件是当前条(也是上一条)的PARENT_ID等于下一条的ID ID PARENT_ID NAME---------- ---------- ---------- 2 1 VP 1 CEO--来学习学习层次查询的一些伪列使用SELECT LEVEL, --树状结构的层次 LPAD(' ', 2 * LEVEL) || NAME, SYS_CONNECT_BY_PATH(NAME, '/'),--层次结构显示 CONNECT_BY_ISLEAF,--是否是叶子节点，即下面没有子节点 CONNECT_BY_ROOT(NAME)--显示树状结构的ROOT FROM TT START WITH ID = 2CONNECT BY PRIOR ID = PARENT_IDORDER SIBLINGS BY ID DESC;--指定子节点相同时，排序的条件--------------------------------------------------------------NATRUAL JOINCREATE TABLE TT (ID INT,CODE VARCHAR2(10));CREATE TABLE TT1 (CODE VARCHAR2(10),NAME VARCHAR2(10));INSERT INTO TT VALUES(1,'A');INSERT INTO TT VALUES(2,'B');INSERT INTO TT VALUES(3,'C');INSERT INTO TT1 VALUES('A','JSP');INSERT INTO TT1 VALUES('B','JAVA');SELECT * FROM TT; ID CODE--------- ---------- 1 A 2 B 3 CSELECT * FROM TT1;CODE NAME---------- ----------A JSPB JAVAD ORACLESELECT TT.ID, CODE, TT1.NAME FROM TT NATURAL JOIN TT1; ID CODE NAME---------- ---------- ---------- 1 A JSP 2 B JAVA--NATRUAL JOIN 会自动根据两个表相同的字段进行连接。不需要指定ON条件--需要注意一点对于连接的字段，不能使用表限定，如下：[email protected]> SELECT TT.ID, TT.CODE, TT1.NAME FROM TT NATURAL JOIN TT1;SELECT TT.ID, TT.CODE, TT1.NAME FROM TT NATURAL JOIN TT1 *第 1 行出现错误:ORA-25155: NATURAL 联接中使用的列不能有限定词----------------------------------------------------------------USING的使用--USING 使用在INNER JOIN 和 OUTER JOIN 中(包括FULL OUTER JOIN)。--需要注意一点，连接的表列不能有限定词SELECT TT.ID, CODE, TT1.NAME FROM TT INNER JOIN TT1 USING (CODE); ID CODE NAME---------- ---------- ---------- 1 A JSP 2 B JAVA SELECT TT.ID, CODE, TT1.NAME FROM TT LEFT JOIN TT1 USING (CODE); ID CODE NAME---------- ---------- ---------- 1 A JSP 2 B JAVA 3 C-------------------------------------------------------------ANY SOME ALLSELECT CODE FROM TT; ID CODE---------- ---------- 1 A 2 B 3 CSELECT CODE FROM TT1;CODE NAME---------- ----------A JSPB JAVAD ORACLE--ANY的用法SELECT * FROM TT WHERE CODE > ANY(SELECT CODE FROM TT1); ID CODE--------- ---------- 3 C 2 B--从结果可以看出，ANY的意思是任何一个。上面的语句意思是显示TT表中大于TT1表中最小的CODE值。--也就等于以下SQLSELECT * FROM TT WHERE CODE > (SELECT MIN(CODE) FROM TT1);--来看SOME，实际上是与ANY是一样的。SELECT * FROM TT WHERE CODE > SOME(SELECT CODE FROM TT1); ID CODE---------- ---------- 3 C 2 B--来看看ALLSELECT * FROM TT1 WHERE CODE > ALL (SELECT CODE FROM TT);CODE NAME---------- ----------D ORACLE--显示的是TT1表中，比TT表中所有的CODE都大的记录。上面的SQL也等价于SELECT * FROM TT1 WHERE CODE > (SELECT MAX(CODE) FROM TT);--------------------------------------------------------------WITH AS的使用--WITH AS相当于把一个SQL的中间结果保存到一个临时表中，供多次使用，用户提高SQL的可读性。--此临时表生命周期只是查询的SQL内部WITH TAB_TT AS?(SELECT * FROM TT)SELECT * FROM TAB_TT;??????? ID CODE---------- ----------???????? 1 A???????? 2 B????????3 C?