7- Decode和Case

 

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;