OCP-1Z0-051-V9.02-13题

13. View the Exhibit and examine the structure of the PRODUCTS table.

You need to generate a report in the following format:

CATEGORIES

5MP Digital Photo Camera's category is Photo

Y Box's category is Electronics

Envoy Ambassador's category is Hardware

Which two queries would give the required output? (Choose two.) 

A. SELECT prod_name  q'''s category is '  prod_category CATEGORIES

FROM products;

B. SELECT prod_name  q'['s ]'category is '  prod_category CATEGORIES

FROM products;

C. SELECT prod_name  q'\'s\'   ' category is '  prod_category CATEGORIES

FROM products;

D. SELECT prod_name  q'<'s >'   'category is '  prod_category CATEGORIES

FROM products;

Answer: CD

答案解析：

在SQL字符串是以单引号作为分界符的，在字符串前面和后面各一个单引号。但是字符串中也能包含单引号，为了使语法分析器能够区分字符串中的单引号还是分界符。规定当字符串中出现单引号时，在其前面添加一个单引号作为区分。也就是说， 在单引号分隔的字符串中，两个连续的单引号''表示一个单引号字符。

sh@TEST1107> select '''' from dual;

'

-

'

四个连续单引号分界为： 
第一个单引号，字符串的开始分界符。
接下来连续的两个单引号，表示字符串的值（一个单引号）
第四个单引号，字符串的结束分界符。

sh@TEST1107> select 'today is ''FRIDAY''' from dual;

'TODAYIS''FRIDAY'

-----------------

today is 'FRIDAY'

在Oracle中，single-quote(‘)是一个表示字符串的关键字。所以在字符串中用两个”表示一个实际的单引号字符。所有才会有了上面第一条SQL的'today is ''FRIDAY'''。双引号“被识别为一个实际的的字符串。其实Oracle提供了一个Q-quote的表达式，用来简化SQL或PLSQL中字符串的表示。

sh@TEST1107>  select q'[today is ''FRIDAY'']' names from dual;

NAMES

-------------------

today is ''FRIDAY''

sh@TEST1107> select q'[today is 'FRIDAY']' names from dual;

NAMES

-----------------

today is 'FRIDAY'

看上面两个的差别可知，使用Q-quote的表达式，只需要将要表示的字符串用一对特殊字符括起来，这对字符必须一致。不一致会报错。

sh@TEST1107> select q'[today is 'FRIDAY'|' names from dual;

ERROR:

ORA-01756: quoted string not properly terminated

sh@TEST1107> select q'|today is 'FRIDAY'|' names from dual;

NAMES

-----------------

today is 'FRIDAY'

从以上实验来解答题中的答案。

A答案错误，使用‘作为特殊字符，is后面缺少单引号，应该为

sh@TEST1107> SELECT prod_name || q'''s category is ''|| prod_category CATEGORIES FROM products;

CATEGORIES

----------------------------------------------------------------------------------------------------

5MP Telephoto Digital Camera's category is Photo

17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories

Envoy 256MB - 40GB's category is Hardware

Y Box's category is Electronics

Mini DV Camcorder with 3.5" Swivel LCD's category is Photo

B. 错误。SELECT prod_name  q'['s ]'category is '  prod_category CATEGORIES FROM products;

category is ' 这里前面缺少一个单引号。缺少category is 开始的单引号。

应该为：

sh@TEST1107> SELECT prod_name || q'['s ]'||'category is '||prod_category CATEGORIES FROM products;

CATEGORIES

----------------------------------------------------------------------------------------------------

5MP Telephoto Digital Camera's category is Photo

17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories

Envoy 256MB - 40GB's category is Hardware

Y Box's category is Electronics

C. 正确SELECT prod_name  q'\'s\'   ' category is '  prod_category CATEGORIES FROM products;

sh@TEST1107> SELECT prod_name||  q'\'s\' ||  ' category is ' || prod_category CATEGORIES FROM products;

CATEGORIES

----------------------------------------------------------------------------------------------------

5MP Telephoto Digital Camera's category is Photo

17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories

Envoy 256MB - 40GB's category is Hardware

Y Box's category is Electronics

D. 正确SELECT prod_name  q'<'s >'   'category is '  prod_category CATEGORIES FROM products;

sh@TEST1107> SELECT prod_name || q'<'s >'  || 'category is ' || prod_category CATEGORIES FROM products;

CATEGORIES

----------------------------------------------------------------------------------------------------

5MP Telephoto Digital Camera's category is Photo

17" LCD w/built-in HDTV Tuner's category is Peripherals and Accessories

Envoy 256MB - 40GB's category is Hardware

Y Box's category is Electronics

注意，题中答案需要添加||来连接字符串。