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;
