1．DECODE, NVL, and NVL2简介：
These functions are used to make decisions based on data values within an SQL statement without resorting(求助) to a procedural language like PL/SQL
DECODE和NVL、NVL2函数语法格式
Function syntax
|
Logic equivalent
|
DECODE(E1, E2, E3, E4)
|
IF E1 = E2 THEN E3 ELSE E4
|
NVL(E1, E2)
|
IF E1 IS NULL THEN E2 ELSE E1
|
NVL2(E1, E2, E3)
|
IF E1 IS NULL THEN E3 ELSE E2
|
2．DECODE函数：
The DECODE function can be thought of as an inline IF statement. DECODE takes four or more expressions as arguments. Each expression can be a column, a literal, a function, or even a subquery.Since the DECODE function compares two expressions and returns one of two expressions to the caller, it is important that the expression types are identical or that they can at least be translated to be the same type. The same exception would be thrown if the two return expressions (E3 and E4) did not have comparable types.
SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,
DECODE(p.status, 'INSTOCK', 'In Stock',
'DISC', 'Discontinued',
'BACKORD', 'Backordered',
'ENROUTE', 'Arriving Shortly',
'UNAVAIL', 'No Shipment Scheduled',
'Unknown') part_status
FROM part p, supplier s
WHERE p.supplier_id = s.supplier_id;
上述DECODE函数将part表status字段的值和前五个表达式进 行比较，如果有匹配的则返回其后紧接着的字符串，如果没有一个匹配得到，则返回字符串’Uunknown’。
3．NVL和NVL2函数：
The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE. The following example uses NVL2 to produce the same results as the DECODE example shown in the previous section:
SELECT lname, NVL2(manager_emp_id, 'NON-MANAGER', 'MANAGER') emp_type
FROM employee;
注 意：NVL2函数的语法是NVL2(E1, E2, E3) IF E1 IS NULL THEN E3 ELSE E2， 其中空的时候是用E3赋值，而非用E2赋值
4．Case子句简介：
Case子句相对于Decode函数的优势：
● CASE expressions can be used everywhere that DECODE functions are permitted.
● CASE expressions are more readable than DECODE expressions.
● CASE expressions execute faster than DECODE expressions
● CASE expressions handle complex logic more gracefully than DECODE expressions.
● CASE is ANSI-compliant, whereas DECODE is proprietary.
Since CASE is built into Oracle's SQL grammar, there is no need to call a function in order to evaluate the if-then-else logic. While the difference in execution time is miniscule for a single call, the aggregate time savings from not calling a function should become noticeable when working with large result sets.
(由 于Oracle的SQL语法包含了CASE，所以在需要计算if-then-else逻辑时不需要调用函数，在只调用一次时，两者的差别可以 忽略不计，但如果正在处理的是一个大型的记录集，那么每次调用函数加起来的时间总和就值得考虑了。
使 用CASE和使用DECODE函数相比，唯一的劣势就是Oracle 8i PL/SQL不支持CASE函数。)
5．Case子句的语法：
CASE
WHEN C1 THEN R1
WHEN C2 THEN R2
...
WHEN CN THEN RN
ELSE RD
END
Conditions are evaluated in order. When a condition is found that evaluates to TRUE, the corresponding result is returned, and execution of the CASE logic ends. Therefore, carefully order WHEN clauses to ensure that the desired results are achieved
SELECT co.order_nbr, co.cust_nbr,
CASE WHEN co.expected_ship_dt IS NULL THEN 'NOT YET SCHEDULED'
WHEN co.expected_ship_dt <= SYSDATE THEN 'SHIPPING DELAYED'
WHEN co.expected_ship_dt <= SYSDATE + 2 THEN 'SHIPPING SOON'
ELSE 'BACKORDERED'
END ship_status
FROM cust_order co
WHERE co.ship_dt IS NULL AND co.cancelled_dt IS NULL;
Similar to DECODE, all results in the CASE expression must have comparable types; otherwise, ORA-932 will be thrown. Each condition in each WHEN clause is independent of the others, however, so your conditions can include various data types, as demonstrated in the next example:
SELECT co.order_nbr, co.cust_nbr,
CASE
WHEN co.sale_price > 10000 THEN 'BIG ORDER'
WHEN co.cust_nbr IN (SELECT cust_nbr FROM customer WHERE tot_orders > 100)
THEN 'ORDER FROM FREQUENT CUSTOMER'
WHEN co.order_dt < TRUNC(SYSDATE) -- 7 THEN 'OLD ORDER'
ELSE 'UNINTERESTING ORDER'
END
FROM cust_order co
WHERE co.ship_dt IS NULL AND co.cancelled_dt IS NULL;
6．简单CASE子句：
Simple CASE expressions are structured differently than searched CASE expressions in that the WHEN clauses contain expressions instead of conditions, and a single expression to be compared to the expressions in each WHEN clause is placed in the CASE clause. Here's the syntax
(简 单CASE表达式在结构上和搜索CASE表达式有着一定的区别：首先WHEN子句包含的是表达式而非条件，其次原来每个WHEN子句中独立的 比较条件被移至CASE子句处，成为唯一的条件。因为简单CASE表达式中每个WHEN的表达式都要和CASE中的条件进行比较，所以它们的类型必须都为 一致的，这使简单CASE表达式的灵活性受到一定的影响)
CASE E0
WHEN E1 THEN R1
WHEN E2 THEN R2
...
WHEN EN THEN RN
ELSE RD
END
SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,
CASE p.status
WHEN 'INSTOCK' THEN 'In Stock'
WHEN 'DISC' THEN 'Discontinued'
WHEN 'BACKORD' THEN 'Backordered'
WHEN 'ENROUTE' THEN 'Arriving Shortly'
WHEN 'UNAVAIL' THEN 'No Shipment Scheduled'
ELSE 'Unknown'
END part_status
FROM part p, supplier s
WHERE p.supplier_id = s.supplier_id;
3. DECODE and CASE Examples:
A．行转列：
You may have run into a situation where you are performing aggregations over a finite set of values, such as days of the week or months of the year, but you want the result set to contain one row with N columns rather than N rows with two columns.
例子：行式统计每个季度的销售额
SELECT TO_CHAR(order_dt, 'Q') sales_quarter, SUM(sale_price) tot_sales
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY TO_CHAR(order_dt, 'Q')
ORDER BY 1;
S TOT_SALES
- ----------
1 9739328
2 10379833
3 9703114
4 9772633
例子：列式统计每个季度的销售额
SELECT SUM(DECODE(TO_CHAR(order_dt, 'Q'), '1', sale_price, 0)) Q_1,
SUM(DECODE(TO_CHAR (order_dt, 'Q'), '2', sale_price, 0)) Q_2,
SUM(DECODE(TO_CHAR (order_dt, 'Q'), '3', sale_price, 0)) Q_3,
SUM(DECODE(TO_CHAR (order_dt, 'Q'), '4', sale_price, 0)) Q_4
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY');
Q_1 Q_2 Q_3 Q_4
---------- ---------- ---------- ----------
9739328 10379833 9703114 9772633
Each of the four columns in the previous query are identical, except for the quarter being checked by the DECODE function. For the Q_1 column, for example, a value of 0 is returned unless the order falls in the first quarter, in which case the sale_price column is returned. When the values from all orders in 2001 are summed, only the first quarter orders are added to the total (for Q_1), which has the effect of summing all first quarter orders while ignoring orders for quarters 2, 3, and 4. The same logic is used for Q_2, Q_3, and Q_4 to sum orders for quarters 2, 3, and 4 respectively
SELECT SUM(CASE WHEN TO_CHAR(order_dt, 'Q') = '1' THEN sale_price ELSE 0 END) Q_1,
SUM(CASE WHEN TO_CHAR(order_dt, 'Q') = '2' THEN sale_price ELSE 0 END) Q_2,
SUM(CASE WHEN TO_CHAR(order_dt, 'Q') = '3' THEN sale_price ELSE 0 END) Q_3,
SUM(CASE WHEN TO_CHAR(order_dt, 'Q') = '4' THEN sale_price ELSE 0 END) Q_4
FROM cust_order
WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
AND order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY');
B. 选择性更新：
In some situations, you may need to modify data only if certain conditions exist. For example, you have a table that records information such as the total number of orders and the largest order booked during the current month. Here's the table definition
describe mtd_orders;
Name Null? Type
----------------------------------------- -------- ------------
TOT_ORDERS NOT NULL NUMBER(7)
TOT_SALE_PRICE NOT NULL NUMBER(11,2)
MAX_SALE_PRICE NOT NULL NUMBER(9,2)
UPDATE mtd_orders mtdo
SET (mtdo.tot_orders, mtdo.tot_sale_price, mtdo.max_sale_price) =
(SELECT mtdo.tot_orders + day_tot.tot_orders,
mtdo.tot_sale_price + NVL (day_tot.tot_sale_price, 0),
DECODE (GREATEST (mtdo.max_sale_price,
NVL (day_tot.max_sale_price, 0)
),
mtdo.max_sale_price, mtdo.max_sale_price,
day_tot.max_sale_price
) --判断是否有比 当前最高销售额更高的销售额，有则更新该字段
FROM (SELECT COUNT (*) tot_orders,
SUM (sale_price) tot_sale_price,
MAX (sale_price) max_sale_price
FROM cust_order
WHERE cancelled_dt IS NULL AND order_dt >= TRUNC (SYSDATE)) day_tot);
In this statement, the max_sale_price column is set equal to itself unless the value returned from the subquery is greater than the current column value, in which case the column is set to the value returned from the subquery. The next statement uses CASE to perform the same optional update
UPDATE mtd_orders mtdo
SET (mtdo.tot_orders, mtdo.tot_sale_price, mtdo.max_sale_price) =
(SELECT mtdo.tot_orders + day_tot.tot_orders,
mtdo.tot_sale_price + day_tot.tot_sale_price,
CASE --判断是否有比当前最高销售额更高的销售额，有则 更新该字段
WHEN day_tot.max_sale_price > mtdo.max_sale_price
THEN day_tot.max_sale_price
ELSE mtdo.max_sale_price
END
FROM (
SELECT COUNT (*) tot_orders,
SUM (sale_price) tot_sale_price,
MAX (sale_price) max_sale_price
FROM cust_order
WHERE cancelled_dt IS NULL AND order_dt>= TRUNC(SYSDATE)) day_tot);
C．除零错误：
As a general rule, you should write your code so unexpected data values are handled gracefully. One of the more common arithmetic errors is ORA-01476: divisor is equal to zero. Whether the value is retrieved from a column, passed in via a bind variable, or returned by a function call, always wrap divisors with DECODE or CASE, as illustrated by the following example
SELECT p.part_nbr, SYSDATE + (p.inventory_qty /
DECODE(my_pkg.get_daily_part_usage(p.part_nbr), NULL, 1,
0, 1, my_pkg.get_daily_part_usage(p.part_nbr))) anticipated_shortage_dt
FROM part p
WHERE p.inventory_qty > 0;
SELECT p.part_nbr, SYSDATE + (p.inventory_qty /
CASE WHEN my_pkg.get_daily_part_usage(p.part_nbr) > 0
THEN my_pkg.get_daily_part_usage(p.part_nbr)
ELSE 1 END) anticipated_shortage_dt
FROM part p
WHERE p.inventory_qty > 0;
引 用：
http://bonnyzouming.spaces.live.com/blog/cns!CA8A6A288D8C7AEC!178.trak
引 述這則內容的部落格
沒有留言:
張貼留言