oracle数据库操作和事务处理

Oracle数据库Oracle数据库操作和事务处理邢慧芬第5章SQL语言基础及调优5.1SQL语言简介5.2用户模式5.3检索数据5.4连接查询5.5Oracle常用系统函数5.6子查询的用法5.7操作数据库5.8事务处理5.9常规SQL语句优化5.10小结本讲大纲：插入数据更新数据删除数据本讲大纲：插入数据更新数据删除数据插入数据（INSERT语句）插入数据就是将数据记录添加到已经存在的数据表中，Oracle数据库通过INSERT语句来实现插入数据记录。该语句既可以实现向数据表中一次插入一条记录，也可以使用SELECT子句将查询结果集批量插入数据表。插入数据（INSERT语句）单条插入数据是INSERT语句最基本的用法，其用法格式如下：INSERTINTOtable_name[(column_name1[,column_name2]…)]VALUES(express1[,express2]…)插入数据（INSERT语句）【例1】在dept表中，插入一条记录。INSERTINTOdept(deptno,dname,loc)VALUES(40,’DESIGN’,’Beijing’)；能否插入成功？插入数据（INSERT语句）INSERTINTO语句不但可以将一条数据记录插入到表中，也可以通过使用子查询的形式将一个数据记录集插入到表中，实现批量插入。插入数据（INSERT语句）INSERTINTOSELECT语句语句形式为：InsertintoTable2(field1,field2,...)selectvalue1,value2,...fromTable1插入数据（INSERT语句）【例2】求每个部门的平均工资，并将结果保存到数据库中。（1）在数据库中建立一个有两个属性列的新表，其中一列存放部门号别名，另一列存放相应部门的员工的平均工资。CREATETABLEAvgSal(Dept_IDnumber(2),Sal_Avgnumber(7,2));其中Dept_ID代表部门类编号，Sal_Avg代表平均工资。插入数据（INSERT语句）【例2】求每个部门的平均工资，并将结果保存到数据库中。（2）对数据库的员工表按部门号分组求平均工资，再把部门号和平均工资存入新表中。INSERTINTOAvgSal(Dept_ID,Sal_Avg)SELECTdeptno,AVG(sal)FROMempGROUPBYdeptno;（3）查看表AvgSal表中的记录。SELECT*FROMAvgSal;更新数据（UPDATE语句）在更新数据时，更新的列数可以由用户自己指定，列于列之间用逗号（“,”）分隔；更新的条数可以通过WHERE子句来加以限制，使用WHERE子句时，系统只更新符合WHERE条件的记录信息。更新数据（UPDATE语句）UPDATE语句的语法格式如下：UPDATEtable_nameSET{column_name1=express1[,column_name2=express2...]|(column_name1[,column_name2…])=(selectSubquery)}[WHEREcondition]更新数据（UPDATE语句）【例3】把emp表中职务是’SALESMAN’的工资上调20%。UPDATEempSETsal=sal*1.2WHEREjob=‘SALESMAN’;更新数据（UPDATE语句）在UPDATE语句中使用子查询，可以通过构造复杂的更新操作条件来实现更新数据记录的操作。【例4】将和SCOTT一个部门的所有员工工资上调20%。UPDATEempSETsal=sal*1.2WHEREdeptnoin(SELECTdeptnoFROMempWHEREename=‘SCOTT’);再使用查询语句：SELECTename,sal,deptnoFROMemp;更新数据（UPDATE语句）在UPDATE语句中的set子句中使用子查询。【例4】将SMITH调任到ALLEN所在的部门，其工作和工资、奖金和都和ALLEN一样updateempt1set(t1.job,t1.sal,t1.deptno,t1.comm)=(selectt2.job,t2.sal,t2.deptno,t2.commfromempt2wheret2.ename=‘ALLEN’)WHEREename=‘SMITH’;再使用查询语句：SELECTename,sal,deptnoFROMemp;更新数据（UPDATE语句）在UPDATE语句中set子句中使用子查询，实现批量修改。【例4】将各部门员工的工资修改为该员工所在部门平均工资加500。updateempt1sett1.sal=(selectnew_salfrom(selectavg(sal)+500new_sal,deptnofromempgroupbydeptno)t2wheret1.deptno=t2.deptno);updateempt1sett1.sal=(selectavg(sal)+500new_salfromempt2wheret2.deptno=t1.deptno)再使用查询语句：SELECTename,sal,deptnoFROMemp;删除数据（DELETE语句）DELETE语句用来删除数据库中的所有记录和指定范围的记录，若要删除指定范围的记录，同UPDATE语句一样，要通过WHERE子句进行限制，其语法格式如下：DELETEFROMtable_name[WHEREcondition]删除数据（DELETE语句）【例5】把emp表中empno=7369的员工信息删除。DELETEFROMempWHEREempno=7369;删除数据（DELETE语句）在DELETEFROM语句中使用子查询，可以通过构造复杂删除操作条件来实现删除数据记录的操作。【例6】删除和SCOTT一个部门的所有员工信息。DELETEFROMempWHEREdeptnoin(SELECTdeptnoFROMempWHEREename=‘SCOTT’);删除数据（TRUNCATE语句）如果用户确定要删除表中的所有记录，则除了可以使用DELETE语句之外，还可以使用TRUNCATE语句，而且Oracle本身也建议使用TRUNCATE语句。使用TRUNCATE语句删除表中的所有记录要比DELETE语句快得多。这是因为使用TRUNCATE语句删除数据时，它不会产生回滚记录。当然，执行了TRUNCATE语句的操作也就无法使用ROLLBACK语句撤销。删除数据（TRUNCATE语句）语法格式：Truncate[table]table_name[drop|reusestorage];参数说明：dropstorage：删除表中的所有数据并不保存占用空间，为默认值reusestorage：删除表中的所有数据并保存占用空间【例7】TRUNCATEemp;删除数据（TRUNCATE语句）TRUNCATE语句和DELETE语句的异同：相同点：TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。删除数据（TRUNCATE语句）TRUNCATE语句和DELETE语句的异同：不同点：DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATETABLE则一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，而是在事务日志中只记录页释放，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。删除数据（TRUNCATE语句）说明：TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变如果要删除表定义及其数据，请使用DROPTABLE语句TRUNCATE是一个DDL，像其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令在SQL*PLUS环境下，直接采用TRUNCATETABLE命令即可，要在PL/SQL中使用，只能采用动态语句实现删除数据（TRUNCATE语句）创建一个存储过程，实现使用truncate命令动态删除数据表，代码及运行结果如下：createorreplaceproceduretrun_table(table_deletedinvarchar2)as--创建一个存储过程，传入一个表示表名称的参数，实现清空指定的表cur_nameinteger;--定义内部变量，存储打开的游标begincur_name:=dbms_sql.open_cursor;--打开游标dbms_sql.parse(cur_name,'truncatetable'||table_deleted||'dropstorage',dbms_sql.native);--执行truncatetabletb_name命令，从而实现清空指定的表dbms_sql.close_cursor(cur_name);--关闭游标exceptionwhenothersthendbms_sql.close_cursor(cur_name);--出现异常，关闭游标raise;endtrun_table;第5章SQL语言基础及调优5.1SQL语言简介5.2用户模式5.3检索数据5.4连接查询5.5Oracle常用系统函数5.6子查询的用法5.7操作数据库5.8事务处理5.9常规SQL语句优化5.10小结5.8事务处理1、什么是事务2、如何定义事务3、事务的特性4、操作事务5.8事务概述1、什么是事务2、如何定义事务3、事务的特性4、操作事务1、什么是事务事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位事务和程序是两个概念在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序一个应用程序通常包含多个事务事务是恢复和并发控制的基本单位5.8事务概述1、什么是事务2、如何定义事务3、事务的特性4、操作事务二、如何定义事务显式定义方式BEGINTRANSACTIONBEGINTRANSACTIONSQL语句1SQL语句1SQL语句2SQL语句2。。。。。。。。。。COMMITROLLBACK隐式方式当用户没有显式地定义事务时，DBMS按缺省规定自动划分事务事务结束COMMIT事务正常结束提交事务的所有操作（读+更新）事务中所有对数据库的更新永久生效ROLLBACK事务异常终止事务运行的过程中发生了故障，不能继续执行回滚事务的所有更新操作事务滚回到开始时的状态5.8事务概述1、什么是事务2、如何定义事务3、事务的特性4、操作事务三、事务的特性(ACID特性)事务的ACID特性：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持续性（Durability）原子性事务是数据库的逻辑工作单位事务中包括的诸操作要么都做，要么都不做2.一致性事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态一致性状态：数据库中只包含成功事务提交的结果不一致状态：数据库中包含失败事务的结果一致性与原子性银行转帐：从帐号A中取出一万元，存入帐号B。定义一个事务，该事务包括两个操作这两个操作要么全做，要么全不做全做或者全不做，数据库都处于一致性状态。如果只做一个操作，数据库就处于不一致性状态。B=B+1A=A-1BA3.隔离性对并发执行而言一个事务的执行不能被其他事务干扰一个事务内部的操作及使用的数据对其他并发事务是隔离的并发执行的各个事务之间不能互相干扰T1的修改被T2覆盖了！读A=16A←A-3写回A=13①读A=16②③A←A-1写回A=15④T2T14.持续性