oracle9i中insert 語句新增特性(轉)

oracle9i中insert 語句新增特性(轉)[@more@]1、無條件insert all 語法示例(將查詢出的資料全部插入到指定的表格中):
insert all
into emp_name(emp_id,lastname,firstname) values
(employeeid,lastname,firstname)
into emp_addr(emp_id,address)values (employeeid,
address)
select employeeid,lastname,firstname,address
from employees where employeeid>3
2、有條件insert all語法示例( sal >10000 可以換成其他的條件例如like 子句)
insert all
when sal>10000 then
insert into history_sal values(empid,hireadate,sal)
when sal >200 then
insert into mgr_history values (empid,mgr,sal)
select employeeid empid,hire_date hiredate,salary sal,
manager_id mgr
from employees
where employee_id>200;
3、有條件的insert first語句表示首先校驗第一個條件（first）如果第
一個條件滿足,其它條件就不再進行判定和執行，如果第一個條件不滿足
就進入一個多條件when的判定的條件中。例如：
insert first
when sal >25000 then
into special_sal values (deptid,sal)
when hiredate like ('%00%') then
into hiredate_history_00 values (deptid,hiredate)
when hiredate like '%99%' then
into hiredate_history_99 values (deptid,hiredate)
else
into hiredate_history values (deptid,hiredate)
select department_id deptid,sum(salary) sal ,
max(hire_date ) hiredate
from employees
group by department_id ;


建立外部擴充套件表，即使用外部檔案的資料作為資料表中的資料，實際查詢的結果
是外部檔案的資料內容
1、建立資料檔案的目錄
create directory emp_dir as 'flat_files';
2、根據檔案建立資料表的結構
create table oldemp
(empno,number,empname char(200),birthdate date)
organization external
(type oracle_loader
default dircetory emp_dir
access parameters
(records delimited by newline
badfile 'bad_emp'
logfile 'log_emp'
fields terminated by ','
(empno char,
empname char ,
birthdate char date format date mask 'dd-mon-yyyy'))
location ('emp1.txt')
parallel 5
reject limit 200;
3、可以使用select * from oldemp進行讀取資料了

關聯更新語句
update test2 set address =(select '2' from test1 where test1.name=test2.name and
test1.xh='1005') where test2.xh ='1005';