Oracle Insert增强与SQL进阶

柔情只为你懂 2022-05-19 06:38 142阅读 0赞

**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;