1、20Copyright Oracle Corporation, 2001. All rights reserved.Oracle9i 對(duì)對(duì) DML 和和 DDL 語(yǔ)句的擴(kuò)展語(yǔ)句的擴(kuò)展 20-2Copyright Oracle Corporation, 2001. All rights reserved.目標(biāo)目標(biāo)通過(guò)本章學(xué)習(xí)，您將可以通過(guò)本章學(xué)習(xí)，您將可以:描述多表插入的特點(diǎn)描述多表插入的特點(diǎn)使用不同類(lèi)型的多表插入使用不同類(lèi)型的多表插入無(wú)條件的無(wú)條件的INSERT旋轉(zhuǎn)旋轉(zhuǎn) INSERT有條件的有條件的 ALL INSERT有條件的有條件的 FIRST INSERT創(chuàng)建和使用外部表創(chuàng)建和使用外部表

2、創(chuàng)建主鍵約束的同時(shí)創(chuàng)建索引創(chuàng)建主鍵約束的同時(shí)創(chuàng)建索引20-3Copyright Oracle Corporation, 2001. All rights reserved.INSERT 語(yǔ)句語(yǔ)句使用使用 INSERT 語(yǔ)句向表中插入新的數(shù)據(jù)語(yǔ)句向表中插入新的數(shù)據(jù)使用上面的語(yǔ)句每次只能向表中插入一行數(shù)據(jù)使用上面的語(yǔ)句每次只能向表中插入一行數(shù)據(jù)INSERT INTOtable (column , column.)VALUES(value , value.);INSERT INTO departments(department_id, department_name, manager_id, loc

3、ation_id)VALUES (70, Public Relations, 100, 1700);1 row created.20-4Copyright Oracle Corporation, 2001. All rights reserved.UPDATE 語(yǔ)句語(yǔ)句使用使用UPDATE 語(yǔ)句更新表中的數(shù)據(jù)語(yǔ)句更新表中的數(shù)據(jù)使用上面的語(yǔ)句每次可更新表中的一行或多行數(shù)據(jù)使用上面的語(yǔ)句每次可更新表中的一行或多行數(shù)據(jù) 使用使用 WHERE 子句指定更新的條件子句指定更新的條件UPDATEtableSETcolumn = value , column = value, .WHERE conditio

4、n;UPDATE employeesSET department_id = 70WHERE employee_id = 142;20-5Copyright Oracle Corporation, 2001. All rights reserved.多表多表 INSERT 語(yǔ)句語(yǔ)句INSERT.SELECT 是是使用一個(gè)使用一個(gè)DML 語(yǔ)句向多個(gè)表中語(yǔ)句向多個(gè)表中插入數(shù)據(jù)的一部分插入數(shù)據(jù)的一部分多表多表INSERT 語(yǔ)句可作為數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用中向目標(biāo)數(shù)據(jù)庫(kù)語(yǔ)句可作為數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用中向目標(biāo)數(shù)據(jù)庫(kù)傳送數(shù)據(jù)的一種方法傳送數(shù)據(jù)的一種方法它具有更高的效率它具有更高的效率:避免使用多各避免使用多各DML 語(yǔ)句語(yǔ)句

5、 使用一個(gè)使用一個(gè)DML 完成完成 IF.THEN 的邏輯處理的邏輯處理20-6Copyright Oracle Corporation, 2001. All rights reserved.多表多表 INSERT 語(yǔ)句的類(lèi)型語(yǔ)句的類(lèi)型Oracle9i 提供以下四種多表提供以下四種多表INSERT 語(yǔ)句類(lèi)型語(yǔ)句類(lèi)型: 無(wú)條件的無(wú)條件的 INSERT有條件的有條件的 ALL INSERT有條件的有條件的 FIRST INSERT旋轉(zhuǎn)旋轉(zhuǎn) INSERT20-7Copyright Oracle Corporation, 2001. All rights reserved.多表多表 INSERT 語(yǔ)句

6、語(yǔ)句INSERT ALL conditional_insert_clauseinsert_into_clause values_clause (subquery)ALL FIRSTWHEN condition THEN insert_into_clause values_clauseELSE insert_into_clause values_clause conditional_insert_clause語(yǔ)法語(yǔ)法20-8Copyright Oracle Corporation, 2001. All rights reserved.無(wú)條件的無(wú)條件的 INSERT ALL 應(yīng)用舉例應(yīng)用舉例INS

7、ERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200; 20-9Copyright Oracle Corporation, 2001. All rights reserved.有條件的有條件的 INSERT ALL 應(yīng)用舉例應(yīng)用舉例INSERT ALL

8、 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 HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200; 20-10Copyright Oracle Corporation, 2001. All rights reserved.有條件的有條件

9、的 FIRST INSERT 應(yīng)用舉例應(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 hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIR

10、EDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;20-11Copyright Oracle Corporation, 2001. All rights reserved.旋轉(zhuǎn)旋轉(zhuǎn) INSERT 應(yīng)用舉例應(yīng)用舉例 INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,w

11、eek_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_FRI FROM sales_source_data; 20-12Cop

12、yright Oracle Corporation, 2001. All rights reserved.外部表外部表外部表是只讀的表，其數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)外的平面文件中外部表是只讀的表，其數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)外的平面文件中外部表的各種參數(shù)在外部表的各種參數(shù)在 CREATE TABLE 語(yǔ)句中指定語(yǔ)句中指定使用外部表使用外部表, 數(shù)據(jù)可以存儲(chǔ)到外部文件或從外部文件中上載數(shù)據(jù)可以存儲(chǔ)到外部文件或從外部文件中上載數(shù)據(jù)到數(shù)據(jù)庫(kù)數(shù)據(jù)到數(shù)據(jù)庫(kù)數(shù)據(jù)可以使用數(shù)據(jù)可以使用 SQL訪問(wèn)訪問(wèn), 但不能使用但不能使用 DML 后在外部表上創(chuàng)后在外部表上創(chuàng)建索引建索引20-13Copyright Oracle Corpor

13、ation, 2001. All rights reserved.創(chuàng)建路徑創(chuàng)建路徑 創(chuàng)建外部表之前應(yīng)先使用創(chuàng)建外部表之前應(yīng)先使用CREATE DIRECTORYCREATE DIRECTORY語(yǔ)句創(chuàng)建路徑語(yǔ)句創(chuàng)建路徑CREATE DIRECTORY emp_dir AS /flat_files ;20-14Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建外部表舉例創(chuàng)建外部表舉例 CREATE TABLE oldemp ( empno NUMBER, empname CHAR(20), birthdate DATE) ORGAN

14、IZATION 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;20-15Copy

15、right Oracle Corporation, 2001. All rights reserved.查詢外部表查詢外部表 SELECT * FROM oldempemp1.txt20-16Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建主鍵約束同時(shí)創(chuàng)建索引舉例創(chuàng)建主鍵約束同時(shí)創(chuàng)建索引舉例 CREATE TABLE NEW_EMP(employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id),fir

16、st_name VARCHAR2(20),last_name VARCHAR2(25);SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXESWHERE TABLE_NAME = NEW_EMP;20-17Copyright Oracle Corporation, 2001. All rights reserved.總結(jié)總結(jié)通過(guò)本章學(xué)習(xí)通過(guò)本章學(xué)習(xí),您已經(jīng)可以您已經(jīng)可以:使用多表插入代替多個(gè)單獨(dú)的使用多表插入代替多個(gè)單獨(dú)的 DML 語(yǔ)句語(yǔ)句創(chuàng)建外部表創(chuàng)建外部表 創(chuàng)建主鍵約束同時(shí)創(chuàng)建索引創(chuàng)建主鍵約束同時(shí)創(chuàng)建索引20-18Copyright Oracle Corporation, 2001