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;

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

 
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
上一篇:Eclipse创建JSP、HTML、CSS文件默认字符集设置成UTF-8


下一篇:GitLab查询当前版本