最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处:
http://www.plsqlchallenge.com/
作者:Steven Feuerstein
运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品
我执行了这些语句:
CREATE TYPE plch_food_t AS OBJECT
(
name VARCHAR2 (100),
food_group VARCHAR2 (100),
grown_in VARCHAR2 (100)
)
NOT FINAL;
/
CREATE TYPE plch_dessert_t
UNDER plch_food_t
(
contains_chocolate CHAR (1),
year_created NUMBER (4)
)
NOT FINAL;
/
CREATE TYPE plch_cake_t
UNDER plch_dessert_t
(
diameter NUMBER,
inscription VARCHAR2 (200)
);
/
CREATE TABLE meals
(
served_on DATE,
appetizer plch_food_t,
main_course plch_food_t,
dessert plch_dessert_t
);
BEGIN
INSERT INTO meals
VALUES (SYSDATE + 1,
plch_food_t ('Shrimp cocktail', 'PROTEIN', 'Ocean'),
plch_food_t ('Stir fry tofu', 'PROTEIN', 'Vat'),
plch_cake_t ('Apple Pie',
'FRUIT',
'Baker''s Square',
'N',
2001,
8,
NULL));
INSERT INTO meals
VALUES (SYSDATE + 1,
plch_food_t ('Fried Calamari', 'PROTEIN', 'Ocean'),
plch_dessert_t ('Butter cookie',
'CARBOHYDRATE',
'Oven',
'N',
2001),
plch_cake_t ('French Silk Pie',
'CARBOHYDRATE',
'Baker''s Square',
'Y',
2001,
6,
'To My Favorite Frenchman'));
INSERT INTO meals
VALUES (SYSDATE + 1,
plch_food_t ('Fried Calamari', 'PROTEIN', 'Ocean'),
plch_cake_t ('French Silk Pie',
'CARBOHYDRATE',
'Baker''s Square',
'Y',
2001,
6,
'To My Favorite Frenchman'),
plch_dessert_t ('Butter cookie',
'CARBOHYDRATE',
'Oven',
'N',
2001));
END;
/
哪些选项在执行之后会导致下列两行被显示(两行的顺序无关紧要)?
Butter cookie-N
French Silk Pie-Y
(A)
BEGIN
FOR rec IN (SELECT *
FROM meals
WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (
rec.main_course.name || '-' ||
rec.main_course.contains_chocolate);
END LOOP;
END;
/
(B)
DECLARE
l_dessert plch_dessert_t;
BEGIN
FOR rec IN (SELECT * FROM meals)
LOOP
BEGIN
l_dessert := TREAT (rec.main_course AS plch_dessert_t);
DBMS_OUTPUT.put_line (
l_dessert.name || '-' || l_dessert.contains_chocolate);
EXCEPTION
WHEN VALUE_ERROR
THEN
NULL;
END;
END LOOP;
END;
/
(C)
BEGIN
FOR rec IN (SELECT * FROM meals)
LOOP
DBMS_OUTPUT.put_line (
CASE
WHEN rec.main_course.contains_chocolate IS NOT NULL
THEN
rec.main_course.name
|| '-'
|| rec.main_course.contains_chocolate
ELSE
NULL
END);
END LOOP;
END;
/
(D)
BEGIN
FOR rec IN (SELECT *
FROM meals
WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (
rec.main_course.name || '-' ||
rec.TREAT (rec.main_course AS plch_dessert_t).contains_chocolate);
END LOOP;
END;
/
(E)
DECLARE
l_dessert plch_dessert_t;
BEGIN
FOR rec IN (SELECT *
FROM meals
WHERE TREAT (main_course AS plch_dessert_t) IS NOT NULL)
LOOP
l_dessert := TREAT (rec.main_course AS plch_dessert_t);
DBMS_OUTPUT.put_line (
rec.main_course.name || '-' ||
l_dessert.contains_chocolate);
END LOOP;
END;
/