Oracle_9i详解之处理数据

ORACLE_9I详解之处理数据目标通过本章学习，您将可以:使用DML语句向表中插入数据更新表中数据从表中删除数据将表中数据和并控制事务数据控制语言DML可以在下列条件下执行:向表中插入数据修改现存数据删除现存数据事务是由完成若干项工作的DML语句组成的。插入数据DEPARTMENTS新行向DEPARMENTS表中插入新的记录INSERT语句语法使用INSERT语句向表中插入数据。使用这种语法一次只能向表中插入一条数据。INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);插入数据为每一列添加一个新值。按列的默认顺序列出各个列的值。在INSERT子句中随意列出列名和他们的值。字符和日期型数据应包含在单引号中。INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);1rowcreated.INSERTINTOdepartmentsVALUES(100,'Finance',NULL,NULL);1rowcreated.INSERTINTOdepartments(department_id,department_name)VALUES(30,'Purchasing');1rowcreated.向表中插入空值隐式方式:在列名表中省略该列的值。•显示方式:在VALUES子句中指定空值。INSERTINTOemployees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)VALUES(113,'Louis','Popp','LPOPP','515.124.4567',SYSDATE,'AC_ACCOUNT',6900,NULL,205,100);1rowcreated.插入指定的值SYSDATE记录当前系统的日期和时间。INSERTINTOemployeesVALUES(114,'Den','Raphealy','DRAPHEAL','515.127.4561',TO_DATE('FEB3,1999','MONDD,YYYY'),'AC_ACCOUNT',11000,NULL,100,30);1rowcreated.插入指定的值加入新员工检查插入的数据INSERTINTOdepartments(department_id,department_name,location_id)VALUES(&department_id,'&department_name',&location);创建脚本在SQL语句中使用&变量指定列值。&变量放在VALUES子句中。1rowcreated.从其它表中拷贝数据在INSERT语句中加入子查询。不必书写VALUES子句。子查询中的值列表应于INSERT子句中的列名对应。INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';4rowscreated.更新数据EMPLOYEES更新EMPLOYEES表UPDATE语句语法使用UPDATE语句更新数据。可以一次更新多条数据。UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];UPDATEemployeesSETdepartment_id=70WHEREemployee_id=113;1rowupdated.更新数据使用WHERE子句指定需要更新的数据。如果省略WHERE子句，则表中的所有数据都将被更新。UPDATEcopy_empSETdepartment_id=110;22rowsupdated.UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.在UPDATE语句中使用子查询更新114号员工的工作和工资使其与205号员工相同。UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=200);1rowupdated.在UPDATE语句中使用子查询在UPDATE中使用子查询，使更新基于另一个表中的数据。UPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;更新中的数据完整性错误从表DEPARTMENTS中删除一条记录。删除数据DEPARTMENTSDELETE语句使用DELETE语句从表中删除数据。DELETE[FROM]table[WHEREcondition];删除数据使用WHERE子句指定删除的记录。如果省略WHERE子句，则表中的全部数据将被删除。DELETEFROMdepartmentsWHEREdepartment_name='Finance';1rowdeleted.DELETEFROMcopy_emp;22rowsdeleted.DELETEFROMemployeesWHEREdepartment_id=(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE'%Public%');1rowdeleted.在DELETE中使用子查询在DELETE中使用子查询，使删除基于另一个表中的数据。删除中的数据完整性错误DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfound在INSERT语句中使用子查询INSERTINTO(SELECTemployee_id,last_name,email,hire_date,job_id,salary,department_idFROMemployeesWHEREdepartment_id=50)VALUES(99999,'Taylor','DTAYLOR',TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK',5000,50);1rowcreated.在INSERT语句中使用子查询VerifytheresultsSELECTemployee_id,last_name,email,hire_date,job_id,salary,department_idFROMemployeesWHEREdepartment_id=50;在DML语句中使用WITHCHECKOPTION关键字使用子查询表示DML语句中使用的表WITHCHECKOPTION关键字避免修改子查询范围外的数据INSERTINTO(SELECTemployee_id,last_name,email,hire_date,job_id,salaryFROMemployeesWHEREdepartment_id=50WITHCHECKOPTION)VALUES(99998,'Smith','JSMITH',TO_DATE('07-JUN-99','DD-MON-RR'),'ST_CLERK',5000);INSERTINTO*ERRORatline1:ORA-01402:viewWITHCHECKOPTIONwhere-clauseviolation显式默认值使用DEFAULT关键字表示默认值可以使用显示默认值控制默认值的使用显示默认值可以在INSERT和UPDATE语句中使用显示使用默认值在插入操作中使用默认值:在更新操作中使用默认值:INSERTINTOdepartments(department_id,department_name,manager_id)VALUES(300,'Engineering',DEFAULT);UPDATEdepartmentsSETmanager_id=DEFAULTWHEREdepartment_id=10;合并语句按照指定的条件执行插入或更新操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGEINTOtable_nametable_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,e.commission_pct,e.manager_id,e.department_id);合并语句举例在对表COPY_EMP使用merge语句，根据指定的条件从表EMPLOYEES中插入或更新数据。合并语句举例MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESET...WHENNOTMATCHEDTHENINSERTVALUES...;SELECT*FROMCOPY_EMP;norowsselectedSELECT*FROMCOPY_EMP;20rowsselected.数据库事务数据库事务由以下的部分组成:一个或多个DML语句一个DDL语句一个DCL语句数据库事务以第一个DML语句的执行作为开始以下面的其中之一作为结束:COMMIT或ROLLBACK语句DDL或DCL语句（自动提交）用户会话正常结束系统异常终了COMMIT和ROLLBACK语句的优点使用COMMIT和ROLLBACK语句,我们可以:确保数据完整性。数据改变被提交之