1Z0-051 QUESTION 23 数据类型应该注意的地方

QUESTION 23
Examine the structure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULL NUMBER(6)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_STATUS NOT NULL CHAR
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY VARCHAR2
CUST_CREDIT_LIMIT NUMBER
Which statements are true regarding the creation and storage of data in the above table structure? (Choose
all that apply.)
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.

D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.

E. The TRANS_VALIDITY column would have a maximum size of one character.

F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds,

and fractions of seconds.

答案：BC

解析：

CHAR可以不指定精度，所以A选项不正确。

SQL> create table transactions(
  2  cust_status char not null);

Table created.

VARCHAR2应该指定精度，例如varchar(20)，不指定会报错，应该B选项正确。

SQL> create table transactions(
  2  trans_validity varchar2);
trans_validity varchar2)
                       *
ERROR at line 2:
ORA-00906: missing left parenthesis

CHAR默认值为1，所以C正确。

SQL> create table transactions(
  2  cust_status char not null);

Table created.

SQL> desc transactions;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------

 CUST_STATUS                               NOT NULL CHAR(1)

NUMBER可以没有精度，也可以存储小数。

SQL> create table transactions(
  2  cust_credit_limit number);

Table created.

SQL> desc transactions;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------

 CUST_CREDIT_LIMIT                                  NUMBER

SQL> insert into transactions values(1.03);

1 row created.

SQL> select * from transactions;

CUST_CREDIT_LIMIT
-----------------
             1.03

VARCHAR2没有精度会报错，因此，E选项也错。

F选项中DATE数据类型不能存储fractions of seconds，只有TIMESTAMP可以。参看Oracle官方文档：

http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1841

The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. TheTIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.