PL/SQL CHALLENGE 每日一题

sohay

ACD
B错，因为常量初始化后不能再被赋值

newkid

2011-10-27 答案CD. ORACLE只对嵌套表(NESTED TABLE)和可变数组(VARRAY)提供构造函数(constructor function), ASSOCIATED ARRAY (即INDEX BY表)则没有这个构造函数，必须采用自定义函数。常量在声明的部分必须赋值，不能够在块中赋值。

======================================
2011-10-28 LIKE条件中的通配符
作者：Marc Thompson
难度：低

我创建了这张表并填入数据：
CREATE TABLE plch_new_parts
(
   partnum    NUMBER
, partname   VARCHAR2 (50)
)
/

BEGIN
   INSERT INTO plch_new_parts (partnum, partname)
        VALUES (1, 'MY PART NUMBER 1');

   INSERT INTO plch_new_parts (partnum, partname)
        VALUES (2, 'MY_PART_NUMBER_2');

   INSERT INTO plch_new_parts (partnum, partname)
        VALUES (3, 'MY_PART NUMBER_3');

   COMMIT;
END;
/

当我执行下列这段代码，屏幕上会显示什么？
DECLARE
   l_counts   DBMS_SQL.number_table;

BEGIN
   l_counts (1) := 0;
   l_counts (2) := 0;
   l_counts (3) := 0;
   l_counts (4) := 0;

   FOR rec IN (  SELECT partname
                   FROM plch_new_parts
               ORDER BY partnum)
   LOOP
      IF rec.partname LIKE 'MY PART NUMBER %'
      THEN
         l_counts (1) := l_counts (1) + 1;
      END IF;

      IF rec.partname LIKE 'MY_PART_NUMBER_%'
      THEN
         l_counts (2) := l_counts (2) + 1;
      END IF;

      IF rec.partname LIKE 'MY_PART NUMBER_%'
      THEN
         l_counts (3) := l_counts (3) + 1;
      END IF;

      IF rec.partname LIKE 'MY?PART?NUMBER?*'
      THEN
         l_counts (4) := l_counts (4) + 1;
      END IF;
   END LOOP;

   FOR indx IN 1 .. l_counts.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_counts (indx));
   END LOOP;
END;
/

(A)
1
1
1
0

(B)
1
3
2
0

(C)
0
0
0
3


(D)
3
3
3
3

(E)
1
3
2
3

hanaka

2011-10-28  B

gyhgood

B

'MY?PART?NUMBER?*' 这个是用来忽悠人的吧

newkid

2011-10-28答案B.
_匹配单个字符，%匹配任意个字符，*和?是忽悠人的。

Rain清风

学习了

newkid

2011-10-31 对集合的密集性判断

作者：Steven Feuerstein
难度：中

我创建了这个工具函数来显示一个布尔型变量的值：
CREATE OR REPLACE PROCEDURE plch_bpl (val IN BOOLEAN)
IS
BEGIN
   DBMS_OUTPUT.put_line (
      CASE val WHEN TRUE THEN 'TRUE' WHEN FALSE THEN 'FALSE' ELSE 'NULL' END);
END plch_bpl;
/

下列的选项中都包含了下面这个函数的实现代码：
CREATE OR REPLACE FUNCTION plch_is_dense (numbers_in IN DBMS_SQL.number_table)
   RETURN BOOLEAN

我写了另一个帮助过程来测试这个 plch_is_dense 函数:
CREATE OR REPLACE PROCEDURE plch_test_is_dense
IS
   l_numbers   DBMS_SQL.number_table;
BEGIN
   l_numbers (1000) := 1;
   l_numbers (3000) := 1;
   plch_bpl (plch_is_dense (l_numbers));
   l_numbers.delete;
   l_numbers (2) := 1;
   l_numbers (3) := 1;
   plch_bpl (plch_is_dense (l_numbers));
END;
/

哪些选项创建的plch_is_dense使得plch_test_is_dense在执行之后会显示这两行文本？
FALSE
TRUE

(A)
IS
BEGIN
   RETURN numbers_in.COUNT = (numbers_in.LAST - numbers_in.FIRST);
END;



(B)
IS
   l_number   NUMBER;
BEGIN
   FOR indx IN numbers_in.FIRST .. numbers_in.LAST
   LOOP
      l_number := numbers_in (indx);
   END LOOP;

   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

(C)
IS
   l_number   NUMBER;
BEGIN
   FOR indx IN 1 .. numbers_in.COUNT
   LOOP
      l_number := numbers_in (indx);
   END LOOP;

   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

(D)
IS
BEGIN
   RETURN numbers_in.COUNT = (numbers_in.LAST - numbers_in.FIRST + 1);
END;

gyhgood

看代码马虎了，居然把=想成了赋值。。。。。。。。。

774256182

学习 学习

newkid

10-31答案BD. 对于非密集型的COLLECTION访问不能从1开始，而是要用FIRST方法。访问不存在的元素会引发NO_DATA_FOUND异常。

=============================================
2011-11-1 求嵌套表平均值

作者：Steven Feuerstein
难度：中

我创建了一个SCHEMA级别的NUMBER型嵌套表：
CREATE OR REPLACE TYPE plch_numbers_t IS TABLE OF NUMBER
/

哪些选雄实现了一个名字为plch_avg的函数，能够计算一个上述嵌套表中所有元素的平均值，从而使得下列代码块执行之后会显示"3.5"?
DECLARE
   l_numbers   plch_numbers_t
                  := plch_numbers_t (1, 2, 3, 4, 5, 6);
BEGIN
   DBMS_OUTPUT.put_line (plch_avg (l_numbers));
END;
/

(A)
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
   l_index   PLS_INTEGER := numbers_in.FIRST;
   l_sum     NUMBER := 0;
BEGIN
   WHILE l_index IS NOT NULL
   LOOP
      l_sum := l_sum + numbers_in (l_index);
      l_index := numbers_in.NEXT (l_index);
   END LOOP;

   RETURN l_sum / numbers_in.COUNT;
END;
/

(B)
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
   l_average   NUMBER;
BEGIN
   SELECT AVG (COLUMN_VALUE) INTO l_average
     FROM TABLE (numbers_in);
   RETURN l_average;
END;
/

(C)
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
BEGIN
   RETURN AVG (numbers_in);
END;
/

(D)
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
   l_index   PLS_INTEGER := numbers_in.FIRST;
   l_average     NUMBER := 0;
BEGIN
   WHILE l_index IS NOT NULL
   LOOP
      l_average := l_average + numbers_in (l_index)/numbers_in.count;
      l_index := numbers_in.NEXT (l_index);
   END LOOP;

   RETURN l_average;
END;
/

(E)
CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
   l_average   NUMBER;
BEGIN
   SELECT AVG (numbers_in(ROWNUM)) INTO l_average
     FROM numbers_in;

   RETURN l_average;
END;
/