question one
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 10000, then the detail 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
answer:C
解析：你需要将存在表NEW_CUST中的用户信息加载到表CUST和CUST_SPECIAL中。当用户的信用额度限制高于10000时，该用户记录插入表CUST_SPECIAL中。所有的用户记录都要插入表CUST 中。一下那种技术可以有效地完成上述要求？
答案是C.the multitable insert command
multitable insert以下是官方文档中对于multitable insert 的示例
Multitable Inserts: Examples
-
The following example uses the multitable insert syntax to insert into the sample table sh.sales some data from an input table with a different structure.
以下例子通过使用multitable insert将不同格式的输入表数据插入到示例表 sh.sales中。 -
Note:
A number of NOT NULL constraints on the sales table have been disabled for purposes of this example, because the example ignores a number of table columns for the sake of brevity.
The input table looks like this:
注意：在本例中，为了简化忽略了若干表的字段，因此sales表中的非空约束被禁用。
SELECT * FROM sales_input_table;
PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
111 222 01-OCT-00 100 200 300 400 500 600 700
222 333 08-OCT-00 200 300 400 500 600 700 800
333 444 15-OCT-00 300 400 500 600 700 800 900
- The multitable insert statement looks like this:
multitable insert语句是这样的:
INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
- Assuming these are the only rows in the sales table, the contents now look like this:
假设表sales中只有以上记录，那么此时sales表的内容如下：
SELECT * FROM sales
ORDER BY prod_id, cust_id, time_id;
PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT COST
---------- ---------- --------- - ---------- ------------- ---------- ----------
111 222 01-OCT-00 100
111 222 02-OCT-00 200
111 222 03-OCT-00 300
111 222 04-OCT-00 400
111 222 05-OCT-00 500
111 222 06-OCT-00 600
111 222 07-OCT-00 700
222 333 08-OCT-00 200
222 333 09-OCT-00 300
222 333 10-OCT-00 400
222 333 11-OCT-00 500
222 333 12-OCT-00 600
222 333 13-OCT-00 700
222 333 14-OCT-00 800
333 444 15-OCT-00 300
333 444 16-OCT-00 400
333 444 17-OCT-00 500
333 444 18-OCT-00 600
333 444 19-OCT-00 700
333 444 20-OCT-00 800
333 444 21-OCT-00 900
- The next examples insert into multiple tables. Suppose you want to provide to sales representatives some information on orders of various sizes. The following example creates tables for small, medium, large, and special orders and populates those tables with data from the sample table oe.orders:
下面就是向多个表插入数据。假设你想给销售人员提供一些不同大小的订单信息。接下来的例子就是创建了一个小，中，大以及特殊定单的表，表中的数据来源于示例表 oe.orders：
CREATE TABLE small_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_rep_id NUMBER(6)
);
CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;
CREATE TABLE special_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
credit_limit NUMBER(9,2),
cust_email VARCHAR2(30)
);
- The first multitable insert populates only the tables for small, medium, and large orders:
首先进行的多表插入是将小，中，大订单插入相应表中：
INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total > 200000 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
- You can accomplish the same thing using the ELSE clause in place of the insert into the large_orders table:
你也可以像下面的例子中一样，通过else语句来实现大订单信息的插入：
INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
- The next example inserts into the small, medium, and large tables, as in the preceding example, and also puts orders greater than 290,000 into the special_orders table. This table also shows how to use column aliases to simplify the statement:
接下来的例子除了同上例将小、中、大订单插入表中以外，还将订单量大于29000的特殊订单插入表中。除此之外，还举例说明了如何通过别名来简化语句。
INSERT ALL
WHEN ottl <= 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl <= 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 200000 THEN
into large_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
- Finally, the next example uses the FIRST clause to put orders greater than 290,000 into the special_orders table and exclude those orders from the large_orders table:
最后这个例子，使用First语句，将订单大于290000的记录插入special_orders中，同时这些记录将不会进入large_orders 表。
INSERT FIRST
WHEN ottl <= 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl <= 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
WHEN ottl > 200000 THEN
INTO large_orders
VALUES(oid, ottl, sid, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
