最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：Steven Feuerstein
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我执行了下列语句:
CREATE TABLE plch_names (my_name VARCHAR2 (100) UNIQUE)
/
BEGIN
FOR indx IN 1 .. 20
LOOP
INSERT INTO plch_names (my_name)
VALUES ('Steven' || indx);
END LOOP;
COMMIT;
END;
/
哪些选项会使得下列代码块执行之后，屏幕上会显示 "21" ?
DECLARE
l_count PLS_INTEGER;
BEGIN
plch_add_name ('Steven1');
SELECT COUNT (*) INTO l_count FROM plch_names;
DBMS_OUTPUT.put_line (l_count);
END;
/
(A)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
BEGIN
INSERT INTO plch_names (my_name)
VALUES (new_name_in);
END;
/
(B)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
BEGIN
INSERT INTO plch_names (my_name)
VALUES (new_name_in);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
INSERT INTO plch_names (my_name)
VALUES (new_name_in || '1');
END;
/
(C)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
l_name plch_names.my_name%TYPE := new_name_in;
l_dummyCHAR (1);
l_all_done BOOLEAN := FALSE;
BEGIN
LOOP
BEGIN
SELECT 'x'
INTO l_dummy
FROM plch_names
WHERE my_name = l_name;
l_name := l_name || '1';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO plch_names (my_name)
VALUES (l_name);
l_all_done := TRUE;
END;
EXIT WHEN l_all_done;
END LOOP;
END;
/
(D)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
l_nameplch_names.my_name%TYPE := new_name_in;
l_dummy CHAR (1);
BEGIN
LOOP
BEGIN
SELECT 'x'
INTO l_dummy
FROM plch_names
WHERE my_name = l_name;
l_name := l_name || '1';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO plch_names (my_name)
VALUES (l_name);
END;
EXIT WHEN SQL%ROWCOUNT = 1;
END LOOP;
END;
/
(E)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
l_name plch_names.my_name%TYPE := new_name_in;
BEGIN
LOOP
BEGIN
INSERT INTO plch_names (my_name)
VALUES (l_name);
EXIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
l_name := l_name || '1';
END;
END LOOP;
END;
/
(F)
CREATE OR REPLACE PROCEDURE plch_add_name (
new_name_in IN VARCHAR2)
AUTHID DEFINER
IS
l_name plch_names.my_name%TYPE := new_name_in;
l_dummyCHAR (1);
l_all_done BOOLEAN := FALSE;
BEGIN
LOOP
BEGIN
SELECT 'x'
INTO l_dummy
FROM plch_names
WHERE my_name = new_name_in;
l_name := l_name || '1';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO plch_names (my_name)
VALUES (l_name);
l_all_done := TRUE;
END;
EXIT WHEN l_all_done;
END LOOP;
END;
/
