ocp考試題庫：1Z0-051-006題：完整題庫請點擊這里聯(lián)系老師咨詢了解

6. Examine the structure of the SHIPMENTS table:

name Null Type

PO_ID NOT NULL NUMBER(3)

PO_DATE NOT NULL DATE

SHIPMENT_DATE NOT NULL DATE

SHIPMENT_MODE VARCHAR2(30)

SHIPMENT_COST NUMBER(8,2)

You want to generate a report that displays the PO_ID and the penalty amount to be paid if the

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.

Evaluate the following two queries:

SQL> SELECT po_id, CASE

WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN

TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY

FROM shipments;

SQL>SELECT po_id, DECODE

(MONTHS_BETWEEN (po_date,shipment_date)>1,

TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY

FROM shipments;

Which statement is true regarding the above commands?

A. Both execute successfully and give correct results.

B. Only the first query executes successfully but gives a wrong result.

C. Only the first query executes successfully and gives the correct result.

D. Only the second query executes successfully but gives a wrong result.

E. Only the second query executes successfully and gives the correct result.

Answer: C

試題解析：

題意要求： 顯示PO_ID和支付的罰款總金額，SHIPMENT_DATE與PO_DATE進行比較，SHIPMENT_DATE如果比PO_DATE晚一個月，則每天罰款$20。

MONTHS_BETWEEN(date1, date2): 返回date1和date2兩個日期之間間隔幾個月，結(jié)果可以為正或為負。如果date1晚于date2則結(jié)果為正;如果date1早于date2則結(jié)果為負;結(jié)果的非整數(shù)部分也代表月間隔的一部分。

DECODE(expr,search1,result1[,search2,result2……,default])：比較expr與search，如果等于search1則返回result1，如果等于search2則返回result2，依次類推，如果都不等于，如果有default則返回default，否則返回NULL.

ORACLE在比較之前，會自動把expr和每一個search隱式轉(zhuǎn)換成第一個search(search1)的數(shù)據(jù)類型。自動把返回值轉(zhuǎn)換成第一個result(result1)的數(shù)據(jù)類型。如果第一個result的數(shù)據(jù)類型為CHAR或者值是null，則Oracle轉(zhuǎn)換返回值為VARCHAR2.

在DECODE函數(shù)中,NULL是相等的，如果expr為空，則Oracle將會返回第一個為NULL的search所對應(yīng)的result。DECODE列表中的最大表達式個數(shù)為255個。

第一個SQL的表達是正確，可以正確執(zhí)行的。其實我覺得這道題的答案有點出入，MONTHS_BETWEEN返回的是月數(shù)，每天罰款$20，相乘起來，因為一個是天的單位，一個是月的單位，如果題中改成每月罰款20，則才是正確的。

DECODE 的表達是錯誤的。

sh@TEST0924> SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)>1,

2 TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY

3 FROM shipments;

SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)>1,

*

ERROR at line 1:

ORA-00907: missing right parenthesis