|
课程目标： - 使用子查询处理数据
- 使用多表插入
- 使用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
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-12-14 12:51:38
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对