1Z0-051 QUESTION 6 CASE和DECODE用法

最新推荐文章于 2024-09-22 20:06:36 发布

create_oracle

最新推荐文章于 2024-09-22 20:06:36 发布

阅读量590

点赞数

分类专栏： OCP之路

本文链接：https://blog.youkuaiyun.com/create_oracle/article/details/25188759

版权

OCP之路专栏收录该内容

37 篇文章

订阅专栏

这篇博客主要探讨了Oracle数据库中CASE语句和DECODE函数的使用。文章指出，第二个查询由于语法错误无法成功执行。CASE的详细语法参考了官方文档，而DECODE的用法也在另一份官方文档中进行了说明。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

QUESTION 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.

答案：C

解析：

case的语法参考ORACLE官方文档：

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm

摘要如下：

  SELECT job_id INTO jobid from employees WHERE employee_id = empid;
  CASE
    WHEN jobid = 'PU_CLERK' THEN sal_raise := .09;
    WHEN jobid = 'SH_CLERK' THEN sal_raise := .08;
    WHEN jobid = 'ST_CLERK' THEN sal_raise := .07;
    ELSE sal_raise := 0;
  END CASE;

故，第一条语句执行成功，并返回正确结果。

decode的语法参考ORACLE官方文档：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm#SQLRF00631

摘要如下：

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') 
       "Location of inventory" FROM inventories
       WHERE product_id < 1775;

故，第二句语法错误，无法执行。

具体如下：

SQL> --create table
SQL> create table shipments(
  2  po_id number(3) not null,
  3  po_date date not null,
  4  shipment_date date not null,
  5  shipment_mode varchar2(30),
  6  shipment_cost number(8,2));

表已创建。

SQL> insert into shipments values(1,sysdate-2*365,sysdate,'1',23);

已创建 1 行。

SQL> insert into shipments values(2,sysdate-2,sysdate,'2',45);

已创建 1 行。

SQL> insert into shipments values(3,sysdate,sysdate,'3',67);

已创建 1 行。



SQL> select * from shipments;

     PO_ID PO_DATE        SHIPMENT_DATE  SHIPMENT_MODE
---------- -------------- -------------- -----------------------------
SHIPMENT_COST
-------------
         1 28-12月-11     27-12月-13     1
           23

         2 25-12月-13     27-12月-13     2
           45

         3 27-12月-13     27-12月-13     3
           67


SQL> SELECT po_id, CASE
  2  WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
  3  TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PEN
  4  FROM shipments;

     PO_ID PENALTY
---------- ----------------------------------------
         1 14600
         2 No Penalty
         3 No Penalty

SQL> SELECT po_id, DECODE
  2  (MONTHS_BETWEEN (po_date,shipment_date)>1,
  3  TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY
  4  FROM shipments;
(MONTHS_BETWEEN (po_date,shipment_date)>1,
                                       *
第 2 行出现错误:
ORA-00907: 缺失右括号