Oracle-PL/SQL基础-DML

    数据操作语言 DML，包含以下关键字的语法：

1. INSERT 插入
2. UPDATE 更新
3. DELETE 删除
4. TRUNCATE 清表
5. MERGE 合并
6. INSTER ALL 多表插入
7. WITH 临时表
8. FOR UPDATE 修改，可以执行要锁定的表

    还是以之前的AA表为例。

--插入并提交
insert into AA (AAA, BBB, CC, DD)
values ('A', null, '27-MAR-19 02.59.42.000000 PM', 16.54);
COMMIT;

--更新并提交
UPDATE AA SET BBB = '1' WHERE BB IS NULL;
COMMIT;

--删除并提交
DELETE FROM AA WHERE AAA = 'A';
COMMIT;

--清空表
TRUNCATE TABLE AA;

--合并存在更新不存在插入
MERGE INTO AA USING (SELECT A FROM BB WHERE ROWNUM<=1) B ON(AA.AAA = B.A)
WHEN MATCHED THEN 
  UPDATE SET BBB = '1'
WHEN NOT MATCHED THEN
  INSERT (AA.AAA, AA.BBB, AA.CC, AA.DD) VALUES ('B', 'B', '27-MAR-19 02.59.42.000000 PM', 16.54);
COMMIT;

/*多表插入*/
INSERT ALL 
WHEN AAA = 'C' THEN 
  INTO AA 
WHEN BBB = 'W' THEN 
  INTO AA 
ELSE INTO AA
SELECT AA.AAA, AA.BBB, AA.CC, AA.DD FROM AA WHERE ROWNUM <= 0;

/*临时表使用*/
WITH TABLE_1 AS
 (SELECT AAA, BBB, CC, DD FROM AA)
SELECT AAA, BBB, CC, DD FROM TABLE_1

/*锁定满足条件的employees表数据*/
SELECT e.employee_id, e.salary, e.commission_pct
  FROM employees e
  JOIN departments d
 USING (department_id)
 WHERE job_id = 'SA_REP'
   AND location_id = 2500
 ORDER BY e.employee_id
   FOR UPDATE OF e.salary;