oracle数据库ppt+中科院培训专用Les20_cn.ppt

1、,Oracle9i 对 DML 和 DDL 语句的扩展,目标,完成本课后, 您应当能够执行下列操作： 描述多表插入的特性 使用下面的多表插入类型 无条件 INSERT 枢轴式 INSERT 条件 ALL INSERT 条件 FIRST INSERT 创建和使用外部表 在创建主键约束时命名索引,INSERT 语句回顾,用 INSERT 语句添加新行到表中 用此语法一次只能插入一行,INSERT INTOtable (column , column.) VALUES(value , value.);,INSERT INTO departments(department_id, department

2、_name, manager_id, location_id) VALUES (70, Public Relations, 100, 1700); 1 row created.,UPDATE 语句回顾,用 UPDATE 语句修改存在的行 如果需要，一次可以修改多行 如果使用了 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 语句的一部分用于插入行到多表中 多表 INSERT 语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中 下面的方法提供重要的性能改进： 单个 DML 与多 INSERT.SELECT 语句相对 单个 DML 与一个用 IF.THEN 语法做多插入的过程相对,Hidden Slide,多表 INSERT 语句的类型,Oracle9i 引入下面的多表插入语句的类型： 无条件 INSERT 条件 ALL INSERT 条件 FIRST INSERT 枢轴式 (Pivoting) I

4、NSERT,多表 INSERT 语句,INSERT ALL conditional_insert_clause insert_into_clause values_clause (subquery),ALL FIRST WHEN condition THEN insert_into_clause values_clause ELSE insert_into_clause values_clause,conditional_insert_clause,语法,Hidden Slide,INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) IN

5、TO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200; 8 rows created.,无条件 INSERT ALL,从 EMPLOYEES 中选择 EMPLOYEE_ID 大于200的雇员的 EMPLOYEE_ID, HIRE_DATE, SALARY, 和 MANAGER_ID 值 用多表 INSERT 插入这些值到 SAL_HISTORY 和 MGR_

6、HISTORY 表中,条件 INSERT ALL,从 EMPLOYEES 表中选择 EMPLOYEE_ID 大于200的那些雇员的 EMPLOYEE_ID, HIRE_DATE, SALARY 和 MANAGER_ID 值 如果 SALARY 大于 $10,000，用一个条件多表 INSERT语句插入这些值到 SAL_HISTORY 表中 如果 MANAGER_ID 大于 200，用一个多表 INSERT 语句插入这些值到 MGR_HISTORY 表中,条件 INSERT ALL,INSERT ALL WHEN SAL 10000 THEN INTO sal_history VALUES(EM

7、PID,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; 4 rows created.,条件 FIRST INSERT,从 EMPLOYEES 表中选择 DEPARTMENT_ID, SUM(SALARY) 和 MAX(HIRE_DATE) 如果 SUM(SALARY) 大于 $25,

8、000 则用一个条件 FIRST 多表 INSERT 插入这些值到 SPECIAL_SAL 表中 如果第一个 WHEN 子句的值为 true，则该行的后面的 WHEN 子句被跳过 对于那些不满足第一个 WHEN 条件的行，用一个条件多表 INSERT 基于 HIRE_DATE 列的值插入 HIREDATE_HISTORY_00, 或 HIREDATE_HISTORY_99, 或 HIREDATE_HISTORY 表。,条件 FIRST INSERT,INSERT FIRST WHEN SAL 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHE

9、N 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

10、 BY department_id; 8 rows created.,枢轴式 (Pivoting) INSERT,支持从非关系数据库表中接受一组销售记录， SALES_SOURCE_DATA 的格式如下： EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI 你可能想要以一种典型的相关格式存储这些记录到 SALES_INFO 表中： EMPLOYEE_ID, WEEK, SALES 使用 pivoting INSERT，从非关系数据库表转换销售记录集到关系格式,枢轴式 (Pivoting) IN

11、SERT,INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO 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

12、EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data; 5 rows created.,Hidden Slide,外部表,外部表是只读表，在外部表中数据被存储在数据库外面的文件中 用 CREATE TABLE 语句创建外部表的元数据 借助外部表的帮助，Oracle 数据能够被作为文件存储或卸载 数据能够用 SQL 查询，但你不能用 DML 并且不能创建索引,创建外部表,与 CREATE TABLE 语法一起用 external_table_clause

13、 创建一个外部表 指定 ORGANIZATION 作为 EXTERNAL 来指出表是位于数据库之外的 external_table_clause 由访问驱动 TYPE, external_data_properties 和 REJECT LIMIT 组成 external_data_properties 由下面的部分组成： 默认目录 访问参数 位置,创建外部表的例子,创建一个 DIRECTORY 对象，它指出外部数据源所在的文件系统目录,CREATE DIRECTORY emp_dir AS /flat_files ;,创建外部表的例子,CREATE TABLE oldemp ( empno

14、NUMBER, empname 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 (

15、emp1.txt) PARALLEL 5 REJECT LIMIT 200; Table created.,Hidden Slide,查询外部表,SELECT * FROM oldemp,emp1.txt,用 CREATE TABLE 语句创建索引,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 INDEX_NAME, TABLE_NAME FROM USER_INDEXESWHERE TABLE_NAME = NEW_EMP;,小结,在本课中, 您