PL/SQL CHALLENGE 每日一题

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;
/

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;
/

newkid

2012-2-13 答案 ACD.
B,E: CONSTANT的值必须在声明的时候赋予，不可以在代码中赋值。
F: INITIALIZE 不是正确的关键字，必须用DEFAULT或者 :=
==============================================
2012-2-14 打开游标后ROLLBACK的影响
作者：Valentin Nikotin
难度：中


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

BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Smith', 100000);
   COMMIT;
END;
/

然后我运行如下的代码：
DECLARE
   CURSOR emps_cur
   IS
        SELECT * FROM plch_employees
      ORDER BY employee_id;

   emp_rec   emps_cur%ROWTYPE;
BEGIN
   INSERT INTO plch_employees
        VALUES (200, 'Jones', 1000000);  

   OPEN emps_cur;

   ROLLBACK;  

   LOOP
      FETCH emps_cur INTO emp_rec;
      EXIT WHEN emps_cur%NOTFOUND;

      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;

   CLOSE emps_cur;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Error');
END;
/

执行之后会显示什么？

(A)
Smith
Jones

(B)
Error

(C)
Smith
Error

(D)
Smith

newkid

2012-2-14 答案B
如果你打开游标之后做了回滚，你将无法再从游标FETCH数据，ORACLE会抛出异常：
ORA-01002: fetch out of sequence

如果你的游标带有FOR UPDATE子句，在打开游标之后你做了提交，那么再试图FETCH时同样会发生这个错误。
===========================
2012-2-15 删除数组元素
作者：Steven Feuerstein
难度：中

我写了下面这个代码块：（未完成）
DECLARE
   TYPE suits_t IS VARRAY (4) OF VARCHAR2 (20);

   l_card_suits   suits_t
                     := suits_t ('Hearts'
                               ,  'Spades'
                               ,  'Diamonds'
                               ,  'Clubs');
BEGIN
   /*REMOVE_TWO*/
   DBMS_OUTPUT.put_line (l_card_suits.COUNT);
END;

哪些选项可用来代替 /*REMOVE_TWO*/ 注释，从而使得代码块执行之后会显示"2"?

(A)
l_card_suits.DELETE (3, 4);

(B)
DELETE FROM TABLE (l_card_suits)
      WHERE COLUMN_VALUE IN ('Diamonds', 'Clubs');

(C)
l_card_suits.TRIM (2);

(D)
l_card_suits (3) := NULL;
l_card_suits (4) := NULL;

newkid

2012-2-15 答案 C.

A: varray不能用DELETE方法。你只能用TRIM方法从尾部删除数组元素。
B: 这是SQL语法，不能用于数组。错误：ORA-00903: invalid table name
C: 正确的TRIM用法。
D: 只是把数组元素赋值为NULL, 还在数组里面。

==============================================
2012-2-16 FORALL中的VALUES OF子句
作者：Steven Feuerstein
难度：高

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

BEGIN
   INSERT INTO plch_employees VALUES (100, 'Picasso', 1000000);
   INSERT INTO plch_employees VALUES (200, 'Mondrian', 1000000);
   INSERT INTO plch_employees VALUES (300, 'O''Keefe', 1000000);
   COMMIT;
END;
/

然后我写了下面这个不完整的代码块：
DECLARE
   /*DECLARE*/
   l_salary NUMBER;
BEGIN
   /*SETUP*/

   FORALL indx IN VALUES OF l_indexes
      UPDATE plch_employees
         SET salary = salary * 2
       WHERE employee_id = l_ids (indx);

   SELECT SUM (salary)/1000000 INTO l_salary FROM plch_employees;

   DBMS_OUTPUT.put_line (l_salary);
END;
/

哪些选项可用来替代上面的两个注释，从而使得这个块执行后会显示 "5"?

(A)
用如下取代 /*DECLARE*/:
TYPE pls_integer_t IS TABLE OF PLS_INTEGER
                            INDEX BY PLS_INTEGER;
l_ids       pls_integer_t;
l_indexes   pls_integer_t;

用如下取代 /*SETUP*/:
l_ids (1) := 100;
l_ids (2) := 200;
l_ids (3) := 300;
l_indexes (200) := 2;
l_indexes (300) := 3;

(B)
用如下取代 /*DECLARE*/:
TYPE pls_integer_t IS TABLE OF PLS_INTEGER
                         INDEX BY PLS_INTEGER;
l_ids       pls_integer_t;
l_indexes   pls_integer_t;

用如下取代 /*SETUP*/:
l_ids (1) := 100;
l_ids (2) := 200;
l_ids (3) := 300;
l_indexes (1) := 100;
l_indexes (3) := 300;

(C)
用如下取代 /*DECLARE*/:
TYPE pls_integer_t IS TABLE OF PLS_INTEGER
                            INDEX BY PLS_INTEGER;
l_ids       pls_integer_t;
l_indexes   pls_integer_t;

用如下取代 /*SETUP*/:
l_ids (1) := 100;
l_ids (2) := 200;
l_ids (3) := 300;
l_indexes (200) := 2;
l_indexes (300) := 3;
l_indexes (400) := 3;

(D)
用如下取代 /*DECLARE*/:
TYPE pls_integer_t IS TABLE OF PLS_INTEGER
                            INDEX BY PLS_INTEGER;
l_ids       pls_integer_t;
l_indexes   pls_integer_t;

用如下取代 /*SETUP*/:
l_ids (1) := 100;
l_ids (2) := 200;
l_ids (3) := 300;
l_indexes (1) := 1;
l_indexes (2) := 3;

(E)
用如下取代 /*DECLARE*/:
TYPE numbers_t IS TABLE OF NUMBER;
l_ids       numbers_t := numbers_t (100, 200, 300);
l_indexes   numbers_t := numbers_t (1, 3);

把 /*SETUP*/ 用空白取代。

周应侯

newkid 发表于 2012-2-15 02:57
正确。
=========================================
2012-2-13

嗯~这个简单acd吧~
赋值符号位default或者:=
包想了一会儿，
没有存储过程和函数就不需要包头吧