《Oracle处理数据》PPT课件.ppt

处理数据 目标 通过本章学习，您将可以 : 使用 DML 语句 向表中插入数据 更新表中数据 从表中删除数据 控制事务 数据控制语言 DML 可以在下列条件下执行 : 向表中插入数据 修改现存数据 删除现存数据 事务是由完成若干项工作的 DML语句组成的。 插入数据 DEPARTMENTS New row insert a new row into the DEPARMENTS table INSERT 语句语法 使用 INSERT 语句向表中插入数据。 使用这种语法一次只能向表中插入一条数据。 INSERT INTO table (column , column.) VALUES (value , value.); 插入数据 为每一列添加一个新值。 按列的默认顺序列出各个列的值。 在 INSERT 子句中随意列出列名和他们的值。 字符和日期型数据应包含在单引号中。 INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, Public Relations, 100, 1700); 1 row created. INSERT INTO departments VALUES (100, Finance, NULL, NULL); 1 row created. INSERT INTO departments (department_id, department_name ) VALUES (30, Purchasing); 1 row created. 向表中插入空值 隐式方式 : 在列名表中省略该列的值。 显示方式 : 在 VALUES 子句中指定空值。 INSERT INTO employees (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); 1 row created. 插入指定的值 SYSDATE 记录当前系统的日期和时间。 INSERT INTO employees VALUES (114, Den, Raphealy, DRAPHEAL, 515.127.4561, TO_DATE(FEB 3, 1999, MON DD, YYYY), AC_ACCOUNT, 11000, NULL, 100, 30); 1 row created. 插入指定的值 加入新员工 检查插入的数据 INSERT INTO departments (department_id, department_name, location_id) VALUES ( 创建脚本 在 SQL 语句中使用 4 rows created. 从其它表中拷贝数据 更新数据 EMPLOYEES Update rows in the EMPLOYEES table. UPDATE 语句语法 使用 UPDATE 语句更新数据。 可以一次更新多条数据。 UPDATE table SET column = value , column = value, . WHERE condition; UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated. 使用 WHERE 子句指定需要更新的数据。 如果省略 WHERE子句，则表中的所有数据都将被更新。 更新数据 UPDATE copy_emp SET department_id = 110; 22 rows updated. UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 1 row updated. 在 UPDATE语句中使用子查询 更新 114号员工的工作和工资使其与 205号员工相同。 UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated. 在 UPDATE语句中使用子查询 在 UPDATE 中使用子查询，使更新基于另一个表中的数据。 UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found UPDATE employees SET department_id = 55 WHERE department_id = 110; 更新中的数据完整性错误 从表 DEPARTMENTS 中删除一条记录。 删除数据 DEPARTMENTS DELETE 语句 使用 DELETE 语句从表中删除数据。 DELETE FROM table WHERE condition; 使用 WHERE 子句指定删除的记录。 如果省略 WHERE子句，则表中的全部数据将被删除。 删除数据 DELETE FROM departments WHERE department_name = Finance; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted. DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%); 1 row deleted. 在 DELETE 中使用子查询 在 DELETE 中使用子查询，使删除基于另一个表中的数据。 删除中的数据完整性错误 DELETE FROM departments WHERE department_id = 60; DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found 使用 MERGE合并表数据 使用 MERGE 语句，可以对指定的两个表执行合并操作。 语法： MEGER INTO table1 alias1 USING table2 alias2 ON join condition WHEN MATCHED THEN UPDATE 子句 WHEN NOT MATCHED THEN INSERT 子句 。 语法说明： 1. table1 alias1 表示需要合并的目标表及别名。 2. table2 alias2 表示需要合并的源表及别名 (把 table2的数据合并到 table1)。 3. join _ condition 表示合并条件。 4. WHEN MATCHED THEN UPDATE 表示如果符合合并条件，则执行更新操作。 5. WHEN NOT MATCHED THEN INSERT 表示如果不符合合并条件，则执行插入 操作 。 事务 (Transaction) 数据库事务是由一系列 SQL语句组成的最小逻辑单元 一个逻辑工作单元 由多个 SQL语句组成 Oracle系统以事务为单位来处理数据，以保证数据的一致性。 事务里的一系列 SQL语句要么同时成功，要么同时失败 事务的特点 (ACID) 原子性 (Atomicity): 事务必须是原子工作单元；对于其数据修改，要么全都 执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标， 并且是相互依赖的 一致性 (Consistency):事务在完成时，必须使所有的数据都保持一致状态。 隔离性 (insulation):由并发事务所作的修改必须与任何其它并发事务所作的 修改隔离。 持久性 (Duration):事务完成之后，它对于系统的影响是永久性的。 事务的组成 数据库事务由以下的部分组成 : 一个或多个 DML 语句 一个 DDL 语句 一个 DCL 语句 事务控制 开始于上一个事务结束后的第一个 DML 语句 以下面的其中之一作为结束 : COMMIT 或 ROLLBACK 语句 DDL 或 DCL 语句（隐式提交） 用户会话正常结束 连接异常、系统异常 事务的提交和回滚 commit显示提交事务 对数据库的操作将永久性的记录在数据库中 rollback显示回滚事务 对数据库的操作将被取消 COMMIT和 ROLLBACK语句的优点 使用 COMMIT 和 ROLLBACK语句 ,我们可以 : 确保数据完整性。 数据改变被提交之前预览。 将逻辑上相关的操作分组。 事务的隐式提交 执行一个 DDL语句 执行一个 DCL语句 从 SQL*PLUS正常退出 事务的隐式回滚 强行退出 SQL*PLUS 连接异常 系统异常 SAVEPOINT 在事务的处理过程中，如果发生了错误并且使用 ROLLBACK 进行了回滚， 则在整个事务处理中对数据所做的修改都将被撤销。在一个庞大的事中。 这种操作将会浪费大量的资源这时，可以为该事务建立一个或多个保存 点 (SAVEPOINT)。使用保存点可以让用户将一个规模比较大的事务分割成 一系列较小的部分。当回滚事务时，就可以回滚到指定的保存点。 语法： savepoint savepoint_name 回滚到某保存点 rollback savepoint_name UPDATE. SAVEPOINT update_done; Savepoint created. INSERT. ROLLBACK TO update_done; Rollback complete. 回滚到保留点 使用 SAVEPOINT 语句在当前事务中创建保存点。 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。 提交或回滚前的数据状态 改变前的数据状态是可以恢复的 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 DML语句所涉及到的行被锁定， 其他用户不能操作。 提交后的数据状态 数据的改变已经被保存到数据库中。 改变前的数据已经丢失。 所有用户可以看到结果。 锁被释放， 其他用户可以操作涉及到的数据。 所有保存点被释放。 COMMIT; Commit complete. 改变数据 提交改变 DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700); 1 row inserted. 提交数据 数据回滚后的状态 使用 ROLLBACK 语句可使数据变化失效 : 数据改变被取消。 修改前的数据状态可以被恢复。 锁被释放。 DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete. 读一致性 读一致性为数据提供一个一致的视图。 一个用户的对数据的改变不会影响其他用户的改变。 Session不同 SELECT * FROM userA.employees; 读一致性 UPDATE employees SET salary = 7000 WHERE last_name = Goyal; 数据块 回滚段 改变和未改变 的数据 改变之前的 数据 User A User B 快照 总结 功能 插入 修正 删除 提交 保存点 回滚 语句 INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK 通过本章学习 , 您应学会如何使用 DML语句改变数据和事务 控制