PL/SQL CHALLENGE 每日一题

newkid

2011-12-28 答案AC
B: SET ROW 子句不支持 PLSQL函数。
D: UPDATE 少了WHERE 变成全表更新。

要注意的是，当采用SET ROW更新一整行数据时，所有的列包括主键都会被修改，哪怕你这一列的数据并没有发生变化。
另外%ROWTYPE 就是一种记录类型的简单写法，SET ROW在更新时是按照列的顺序来的，和列名没有关系。

CREATE TABLE t (id NUMBER PRIMARY KEY, N1 NUMBER, N2 NUMBER);
INSERT INTO t VALUES (1,2,3);
COMMIT;

DECLARE
   TYPE t_row IS RECORD (N2 NUMBER,N1 NUMBER, ID NUMBER);  ----- 故意写反
   lv_row t_row;
BEGIN
   lv_row.id :=5;
   lv_row.n1 :=6;
   lv_row.n2 :=7;
   UPDATE t SET ROW = lv_row;
END;
/

PL/SQL procedure successfully completed.

select * from t;

        ID         N1         N2
---------- ---------- ----------
         7          6          5

根据结果可知，它是按照RECORD定义里面的顺序来更新列的，并不是按名字匹配。


---------------------------------------------------
2011-12-29 RAISE_APPLICATION_ERROR
作者：Steven Feuerstein     
难度：中

我需要写一个过程来检查账户余额。如果余额小于1000,则抛出一个异常。
这里有三种可能做法：

1. 我们把这种称为 PEI (PRAGMA EXCEPTION_INIT) 方法：
CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
IS
   e_balance_too_low   EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_balance_too_low, -20704);
BEGIN
   IF balance_in < 1000
   THEN
      RAISE e_balance_too_low;
   END IF;
END;

2. 我们把这种称为 RAE (RAISE_APPLICATION_ERROR) 方法：
CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
IS
BEGIN
   IF balance_in < 1000
   THEN
      RAISE_APPLICATION_ERROR (-20704, 'Balance too low!');
   END IF;
END;

3. 我们把这种称为 VE (VALUE_ERROR) 方法：
CREATE OR REPLACE PROCEDURE plch_check_balance (
   balance_in IN NUMBER)
IS
BEGIN
   IF balance_in < 1000
   THEN
      RAISE VALUE_ERROR;
   END IF;
END;

哪些选项正确描述了这些方法的差异？

(A)
PEI和VE会强迫代码块中止，RAE则允许块继续执行。

(B)
PEI和VE提供"通用"的Oracle错误信息，RAE提供和应用相关的错误信息。

(C)
PEI和RAE的实现方法允许你把错误代码设置为一个不被Oracle使用的代码，并可以通过调用SQLCODE返回该代码。

(D)
RAE方法不能编译通过；你只能在数据库触发器里使用RAISE_APPLICATION_ERROR。

visual2006

要注意的是，当采用SET ROW更新一整行数据时，所有的列包括主键都会被修改，哪怕你这一列的数据并没有发生变化。
如果主键都被修改了，那是一条新纪录了吧！难道SET ROW还把老纪录给咔嚓了？

newkid

visual2006 发表于 2011-12-31 11:36
要注意的是，当采用SET ROW更新一整行数据时，所有的列包括主键都会被修改，哪怕你这一列的数据并没有发生 ...

咔嚓是什么意思？SET ROW还是UPDATE而不是DELETE+INSERT. 它只是UPDATE了所有的列。

newkid

2011-12-29 答案BC
A: RAISE_APPLICATION_ERROR的效果等同RAISE, 会使得当前块停止执行。
D: RAISE_APPLICATION_ERROR可在PLSQL的任何地方调用，并不是只有触发器里能用。

==================================================
2011-12-30 锁表语句LOCK TABLE
作者：Steven Feuerstein
难度：中

我创建了这张零部件表(plch_parts)并填入数据：
CREATE TABLE plch_parts
(
   partnum    INTEGER PRIMARY KEY
, partname   VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');

   INSERT INTO plch_parts
        VALUES (500, 'Monitor');

   COMMIT;
END;
/

假设下列选项执行的时候，最多只可能有另外一个会话正在同时修改plch_parts表，但是没有死锁发生，并且那个会话总是在10秒钟之内结束事务。

哪些选项包含了一个代码块，会锁住plch_parts表的所有行，从而使得：
(a)update语句可以顺利执行，而不需担心其他会话可能锁住表中的行 (b)执行滞后会显示"3"

(A)
BEGIN
   LOCK TABLE plch_parts IN EXCLUSIVE MODE;

   UPDATE plch_parts
      SET partname = UPPER (partname);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   UNLOCK TABLE plch_parts;

   COMMIT;
END;
/

(B)
BEGIN
   LOCK TABLE plch_parts IN EXCLUSIVE MODE NOWAIT;

   UPDATE plch_parts
      SET partname = UPPER (partname);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   COMMIT;
END;
/

(C)
BEGIN
   LOCK TABLE plch_parts IN EXCLUSIVE MODE;

   UPDATE plch_parts
      SET partname = UPPER (partname);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   COMMIT;
END;
/

(D)
BEGIN
   UPDATE plch_parts
      SET partname = UPPER (partname)
      LOCK ROWS IN EXCLUSIVE MODE;

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   COMMIT;
END;
/

(E)
BEGIN
   LOCK TABLE plch_parts IN EXCLUSIVE MODE WAIT 30;

   UPDATE plch_parts
      SET partname = UPPER (partname);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   COMMIT;
END;
/

newkid

2011-12-30 答案CE.
A: ORACLE没有提供UNLOCK语句，会报语法错误
B: LOCK TABLE 指定了NOWAIT选项，如果其他会话锁住了行就会引发异常。
C: 和B相比没有NOWAIT选项，因此假如行被其他会话锁住，会一直等待直到对方解锁。
D: 语法错误，UPDATE语句并没有LOCK ROWS选项。
E: 既然其他会话锁表不超过10秒，WAIT 30选项足够等待其他会话解锁。

==================================================
2012-1-2 SUBSTR的负数参数
作者：Steven Feuerstein
难度：低

哪些选项实现了这个函数：
FUNCTION plch_backwards (
   string_in   IN VARCHAR2
, start_in    IN PLS_INTEGER
, length_in   IN PLS_INTEGER)
   RETURN VARCHAR2

从而使得下列代码执行之后会显示 "Backwards=567":
BEGIN
   DBMS_OUTPUT.put_line (
      'Backwards=' || plch_backwards ('123456789', -3, 3));
END;
/

(A)
IS
BEGIN
   RETURN SUBSTR (string_in, start_in, length_in);
END;

(B)
IS
BEGIN
   RETURN SUBSTR (string_in
                ,  start_in - length_in
                ,  length_in);
END;

(C)
IS
BEGIN
   RETURN SUBSTR (string_in
                ,  start_in - length_in + 1
                ,  length_in);
END;

(D)
IS
BEGIN
   RETURN SUBSTR (string_in, start_in, -1 * length_in);
END;

visual2006

A 从倒数第3个字符开始顺数3，X
B 起始位置少一位，X
C
D 长度为负数，X

newkid

2012-1-2 答案C, 楼上正确。

==================================================
2012-1-3 自治事务, 在声明部分调用子过程
作者：mentzel.iudith
难度：高

我创建了如下的表并填入数据：
CREATE TABLE plch_employees
(
   employee_id     INTEGER
,  salary          NUMBER
,  department_id   INTEGER
)
/

BEGIN
   INSERT INTO plch_employees VALUES (100, 1000000, 10);
   INSERT INTO plch_employees VALUES (200, 1000000, 10);
   INSERT INTO plch_employees VALUES (300, 1000000, 20);
   COMMIT;
END;
/

然后我创建了一个函数来把指定部门的员工薪水翻倍，并将修改的行数返回：
CREATE OR REPLACE FUNCTION plch_update (
   p_department_id_in IN plch_employees.department_id%TYPE)
   RETURN PLS_INTEGER
IS
BEGIN
   UPDATE plch_employees
      SET salary = salary * 2
    WHERE department_id = p_department_id_in;

   RETURN SQL%ROWCOUNT;
END plch_update;
/

接着又创建了一个过程来显示员工表中薪水＝2000000的行数：
CREATE OR REPLACE PROCEDURE plch_show_count
IS
   l_count   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count
     FROM plch_employees
    WHERE salary = 2000000;

   DBMS_OUTPUT.put_line ('Count=' || l_count);
END plch_show_count;
/

下列代码块执行之后，我的屏幕上会显示什么？
DECLARE
   PROCEDURE plch_process
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_rows   PLS_INTEGER := plch_update (20);
   BEGIN
      plch_show_count;
      ROLLBACK;
      plch_show_count;
   END plch_process;
BEGIN
   DBMS_OUTPUT.put_line ('Count=' || plch_update (10));
   plch_process;
   plch_show_count;
END;
/

(A)
Count=2
Count=0
Count=0
Count=3

(B)
Count=2
Count=1
Count=0
Count=2

(C)
Count=2
Count=3
Count=0
Count=3

(D)
Count=2
Count=0
Count=0
Count=2

visual2006

B

newkid

2012-1-3 答案A

这里有个特别需要注意的地方，plch_update虽然是在自治事务的声明之后调用的，但它是在BEGIN之前，所以不算在自治事务里面，还是主事务的一部分。在做实验之前我也以为是B。

==================================================
2012-1-4 简单判断

作者：Steven Feuerstein
难度：低

我需要写这样一个函数：
FUNCTION plch_bonus_level (
   salary_in IN NUMBER)
   RETURN VARCHAR2

它实现了如下的逻辑:
如果薪水低于10000, 返回 'MAXIMUM'.
否则, 如果薪水低于 20000, 返回 'NORMAL'.
否则, 如果薪水低于 30000, 返回 'MINIMAL'.
否则, 返回 'NONE'.

哪些选项提供了能够满足这些需求的函数体？

(A)
IS
BEGIN
   IF salary_in < 10000
   THEN
      RETURN 'MAXIMUM';
   END IF;

   IF salary_in < 20000
   THEN
      RETURN 'NORMAL';
   END IF;

   IF salary_in < 30000
   THEN
      RETURN 'MINIMAL';
   END IF;

   RETURN 'NONE';
END;

(B)
IS
BEGIN
   RETURN CASE
             WHEN salary_in < 10000 THEN 'MAXIMUM'
             WHEN salary_in < 20000 THEN 'NORMAL'
             WHEN salary_in < 30000 THEN 'MINIMAL'
             ELSE 'NONE'
          END;
END;

(C)
IS
BEGIN
   IF salary_in <= 10000
   THEN
      RETURN 'MAXIMUM';
   END IF;

   IF salary_in <= 20000
   THEN
      RETURN 'NORMAL';
   END IF;

   IF salary_in <= 30000
   THEN
      RETURN 'MINIMAL';
   END IF;

   RETURN 'NONE';
END;

(D)
IS
   l_return   VARCHAR2 (7);
BEGIN
   IF salary_in < 10000
   THEN
      l_return := 'MAXIMUM';
   ELSIF salary_in < 20000
   THEN
      l_return := 'NORMAL';
   ELSIF salary_in < 30000
   THEN
      l_return := 'MINIMAL';
   ELSE
      l_return := 'NONE';
   END IF;

   RETURN l_return;
END;

gyhgood

C
其他的都没考虑相等情况