Oracle笔记-Multitable INSERT 的用法

 

        前言:为避免日趋衰退的记忆力,转载参考文章,以供日后查阅。

一、Insert基础用法

语法:

    Insert Into 表名 (字段1,字段2,字段3...)

    Values (值1,值2,值3...)

例子:

INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);

语法:

    Insert Into 表名 (字段1,字段2,字段3...)

    select 语句

不做任何解释,实在是没啥好说的�_�,注意别跟create table ...as select一样,insert中的select前面可没as ^_^

二、Unconditional INSERT ALL 用法

直接拿例子了:

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;

解释:将select查询出来的结果,每返回一行就分别插入表sal_history 和mgr_history 中,优点就是只做一次查询即可分别查询2个表,假如使用基础用法,将进行2次查询。

三、Conditional INSERT ALL

还是直接拿例子:

INSERT ALL
    WHEN SAL > 10000 THEN
        INTO sal_history VALUES(EMPID,HIREDATE,SAL)
   WHEN MGR > 200   THEN
       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;

解释:将select查询出来的结果,每返回一行就判断,SAL > 10000 就插入表sal_history ,MGR > 200就插入mgr_history ,优点和前面提到一样。

四、Conditional FIRST INSERT

仍然是例子:

INSERT FIRST
   WHEN SAL  > 25000          THEN
       INTO special_sal VALUES(DEPTID, SAL)
   WHEN HIREDATE like ('%') THEN
      INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
   WHEN HIREDATE like ('�%') 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;

解释:将select查询出来的结果,每返回一行就判断,SAL  > 25000就插入表special_sal ,否则HIREDATE like ('%') ,符合就插入hiredate_history_00,前面2个条件还是不成力,就判断HIREDATE like ('�%') ,符合就插入表hiredate_history_99 ,前面3个条件都不符合,只好插入表hiredate_history 了。

打完这我都头晕了,假如学过程序设计,看下面的清晰明了:

if  SAL  > 25000 then

    INTO special_sal VALUES(DEPTID, SAL)
else

(

    if HIREDATE like ('%') THEN

         INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
    else

        (

            if HIREDATE like ('�%') THEN

                INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
            else

                INTO hiredate_history VALUES(DEPTID, HIREDATE)
        )

 )

假如还看不理解,看官方的这句话吧,“If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be skipped.”


五、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;

解释:老实说,看不出有啥用法,真的非要说,咱就把它当作行列转换吧,如果上面表在加多一列,比如INTO sales_info VALUES (employee_id,week_id,week_which,sales_MON) 改成 INTO sales_info VALUES (employee_id,week_id,'星期一',sales_MON)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值