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

Copyright©OracleCorporation,2001.Allrightsreserved.Oracle9i对DML和DDL语句的扩展20-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够执行下列操作：•描述多表插入的特性•使用下面的多表插入类型–无条件INSERT–枢轴式INSERT–条件ALLINSERT–条件FIRSTINSERT•创建和使用外部表•在创建主键约束时命名索引20-3Copyright©OracleCorporation,2001.Allrightsreserved.INSERT语句回顾•用INSERT语句添加新行到表中•用此语法一次只能插入一行INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);1rowcreated.20-4Copyright©OracleCorporation,2001.Allrightsreserved.UPDATE语句回顾•用UPDATE语句修改存在的行•如果需要，一次可以修改多行•如果使用了WHERE字句，可以指定一行或多行被修改UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEemployeesSETdepartment_id=70WHEREemployee_id=142;1rowupdated.20-5Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句概述•INSERT...SELECT语句能够作为单个的DML语句的一部分用于插入行到多表中•多表INSERT语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中•下面的方法提供重要的性能改进：–单个DML与多INSERT...SELECT语句相对–单个DML与一个用IF...THEN语法做多插入的过程相对20-7Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句的类型Oracle9i引入下面的多表插入语句的类型：•无条件INSERT•条件ALLINSERT•条件FIRSTINSERT•枢轴式(Pivoting)INSERT20-8Copyright©OracleCorporation,2001.Allrightsreserved.多表INSERT语句INSERT[ALL][conditional_insert_clause][insert_into_clausevalues_clause](subquery)[ALL][FIRST][WHENconditionTHEN][insert_into_clausevalues_clause][ELSE][insert_into_clausevalues_clause]conditional_insert_clause语法20-10Copyright©OracleCorporation,2001.Allrightsreserved.INSERTALLINTOsal_historyVALUES(EMPID,HIREDATE,SAL)INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;8rowscreated.无条件INSERTALL•从EMPLOYEES中选择EMPLOYEE_ID大于200的雇员的EMPLOYEE_ID,HIRE_DATE,SALARY,和MANAGER_ID值•用多表INSERT插入这些值到SAL_HISTORY和MGR_HISTORY表中20-11Copyright©OracleCorporation,2001.Allrightsreserved.条件INSERTALL•从EMPLOYEES表中选择EMPLOYEE_ID大于200的那些雇员的EMPLOYEE_ID,HIRE_DATE,SALARY和MANAGER_ID值•如果SALARY大于$10,000，用一个条件多表INSERT语句插入这些值到SAL_HISTORY表中•如果MANAGER_ID大于200，用一个多表INSERT语句插入这些值到MGR_HISTORY表中20-12Copyright©OracleCorporation,2001.Allrightsreserved.条件INSERTALLINSERTALLWHENSAL10000THENINTOsal_historyVALUES(EMPID,HIREDATE,SAL)WHENMGR200THENINTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;4rowscreated.20-13Copyright©OracleCorporation,2001.Allrightsreserved.条件FIRSTINSERT•从EMPLOYEES表中选择DEPARTMENT_ID,SUM(SALARY)和MAX(HIRE_DATE)•如果SUM(SALARY)大于$25,000则用一个条件FIRST多表INSERT插入这些值到SPECIAL_SAL表中•如果第一个WHEN子句的值为true，则该行的后面的WHEN子句被跳过•对于那些不满足第一个WHEN条件的行，用一个条件多表INSERT基于HIRE_DATE列的值插入HIREDATE_HISTORY_00,或HIREDATE_HISTORY_99,或HIREDATE_HISTORY表。20-14Copyright©OracleCorporation,2001.Allrightsreserved.条件FIRSTINSERTINSERTFIRSTWHENSAL25000THENINTOspecial_salVALUES(DEPTID,SAL)WHENHIREDATElike('%00%')THENINTOhiredate_history_00VALUES(DEPTID,HIREDATE)WHENHIREDATElike('%99%')THENINTOhiredate_history_99VALUES(DEPTID,HIREDATE)ELSEINTOhiredate_historyVALUES(DEPTID,HIREDATE)SELECTdepartment_idDEPTID,SUM(salary)SAL,MAX(hire_date)HIREDATEFROMemployeesGROUPBYdepartment_id;8rowscreated.20-15Copyright©OracleCorporation,2001.Allrightsreserved.枢轴式(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•使用pivotingINSERT，从非关系数据库表转换销售记录集到关系格式20-16Copyright©OracleCorporation,2001.Allrightsreserved.枢轴式(Pivoting)INSERTINSERTALLINTOsales_infoVALUES(employee_id,week_id,sales_MON)INTOsales_infoVALUES(employee_id,week_id,sales_TUE)INTOsales_infoVALUES(employee_id,week_id,sales_WED)INTOsales_infoVALUES(employee_id,week_id,sales_THUR)INTOsales_infoVALUES(employee_id,week_id,sales_FRI)SELECTEMPLOYEE_ID,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRIFROMsales_source_data;5rowscreated.20-18Copyright©OracleCorporation,2001.Allrightsreserved.外部表•外部表是只读表，在外部表中数据被存储在数据库外面的文件中•用CREATETABLE语句创建外部表的元数据•借助外部表的帮助，Oracle数据能够被作为文件存储或卸载•数据能够用SQL查询，但你不能用DML并且不能创建索引20-19Copyright©OracleCorporation,2001.Allrightsreserved.创建外部表•与CREATETABLE语法一起用external_table_clause创建一个外部表•指定ORGANIZATION作为EXTERNAL来指出表是位于数据库之外的•external_table_clause由访问驱动TYPE,external_data_properties和REJECTLIMIT组成•external_data_properties由下面的部分组成：–默认目录–访问参数–位置20-20Copyright©OracleCorporation,2001.Allrightsreserved.创建外部表的例子创建一个DIRECTORY对象，它指出外部数据源所在的文件系统目录CREATEDIRECTORYemp_dirAS'/flat_files';20-21Copyright©OracleCorporation,2001.Allrightsreserved.创建外部表的例子CREATETABLEoldemp(empnoNUMBER,empnameCHAR(20),birthdateDATE)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYemp_dirACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINEBADFILE'bad_emp'LOGFILE'log_emp'FIELDSTERMINATEDBY','(empnoCHAR,empnameCHAR,birthdateCHARdate_formatdatemaskdd-mon-yyyy))LOCATION('emp1.txt'))PARALLEL5REJECTLIMIT200;Tablecreated.20-23Copyright©OracleCorporation,2001.Allrightsreserved.查询外部表SELECT*FROMoldempemp1.txt20-24Copyright©OracleCorporation,2001.Allrightsreserved.用CREATETABLE语句创建索引CREATETABLENEW_EMP(empl