Oracle Insert增强与SQL进阶

INSERT增强与SQL进阶：
一、同时向多张表中插入数据
语法：
INSERT ALL INTO （表1）
VALUES (查出来的字段1, 查出来的字段2)
INTO (表2)
VALUES (查出来的字段1, 查出来的字段2)
SELECT 语句;
举例：

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;

这句代码表示将查询的数据按照条件分别插入两个表中。

二、一个来源插入多个目标表（有条件，首次匹配即跳到下一条）

INSERT FIRST WHEN sal > 25000 THEN INTO special_sal
VALUES
  (deptid, sal)

WHEN hiredate LIKE
  ('%00%') THEN INTO hiredate_history_00
VALUES
  (deptid, hiredate)

WHEN hiredate LIKE
  ('%99%') THEN INTO hiredate_history_99
VALUES
  (deptid, hiredate)

ELSE INTO hiredate_history
VALUES
  (deptid, hiredate)
  SELECT department_id deptid, SUM(salary) sal, MAX(hire_date) hiredate
    FROM employees
   GROUP BY department_id;

这句话最重要的思想是如果数据被第一个表匹配了，那么它就不会参与下一个表的匹配（语法就是把上一个例子的all改成了first）。

三、实现列转行

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;

create table sales_source_data
(EMPLOYEE_ID number,
 week_id number, 
 sales_MON number, 
 sales_TUE number, 
 sales_WED number, 
 sales_THUR number,
 sales_FRI number);
 
create table sales_info(employee_id number,week_id number, sales_everyday number);
 
insert into sales_source_data values (149,1,10000,12000,15000,11000,6000);

这个其实就是对Insert all的使用例子。
四、分析函数

SELECT e.last_name
      ,e.salary
      ,d.department_name
      ,AVG(e.salary) over(PARTITION BY d.department_name) department_avg_salary
      ,MAX(e.salary) over(PARTITION BY d.department_name) department_max_salary
      ,MIN(e.salary) over(PARTITION BY d.department_name) department_min_salary
  FROM employees   e
      ,departments d
 WHERE 1 = 1
   AND e.department_id = d.department_id;


SELECT d.department_name
      ,e.last_name
      ,e.salary
      ,rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1
      ,dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2
      ,row_number() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank3
  FROM employees   e
      ,departments d
 WHERE 1 = 1
   AND e.department_id = d.department_id;

五、闪回功能（误删找回）

DELETE FROM departments
WHERE department_name = 'Shipping';

COMMIT;

SELECT * FROM departments WHERE department_name = 'Shipping'; 

insert into departments
SELECT * FROM departments AS OF TIMESTAMP SYSDATE -5/(24*60)
WHERE department_name = 'Shipping';

六、全局临时表

CREATE GLOBAL TEMPORARY TABLE temp_table_session1 (header_id number) ON COMMIT PRESERVE ROWS;


CREATE GLOBAL TEMPORARY TABLE temp_table_session2 (header_id number) ON COMMIT delete  ROWS;

insert into temp_table_session1 values(1);

select * from temp_table_session1;


insert into temp_table_session2 values(1);

select * from temp_table_session2;