最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
http://www.itpub.net/thread-1499223-1-1.html
原始出处：
http://www.plsqlchallenge.com/
作者：Steven Feuerstein
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我创建了一个表并填入数据:
CREATE TABLE plch_employees
(
employee_id INTEGER,
last_name VARCHAR2 (100),
salaryNUMBER (8, 0)
)
/
BEGIN
INSERT INTO plch_employees
VALUES (100, 'Ninhursag', 1000000);
INSERT INTO plch_employees
VALUES (200, 'Inanna', 1000000);
INSERT INTO plch_employees
VALUES (300, 'Enlil', 1000000);
COMMIT;
END;
/
然后我写了下列未完成的代码块:
DECLARE
failure_in_forall EXCEPTION;
PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);
TYPE employee_aat IS TABLE OF plch_employees.employee_id%TYPE
INDEX BY PLS_INTEGER;
l_employees employee_aat;
TYPE values_aat IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
l_employee_values values_aat;
BEGIN
/*FORALL_HEADER*/
UPDATE plch_employees
SET salary =
salary
* CASE employee_id WHEN 200 THEN 1 ELSE 100 END
WHERE employee_id = l_employees (l_index);
EXCEPTION
WHEN failure_in_forall
THEN
DBMS_OUTPUT.put_line ('Error Indexes:');
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
END LOOP;
ROLLBACK;
END;
/
哪些选项可用来取代/*FORALL_HEADER*/注释，使得代码块执行之后会显示如下文本:
Error Indexes:
1
3
(A)
l_employees (-77) := 100;
l_employees (13067) := 200;
l_employees (1070) := 300;
--
l_employee_values (100) := -77;
l_employee_values (200) := 13067;
l_employee_values (300) := 1070;
FORALL l_index IN VALUES OF l_employee_values
SAVE EXCEPTIONS
(B)
l_employees (1) := 100;
l_employees (2) := 200;
l_employees (3) := 300;
FORALL l_index IN l_employees.FIRST .. l_employees.LAST
SAVE EXCEPTIONS
(C)
l_employees (100) := 100;
l_employees (200) := 200;
l_employees (300) := 300;
FORALL l_index IN l_employees.FIRST .. l_employees.LAST
SAVE EXCEPTIONS
(D)
l_employees (-77) := 100;
l_employees (13067) := 200;
l_employees (1070) := 300;
--
l_employee_values (100) := -77;
l_employee_values (200) := 13067;
l_employee_values (300) := 1070;
FORALL l_index IN VALUES OF l_employee_values
|
