PL/SQL CHALLENGE 每日一题

jazovo

B.

关键就是 TYPE strings_aat IS TABLE OF BOOLEAN INDEX BY VARCHAR2 (100);

还可以写的更简单一些。

CREATE OR REPLACE
  FUNCTION plch_remove_dups(
      strings_in IN plch_strings_t)
    RETURN plch_strings_t
  IS
  TYPE strings_aat IS TABLE OF BOOLEAN INDEX BY VARCHAR2 (100);
  l_string VARCHAR2(100);
  l_strings strings_aat;
  l_return plch_strings_t := plch_strings_t ();
BEGIN
  FOR indx IN 1 .. strings_in.COUNT
  LOOP
    l_string := strings_in (indx);
    IF NOT l_strings.EXISTS(l_string) THEN
      l_strings (l_string) := TRUE;
      l_return.EXTEND;
      l_return (l_return.LAST) := l_string;
    END IF;
  END LOOP;
  RETURN l_return;
END;

edwzhang

刚才去注册了

tyoodz

jazovo 发表于 2011-12-16 09:22
B.

关键就是 TYPE strings_aat IS TABLE OF BOOLEAN INDEX BY VARCHAR2 (100);

学习了

newkid

2011-12-14 答案 BC.
A: 不存在DISTINCT这样一个函数。
B: 代码有点多，jazovo的改法少掉了一个循环，但最好还是用C的方法。
C: SET函数会去除重复元素（只能对嵌套表使用），我测试的结果显示最终集合中的元素以首次出现的顺序排列。
D: EXISTS函数用错了，在这里只能判断某个下标是否存在，不能判断下标对应的元素值是否存在。
===============================
2011-12-15 11G新功能：RESULT_CACHE函数
作者：Steven Feuerstein
难度：高

我创建了两张表，一个视图和一个过程：
CREATE TABLE plch_tab1 (n NUMBER)
/

CREATE TABLE plch_tab2 (n NUMBER)
/

CREATE OR REPLACE VIEW plch_view
AS
   SELECT n FROM plch_tab1
   UNION
   SELECT n FROM plch_tab2
/

BEGIN
   INSERT INTO plch_tab1
        VALUES (1);

   INSERT INTO plch_tab2
        VALUES (2);

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE plch_query
IS
BEGIN
   FOR rec IN (SELECT * FROM plch_tab1)
   LOOP
      NULL;
   END LOOP;
END;
/

下列的每个选项实现了这样的一个函数:
FUNCTION plch_func (n IN NUMBER)
   RETURN NUMBER
   RESULT_CACHE

我创建了如下的过程来测试plch_func:
PROCEDURE plch_test (n IN NUMBER)
IS
BEGIN
   DBMS_OUTPUT.put_line (plch_func (1));

   EXECUTE IMMEDIATE
      'UPDATE plch_tab' || n || ' SET n = n + 0';

   DBMS_OUTPUT.put_line (plch_func (1));
   ROLLBACK;
END;


哪些选项在执行这段代码之后：
BEGIN
  plch_test (1);
END;
/

会显示如下的文本：
Running plch_func
1
Running plch_func
1

(A)
IS
   l_n   NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   SELECT n
     INTO l_n
     FROM plch_tab1
    WHERE n = plch_func.n;

   RETURN l_n;
END;

(B)
IS
   l_n   NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   EXECUTE IMMEDIATE
      'SELECT n FROM plch_tab' || n || ' WHERE n = :n'
      INTO l_n
      USING n;

   RETURN l_n;
END;

(C)
IS
   l_n   NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   SELECT n
     INTO l_n
     FROM plch_view
    WHERE n = plch_func.n;

   RETURN l_n;
END;

(D)
IS
   l_n   plch_tab1.n%TYPE := n;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   RETURN l_n;
END;

(E)
IS
   l_n   plch_tab1.n%TYPE := n;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   plch_query;
   RETURN l_n;
END;

jazovo

newkid 发表于 2011-12-17 00:32
2011-12-14 答案 BC.
A: 不存在DISTINCT这样一个函数。
B: 代码有点多，jazovo的改法少掉了一个循环，但最 ...

学习了！问题不在于不了解SET函数，而在于没有研究每一个选项就下结论。

newkid

2011-12-15 答案 ABCE.
A: 代码里静态地引用了plch_tab1表, 所以当plch_test修改了plch_tab1表（是通过动态SQL修改的），缓存就无效了。
B: 代码里动态地引用了plch_tab1表。在11.2版本，ORACLE甚至能自动检测到动态SQL里面的依赖性，所以当plch_test修改了plch_tab1表（是通过动态SQL修改的），缓存就无效了。
C: 这个选项引用的是plch_view视图，它查询的是plch_tab1表的数据，在11.2版本，ORACLE能自动检测到视图里的依赖性，所以当plch_test修改了plch_tab1表（是通过动态SQL修改的），缓存就无效了。
D: 这个选项不存在对plch_tab1表的依赖性，所以第二次调用时直接返回缓存里的结果而不会真正执行代码。
E: 这个选项调用了另外一个子程序，而该子程序查询了plch_tab1表。在11.2版本，ORACLE能自动检测到这种间接的依赖性，所以当plch_test修改了plch_tab1表（是通过动态SQL修改的），缓存就无效了。
===============================
2011-12-16 MONTHS_BETWEEN函数在月末的表现
作者：Steven Feuerstein
难度：中

哪些代码块执行之后会显示 "1"?

(A)
BEGIN
   DBMS_OUTPUT.put_line (
      MONTHS_BETWEEN (DATE '2012-01-16',  DATE '2011-12-16'));
END;
/

(B)
BEGIN
   DBMS_OUTPUT.put_line (
      MONTHS_BETWEEN (DATE '2011-12-16', DATE '2012-01-16'));
END;
/

(C)
BEGIN
   DBMS_OUTPUT.put_line (
      MONTHS_BETWEEN (DATE '2012-02-28', DATE '2012-01-31'));
END;
/

(D)
BEGIN
   DBMS_OUTPUT.put_line (
      MONTHS_BETWEEN (DATE '2012-05-15', DATE '2012-04-16')
         + 1/31);
END;
/

gyhgood

居然看花眼了，A B看成一样的了

newkid

2011-12-16 答案AD.
A: 正好相差一个月（“日”相同）
B: 正好相差一个月, 但是大小搞反了。MONTHS_BETWEEN相当于第一个参数减去第二个。
C: 如果非闰年，那么2月28和1月31都是当月最后一天，被认为正好相差一个月；但2012年是闰年，所以本选项会返回0.903225806，即不足一月。
D: 如果MONTHS_BETWEEN的两个参数的“日”相同，或者都是最后一天，则MONTHS_BETWEEN会返回整数，否则会扣除或补足天数/31。
本选项中，4月16和5月15相差不到一个月（差一天），所以补上1/31就刚好等于1.

从下列查询可以看到MONTHS_BETWEEN的一些奇怪特点：

select MONTHS_BETWEEN(DATE '2011-02-28', DATE '2011-01-31') GAP_28_31
      ,MONTHS_BETWEEN(DATE '2011-02-28', DATE '2011-01-30') GAP_28_30
      ,MONTHS_BETWEEN(DATE '2011-02-28', DATE '2011-01-28') GAP_28_28
  from dual;

GAP_28_31  GAP_28_30   GAP_28_28
---------- ----------  ----------
         1 0.935483871          1

1月的日从 31 变到 30 再到28, MONTHS_BETWEEN 却是1 到 0.9 再变回1。
---------------------------------------------------
2011-12-19 变量、参数的声明及初始化
作者：Steven Feuerstein     
难度：中

哪些选项包含的程序单元定义在编译时不会报错？如果有多于一个语句，要求所有语句编译都不报错。

(A)
CREATE OR REPLACE PACKAGE plch_pkg
IS
   a_number   NUMBER;

   PROCEDURE a_procedure;
END;
/

(B)
CREATE OR REPLACE PACKAGE plch_pkg
IS
   a_number   NUMBER;

   PROCEDURE a_procedure;

   a_string   VARCHAR2 (100);

   FUNCTION a_function
      RETURN NUMBER;
END;
/

(C)
CREATE OR REPLACE PACKAGE plch_pkg
IS
   a_number   NUMBER;

   PROCEDURE a_procedure (
      string_in IN VARCHAR2 DEFAULT a_string);

   a_string   VARCHAR2 (100);

   FUNCTION a_function
      RETURN NUMBER;
END;
/


(D)
CREATE OR REPLACE PACKAGE plch_pkg
IS
   a_number   NUMBER;

   PROCEDURE a_procedure;

   a_string   VARCHAR2 (100);

   FUNCTION a_function
      RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   a_number2   NUMBER;

   PROCEDURE a_procedure
   IS
   BEGIN
      NULL;
   END;

   a_string2   VARCHAR2 (100);

   FUNCTION a_function
      RETURN NUMBER
   IS
   BEGIN
      RETURN TO_NUMBER (a_string2);
   END;

END;
/

newkid

2011-12-19 答案AB.
C: a_procedure的定义引用了a_string, 而a_string是后来才定义的。如果对调一下顺序就可以编译了。
D: 包体编译出错，a_string2的变量定义必须在过程a_procedure之前。
---------------------------------------------------
2011-12-20 求月末日期
作者：Steven Feuerstein     
难度：低

我们的员工表看起来像这样:
CREATE TABLE plch_employees
(
   employee_id   INTEGER
, last_name     VARCHAR2 (100)
, first_name    VARCHAR2 (100)
, hire_date     DATE
, salary        NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100
              , 'Clooney'
              , 'Sam'
              , DATE '2011-12-07'
              , 10000);

   INSERT INTO plch_employees
        VALUES (200
              , 'Craig'
              , 'Donna'
              , DATE '2011-12-01'
              , 15000);


   INSERT INTO plch_employees
        VALUES (300
              , 'Mirren'
              , 'Horace'
              , DATE '2011-11-23'
              , 17000);

   COMMIT;
END;
/

员工在被录用的当月的最后一天被评估（即使这天是周六或周日!）。我写了一个过程来显示每个员工的姓和评估日期：

CREATE OR REPLACE PROCEDURE plch_show_evals
IS
BEGIN
   FOR employee_rec IN (  SELECT last_name, hire_date
                            FROM plch_employees
                        ORDER BY hire_date)
   LOOP
      DBMS_OUTPUT.put_line (
            employee_rec.last_name
         || ':'
         || TO_CHAR (
               plch_last_date (employee_rec.hire_date)
             , 'YYYY-MM-DD'));
   END LOOP;
END;
/

哪些选项实现了函数，从而使得过程plch_show_evals在执行之后，将会显示下列文本？
Mirren:2011-11-30
Craig:2011-12-31
Clooney:2011-12-31

(A)
CREATE OR REPLACE FUNCTION plch_last_date (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN TRUNC (ADD_MONTHS (date_in, 1), 'MON') - 1;
END;

(B)
CREATE OR REPLACE FUNCTION plch_last_date (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN LAST_DATE (date_in);
END;

(C)
CREATE OR REPLACE FUNCTION plch_last_date (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN LAST_DAY (date_in);
END;

(D)
CREATE OR REPLACE FUNCTION plch_last_date (date_in IN DATE)
   RETURN DATE
IS
BEGIN
   RETURN date_in.LAST_DAY;
END;

newkid

2011-12-20 答案AC, 很简单的日期运算。
---------------------------------------------------
2011-12-21 LEAST函数的不同参数类型
作者：Steven Feuerstein     
难度：低

哪些代码块执行之后会显示 "111" ?

(A)
BEGIN
   DBMS_OUTPUT.put_line (LEAST ('111'
                              , 2
                              , 3
                              , 4));
END;

(B)
BEGIN
   DBMS_OUTPUT.put_line (LEAST ('111'
                              , ' 2'
                              , ' 3'
                              , ' 4'));
END;

(C)
BEGIN
   DBMS_OUTPUT.put_line (LEAST (111
                              , 2
                              , 3
                              , 4));
END;

(D)
BEGIN
   DBMS_OUTPUT.put_line (LEAST (111
                              , '2'
                              , '3'
                              , '4'));
END;

(E)
BEGIN
   DBMS_OUTPUT.put_line (LEAST ('111'
                              , '2'
                              , '3'
                              , '4'));
END;