OCP SQL 例子-2

--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_LOADER
DEFAULT DIRECTORY ORCL_LOG
ACCESS 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.log
Sun Oct 21 09:39:32 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.1 Service Pack 1
CPU                 : 4 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:285M/1991M
Sun Oct 21 09:39:32 2012
Starting ORACLE instance (normal)
------------------------------------------------------------------------------------------------------------------------------------
--MERGE的使用
--先来看下Merge的语法
/*
MERGE INTO TABLE
USING TABLE|SUBQUERY
ON CONDITION
WHEN MATCHED THEN UPDATE SET COL = EXPRESSION | DEFAULT
     WHERE_CLAUSE
     DELETE WHERE_CLAUSE
WHEN NOT MATCHED THEN INSERT(COL,COL2)
     VALUES(EXPR1,EXPR2,|DEFAULT)
     WHERE_CLAUSE
WHERE 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       U
SELECT * FROM TAB2;

        ID NAME
---------- ----------
         2 A
         3 MING
         4 B

MERGE 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 1

SELECT * 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 = 2
CONNECT BY PRIOR ID = PARENT_ID
ORDER SIBLINGS BY ID DESC;--指定子节点相同时，排序的条件
------------------------------------------------------------------------------------------------------------------------------------
--NATRUAL JOIN
CREATE 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 C
SELECT * FROM TT1;
CODE       NAME
---------- ----------
A          JSP
B          JAVA
D          ORACLE

SELECT TT.ID, CODE, TT1.NAME FROM TT NATURAL JOIN TT1;

        ID CODE       NAME
---------- ---------- ----------
         1 A          JSP
         2 B          JAVA

--NATRUAL JOIN 会自动根据两个表相同的字段进行连接。不需要指定ON条件
--需要注意一点对于连接的字段，不能使用表限定，如下：

admin@ORCL> 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 ALL
SELECT CODE FROM TT;
        ID CODE
---------- ----------
         1 A
         2 B
         3 C

SELECT CODE FROM TT1;
CODE       NAME
---------- ----------
A          JSP
B          JAVA
D          ORACLE

--ANY的用法
SELECT * FROM TT WHERE CODE > ANY(SELECT CODE FROM TT1);
       ID CODE
--------- ----------
        3 C
        2 B
--从结果可以看出，ANY的意思是任何一个。上面的语句意思是显示TT表中大于TT1表中最小的CODE值。
--也就等于以下SQL

SELECT * 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
--来看看ALL
SELECT * 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
--listagg的使用
WITH TEMP AS(
  SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL
  SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL
  SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL
  SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL
  SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL
  SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL
)
SELECT POPULATION,
      NATION,
      CITY,
      LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY DESC) OVER (PARTITION BY NATION) RANK
      FROM TEMP;
POPULATION NATIO CITY      RANK
---------- ----- --------- ------------------------------
      1500 CHINA SHANGHAI  SHANGHAI,GUANGZHOU,BEIJING
       500 CHINA GUANGZHOU SHANGHAI,GUANGZHOU,BEIJING
       500 CHINA BEIJING   SHANGHAI,GUANGZHOU,BEIJING
       500 JAPAN TOKYO     TOKYO
      1000 USA   NEW YORK  NEW YORK,BOSTOM
       500 USA   BOSTOM    NEW YORK,BOST