ORACLE 9i新增的查询及修改语句语法

please

########################## 高级子查询 ####################################

/*多栏位子查询*/
select select_list from table1 where (col1,col2) in (select ...);
/*case语句、order by中的子查询*/
select col1,col2,
       (case when clo3=
                  (select col4 from table2 where col5 = value)
        then 'value1'
        else 'result'
        end) location
from table1;

select col1,col2 from table1 order by (select col3 from table2 where table1.col1=table2.col4);

/*高级关联子查询*/
select col1,col2,col3 from table1 where 2 <= (select count(*) from table2 where col4=table1.col1);

/*关联update*/
update table1 t1 set col1=(select col2 from table2 t2 where t1.col1=t2.col3);

/*关联删除*/
delete from table1 t1 where col1 = (select col2 from table2 t2 where t1.col2=t2.col3);

/*WITH 语句*/
WITH
with_name1 as (select col1,col2,col3,... from table1 where ...),
with_name2 as (select col4,col5,col6,... from with_name1)
select * from with_name1 where col1 >; (select col4 from with_name2) order by ...;

/*分层查询*/
select [LEVEL],col1,expr... from table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

example:
/*从树的上到下遍历,start with后面的字段可看作父字段*/
select col1,col2,col3,col4 from table start with col4=5 connect by prior col4=col1;
/*分层从上到下遍历树,start with后面的字段可看作父字段*/
select level col1,col2,col3,col4 from table start with col4=5 connect by prior col4=col1;

########################## 扩展的DDL和DML语句(9i) ############################

/*多表的条件插入: insert 、ALL insert 、FIRST insert 、privoting insert(旋转插入)*/
/*语法：无条件多表插入*/
insert [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery);

example:
INSERT ALL
     INTO table1 values(col1,col2,col3)
     INTO table2 values(col1,col4,col3)
     SELECT col1,col2,col3,col4 from table3 where col5>;200;


/*语法：有条件多表插入*/
INSERT [ALL][FRIST]                           
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause];

example:
INSERT ALL
     WHEN col1>;10000 THEN
        INTO table1 values(col1,col2,col3)
     WHEN col2>;200 THEN
        INTO table2 values(col1,col4,col3)
     SELECT col1,col2,col3,col4 from table3 where col5>;200;

INSERT FIRST
     WHEN col2 >; 25000 THEN
        INTO table1 values(col1,col2)
     WHEN col3 like ('%00%') THEN
        INTO table2 values(col1,col3)
     WHEN col3 like (%99%) THEN
        INTO table3 values(col1,col3)
     ELSE
     INTO table4 values(col1,col3)
     SELECT col1,col2,col3 from table5 where .... group by ... ;

/*旋转插入*/
example:
INSERT ALL
   INTO table1 values(col1,col2,col3)
   INTO table1 values(col1,col2,col4)
   INTO table1 values(col1,col2,col5)
   INTO table1 values(col1,col2,col6)
SELECT col1,col2,col3,col4,col5,col6 from table2 where ... [group by ...];

/*创建外部表  External Tables,象访问表一样，读取外部的文本文件*/
/*语法*/
example:
CREATE DIRECTORY dir_name AS '/fs1/sdafa/afda';

CREATE TABLE table (
col1 number,
col2 char(20),
col3 date
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir_name
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_file1'
LOGFILE 'log_file1'
FIELDS TERMINATED BY ','
(col1 CHAR,
col2 CHAR,
col3 CHAR date_format date mask "dd-mon-yyyy")
LOCATION ('src_file1.txt'))
PARALLEL 5
REJECT LIMIT 200;

please

9i新增的高级SQL语句语法