5.7DML语句和事务控制

Oracle DML

DML

Data Manipulation Language，数据操纵语言；简单的说就是SQL中的增、删、改 等语句。

INSERT

INSERT语句

INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);

方式一：写出表名+列名

注意：在“列”中，对于不允许为NULL的列，必须写出来；对于允许为NULL的列，可以不写出来 在Value中，对于列中未写出来的列，默认赋予NULL值

INSERT INTO departments (department_id, department_name )
VALUES (30, 'Purchasing' );

方式二：仅写出表名

注意：在Value中必须对应写出每个列的值，即是是允许NULL的字段，也必须显式的给出 NULL

INSERT INTO departments (department_id, department_name )
VALUES (30, 'Purchasing' );

方式三：从另一个表中 Copy 一行

语法：

INSERT INTO table [ column (, column) ] subquery;

注意：在这种方式下，不要使用VALUES 关键字

方式四：使用子查询作为插入目标

INSERT INTO
 (SELECT employee_id, last_name,
 email, hire_date, job_id, salary, department_id
 FROM employees
 WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'),
 'ST_CLERK', 5000, 50);

WITH CHECK OPTION 可以检查要插入的内容，是否符合目标子查询的Where条件

INSERT INTO (SELECT employee_id, last_name, email,
 hire_date, job_id, salary
 FROM employees
 WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
 TO_DATE('07-JUN-99', 'DD-MON-RR'),
 'ST_CLERK', 5000);
INSERT INTO
 *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

INSERT增强

一个来源插入多个目标表（有条件，首次匹配即跳到下一条）

INSERT FIRST
 WHEN SAL > 25000 THEN
 INTO special_sal VALUES(DEPTID, SAL)
 WHEN HIREDATE like ('%00%') THEN
 INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
 WHEN HIREDATE like ('%99%') THEN
 INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
 ELSE
 INTO hiredate_history VALUES(DEPTID, HIREDATE)
 SELECT department_id DEPTID, SUM(salary) SAL,
 MAX(hire_date) HIREDATE
 FROM employees
 GROUP BY department_id;

列转行（一行变多行，交叉报表的反操作）

INSERT ALL
 INTO sales_info VALUES (employee_id,week_id,sales_MON)
 INTO sales_info VALUES (employee_id,week_id,sales_TUE)
 INTO sales_info VALUES (employee_id,week_id,sales_WED)
 INTO sales_info VALUES (employee_id,week_id,sales_THUR)
 INTO sales_info VALUES (employee_id,week_id, sales_FRI)
 SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
 sales_WED, sales_THUR,sales_FRI
 FROM sales_source_data;

UPDATE

UPDATE 语句

UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

方式一：更新符合条件的行中某些列为具体的值

UPDATE employees SET department_id = 70
WHERE employee_id = 113;

方式二：使用子查询的结果作为更新后的值

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;

注意：当存在约束的时候，某些更新可能会失败

DELETE

DELETE语句

DELETE [FROM] table
[WHERE condition];

举例一：删除某些符合条件的记录

DELETE FROM departments
WHERE department_name = 'Finance';

举例二：删除一张表中的所有记录

DELETE FROM copy_emp;

如果遇到这种需求，也可以使用TRUNCATE 语句，TRUNCATE TABLE copy_emp ，但要注意，TRUNCATE 语句无法回滚，因此除非是单独执行，并非常确认，否则慎用。 :::tips 注意：当存在约束的时候，某些删除操作可能会失败 :::

MERGE 语句：比较整合语句

语法

MERGE INTO table_name table_alias
 USING (table|view|sub_query) alias
 ON (join condition)
 WHEN MATCHED THEN
 UPDATE SET
 col1 = col_val1,
 col2 = col2_val
 WHEN NOT MATCHED THEN
 INSERT (column_list)
 VALUES (column_values);

MERGE INTO copy_emp c
 USING employees e
 ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
 UPDATE SET
 c.first_name = e.first_name,
 c.last_name = e.last_name,
 ...
 c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(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);

事务控制

事务过程

ORACLE事务的过程

隐式的事务提交或回滚动作：
Commit，rollback 是显式的提交和回滚语句，还有一些隐式的提交和回滚是大家需要知道并引起注意的： 当如下事件发生是，会隐式的执行Commit动作：
1、数据定义语句被执行的时候，比如新建一张表：Create Table …
2、数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)
3、正常退出 iSQLPlus 或者PLSQL DEVELOPER，而没有显式的执行 COMMIT 或者 ROLLBACK 语句 。
当如下事件发生时，会隐式执行Rollback 动作：
1、非正常退出 iSQLPlus，PLSQL DEVELOPER，或者发生系统错误。
在Commit 或者 Rollback前后数据的状态：
1、在数据已经被更改，但没有Commit前 ，被更改记录处于被锁定状态，其他用户无法进行更改；
2、在数据已经被更改，但没有Commit前 ，只有当前Session的用户可以看到这种变更 ，其他Session的用户 看不到数据的变化。
3、在数据已经被更改，并且被Commit后，被更改记录自动解锁，其他用户可以进行更改；
4、在数据已经被更改，并且被Commit后，其他Session的用户再次访问这些数据时，看到的是变化后的数据。 那么同理可知Rollback前后数据的状态及锁的变化。
读一致性： 用户对数据库的访问无非是两种情况
1、读数据 ： Select 语句
2、写数据 ： Insert 、Delete、Update 如果A用户要读的数据，B用户正在改，那么是否要等B改完再读出来呢？ 如何保证在及其相近的时间内，让 多个用户读到一致的数据呢？（假设有用户正在改这一批数据的过程中，尚未提交。） Oracle的“读一致性”概念是指：
1、在任何时候，确保提供数据的一致性视图。
2、一个用户对数据的更改不会影响另一个用户对数据的更改。
3、“读一致性” 确保在同一时刻：

• 3.1 读数据的人不需要等待写数据的人
• 3.2 写数据的人不需要等待读数据的人 而“读一致性”的通俗理解就是: 对于有人正在修改过程中的一批数据，在其位提交前，其他用户读到的是 一致的内容。