PL/SQL CHALLENGE 每日一题

xhr8334

为什么有into就能显示输出结果，没有into就不显示呢。。。求解释。。。

周应侯

这个是好东西啊，建议弄成ptf

魔界卧龙

一个个的试，不过好多不懂啊

newkid

2012-2-6 答案ABD.
A: 两个函数各执行一次，因为它们的值被显式赋值到变量中
B: 两个函数各执行一次，因为它们的值必须被隐式赋给记录变量rec, 尽管这个变量在循环中未被用到。
C: 因为没有INTO子句, ORACLE不会执行相应的函数。（显得太聪明了以至于有些奇怪！）
D: 因为有INTO, 为了获取值，ORACLE会执行函数。
E: 同C, 只是换了一种方法执行动态SQL
F: 因为dbms_sql.define_column只被调用了一次，相当于隐式游标的一个INTO, 所以该函数只会执行一次。
============================================
2012-2-7 布尔型变量的显示
作者：Steven Feuerstein
难度：低

哪些选项在执行后会导致下列三行文本被显示?
TRUE
FALSE
NULL

(A)
DECLARE
   l_flag1   BOOLEAN := TRUE;
   l_flag2   BOOLEAN := FALSE;
   l_flag3   BOOLEAN;
BEGIN
   DBMS_OUTPUT.put_line (
      CASE WHEN l_flag1 THEN 'TRUE' ELSE 'FALSE' END);
   DBMS_OUTPUT.put_line (
      CASE WHEN l_flag2 THEN 'TRUE' ELSE 'FALSE' END);
   DBMS_OUTPUT.put_line (
      CASE WHEN l_flag3 THEN 'TRUE' ELSE 'FALSE' END);
END;
/

(B)
DECLARE
   l_flag1   BOOLEAN := TRUE;
   l_flag2   BOOLEAN := FALSE;
   l_flag3   BOOLEAN;
BEGIN
   DBMS_OUTPUT.put_line (l_flag1);
   DBMS_OUTPUT.put_line (l_flag2);
   DBMS_OUTPUT.put_line (l_flag3);
END;
/

(C)
DECLARE
   l_flag1   BOOLEAN := TRUE;
   l_flag2   BOOLEAN := FALSE;
   l_flag3   BOOLEAN;

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN CASE
                WHEN boolean_in THEN 'TRUE'
                WHEN NOT boolean_in THEN 'FALSE'
                ELSE 'NULL'
             END;
   END;
BEGIN
   DBMS_OUTPUT.put_line (to_char (l_flag1));
   DBMS_OUTPUT.put_line (to_char (l_flag2));
   DBMS_OUTPUT.put_line (to_char (l_flag3));
END;
/

(D)
DECLARE
   l_flag1   BOOLEAN := TRUE;
   l_flag2   BOOLEAN := FALSE;
   l_flag3   BOOLEAN;

   PROCEDURE put_line (boolean_in IN BOOLEAN)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
         CASE
            WHEN boolean_in THEN 'TRUE'
            WHEN NOT boolean_in THEN 'FALSE'
            ELSE 'NULL'
         END);
   END;
BEGIN
   put_line (l_flag1);
   put_line (l_flag2);
   put_line (l_flag3);
END;
/

newkid

2012-2-7 答案CD.
A: 没有考虑NULL
B: NULL值直接被输出时是空白，而不是字符串"NULL"

============================================
2012-2-8 显式游标/隐式游标
作者：Steven Feuerstein
难度：高

我创建了一张表并填入数据:
CREATE TABLE plch_employees
(
   employee_id   INTEGER,
   last_name     VARCHAR2 (100),
   salary        NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (138, 'Mondrian', 1000000);

   COMMIT;
END;
/

然后我创建了五个不同的过程来从这张表获取一行：
CREATE OR REPLACE PROCEDURE plch_use_ref_cursor (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_cv         SYS_REFCURSOR;
   l_employee   plch_employees%ROWTYPE;
BEGIN
   OPEN l_cv FOR
      SELECT *
        FROM plch_employees
       WHERE employee_id = id_in;

   FETCH l_cv INTO l_employee;

   CLOSE l_cv;
END;
/

CREATE OR REPLACE PROCEDURE plch_use_nds (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_employee   plch_employees%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE
      'select * from plch_employees where employee_id = :id'
      INTO l_employee
      USING id_in;
END;
/

CREATE OR REPLACE PROCEDURE plch_use_implicit (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_employee   plch_employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM plch_employees
    WHERE employee_id = id_in;
END;
/

CREATE OR REPLACE PROCEDURE plch_use_explicit (
   id_in IN plch_employees.employee_id%TYPE)
IS
   CURSOR emp_cur
   IS
      SELECT *
        FROM plch_employees
       WHERE employee_id = id_in;

   l_employee   plch_employees%ROWTYPE;
BEGIN
   OPEN emp_cur;

   FETCH emp_cur INTO l_employee;

   CLOSE emp_cur;
END;
/

CREATE OR REPLACE PROCEDURE plch_use_cfl (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_employee   plch_employees%ROWTYPE;
BEGIN
   FOR rec IN (SELECT *
                 FROM plch_employees
                WHERE employee_id = id_in)
   LOOP
      l_employee := rec;
   END LOOP;
END;
/


下列哪些选项包含了一个关于这些过程的性能的正确陈述？

(A)
PL/SQL优化器“重写”了显式游标的实现方法(plch_use_explicit)所以它实际上执行了一个隐性游标。

(B)
在大多数情况下，plch_use_implicit过程在这几种之中是最有效的。

(C)
包含本地动态SQL的过程(plch_use_nds)是最慢的实现方法。

(D)
PL/SQL优化器“重写”了FOR循环的实现方法(plch_use_cfl)，所以它实际上执行了一个单独的隐性查询。

bell6248

newkid 发表于 2012-2-10 01:06
2012-2-7 答案CD.
A: 没有考虑NULL
B: NULL值直接被输出时是空白，而不是字符串"NULL"

2012-2-8 显式游标/隐式游标

答案C， D

A: 不是隐性游标， 而是explicit游标。
B: 我觉得除了plch_use_nds， 是最慢的由于使用了动态的方法[虽然已经使用了binding variable]，
     其他的4种方法，效率几乎是一样的.

newkid

2012-2-8 答案B.
A: 优化器并不会把这个显式游标转换为隐式。
B: 正确，当获取单独一行时，隐式游标通常是最快的。也有显式游标更快的情况，比如，当你运行代码之前，你执行了这两句：

insert into plch_employees
   select * from plch_employees connect by level <= 10000;
rollback;

那么显式就会比隐式游标快。因为在隐式游标中，ORACLE需要检查结果是否多于一行，如果是的话它会抛出TOO_MANY_ROWS异常。本例中employee_id没有唯一索引。上面的INSERT导致表的高水位线被推得很远，ORACLE不得不扫描更多的块来确保查询只返回一行。

C: 你可以会以为这是最慢的，因为它不是静态SQL. 然而，在最近版本的ORACLE数据库中，执行动态SQL的开销已经非常小了，其代价小于打开一个游标变量(plch_use_refcur)。用游标变量永远需要软解析。
D: 优化器不会把这个FOR循环转换为隐式游标。因为编译阶段优化器不知道这个查询只返回一行，数据是变化的，多行结果的可能性始终存在，转换后就不等价了。优化器对FOR循环所做的优化是BULK FETCH, 即批量获取而不是逐行获取。
============================================
2012-2-9  NOCOPY Hint何时忽略
作者：mentzel.iudith
难度：高

我创建了如下的过程:
CREATE OR REPLACE PROCEDURE plch_proc (
   p1 IN NUMBER, p2 IN OUT NOCOPY NUMBER )
AS
BEGIN
    p2 := p1 + p2 ;

    RAISE PROGRAM_ERROR;
END;
/

哪些选项在执行之后会显示 "Result is 100"?

(A)
DECLARE
     a  NUMBER NOT NULL := 10 ;
     b  NUMBER NOT NULL := 90 ;
BEGIN
     BEGIN
         plch_proc (a, b);
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;

     DBMS_OUTPUT.put_line ('Result is '|| b);
END;

(B)
DECLARE
     a  NUMBER(3) := 10 ;
     b  NUMBER(3) := 90 ;
BEGIN
     BEGIN
         plch_proc (a, b);
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;

     DBMS_OUTPUT.put_line ('Result is '|| b);
END;

(C)
DECLARE
    TYPE t_number IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    l_numbers  t_number;
BEGIN
    l_numbers(10) := 10;
    l_numbers(90) := 90;

    BEGIN
        plch_proc ( l_numbers(l_numbers.FIRST),
                    l_numbers(l_numbers.LAST) );
    EXCEPTION
         WHEN OTHERS THEN NULL;
    END;

    DBMS_OUTPUT.put_line ('Result is '|| l_numbers(l_numbers.LAST) );   
END;

(D)
DECLARE
     a  NUMBER := 10 ;
     b  NUMBER := 90 ;
BEGIN
     BEGIN
         plch_proc (a, b);
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;

     DBMS_OUTPUT.put_line ('Result is '|| b);
END;

(E)
DECLARE
     a  VARCHAR2(5) := '10' ;
     b  VARCHAR2(5) := '90' ;
BEGIN
     BEGIN
         plch_proc (a, b);
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;

     DBMS_OUTPUT.put_line ('Result is '|| b);
END;

newkid

2012-2-9 答案D.

A: 变量声明中的NOT NULL约束使得NOCOPY 失效
B: 对NUMBER型的宽度定义使得NOCOPY 失效
C: 数组元素不能以地址方式传参，因此NOCOPY失效
D: 正确
E: 隐式类型转换使得NOCOPY 失效
=========================================
2012-2-10
作者：Steven Feuerstein
难度：低

下列的每个选项都包含了一个函数体，其函数头如下：
FUNCTION plch_rest_of_string (
   string_in IN VARCHAR2, from_in IN PLS_INTEGER)
   RETURN VARCHAR2
IS

这个函数接受一个字符串string_in，还有字符串的开始位置from_in,　然后返回从from_in到字符串结束为止的部分。

哪些选项达到这个要求，满足所有可能的VARCHAR2值？假设from_in永远是正数。

(A)
BEGIN
   RETURN SUBSTR (string_in, 1, from_in);
END plch_rest_of_string;
/

(B)
BEGIN
   RETURN SUBSTR (string_in, from_in);
END plch_rest_of_string;
/

(C)
BEGIN
   RETURN SUBSTR (string_in, from_in, 4000);
END plch_rest_of_string;
/

(D)
BEGIN
   RETURN SUBSTR (string_in, from_in, 32767);
END plch_rest_of_string;
/

niuxxf

BD

newkid

niuxxf 发表于 2012-2-14 13:40
BD

正确。
=========================================
2012-2-13
作者：Steven Feuerstein
难度：低

哪些代码块在执行之后会显示 "O Frabjous Day!" ?

(A)
DECLARE
   c_jabberwocky CONSTANT VARCHAR2 (100) := 'O Frabjous Day!';
BEGIN
   DBMS_OUTPUT.put_line (c_jabberwocky);
END;
/

(B)
DECLARE
   c_jabberwocky CONSTANT VARCHAR2 (100);
BEGIN
   c_jabberwocky := 'O Frabjous Day!';
   DBMS_OUTPUT.put_line (c_jabberwocky);
END;
/

(C)
DECLARE
   c_jabberwocky CONSTANT VARCHAR2 (100)
                               DEFAULT 'O Frabjous Day!' ;
BEGIN
   DBMS_OUTPUT.put_line (c_jabberwocky);
END;
/

(D)
CREATE OR REPLACE PACKAGE plch_poetry
IS
   c_jabberwocky   CONSTANT VARCHAR2 (100)
                               DEFAULT 'O Frabjous Day!' ;
END plch_poetry;
/

BEGIN
   DBMS_OUTPUT.put_line (plch_poetry.c_jabberwocky);
END;
/

(E)
CREATE OR REPLACE PACKAGE plch_poetry
IS
   c_jabberwocky   CONSTANT VARCHAR2 (100);
END plch_poetry;
/

CREATE OR REPLACE PACKAGE BODY plch_poetry
IS
BEGIN
   c_jabberwocky := 'O Frabjous Day!';
END plch_poetry;
/

BEGIN
   DBMS_OUTPUT.put_line (plch_poetry.c_jabberwocky);
END;
/

(F)
DECLARE
   c_jabberwocky CONSTANT VARCHAR2 (100)
      INITIALIZE 'O Frabjous Day!';
BEGIN
   DBMS_OUTPUT.put_line (c_jabberwocky);
END;
/