Oracle_071_lesson_p19

Manipulating Data 数据操作

you should be able to:
1、Specify explicit default values in the INSERT and UPDATE statements
2、Describe the features of multitable INSERTs
3、Use the following types of multitable INSERTs:
4、Unconditional INSERT
5、Conditional INSERT ALL
6、Conditional INSERT FIRST
7、Pivoting INSERT
8、Merge rows in a table
9、Perform flashback operations
10、Track the changes made to data over a period of time

default 值
1、default with insert:
INSERT INTO deptm3
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);

2、default with update
UPDATE deptm3
SET manager_id = DEFAULT WHERE department_id = 10;

multitable insert statements 多表insert语句
INSERT…SELECT...

INSERT ALL
INTO target_a VALUES(…,…,…)
INTO target_b VALUES(…,…,…)
INTO target_c VALUES(…,…,…)
SELECT …
FROM sourcetab
WHERE …;

Unconditional INSERT ALL 无条件全插入
例：
create table sal_history as
select employee_id,hire_date,salary
from emp
where 1=0;

create table mgr_history as
select employee_id,manager_id,hire_date
from emp
where 1=0;

INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;

Conditional INSERT ALL 有条件全插入 （会逐条匹配）
例：
INSERT ALL
WHEN HIREDATE < '01-JAN-05' THEN
INTO emp_history VALUES(EMPID,HIREDATE,SAL)
WHEN COMM IS NOT NULL THEN
INTO emp_sales VALUES(EMPID,COMM,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, commission_pct COMM
FROM employees;

Conditional INSERT FIRST 有条件INSERT FIRST （当找到满足条件则不再向下匹配）
例：
INSERT FIRST
WHEN salary < 5000 THEN
INTO sal_low VALUES (employee_id, last_name, salary)
WHEN salary between 5000 and 10000 THEN
INTO sal_mid VALUES (employee_id, last_name, salary)
ELSE
INTO sal_high VALUES (employee_id, last_name, salary)
SELECT employee_id, last_name, salary
FROM employees;

Pivoting INSERT 旋转插入（行列转换）
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;

Merging rows in a table 可新表数据更新到旧表
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

MERGE INTO copy_emp3 c
USING (SELECT * FROM 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,
...

DELETE WHERE (E.COMMISSION_PCT IS NOT NULL)
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);

TRUNCATE TABLE copy_emp3;
SELECT * FROM copy_emp3;
no rows selected

MERGE INTO copy_emp3 c
USING (SELECT * FROM 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,
...

DELETE WHERE (E.COMMISSION_PCT IS NOT NULL)
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, ...

SELECT * FROM copy_emp3;
107 rows selected.

MERGE语法详解
merge语法是根据源表对目标表进行匹配查询，匹配成功时更新，不成功时插入。

其基本语法规则是

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)

when matched then update set a.更新字段=b.字段

when not macthed then insert into a(字段1,字段2……)values(值1,值2……)

变种写法①，只更新：

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)

when matched then update set a.更新字段=b.字段,a.更新字段2=b.字段2……

变种写法②，只插入：

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)

when not macthed then insert into a(字段1,字段2……)values(值1,值2……)

注：条件字段不可更新

对于Oracle来说，merge是9i新增的语法，在10g进行了一些增强，如下：

测试环境：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

①条件操作：

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)

when matched then update set a.更新字段=b.字段 where 限制条件

when not macthed then insert into a(字段1,字段2……)values(值1,值2……) where 限制条件

举例：

merge into test_merge a
using test b
on(a.no=b.no)
when matched then update set a.no2=b.no2 where a.no<>1
when not matched then insert values(b.no,b.no2) where a.no<>100

当然也支持变种①②的写法

②删除操作

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)

when matched then update set a.更新字段=b.字段

delete where b.字段=xxx

举例：

merge into test_merge a
using test b
on(a.no=b.no)
when matched then update set a.no2=b.no2 where a.no<>1
delete
where b.no=14

备注：删除动作针对的也是目标表，并且必须在语句最后

基本上merge的用法就是以上这些，建议平常可以多用，比单独的update+insert的方式效率要更高，尤其是on条件下有唯一索引的时候，效率更高

flashback table 闪回表
system change number (SCN)
FLASHBACK TABLE [ schema. ] table [, [ schema. ] table ]... TO { { { SCN | TIMESTAMP } expr | RESTORE POINT restore_point } [ { ENABLE | DISABLE } TRIGGERS ] | BEFORE DROP [ RENAME TO table ] } ;
例：
DROP TABLE emp3;
SELECT original_name, operation, droptime FROM recyclebin;
FLASHBACK TABLE emp3 TO BEFORE DROP;

select current_timestamp from dual; 查询当前时间戳
flashback table emp to timestamp ( current_timestamp - interval '5' minute);当前时间减5分钟.

闪回查询：
例：
SELECT salary FROM employees3
WHERE last_name = 'Chung';

UPDATE employees3 SET salary = 4000
WHERE last_name = 'Chung';

SELECT salary FROM employees3
WHERE last_name = 'Chung';

SELECT salary FROM employees3
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)
WHERE last_name = 'Chung';

闪回版本查询：commit 后才会记录版本信息
select * from emp as of timestamp ( current_timestamp - interval '5' minute);
select version_starttime, version_endtime, salary
from emp versions between scn minvalue and maxvalue where emp_id=100;

例：
SELECT salary FROM employees3
WHERE employee_id = 107;

UPDATE employees3 SET salary = salary * 1.30
WHERE employee_id = 107;

COMMIT;

SELECT salary FROM employees3
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 107;

例：
SELECT versions_starttime "START_DATE", versions_endtime "END_DATE",
salary FROM employees VERSIONS BETWEEN SCN MINVALUE
AND MAXVALUE WHERE last_name = 'Lorentz';

SELECT salary FROM employees3
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 107;

转载于:https://blog.51cto.com/3938853/2159740