Oracle数据库数据编程SQL＜2.3 DML增、删、改及merge into＞

目录

一、DML数据操纵语言（Aate Manipulation Language)

二、【insert】插入数据

1、单行插入

2、批量插入

3、将数据同时插入到多张表insert all/insert first

三、【update】 更新数据

1、语法

2、举例

3、update使用注意事项：

四、【delete】删除数据---多用于删除特定数据

1、语法

2、delete from 表 不加条件则删除全部数据

五、delete 和 truncate的异同

1、相同点：都可以删除表中数据

2、不同点：

六、把序列的值作为主键值插入

七、merge into 用表2更新表1

1、语法

2、举例

八、MERGE高级用法

1、只更新不插入

2、只插入不更新

3、条件操作

九、DML操作注意事项

1、事务控制

2、锁机制

3、性能优化

4、PL/SQL批量DML示例：

十、Oracle特有DML功能

1. RETURNING子句

2. 闪回查询（Flashback Query）

3. 批量绑定（BULK COLLECT & FORALL）

十一、DML最佳实践

---

一、DML数据操纵语言（Aate Manipulation Language)

1、插入数据 insert into

2、更新数据 update set

3、删除数据 delete from

4、DML 语言 可以commit（提交），rollback（回滚）

二、【insert】插入数据

1、单行插入

【1】insert into 表名(列1，列2，...列n)values(值1，值2，...值n)create table emp0 as select *from emp where 3=999;
insert into emp0 (empno,ename,hiredate,sal)values(33,'霍泂宇',date'2019-1-1',99999);
select *from emp0;
drop table emp0;【2】insert into 表名 values(值1，值2，...值n)--必须和列数相同且对应insert into emp0 values(2,'小B','CLERK',1,sysdate,30,'',20);【3】但是如果表中列的约束有默认default约束，就必须用语法1,假如列2是default约束insert into 表名(列1，列3，...列n)values(值1，值3，...值n);

2、批量插入

【1】insert into 表名 (列1，列2，…列n) 子查询insert into emp0 (empno,ename,sal) select empno,ename,sal from emp where deptno=10【2】insert into 表名 子查询 （插入表与建好的表列数必须相同）insert into emp0 select * from emp where deptno=20
-----emp表10部门的ename，sal，deptno插入到emp0
-----emp表的20部门的empno，hiredate，deptno插入到emp0
-----emp表工作是MANAGER全部信息插入到emp0
insert into emp0 (ename，sal，deptno)select ename，sal，deptno from emp where deptno=10;
insert into emp0 (empno，hiredate，deptno)select empno，hiredate，deptno from emp where deptno=20;
insert into emp0 select * from emp where job='MANAGER'

3、将数据同时插入到多张表insert all/insert first

【1】语法
insert all/first
when 条件1 theninto 表1(列1，列2，...列n) values(值1，值2，...值n)
when 条件2 theninto 表1(列1，列2，...列n) values(值1，值2，...值n)......
when 条件n theninto 表1(列1，列2，...列n) values(值1，值2，...值n)
select 列 from  源表 --建3张表，表格式和emp相同没有数据。
create table emp_1 as select * from emp where 1=2;
create table emp_2 as select * from emp where 1=2;
create table emp_3 as select * from emp where 1=2;
select * from emp_1;
select * from emp_2;
select * from emp_3;--将emp表名字带A的插入到emp_1,带B的插入到emp_2,带C的插入到emp_3
insert all--每一个条件都是针对全表挑选
when ename like '%A%' then into emp_1(ename) values(ename)
when ename like '%B%' then into emp_2(ename) values(ename)
when ename like '%C%' then into emp_3(ename) values(ename)
select ename from empinsert first--只能插入一次，前面表插入过了就不再插入到后面的表了
when ename like '%C%' then into emp_3(ename) values(ename)
when ename like '%B%' then into emp_2(ename) values(ename)
when ename like '%A%' then into emp_1(ename) values(ename)
select ename from emp

三、【update】 更新数据

1、语法

update 表名 set 列1=值1,列2=值2……列n=值n {where 条件}select * from emp0 
update emp0 set job='MANAGER',sal=8000 where ename='SMITH'

2、举例

--EMP表10部门工资涨500
--emp20部门的改成11部门
--emp0表全表的empno改成empno+sal
--emp0的ename首字母转大写
create table emp0 as select*from emp--建表
update emp0 set sal=sal+500 where deptno=10;
update emp0 set deptno=11 where deptno=20;
alter table emp0 modify empno number (5)
update emp0 set empno=empno+sal;
update emp0 set ename=initcap(ename)
select *from emp0

3、update使用注意事项：

（1）sp_updatestats可以更新统计信息到最新。exec sp_updatestats 对整个数据库更新

（2）低内存会导致未被客户端连接的查询计划被清除。

（3）修改表结构，修改索引后，查询计划会被清除，可以再修改后运行几遍查询。

（4）使用update时候，order by 会影响查询速度，where中使用函数则会调用筛选器进行扫描，扫描表要尽量避免。

四、【delete】删除数据---多用于删除特定数据

1、语法

delete from 表名 {where 条件}delete from emp0 where ename like '%%'

2、delete from 表 不加条件则删除全部数据

--删除没有奖金的员工
--删除emp0表周日入职的员工
delete from emp0 where nvl（comm,0）=0
delete from  emp0 where  to_char(hiredate,'day')='星期日'

五、delete 和 truncate的异同

1、相同点：都可以删除表中数据

2、不同点：

（1）truncate 快，delete 慢

（2）truncate 是DDL，delete是DML

（3）truncate 不能回滚，delete可以回滚

（4）truncate 不能加条件，delete可以加条件

（5）truncate 可以降低高水位线，delete不能降低高水位线

truncate table emp1
delete from 表名 {where 条件}

六、把序列的值作为主键值插入

select * from emp1create or replace s_vip
start with 1
maxvalue 999
increment by 1
insert into emp1( ename) values ( s_vip.nextval)--创建表
drop table book;        
create table book(       bookId varchar2(4) primary key,   name varchar2(20)         
);   
--创建序列      
create sequence book_seq start with 1 increment by 1;    --创建触发器      
create or replace trigger book_trigger       
before insert on book       
for each row       
begin       
select book_seq.nextval into :new.bookId from dual;      
end ;   --添加数据      
insert into book(name)  values ('cc');    
insert into book(name)  values ('dd');   commit; 
SELECT * FROM book 

七、merge into 用表2更新表1

1、语法

merge into A
using 表B
on (关联条件)---多个关联条件要加括号
-----------------------------------------------------------有则改之
when matched then---满足条件
update set A.列1=B.列1----更新数据
{where 条件}--限制的是被更新的表
{delete where}--可以删除但一般不用
----------------------------------------------------------无则加之
when not matched then---不满足条件
insert(A.列1，A.列2....）values (B.列1，B.列2...)
where 条件--限制的是参考表

2、举例

--创建两张表，emp001的ename列转小写，另一张表emp002的10部门改成40部门
create table emp001 as select empno,lower（ename）ename,deptno from emp;
create table emp002 as select empno,ename,decode(deptno,10,40 ,20,20,30) deptno from emp;
update emp001 set ename=upper(ename) where deptno in (10,20)
select *from emp001;
select *from emp002;----用emp002的deptno作为参考修改emp001，
---emp001中没有的插入到emp001中
----emp001中有的，ename改成和emp002相等
merge into emp001 a
using emp002 b 
on (a.deptno=b.deptno and a.empno=b.empno)when matched then---满足条件
update set a.ename=b.ename---有则改之when not matched then---不满足条件
insert （a.empno,a.deptno）values （b.empno,b.deptno）---无则加之/*##########################################################################*/
--建两张表
--emp01 复制emp 的ename,job,sal,depnto,30部门的job列改成首字母大写
--emp02 复制emp 的ename,job,sal,depnto，20部门改成21部门
--用emp02 更新emp01 关联条件deptno=deptno,ename=ename
--更新的时候只更新工资小于2000的，插入的时候只插入工资大于2000的
create table emp01 as select ename,job,sal,deptno from emp;
create table emp02 as select ename,job,sal,deptno from emp;
update emp01 set job=initcap(job) where deptno=30;
update emp02 set deptno=21 where deptno=20;merge into emp01 a
using emp02 b
on (a.deptno=b.deptno and a.ename=b.ename)
when matched then --满足条件
update set a.job=b.job/*更新数据*/where a.sal<2000--限制的是被更新表
when not matched then --不满足条件
insert (a.ename,a.job,a.sal,a.deptno) 
values (b.ename,b.job,b.sal,b.deptno)--插入数据
where b.sal>2000--限制的是参考表select * from emp01;
select * from emp02;

八、MERGE高级用法

1、只更新不插入

MERGE INTO employees t
USING new_salaries s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THENUPDATE SET t.salary = s.new_salary;

2、只插入不更新

MERGE INTO employees t
USING new_hires s
ON (t.employee_id = s.employee_id)
WHEN NOT MATCHED THENINSERT (employee_id, last_name, salary)VALUES (s.employee_id, s.last_name, s.salary);

3、条件操作

MERGE INTO employees t
USING updated_data s
ON (t.employee_id = s.employee_id)
WHEN MATCHED AND s.status = 'TERMINATED' THENDELETE
WHEN MATCHED AND s.salary > t.salary THENUPDATE SET t.salary = s.salary
WHEN NOT MATCHED THENINSERT (employee_id, last_name, salary)VALUES (s.employee_id, s.last_name, s.salary);

九、DML操作注意事项

1、事务控制

-- 开始事务（Oracle中DML自动开始事务）
-- 执行DML操作
COMMIT; -- 提交事务
-- 或
ROLLBACK; -- 回滚事务-- 保存点(SAVEPOINT)
SAVEPOINT before_update;
UPDATE employees SET salary = salary * 1.1;
-- 可以回滚到保存点
ROLLBACK TO before_update;

2、锁机制

• 行级锁：DML操作自动锁定受影响的行

• 表级锁：某些操作可能升级为表锁

• 死锁：避免循环依赖的DML操作

3、性能优化

• 批量操作：使用FORALL批量DML（PL/SQL）

• 减少提交频率：批量提交而非单行提交

• 使用绑定变量：避免硬解析

4、PL/SQL批量DML示例：

DECLARETYPE id_array IS TABLE OF employees.employee_id%TYPE;v_ids id_array := id_array(101, 102, 103);
BEGINFORALL i IN 1..v_ids.COUNTUPDATE employees SET salary = salary * 1.1WHERE employee_id = v_ids(i);COMMIT;
END;
/

十、Oracle特有DML功能

1. RETURNING子句

获取DML操作影响的数据：

-- 插入并返回生成的ID
DECLAREv_new_id employees.employee_id%TYPE;
BEGININSERT INTO employees (employee_id, last_name, hire_date)VALUES (emp_seq.NEXTVAL, '张伟', SYSDATE)RETURNING employee_id INTO v_new_id;DBMS_OUTPUT.PUT_LINE('新员工ID: ' || v_new_id);
END;
/-- 更新并返回旧值和新值
DECLAREv_old_sal NUMBER;v_new_sal NUMBER;
BEGINUPDATE employeesSET salary = salary * 1.1WHERE employee_id = 100RETURNING salary, salary * 1.1 INTO v_old_sal, v_new_sal;DBMS_OUTPUT.PUT_LINE('薪资从 ' || v_old_sal || ' 调整为 ' || v_new_sal);
END;
/

2. 闪回查询（Flashback Query）

查看和恢复历史数据：

-- 查看5分钟前的数据
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);-- 恢复误删数据
INSERT INTO employees
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE employee_id = 1001;

3. 批量绑定（BULK COLLECT & FORALL）

高效批量DML操作：

DECLARETYPE emp_array IS TABLE OF employees%ROWTYPE;v_emps emp_array;
BEGIN-- 批量查询SELECT * BULK COLLECT INTO v_empsFROM employeesWHERE department_id = 10;-- 批量更新FORALL i IN 1..v_emps.COUNTUPDATE employeesSET salary = v_emps(i).salary * 1.1WHERE employee_id = v_emps(i).employee_id;COMMIT;
END;
/

十一、DML最佳实践

1、使用绑定变量：提高SQL重用率

-- 不好
UPDATE employees SET salary = 5000 WHERE employee_id = 100;
UPDATE employees SET salary = 6000 WHERE employee_id = 101;-- 好
UPDATE employees SET salary = :new_sal WHERE employee_id = :emp_id;

2、限制事务大小：避免长时间运行的事务

3、批量操作：使用FORALL代替循环单行DML

4、错误处理：实现健壮的异常处理

BEGIN-- DML操作UPDATE employees SET salary = salary * 1.1;COMMIT;
EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;

5、性能监控：检查执行计划和统计信息

EXPLAIN PLAN FOR UPDATE employees SET salary = salary * 1.1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

6、使用MERGE代替单独INSERT/UPDATE：简化代码并提高性能

7、考虑触发器影响：了解表上的触发器可能带来的副作用