Mastering Oracle Sql 摘记

几个没有被重视的使用：

1.      Multitable inserts

While inserting data into a single table is the norm, there are situations where data from a single source must be inserted either into multiple tables or into the same table multiple times. Such tasks would normally be handled programatically using PL/SQL, but Oracle9i introduced the concept of a multitable insert to allow complex data insertion via a single INSERT statement. For example, let's say that one of Mary Turner's customers wants to set up a recurring order on the last day of each month for the next six months. The following statement adds six rows to the cust_order table using a SELECT statement that returns exactly one row:

(INSERT ALL …SELECT …)

//一次性将一条记录多次插入一张表

INSERT ALL

INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 

  order_dt, expected_ship_dt, status)

VALUES (ord_nbr, cust_nbr, emp_id, 

  ord_dt, ord_dt + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 

  order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 1, cust_nbr, emp_id, 

  add_months(ord_dt, 1), add_months(ord_dt, 1) + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 

  order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 2, cust_nbr, emp_id, 

  add_months(ord_dt, 2), add_months(ord_dt, 2) + 7, status)

SELECT 99990 ord_nbr, c.cust_nbr cust_nbr, e.emp_id emp_id,

 

  last_day(SYSDATE) ord_dt, 'PENDING' status

 

FROM customer c CROSS JOIN employee e

 

WHERE e.fname = 'MARY' and e.lname = 'TURNER'

 

  and c.name = 'Gentech Industries';

 

 

you have seen how multiple rows can be inserted into the same table and how the same rows can be inserted into multiple tables. The next, and final, example of multitable inserts demonstrates how a conditional clause can be used to direct each row of data generated by the SELECT statement into zero, one, or many tables:

 

//一次性将多条记录插入不同的表

//其中WHEN是条件判断。如果有一个符合则执行对应SQL，然后游标走到下一//条记录，从新开始循环判断

//类似于

/*

do

        Switch ?

        Case ? ://sql continue;

        Case ? ://sql continue;

        Case ?://sql  continue;

While(?)

**/

 

(INSERT FIRST WHEN… SELECT…)

 

INSERT FIRST

 

  WHEN order_dt < TO_DATE('2001-01-01', 'YYYY-MM-DD') THEN

 

    INTO cust_order_2000 (order_nbr, cust_nbr, sales_emp_id,

 

      sale_price, order_dt)

 

    VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

 

  WHEN order_dt < TO_DATE('2002-01-01', 'YYYY-MM-DD') THEN

 

    INTO cust_order_2001 (order_nbr, cust_nbr, sales_emp_id,

 

      sale_price, order_dt)

 

    VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

 

  WHEN order_dt < TO_DATE('2003-01-01', 'YYYY-MM-DD') THEN

 

    INTO cust_order_2002 (order_nbr, cust_nbr, sales_emp_id,

 

      sale_price, order_dt)

 

    VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

 

SELECT co.order_nbr, co.cust_nbr, co.sales_emp_id,

 

  co.sale_price, co.order_dt

 

FROM cust_order co

 

WHERE co.cancelled_dt IS NULL

 

  AND co.ship_dt IS NOT NULL;

 

 

2.      MERGE

There are certain situations, especially within Data Warehouse applications, where you may want to either insert a new row into a table or update an existing row depending on whether or not the data already exists in the table. For example, you may receive a nightly feed of parts data that contains both parts that are known to the system along with parts just introduced by your suppliers. If a part number exists in the part table, you will need to update the unit_cost and status columns; otherwise, you will need to insert a new row.

While you could write code that reads each record from the feed, determines whether or not the part number exists in the part table, and issues either an INSERT or UPDATE statement, you could instead issue a single MERGE statement.^[3] Assuming that your data feed has been loaded into the part_stg staging table, your MERGE statement would look something like the following:

^[3] MERGE was introduced in Oracle9i.

 

/*

    简单翻译下：经常我们需要根据数据是否已经存在决定插入还是更新记录。

    这样我们需要3句。一是查询判断，而是更新或者插入。通过MERGE你可以一次性解决这个问题。

它通过WHEN MATCHED 进行是否查找到此记录的条件判断，根据条件判断，你可以做自己的事情。

从oracle9i开始引入的概念。

*/

 

MERGE INTO part p_dest

USING part_stg p_src

ON (p_dest.part_nbr = p_src.part_nbr)

WHEN MATCHED THEN 

  UPDATE SET p_dest.unit_cost = p_src.unit_cost, p_dest.status = p_src.status

WHEN NOT MATCHED THEN 

        INSERT (p_dest.part_nbr, p_dest.name, 

  p_dest.supplier_id, p_dest.status,  p_dest.inventory_qty, 

  p_dest.unit_cost, p_dest.resupply_date)

  VALUES (p_src.part_nbr, p_src.name, 

  p_src.supplier_id, p_src.status, 0, p_src.unit_cost, null);

 

NULLs and Aggregate Functions  （空值和聚合函数关系）
      Count(*) 和 Count(col_1) 都是计算有多少行。但是如果遇到col_1有空值的情况。则count(*) > count(col_1)。这说明，count(*)是不忽略空值的，事实上它数真是行数。count(col_1)则忽略空值的，如果遇到col_1为 空，则不计数的。要注意了！

SUM, MAX, MIN, AVG 同样忽略空值.

SELECT COUNT(*), SUM(sale_price), AVG(sale_price) FROM cust_order;

COUNT(*)  COUNT(sale_price)   SUM(SALE_PRICE) AVG(SALE_PRICE)
20              14                              788                      56.2857143 = 788/14   <------------不是20！

如果要获取 788/20这种平均则：
SELECT AVG(NVL(sale_price,0)) FROM cust_order;这种形式。当遇到 空的时候转换为0。