課程目標：

使用子查詢處理資料
使用多表插入
使用merge
使用閃回版本查詢跟蹤資料在過去一段時間的變化情況

1、使用子查詢

使用子查詢插入資料
使用子查詢查詢資料
使用子查詢更新資料
使用子查詢刪除資料

例子：使用子查詢從其他表複製行進行插入，不需要使用values關鍵字，欄位要匹配

SQL> insert into sales_reps(id,name,salary,commission_pct)

2 select employee_id,last_name,salary,commission_pct

3 from employees where job_id like '%REP%';

33 rows created.

例子：將資料插入到一個子查詢，實際上使用了子查詢的表和欄位，只插入了一行

SQL> create table emp13 as

2 select employee_id,last_name,email,hire_date,job_id,salary,department_id

3 from employees;

Table created.

SQL> insert into(

2 select employee_id,last_name,email,hire_date,job_id,salary,department_id

3 from emp13 where department_id=50)

4 values(99999,'Taylor','DTAYLOR',to_date('07-JUN-99','DD-MON-RR'),'ST_CLREK',5000,50);

1 row created.

檢視結果進行驗證

SQL> select employee_id,last_name,email,hire_date,job_id,salary,department_id

2 from emp13 where department_id=50;

例子：使用子查詢作為資料來源，查詢薪水比部門平均薪水大的員工

SQL> select a.last_name,a.salary,a.department_id,b.salavg

2 from employees a join (

3 select department_id,avg(salary) salavg from employees group by department_id) b

4 on a.department_id=b.department_id and a.salary>b.salavg;

LAST_NAME SALARY DEPARTMENT_ID SALAVG

------------------------- ---------- ------------- ----------

Fay 6000 10 5200

例子：使用其他表子查詢的結果更新表的欄位，更新表emp13中欄位job_id等於表employees的employee_id為205的job_id，欄位salary等於表employees的employee_id為168的salary

SQL> update emp13 set

2 job_id=(select job_id from employees where employee_id=205),

3 salary=(select salary from employees where employee_id=168)

4 where employee_id=114;

1 row updated.

例子：使用其他表子查詢的結果更新表的欄位，更新表emp13中欄位job_id等於表employees的employee_id為200的job_id的記錄，將欄位department_id等於表employees的employee_id為100的department_id

SQL> update emp13 set

2 department_id=(select department_id from employees where employee_id=100)

3 where job_id=(select job_id from employees where employee_id=200);

1 row updated.

例子：使用其他表子查詢的結果刪除表的記錄，刪除表emp13中department_id等於表departments中department_name包含Public字元的department_id的記錄

SQL> delete from emp13

2 where department_id=(select department_id from departments where department_name like '%Public%');

1 row deleted.

例子：透過使用with check option限定插入的值需要滿足where條件，與前面的檢視一樣

SQL> insert into(select employee_id,last_name,email,hire_date,job_id,salary from emp13 where department_id=50 with check option)

2 values(99998,'Smith','JSMITH',to_date('07-JUN-99','DD-MON-RR'),'ST-CLERK',5000);

insert into(select employee_id,last_name,email,hire_date,job_id,salary from emp13 where department_id=50 with check option)

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

2、顯式使用default

可以在插入和更新的時候使用default，如果沒有定義default，那麼就是空

例子：插入的時候使用default

SQL> create table deptm3 as select department_id,department_name,manager_id from departments;

Table created.

檢視欄位的預設值定義

SQL> select table_name,column_name,data_default from user_tab_columns where table_name='DEPTM3';

TABLE_NAME COLUMN_NAME DATA_DEFAULT

------------------------------ ------------------------------ --------------------

DEPTM3 DEPARTMENT_ID

DEPTM3 DEPARTMENT_NAME

DEPTM3 MANAGER_ID

SQL> insert into deptm3(department_id,department_name,manager_id) values(300,'Engineering',default);

1 row created.

例子：更新的時候使用default

SQL> update deptm3 set manager_id=default where department_id=10;

1 row updated.

3、多表插入

一條DML語句向多表插入資料，減少查詢次數，提高效能
常用於資料倉儲對源資料進行抽取

multitable insert 語句型別：

unconditional insert all：無條件全部插入，子查詢返回的每一行都插入目標表
conditional insert all：有條件全部插入，子查詢返回的每一行在滿足特定條件下插入目標表，所有條件都要進行比較
pivoting insert：unconditional insert all的一種特殊情況，旋轉插入，用於將非關係資料轉換成關係資料
conditional insert first：有條件插入第一個滿足條件的，子查詢返回的每一行依次比較條件，插入第一次滿足條件的

多表插入語法：

conditional_insert_clause語法：

多表插入的限制：

不能在檢視或者物化檢視上執行多表插入，只能在表上面執行多表插入語句
不能對遠端表執行多表插入
多表插入時，最多隻能插入999列

例子：無條件全部插入，將表employees中employee_id大於200的記錄分別插入到表sal_history和表mgr_history

先建立2個空表

SQL> create table sal_history as

2 select employee_id empid,hire_date hiredate,salary sal from employees where 1=0;

Table created.

SQL> create table mgr_history as

2 select employee_id empid,manager_id mgr,salary sal from employees where 1=0;

Table created.

再進行插入

SQL> insert all

2 into sal_history values(empid,hiredate,sal)

3 into mgr_history values(empid,mgr,sal)

4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr

5 from employees where employee_id>200;

14 rows created.

例子：有條件全部插入，將表employees中employee_id大於200的記錄中salary大於10000的記錄插入到表sal_history，manager_id大於200的記錄插入到表mgr_history

先回滾

SQL> rollback;

Rollback complete.

SQL> insert all

2 when sal>10000 then into sal_history values(empid,hiredate,sal)

3 when mgr>200 then into mgr_history values(empid,mgr,sal)

4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr

5 from employees where employee_id>200;

5 rows created.

例子：有條件插入第一個滿足條件的，先獲取人員表中每個部門的薪水總和及最大入職時間，如果薪水總和大於25000，則插入表special_sal，如果最大入職時間包含字元00，則插入表hiredate_history_00，如果最大入職時間包含字元99，則插入表hiredate_history_99，如果以上條件都不滿足，則插入表hiredate_history

先回滾

SQL> rollback;

Rollback complete.

建立需要的表

SQL> create table special_sal as

2 select department_id deptid,salary sal from employees where 1=0;

Table created.

SQL> create table hiredate_history_00 as

2 select department_id deptid,hire_date hiredate from employees where 1=0;

Table created.

SQL> create table hiredate_history_99 as

2 select department_id deptid,hire_date hiredate from employees where 1=0;

Table created.

SQL> create table hiredate_history as

2 select * from hiredate_history_99;

Table created.

再進行插入

SQL> insert first

2 when sal>25000 then into special_sal values(deptid,sal)

3 when hiredate like ('%00%') then into hiredate_history_00 values(deptid,hiredate)

4 when hiredate like ('%99%') then into hiredate_history_99 values(deptid,hiredate)

5 else into hiredate_history values(deptid,hiredate)

6 select department_id deptid,sum(salary) sal,max(hire_date) hiredate

7 from employees group by department_id;

12 rows created.

例子：旋轉插入

將非關係資料錶轉換為關係表

SQL> create table sales_source_data(

2 employee_id number(6),

3 week_id number(2),

4 sales_mon number(8,2),

5 sales_tue number(8,2),

6 sales_wed number(8,2),

7 sales_thur number(8,2),

8 sales_fri number(8,2));

Table created.

SQL> insert into sales_source_data values(178,6,1750,2200,1500,1500,3000);

1 row created.

SQL> select * from sales_source_data;

EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI

----------- ---------- ---------- ---------- ---------- ---------- ----------

178 6 1750 2200 1500 1500 3000

SQL> create table sales_info(

2 employee_id number(6),

3 week number(2),

4 sales number(8,2));

Table created.

SQL> insert all

2 into sales_info values(employee_id,week_id,sales_mon)

3 into sales_info values(employee_id,week_id,sales_tue)

4 into sales_info values(employee_id,week_id,sales_wed)

5 into sales_info values(employee_id,week_id,sales_thur)

6 into sales_info values(employee_id,week_id,sales_fri)

7 select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri from sales_source_data;

5 rows created.

4、merge語句

MERGE語句是Oracle9i新增的語法，用來合併UPDATE和INSERT語句。透過MERGE語句，根據一張表或多表聯合查詢的連線條件對另外一張表進行查詢，連線條件匹配上的進行UPDATE，無法匹配的執行INSERT。這個語法僅需要一次全表掃描就完成了全部工作，執行效率要高於INSERT＋UPDATE。透過MERGE能夠在一個SQL語句中對一個表同時執行INSERT和UPDATE操作，經常用於將生產表的資料更新到歷史表。

語法：

例子：比較表employees和表emp13，如果employee_id相等，則更新表emp13的記錄等於表employees的對應記錄，如果不相等，則將表employees表中的記錄插入到表emp13中

SQL> create table emp13 as

2 select employee_id,last_name,salary,department_id from employees where 1=0;

Table created.

SQL> select * from emp13;

no rows selected

SQL> merge into emp13 n

2 using employees e on(n.employee_id=e.employee_id)

3 when matched then

4 update set

5 n.last_name=e.last_name,

6 n.salary=e.salary,

7 n.department_id=e.department_id

8 when not matched then

9 insert values(e.employee_id,e.last_name,e.salary,e.department_id);

108 rows merged.

刪除表emp13一部分記錄，修改emp13一部分記錄

SQL> delete from emp13 where rownum<50;

49 rows deleted.

SQL> update emp13 set last_name='abc',salary=0;

59 rows updated.

再次執行剛才的merge語句，又和剛才一樣了。

SQL> merge into emp13 n

2 using employees e on(n.employee_id=e.employee_id)

3 when matched then

4 update set

5 n.last_name=e.last_name,

6 n.salary=e.salary,

7 n.department_id=e.department_id

8 when not matched then

9 insert values(e.employee_id,e.last_name,e.salary,e.department_id);

108 rows merged.

還可以在匹配的時候新增條件

SQL> merge into emp13 n

2 using employees e on(n.employee_id=e.employee_id)

3 when matched then

4 update set

5 n.last_name=e.last_name,

6 n.salary=e.salary,

7 n.department_id=e.department_id

8 where e.department_id=50

9 when not matched then

10 insert values(e.employee_id,e.last_name,e.salary,e.department_id)

11 where e.department_id=50;

46 rows merged.

還可以在匹配的時候刪除記錄

SQL> create table orders_master(

2 order_id number,

3 order_total number);

Table created.

SQL> create table monthly_orders(

2 order_id number,

3 order_total number);

Table created.

SQL> insert into orders_master values(1,1000);

1 row created.

SQL> insert into orders_master values(2,2000);

1 row created.

SQL> insert into orders_master values(3,3000);

1 row created.

SQL> insert into orders_master values(4,null);

1 row created.

SQL> insert into monthly_orders values(2,2500);

1 row created.

SQL> insert into monthly_orders values(3,null);

1 row created.

SQL> select * from orders_master;

ORDER_ID ORDER_TOTAL

---------- -----------

1 1000

2 2000

3 3000

4

SQL> select * from monthly_orders;

ORDER_ID ORDER_TOTAL

---------- -----------

2 2500

3

SQL> merge into orders_master o

2 using monthly_orders m on(o.order_id=m.order_id)

3 when matched then

4 update set o.order_total=m.order_total

5 delete where(m.order_total is null)

6 when not matched then

7 insert values(m.order_id,m.order_total);

2 rows merged.

SQL> select * from orders_master;

ORDER_ID ORDER_TOTAL

---------- -----------

1 1000

2 2500

4

更新的時候不能更新用於連線條件的列

SQL> merge into emp13 n

2 using employees e on(n.employee_id=e.employee_id)

3 when matched then

4 update set

5 n.last_name=e.last_name,

6 n.salary=e.salary,

7 n.department_id=e.department_id,

8 n.employee_id=e.employee_id

9 when not matched then

10 insert values(e.employee_id,e.last_name,e.salary,e.department_id);

using employees e on(n.employee_id=e.employee_id)

*

ERROR at line 2:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "N"."EMPLOYEE_ID"

5、跟蹤資料改變--閃回版本查詢

閃回版本查詢只返回已經提交的事務

例子：查詢人員薪水的修改記錄

SQL> create table employees3 as select * from employees;

Table created.

SQL> select salary from employees3 where employee_id=107;

SALARY

----------

4200

SQL> update employees3 set salary=salary*1.30 where employee_id=107;

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from employees3 versions between scn minvalue and maxvalue where employee_id=107;

SALARY

----------

5460

4200

SQL> select versions_starttime "start_date",versions_endtime "end_date",versions_operation,salary

2 from employees3 versions between scn minvalue and maxvalue where last_name='Lorentz';

start_date end_date V SALARY

------------------------------ ------------------------------ - ----------

06-NOV-15 08.18.52 AM U 5460

06-NOV-15 08.18.52 AM 4200

6、總結

Use DML statements and control transactions
Describe the features of multitableINSERTs
Use the following types of multitableINSERTs:

–Unconditional INSERT

–Pivoting INSERT

–Conditional ALLINSERT

–Conditional FIRSTINSERT

Merge rows in a table
Manipulate data by using subqueries
Track the changes to data over a period of time

7、相關習題

（1）You need to load information about new customers from the NEW_CUST table into the tables
CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the
details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into
the CUST table. Which technique should be used to load the data most efficiently?

A.external table

B.the MERGE command

C.the multitable INSERT command

D.INSERT using WITH CHECK OPTION

答案：C

（2）View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. Which is the best method to load data from the MARKS_DETAILS table to the MARKS table?

A.Pivoting INSERT

B.Unconditional INSERT

C.Conditional ALL INSERT

D.Conditional FIRST INSERT

答案：A

（3）Evaluate the following statements:

CREATE TABLE digits (id NUMBER(2),

description VARCHAR2(15));

INSERT INTO digits VALUES (1,'ONE');

UPDATE digits SET description ='TWO' WHERE id=1;

INSERT INTO digits VALUES (2,'TWO');

COMMIT;

DELETE FROM digits;

SELECTdescription FROM digits VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

What would be the outcome of the above query?

A.It would not display any values.

B.It would display the value TWO once.

C.It would display the value TWO twice.

D.It would display the values ONE, TWO, and TWO.

答案：C

（4）View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers. What is the error in the following MERGE statement? MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_id = cv.customer_id, c.cust_name = cv.cust_name, c.cust_email = cv.cust_email, c.income_level = cv.income_level WHEN NOT MATCHED THEN INSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv,income_level) WHERE cv.income_level >100000?

A.The CUSTOMER_ID column cannot be updated.

B.The INTO clause is misplaced in the command.

C.The WHERE clause cannot be used with INSERT.

D.CUSTOMER_VU cannot be used as a data source.

答案：A

（5）Evaluate the following statement: CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1% of their salary and also a salary increase of 1%. The salary of each employee can be obtained from the EMPLOYEES table. Which option should be used to perform this task most efficiently?

A.MERGE

B.Unconditional INSERT

C.Conditional ALL INSERT

D.Conditional FIRST INSERT

答案：A

（6）Which statement is true regarding Flashback Version Query?

A.It returns versions of rows only within a transaction.

B.It can be used in subqueries contained only in a SELECT statement.

C.It will return an error if the undo retention time is less than the lower bound time or SCN specified.

D.It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.

答案：D

（7）View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables. Evaluate the following MERGE statement: MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total);What would be the outcome of the above statement?

A.The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

B.The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

C.The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

D.The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and4.

答案：C

（8）The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_ORDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?
A.Pivoting INSERT
B.Unconditional INSERT
C.Conditional ALL INSERT
D.Conditional FIRST INSERT

答案：C

（9）View the Exhibit and examine the data in the CUST_DET table. You executed the following multitable INSERT statement: INSERT FIRST WHEN credit_limit >= 5000 THEN INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THEN INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THEN INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) ELSE INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) SELECT * FROM cust_det；The row will be inserted in _______.

A.CUST_1 table only because CREDIT_LIMIT condition is satisfied
B.CUST_1 and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied
C.CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied
D.CUST_1, CUST_2 and CUST_4 tables because CREDIT_LIMIT and GRADE conditions are satisfied for CUST_1 and CUST_2, and CUST_4 has no condition on it

答案：A

（10）Evaluate the following statement: INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders；Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement ？
A.They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
B.They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
C.They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
D.The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

答案：A

（11）The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_ORDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use？

A.Pivoting INSERT
B.Unconditional INSERT
C.Conditional ALL INSERT
D.Conditional FIRST INSERT

答案：C

（12）You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently？

A.external table
B.the MERGE command
C.the multitable INSERT command
D.INSERT using WITH CHECK OPTION

答案：C

OCP課程16：SQL之處理大資料

相關文章