第7章数据操纵与事务处理

第7章 数据操纵与事务处理主要内容数据插入数据修改数据合并数据删除事务控制7.1 数 据 插 入利用INSERT INTO语句插入数据利
用子查询插入数据向案例数据库表插入初始数据向多个表中插入数据7.1.1 利用INSERT INTO语句插入数据语法INSERT
INTO table_name|view_name [(column1[,column2…])] VALUES(value1
[,values, …]) 注意如果在INTO子句中没有指明任何列名，则VALUES子句中列值的个数、顺序、类型必须与表中列的个数
、顺序、类型相匹配。如果在INTO子句中指定了列名，则VALUES子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型
按位置对应。向表或视图中插入的数据必须满足表的完整性约束。字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入
，或使用TO_DATE函数进行日期转换。 向departments表中插入一行记录。INSERT INTO departments
VALUES(departments_seq.nextval, ''Teaching'',200,1800);向employees表中
插入一行记录。INSERT INTO employees(employee_id, last_name,email,hire_d
ate,job_id,department_id)VALUES(employees_seq.nextval,''sun'',''sfd@
neusoft.edu.cn'',sysdate,''AC_ACCOUNT'',200); 7.1.2 利用子查询插入数据语法INSE
RT INTO table_name|view_name[(column1[,column2,…])subquery;注意INTO
子句中指定的列的个数、顺序、类型必须与子查询中列的个数、顺序和类型相匹配。 统计各个部门的部门号、部门最高工资和最低工资，并将统计
的结果写入到表dept_salary_stat（假设该表已经创建）中。 INSERT INTO emp_salary_stat S
ELECT department_id,max(salary), min(salary) FROM employees GROUP
BY department_id; 向employees表中插入一个员工信息，员工号为1234，员工姓为“Liu”，email为
“[e-mail]”，其他信息与员工号为150的员工信息相同。INSERT INTO employees SE
LECT 1234, first_name,''Liu'',''[e-mail]'',phone_number,hir
e_date,job_id,salary,commission_pct,manager_id,department_idFROM
employees WHERE employee_id=150; 如果要将大量数据插入表中，可以利用子查询直接装载的方式进行。由于
直接装载数据的操作过程不写入日志文件，因此数据插入操作的速度大大提高。利用子查询装载数据语法为：INSERT /+APPEND
/ INTO table_name|view_name[(column1[,column2,…])subquery;复制emplo
yees表中employee_id，salary，job_id,department_id四列的值，并插入到backup_emp表
中 ，语句为：INSERT /+APPEND/ INTO backup_empSELECT employee_id,salar
y,job_id,department_idFROM employees;7.1.3 向案例数据库表插入初始数据INSERT I
NTO ehr.regions SELECT FROM hr.regions;INSERT INTO ehr.countrie
s SELECT FROM hr. countries;INSERT INTO ehr.locations SELECT
FROM hr. locations;INSERT INTO ehr.departments SELECT FROM hr.d
epartments;INSERT INTO ehr.jobs SELECT FROM hr.jobs;INSERT INTO
ehr.employees SELECT FROM hr.employees;INSERT INTO ehr.sal_gra
des SELECT FROM hr.sal_grades;INSERT INTO ehr.job_history SELEC
T FROM hr.job_history;7.1.4 向多个表中插入数据在Oracle 11g中，可以使用INSERT语句
同时向多个表中插入数据。根据数据插入的条件不同，分为：无条件插入：将数据插入所有指定的表中有条件插入：将数据插入符合条件的表中。无
条件多表插入的基本语法为INSERT [ALL] INTO table1 VALUES(column1,column2[,…])I
NTO table2 VALUES(column1,column2[,…])……subquery;利用无条件多表插入，查询empl
oyees表中工资高于8000元的员工信息并分别插入emp_sal和emp_mgr表。INSERT ALLINTO emp_sal
VALUES(employee_id,hire_date,salary)INTO emp_mgr
VALUES(employee_id,manager_id,salary)SELECT employee_id,hire
_date,manager_id,salaryFROM employees WHERE salary>8000; 有条件多表插入语
法为INSERT ALL|FIRST WHEN condition1 THEN INTO table1(column1[,…])
WHEN condition2 THEN INTO table2(column1[,…])……ELSE INTO tablen(c
olumn1[,…])subquery;参数说明ALL：表示一条记录可以同时插入多个满足条件的表中；FIRST：表示一条记录只插入
第一个满足条件的表中。 将employees表中员工信息按照不同部门号分别复制到emp10，emp20，emp30和emp_oth
er表中。同时，将工资低于5000元的员工信息复制到lowsal表中，将工资高于10000元的员工信息复制到highsal表中，将
工资在5000～10000元之间的员工信息复制到middlesal表中。 INSERT ALLWHEN department_id
=10 THEN INTO emp10WHEN department_id =20 THEN INTO emp20WHEN dep
artment_id =30 THEN INTO emp30WHEN department_id =40 THEN INTO em
p_otherWHEN salary<2000 THEN INTO lowsalWHEN salary>4000 THEN INT
O highsalELSE INTO middlesal SELECT FROM employees;多表插入的应用利用多表插
入技术可以实现不同数据源之间的数据转换，可以将非关系数据库的一条记录转换为关系数据库中的多条记录。 将SALES_SOURCE_D
ATA表中的记录转换为SALES_INFO表中的记录。CREATE TABLE sale_source_data(
emp_id NUMBER(6), week_id NUMBER(2), sale_MON NUMB
ER(8,2), sale_TUE NUMBER(8,2), sale_WED NUMBER(8,2)
, sale_THUR NUMBER(8,2), sale_FRI NUMBER(8,2)); IN
SERT INTO sale_source_data VALUES(7844,1,100,200,300,400,500);
CREATE TABLE sale_info( emp_id NUMBER(6), week
NUMBER(2), sale NUMBER(8,2));INSERT ALL INTO sale
_info VALUES (emp_id,week_id,sale_MON) INTO sale_info VAL
UES (emp_id,week_id,sale_TUE) INTO sale_info VALUES (emp_
id,week_id,sale_WED) INTO sale_info VALUES (emp_id,week_i
d,sale_THUR) INTO sale_info VALUES (emp_id,week_id, sale_
FRI) SELECT FROM sale_source_data; 7.2数据修改基本语法修改单行记录修改多
行记录带有子查询的修改基本语法UPDATE table_name|view_nameSET column1=value1[,col
umn2=value2…][WHERE condition]修改单条记录UPDATE employees SET salary=
salary+100,commission_pct=0.4 WHERE employee_id=100;修改多条记录UPDATE
employees SET salary=salary+150 WHERE department_id=20;利用子查询修改记录
UPDATE employees SET salary=300+(SELECT avg(salary) FROM employe
es WHERE department_id=30)WHERE department_id=50;7.3数据合并利用MERGE语句
可以同时完成数据的插入与更新操作。将源表的数据分别与目标表中的数据根据特性条件进行比较（每次只比较一条记录），如果匹配，则利用源表
中的记录更新目标表中的记录，如果不匹配，则将源表中的记录插入目标表中。使用MERGE语句操作时，用户需要具有源表的SELECT对象
权限以及目标表的INSERT，UPDATE对象权限。MERGE语句的基本语法为：MERGE INTO [schema.]targe
t_table [target_alias] USING [schema.]source_table|source_view|so
urce_subquery [source_alias]ON (condition)WHEN MATCHED THEN UPDAT
E SET column1=expression1 [,column2=expression2 …] [where_
clause][DELETE where_clause]WHEN NOT MATCHED THEN INSERT [(col
umn2[,column2…])] VALUES (expresstion1[,expression2…]) [wh
ere_clause];参数说明INTO：指定进行数据更新或插入的目标表；USING：指定用于目标表数据更新或插入的源表或视图或子
查询；ON：决定MERGE语句执行更新操作还是插入操作的条件。对于目标表中满足条件的记录，则利用源表中的相应记录进行更新；而源表中
不满条件的记录将被插入目标表中；where_clause：只有当该条件为真时才进行数据的更新或插入操作；DELETE where_
clause：当目标表中更新后的记录满足该条件时，则删除该记录。 使用“雇员”表（别名e）作为数据源，在该表 的副本“副本_雇员”
表（别名c）中插入并更新行。MERGE INTO 副本_雇员 c USING 雇员eON (c.雇员标识= e.雇员标识)WHEN
MATCHED THEN UPDATESETc.姓氏 = e.姓氏,c.部门标识 = e.部门标识WHEN NOT MATCHE
D THEN INSERT VALUES (e.雇员标识, e.姓氏, e.部门标识);MERGE INTO 副本_雇员 c US
ING 雇员e ON (c.雇员标识= e.雇员标识) WHEN MATCHED THEN UPDATE SET c.姓氏 = e
.姓氏, c.部门标识 = e.部门标识WHEN NOT MATCHED THEN INSERT VALUES (e.雇员标识,
e.姓氏, e.部门标识); MERGE 语句执行前的“副本_雇员”雇员标识 姓氏 部门标识 100 Smith 40 103 C
hang 30 MERGE 执行后的“副本_雇员”雇员标识 姓氏 部门标识 100 King 90 103 Hunold 60 1
52 Davies 50 雇员（来源表）雇员标识 姓氏 部门标识 100 King 90 103 Hunold 60 152 Da
vies 50 现有表source_emp和target_emp，表中数据如下。利用source_emp表中的数据更新target
_emp表中的数据，对target_emp表中存在的员工信息进行更新，对不存在的员工进行信息插入。 SELECT FROM s
ource_emp;EMPNO ENAME DEPTNO------------------------------
100 JOAN 10110 SMITH 20120 TOM
30 SELECT FROM target_emp;EMPNO ENAME DEP
TNO------------------------------------100 MARRY
2020 JACK 40 MERGE INTO target_emp t US
ING source_emp s ON (t.empno=s.empno) WHEN MATCHED THEN UPD
ATE SET t.ename=s.ename,t.deptno=s.deptno WHEN NOT MATC
HED THEN INSERT VALUES(s.empno,s.ename,s.deptno)；SELECT
FROM target_emp;EMPNO ENAME DEPTNO------------------------
------100 JOAN 1020 JACK 40
110 SMITH 20120 TOM 30 7.4删除数据
基本语法删除单条记录删除多条记录带有子查询的删除操作利用TRUNCATE删除数据TRUNCATE与DELETE区别基本语法 DEL
ETE FROM table|view [WHERE condition] 删除单条记录DELETE FROM employees
WHERE employee_id=152; 删除多条记录DELETE FROM employees WHERE depart
ment_id=130 ;带有子查询的删除操作DELETE FROM employees WHERE salary>(SELECT
salary FROM employees WHERE employee_id=101);利用TRUNCATE删除数据TRUNC
ATE TABLE table_nameTRUNCATE与DELETE区别释放存储空间不写入日志文件，因此执行效率较高，但该操作不
可回滚。 7.5事务控制事务概念Oracle事务的隔离级别 Oracle事务处理7.5.1 事务概念原子性（Atomicity）
：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。一致性（Consistency）：事务执行的
结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。隔离性（Isolation）：数据库中一个事务的执行不
受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。持久性（Durability）：一个事务一旦提交，则对数据库中数据的改变
是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。 7.5.2 Oracle事务的隔离级别 数据库中事务的并发运行
，可能导致下列问题：丢失修改：两个事务同时读取数据库中的同一数据并进行修改，一个事务提交的结果破坏了另一个事务提交的结果，导致第一
个事务对数据的修改丢失。读“脏”数据：一个事务对数据的修改在提交之前被其他事务读取。不可重复读：在某个事务读取一次数据后，其他事务
修改了这些数据并进行了提交，当该事务重新读取这些数据时就会得到与前一次不一样的结果。Oracle数据库事务隔离级：READ COM
MITED（提交读）这是事务的默认隔离等级，用于设置语句级的一致性。每个事务所执行的查询操作只能获取在该查询开始之前（不是该事务开
始之前）已经提交的数据。该隔离级别可以防止丢失修改和脏读的问题，但不能防止不可重复读的问题。在该级别的事务中可以执行DML操作（若
数据被加锁则等待其他事务解锁）。SERIALIZABLE（串行化）用于设置事务级的一致性，每个事务只能看到在该事务开始之前已经提交
的数据。该隔离级的事务可以防止丢失修改、脏读和不可重复读的问题。在该级别的事务中可以执行DML操作（若数据被加锁则不等待，返回错误
）。如果数据库中具有大量并发事务，并且应用程序的事务处理能力和响应速度是关键因素，则READ COMMITED隔离级比较合适。如果
数据库中多个事务并发访问数据的概率很低，并且大部分的事务都会持续执行很长时间，这时应用程序更加适合使用SERIALIZABLE隔离
级。SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION IS
OLATION LEVEL READ COMMITTED;ALTER SESSION SET ISOLATION_LEVEL =S
ERIALIZABLE;ALTER SESSION SET ISOLATION_LEVEL =READ COMMITTED;在Oracle数据库中可以设置事务为Read-Only（只读），每个事务只能看到在该事务开始之前已经提交的数据，并且不能在该事务中对数据进行INSERT，UPDATE，DELETE等操作，从而确保取得特定时间点的数据信息。SET TRANSACTION READ ONLY; 7.5.3 Oracle事务处理 事务提交方式用户显式执行COMMIT命令执行特定操作时系统自动提交。说明当事务提交后，用户对数据库修改操作的日志信息由日志缓冲区写入重做日志文件中，释放该事务所占据的系统资源和数据库资源。此时，其他会话可以看到该事务对数据库的修改结果。当执行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令时，系统将自动提交。 事务回滚方式事务全部回滚 ROLLBACK事务部分回滚SAVEPOINT XROLLBACK TO XROLLBACK TO B;（回滚最后一个INSERT操作）ROLLBACK TO A;（回滚后面的INSERT操作和UPDATE操作）ROLLBACK;（回滚全部操作） 总结数据插入数据修改数据合并数据删除事务控制