SQL高级语法——DML

本文介绍了SQL中几种高级插入数据的方法,包括WITH CHECK OPTION约束、WHEN THEN条件判断、INSERT ALL多表插入、INSERT FIRST单表优先插入及MERGE更新插入等,帮助读者掌握更高效的SQL数据操作技能。

一、WITH CHECK OPTION

INSERT INTO
  (SELECT EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY
     FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 50 WITH CHECK OPTION)
VALUES
  (123,
   'Ryan',
   'brj880719@hotmail.com',
   TO_DATE('2011-09-10', 'yyyy-mm-dd'),
   '10',
   5000);

二、WHEN THEN

INSERT
  WHEN MOD(OBJECT_ID, 2) = 1 THEN
       INTO T1(X, Y) VALUES(RN, OBJECT_ID)
  WHEN MOD(OBJECT_ID, 2) = 0 THEN
       INTO T2(X, Y) VALUES(RN, CREATED)
SELECT ROWNUM RN, OBJECT_ID, CREATED FROM ALL_OBJECTS

三、INSERT ALL

每个表都插入
  1. 无条件
    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
  2. 有条件
    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

三、INSERT FIRST

只插入首个满足条件的表
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

四、MERGE

MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
   ON (join condition)
 WHEN MATCHED THEN
      UPDATE 
         SET col1 = col_val1,
             col2 = col_val2
 WHEN NOT MACHED THEN
      INSERT (column_list)
      VALUES (column_values);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值