INSERT ALL INTO SAL_HISTORY(empno, hire_date, sal) VALUES( empid, hiredate, sal ) INTO MGR_HISTORY(employee_id, mgr, salary) VALUES( empid, mgr, sal ) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM EMPLOYEES WHERE employee_id > 200; 这是无条件的insert ---------------- INSERT ALL WHEN sal > 10000 THEN INTO SAL_HISTORY(empno, hire_date, sal) VALUES( empid, hiredate, sal ) WHEN mgr > 200 THEN INTO MGR_HISTORY(employee_id, mgr, salary) 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; 这也是有条件的
2.用过程循环实现,
3.用动态SQL 也可以实现,
当然就是直接写insert 语句对几张表进行操作了。
INTO SAL_HISTORY(empno, hire_date, sal) VALUES( empid, hiredate, sal )
INTO MGR_HISTORY(employee_id, mgr, salary) VALUES( empid, mgr, sal )
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM EMPLOYEES
WHERE employee_id > 200;
这是无条件的insert
----------------
INSERT ALL
WHEN sal > 10000 THEN
INTO SAL_HISTORY(empno, hire_date, sal) VALUES( empid, hiredate, sal )
WHEN mgr > 200 THEN
INTO MGR_HISTORY(employee_id, mgr, salary) 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;
这也是有条件的