Les20_chinese(Oracle公司內(nèi)部數(shù)據(jù)庫培訓(xùn)資料)

1、oracle9i 對 dml 和 ddl 語句的擴(kuò)展,目標(biāo),通過本章學(xué)習(xí)，您將可以: 描述多表插入的特點(diǎn) 使用不同類型的多表插入 無條件的insert 旋轉(zhuǎn) insert 有條件的 all insert 有條件的 first insert 創(chuàng)建和使用外部表 創(chuàng)建主鍵約束的同時創(chuàng)建索引,insert 語句,使用 insert 語句向表中插入新的數(shù)據(jù) 使用上面的語句每次只能向表中插入一行數(shù)據(jù),insert intotable (column , column.) values(value , value.,insert into departments(department_id, departm

2、ent_name, manager_id, location_id) values (70, public relations, 100, 1700); 1 row created,update 語句,使用update 語句更新表中的數(shù)據(jù) 使用上面的語句每次可更新表中的一行或多行數(shù)據(jù) 使用 where 子句指定更新的條件,updatetable setcolumn = value , column = value, . where condition,update employees set department_id = 70 where employee_id = 142; 1 row u

3、pdated,多表 insert 語句,insert.select 是使用一個dml 語句向多個表中插入數(shù)據(jù)的一部分 多表insert 語句可作為數(shù)據(jù)倉庫應(yīng)用中向目標(biāo)數(shù)據(jù)庫傳送數(shù)據(jù)的一種方法 它具有更高的效率: 避免使用多各dml 語句 使用一個dml 完成 if.then 的邏輯處理,多表 insert 語句的類型,oracle9i 提供以下四種多表insert 語句類型: 無條件的 insert 有條件的 all insert 有條件的 first insert 旋轉(zhuǎn) insert,多表 insert 語句,insert all conditional_insert_clause inse

4、rt_into_clause values_clause (subquery,all first when condition then insert_into_clause values_clause else insert_into_clause values_clause,conditional_insert_clause,語法,無條件的 insert all 應(yīng)用舉例,insert all into sal_history values(empid,hiredate,sal) into mgr_history values(empid,mgr,sal) select employee_

5、id empid, hire_date hiredate, salary sal, manager_id mgr from employees where employee_id 200; 8 rows created,有條件的 insert all 應(yīng)用舉例,insert all when sal 10000 then into sal_history values(empid,hiredate,sal) when mgr 200 then into mgr_history values(empid,mgr,sal) select employee_id empid,hire_date hi

6、redate, salary sal, manager_id mgr from employees where employee_id 200; 4 rows created,有條件的 first insert 應(yīng)用舉例,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 hir

7、edate_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; 8 rows created,旋轉(zhuǎn) insert 應(yīng)用舉例,insert all into sales_info values (employee_id,week_id,sales_mon) i

8、nto sales_info values (employee_id,week_id,sales_tue) into sales_info values (employee_id,week_id,sales_wed) into sales_info values (employee_id,week_id,sales_thur) into sales_info values (employee_id,week_id, sales_fri) select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thur,sales_

9、fri from sales_source_data; 5 rows created,外部表,外部表是只讀的表，其數(shù)據(jù)存儲在數(shù)據(jù)庫外的平面文件中 外部表的各種參數(shù)在 create table 語句中指定 使用外部表, 數(shù)據(jù)可以存儲到外部文件或從外部文件中上載數(shù)據(jù)到數(shù)據(jù)庫 數(shù)據(jù)可以使用 sql訪問, 但不能使用 dml 后在外部表上創(chuàng)建索引,創(chuàng)建路徑,創(chuàng)建外部表之前應(yīng)先使用create directory語句創(chuàng)建路徑,create directory emp_dir as /flat_files,創(chuàng)建外部表舉例,create table oldemp ( empno number, empnam

10、e char(20), birthdate date) organization external (type oracle_loader default directory 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; table created,查詢外部表,select * from oldemp,emp1.txt,創(chuàng)建主鍵約束同時創(chuàng)建索引舉例,create table new_emp (employee_id number(6) primary key using index (create index emp_id_idx on new_emp(employee_id), first_name varchar2(20),last_name varchar2(25); table created,select ind