资源描述
Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,20-,17,Copyright Oracle Corporation, 2001. All rights reserved.,Oracle9,i,对,DML,和,DDL,语句的扩展,目标,通过本章学习，您将可以,:,描述多表插入的特点,使用不同类型的多表插入,无条件的,INSERT,旋转,INSERT,有条件的,ALL INSERT,有条件的,FIRST INSERT,创建和使用外部表,创建主键约束的同时创建索引,INSERT,语句,使用,INSERT,语句向表中插入新的数据,使用上面的语句每次只能向表中插入一行数据,INSERT INTO,table,(,column, column.,),VALUES,(value, value.,);,INSERT INTO departments(department_id, department_name,manager_id, location_id),VALUES (70, Public Relations, 100, 1700);,1 row created.,UPDATE,语句,使用,UPDATE,语句更新表中的数据,使用上面的语句每次可更新表中的一行或多行数据,使用,WHERE,子句指定更新的条件,UPDATE,table,SET,column,=,value,column,=,value, .,WHERE,condition,;,UPDATE employees,SET department_id = 70,WHERE employee_id = 142;,1 row updated.,多表,INSERT,语句,INSERT.SELECT,是,使用一个,DML,语句向多个表中插入数据的一部分,多表,INSERT,语句可作为数据仓库应用中向目标数据库传送数据的一种方法,它具有更高的效率:,避免使用多各,DML,语句,使用一个,DML,完成,IF.THEN,的逻辑处理,多表,INSERT,语句的类型,Oracle9,i,提供以下四种多表,INSERT,语句类型:,无条件的,INSERT,有条件的,ALL INSERT,有条件的,FIRST INSERT,旋转,INSERT,多表,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,语法,无条件的,INSERT ALL,应用举例,INSERT 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;,8 rows created.,有条件的,INSERT ALL,应用举例,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 HIREDATE,salary SAL, manager_id MGR,FROM employees WHERE employee_id 200;,4 rows created.,有条件的,FIRST INSERT,应用举例,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;,8 rows created.,旋转,INSERT,应用举例,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 EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data;,5 rows created.,外部表,外部表是只读的表，其数据存储在数据库外的平面文件中,外部表的各种参数在,CREATE TABLE,语句中指定,使用外部表,数据可以存储到外部文件或从外部文件中上载数据到数据库,数据可以使用,SQL,访问, 但不能使用,DML,后在外部表上创建索引,创建路径,创建外部表之前应先使用,CREATE DIRECTORY,语句创建路径,CREATE DIRECTORY emp_dir AS /flat_files ;,创建外部表举例,CREATE TABLE oldemp ( empno 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 (emp1.txt) PARALLEL 5 REJECT LIMIT 200;,Table created.,查询外部表,SELECT * FROM oldemp,emp1.txt,创建主键约束同时创建索引举例,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;,总结,通过本章学习,您已经可以,:,使用多表插入代替多个单独的,DML,语句,创建外部表,创建主键约束同时创建索引,Hidden Slide,Hidden Slide,Hidden Slide,Hidden Slide,
展开阅读全文