Oracle_071_lesson_p19

0755mx

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>　　①条件操作：
　　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.no1
　　when not matched then insert values(b.no,b.no2)  where a.no100
　　当然也支持变种①②的写法
　　②删除操作
　　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.no1
　　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 &quot;START_DATE&quot;,       versions_endtime   &quot;END_DATE&quot;,
　　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;