PL/SQL CHALLENGE 每日一题

newkid

2012-1-10 所有答案都不正确。
在一个serializable事务中，事务一开始的SCN会被记录下来，接下来如果要修改的行的SCN大于这个开始事务的SCN, 就会引发异常：
ORA-08177: can't serialize access for this transaction
注意C,D选项并没有真正修改表而只是加了行锁，但是最后COMMIT了，行的SCN也会被修改。
=====================================
2012-1-11 UTL_FILE读文件
作者：Steven Feuerstein
难度：中

我需要写一个过程来显示一个文件中每一行的前N个字符。

我创建了一个名字为"test.txt"的文件，它包含5行文本:

01234
012345
0123456
01234567
012345678

然后我把它保存在一个目录下，这个目录在ORACLE数据中可以通过DIRECTORY对象"PLCH_TEMP"来访问（这个目录对象在这个SCHEMA下面已经创建好了）

这是我未完成的过程：

CREATE OR REPLACE PROCEDURE plch_show_start (
   dir_in      IN VARCHAR2
, file_in     IN VARCHAR2
, length_in   IN PLS_INTEGER)
IS
   l_file   UTL_FILE.file_type;
   l_line   VARCHAR2 (32767);
BEGIN
   /*BODY*/
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      UTL_FILE.fclose (l_file);
END;

哪些选项可用来取代上面的/*BODY*/注释，从而使得我执行下列代码块时：
BEGIN
   plch_show_start ('PLCH_TEMP', 'test.txt', 6);
END;
/

我会在屏幕上看到：
01234
012345
012345
012345
012345

(A)
l_file :=
   UTL_FILE.fopen (dir_in, file_in, 'R');

LOOP
   UTL_FILE.get_line (
      l_file, SUBSTR (l_line, 1, length_in));
END LOOP;


(B)
l_file :=
   UTL_FILE.fopen (dir_in, file_in, 'R');

LOOP
   UTL_FILE.get_line (l_file, l_line);
   DBMS_OUTPUT.put_line (SUBSTR (l_line, 1, length_in));
END LOOP;

(C)
UTL_FILE.display_file (l_file, length_in);

(D)
l_file :=
   UTL_FILE.fopen (dir_in, file_in, 'R');

LOOP
   UTL_FILE.get_line (l_file, l_line, length_in);
   DBMS_OUTPUT.put_line (l_line);
END LOOP;

(E)
l_file :=
   UTL_FILE.fopen (dir_in, file_in, 'R'
                 , max_linesize   => length_in);

LOOP
   UTL_FILE.get_line (l_file, l_line);
   DBMS_OUTPUT.put_line (l_line);
END LOOP;

visual2006

B,D,E

newkid

2012-1-11 答案B
A: 语法错，get_line的参数不是这样的。
C: 不存在UTL_FILE.display_file这个过程。
D: get_line带第三个参数确实会限制返回字符的个数。但是，在下次调用get_line时，它会从刚才未读完的位置继续往下读，而不是我们希望的另起一行。
E: fopen如果带了第三个参数限制每行的长度，在试图读取超过这个限制的行时，会报错："ORA-29284: file read error".

=====================================
2012-1-12 不规则表名
作者：Ralf Koelling
难度：中

我创建了这个表：
CREATE TABLE plch_parts (
   part_num      NUMBER
, description   VARCHAR2 (30)
)
/

并且填入一些数据：
BEGIN
   INSERT INTO plch_parts VALUES (1, 'Mouse');
   INSERT INTO plch_parts VALUES (2, 'Keyboard');
   INSERT INTO plch_parts VALUES (3, 'Monitor');
   INSERT INTO plch_parts VALUES (4, 'Headset');

   COMMIT;
END;
/

然后我又建立了三个表:
CREATE TABLE "PLCH_parts_a"
AS
   SELECT * FROM plch_parts WHERE ROWNUM <= 1
/

CREATE TABLE "PLCH_PARTS_B"
AS
   SELECT * FROM plch_parts WHERE ROWNUM <= 2
/

CREATE TABLE "PLCH_parts C"
AS
   SELECT * FROM plch_parts WHERE ROWNUM <= 3
/

现在我创建一个过程来显示一个表里面有多少行：
CREATE OR REPLACE PROCEDURE plch_show_count (
   table_in IN VARCHAR2)
IS
   cnt               PLS_INTEGER;
   stmnt             VARCHAR2 (1000);
   table_not_found   EXCEPTION;
   PRAGMA EXCEPTION_INIT (table_not_found, -942);
BEGIN
   stmnt := 'select count(*) from ' || table_in;

   EXECUTE IMMEDIATE stmnt INTO cnt;

   DBMS_OUTPUT.put_line (
      'Table ' || table_in || ', Count: ' || cnt);
EXCEPTION
   WHEN table_not_found
   THEN
      DBMS_OUTPUT.put_line (
         'Table ' || table_in || ' does not exist!');
END;
/

哪个选项正确显示了这个代码块的输出：(译者注：假设这个SCHEMA是全新的，之前在里面没有其他表)
BEGIN
   FOR rec IN (  SELECT table_name
                   FROM user_tables
               ORDER BY table_name)
   LOOP
      plch_show_count (rec.table_name);
   END LOOP;
END;
/

(A)
Table PLCH_PARTS, Count: 4
Table PLCH_PARTS_B, Count: 2
Table PLCH_parts C, Count: 4
Table PLCH_parts_a does not exist!

(B)
Table PLCH_PARTS, Count: 4
Table PLCH_PARTS_B, Count: 2
Table PLCH_parts C, Count: 3
Table PLCH_parts_a, Count: 1

(C)
Table PLCH_PARTS, Count: 4
Table PLCH_PARTS_B, Count: 2
Table PLCH_parts C, Count: 4
Table PLCH_parts_a, Count: 1

(D)
Table PLCH_PARTS, Count: 4
Table PLCH_PARTS_B does not exist!
Table PLCH_parts C does not exist!
Table PLCH_parts_a does not exist!

visual2006

newkid 发表于 2012-1-14 04:16
2012-1-11 答案B
A: 语法错，get_line的参数不是这样的。
C: 不存在UTL_FILE.display_file这个过程。

呵呵，还是中招了！没想到D,E还有这样的陷阱还是实践出真知

newkid

2012-1-12 答案A
注意如果不带双引号，ORACLE会默认把表名转换为大写。因此动态SQL中的PLCH_parts_a会先被转换为PLCH_PARTS_A, 而这个表创建的时候是大小写混用的，所以会报表名不存在错误。
"PLCH_parts C"这张表拼到动态SQL里面之后，变成：
select count(*) from PLCH_parts C
由于多了个空格，C被认为是PLCH_parts的别名，因此这个查询实际上返回了PLCH_parts的行数。
==================================================
2012-1-13 COLLECTIONS
作者：Steven Feuerstein
难度：中

我声明了一个嵌套表类型：
CREATE OR REPLACE TYPE plch_numbers_t IS TABLE OF NUMBER
/

哪些选项的代码块执行之后会显示"246"?
  
(A)
DECLARE
   l_numbers   plch_numbers_t := plch_numbers_t (2, 4, 6);
   l_number    NUMBER := 0;
BEGIN
   FOR indx IN 1 .. l_numbers.COUNT
   LOOP
      l_number :=
           l_number
         +   l_numbers (indx)
           * 10 ** (l_numbers.COUNT - indx);
   END LOOP;

   DBMS_OUTPUT.put_line (l_number);
END;
/

(B)
DECLARE
   l_numbers   plch_numbers_t := plch_numbers_t (2, 4, 6);
   l_number    VARCHAR2 (100);
BEGIN
   FOR indx IN 1 .. l_numbers.COUNT
   LOOP
      l_number := l_numbers (indx) || l_number;
   END LOOP;

   DBMS_OUTPUT.put_line (l_number);
END;
/

(C)
DECLARE
   l_numbers   plch_numbers_t := plch_numbers_t (2, 4, 6);
   l_number    VARCHAR2 (100);
BEGIN
   SELECT LISTAGG (COLUMN_VALUE, '')
             WITHIN GROUP (ORDER BY COLUMN_VALUE)
     INTO l_number
     FROM TABLE (l_numbers);

   DBMS_OUTPUT.put_line (l_number);
END;
/

(D)
DECLARE
   l_numbers   plch_numbers_t := plch_numbers_t (2, 4, 6);
   l_number    VARCHAR2 (100);
BEGIN
   FOR indx IN 1 .. l_numbers.COUNT
   LOOP
      l_number := l_number || l_numbers (indx);
   END LOOP;

   DBMS_OUTPUT.put_line (l_number);
END;
/

newkid

2012-1-13 答案 ACD
B: 拼接的次序反了，结果出来会变成642
=================================================
2012-1-16 CASE表达式
作者：Steven Feuerstein
难度：中

我需要写一个过程，它带有一个整数作为输入参数（玩家的排名），如果这个排名小于等于25则显示"Top Ranked"，否则它显示排名的数字。传递给过程的排名参数永远不会为NULL.

哪些选项实现了这个需求？


(A)
CREATE OR REPLACE PROCEDURE plch_show_ranking (
   rank_in IN PLS_INTEGER)
IS
BEGIN
   DBMS_OUTPUT.put_line (
      CASE
         WHEN rank_in <= 25 THEN 'Top Ranked'
         ELSE rank_in
      END);
END;
/

(B)
CREATE OR REPLACE PROCEDURE plch_show_ranking (
   rank_in IN PLS_INTEGER)
IS
BEGIN
   DBMS_OUTPUT.put_line (
      CASE rank_in
         WHEN <= 25 THEN 'Top Ranked'
         ELSE rank_in
      END);
END;
/

(C)
CREATE OR REPLACE PROCEDURE plch_show_ranking (
   rank_in IN PLS_INTEGER)
IS
BEGIN
   DBMS_OUTPUT.put_line (
      IF rank_in <= 25 THEN 'Top Ranked'
      ELSE TO_CHAR (rank_in)
      END IF);
END;
/

(D)
CREATE OR REPLACE PROCEDURE plch_show_ranking (
   rank_in IN PLS_INTEGER)
IS
BEGIN
   DBMS_OUTPUT.put_line (
      CASE
         WHEN rank_in <= 25 THEN 'Top Ranked'
         ELSE TO_CHAR (rank_in)
      END);
END;
/

visual2006

2012-1-18
A
B 语法错误，X
C IF语句不能用做函数参数 X
D 要求是输出'Top Ranked'或排名数字 X

newkid

2012-1-16 答案 D
A: 会引发VALUE_ERROR异常。只要CASE里面有一个分支是NUMBER型，ORACLE就会试图把所有分支都转换为NUMBER型。

SQL中的表现则是试图转换为第一个分支的类型：

SQL> select case when dummy is not null then '1' else 0 end from dual;
select case when dummy is not null then '1' else 0 end from dual
                                                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SQL> select case when dummy is not null then 1 else '0' end from dual;
select case when dummy is not null then 1 else '0' end from dual
                                               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

B: 语法错误, 简单CASE表达式只能等值比较。
C: 语法错误, IF只能是语句不能是表达式。

=================================================
2012-1-17 定制并行化操作：DBMS_PARALLEL_EXECUTE包的使用
作者：Viacheslav Stepanov
难度：高

我创建了如下的表并插入四行数据：
CREATE TABLE plch_table
(
   pos  number(1)
, data varchar2(11)
)
/

BEGIN
  INSERT INTO plch_table VALUES (5, 'bumblebee');
  INSERT INTO plch_table VALUES (7, 'red herring');
  INSERT INTO plch_table VALUES (8, 'big bear');
  INSERT INTO plch_table VALUES (9, 'frizzled up');
  COMMIT;
END;
/

下列代码块执行之后会显示什么？（假设编译执行这段代码所需要的权限都已赋予这个SCHEMA）

DECLARE
   l_sql           CLOB;
   l_dummy         BINARY_INTEGER;
   val_too_large   EXCEPTION;
   PRAGMA EXCEPTION_INIT (val_too_large, -12899);
BEGIN
   DBMS_PARALLEL_EXECUTE.create_task (
      task_name => 'plch_task');

   l_sql :=
      'select rowid as start_id, rowid as end_id from plch_table';
   DBMS_PARALLEL_EXECUTE.create_chunks_by_sql (
      task_name   => 'plch_task'
    , sql_stmt    => l_sql
    , by_rowid    => TRUE);

   l_sql :=
      'UPDATE plch_table t
               SET    t.data = t.data || t.pos
             WHERE rowid BETWEEN :start_id AND :end_id';

   DBMS_PARALLEL_EXECUTE.run_task (
      task_name        => 'plch_task'
    , sql_stmt         => l_sql
    , language_flag    => DBMS_SQL.native
    , parallel_level   => 1);

   SELECT 1
     INTO l_dummy
     FROM plch_table p
    WHERE p.data = 'big bear8';

   DBMS_OUTPUT.put_line ('SUCCESS');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO BEAR');
   WHEN val_too_large
   THEN
      DBMS_OUTPUT.put_line ('OH, HERRING');
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('FRIZZLED UP');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR');
END;
/

注意：代码中用到的val_too_large异常错误信息如下：
"ORA-12899: value too large for column (actual: %d, maximum: %d)".

(A)
SUCCESS

(B)
NO BEAR

(C)
OH, HERRING

(D)
FRIZZLED UP

(E)
ERROR

newkid

2012-1-17 答案A.
这个UPDATE语句：
UPDATE plch_table t SET t.data = t.data || t.pos
本来在pos=7和pos=9会报错，因为拼接后的data字段有12个字符，超过了定义的宽度11。
但是用了DBMS_PARALLEL_EXECUTE包拆分任务之后，哪怕其中有些任务失败，其他任务也会照样完成。所以最终会显示SUCCESS。
===================================
2012-1-18 FORMAT_CALL_STACK相关
作者：Steven Feuerstein
难度：中

在我的HR schema中，我创建了两个schema级别的过程，名字为PLCH_PROC1 和 PLCH_PROC3，同时还有一个包，名字为PLCH_PKG1，包里含有一个名字为PROC2的过程。

PLCH_PROC3 调用 PLCH_PKG1.PROC2, 后者又调用 PLCH_PROC1。PLCH_PROC1的执行部分有一个嵌套块，里面执行了下列语句：
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);

哪些选项“可能”是PLCH_PROC3之后的显示结果？

(A)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2F6E76D8         8  procedure HR.PLCH_PROC1
2F6E3738         6  package body HR.PLCH_PKG1
2F6E7BB8         9  procedure HR.PLCH_PROC3
2F6E8FB8         2  anonymous block

(B)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2F6E76D8         8  procedure HR.PLCH_PROC1
2F6E3738         6  function HR.PLCH_PKG1.PROC2
2F6E7BB8         9  procedure HR.PLCH_PROC3
2F6E8FB8         2  anonymous block

(C)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2F6E76D8         8  procedure HR.PLCH_PROC1
2F6E3738         6  package body HR.PLCH_PKG1
2F6E7BB8         9  procedure HR.PLCH_PROC3

(D)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2F6E76B8        15  nested block HR.PLCH_PROC1
2F6E76D8         8  procedure HR.PLCH_PROC1
2F6E3738         6  package body HR.PLCH_PKG1
2F6E7BB8         9  procedure HR.PLCH_PROC3
2F6E8FB8         2  anonymous block

newkid

2012-1-18 答案AC.
B: FORMAT_CALL_STACK里面并不包含包里的过程名，只有包的名字。
C: 比A少了最后一行，这种情况发生在用CALL语句来执行PLSQL程序，或者在SQL里面调用PLSQL函数。
D: 嵌套块不会包含在FORMAT_CALL_STACK里面。

===================================
2012-1-19 DBMS_SQL的出错处理
作者：Valentin Nikotin
难度：高

哪个选项实现了过程P2, 可用来取代注释/*p2*/，从而使得代码块执行之后"No error"会显示两次？

declare
  procedure p1 is
    n pls_integer;
    c sys_refcursor;
  begin
    open c for select 1 from dual;
    n := dbms_sql.to_cursor_number(c);
    c := dbms_sql.to_refcursor(n);
    fetch c into n;
    close c;
    dbms_output.put_line('No error');
  exception
    when others then
      dbms_output.put_line('Error');
  end;

  /*p2*/

begin
  p1;
  p2;
  p1;
end;
/

(A)
procedure p2 is
    n pls_integer := dbms_sql.open_cursor;
    c sys_refcursor;
begin
    c := dbms_sql.to_refcursor(n);
exception
    when others then
      null;
end;

(B)
procedure p2 is
    c sys_refcursor;
begin
    c := dbms_sql.to_refcursor(dbms_sql.open_cursor);
exception
    when others then
      null;
end;

(C)
procedure p2 is
begin
    declare
      n pls_integer   := dbms_sql.open_cursor;
      c sys_refcursor := dbms_sql.to_refcursor(n);
    begin
      null;
    end;
exception
    when others then
      null;
end;

(D)
procedure p2 is
    n pls_integer;
    c sys_refcursor;
begin  
    c := dbms_sql.to_refcursor(n);
exception
    when others then
      null;
end;