PL/SQL CHALLENGE 每日一题

newkid

2011-11-24 答案ABE.

PL/SQL 函数的结果缓存机制提供了一种把PL/SQL 函数的结果缓存在SGA里面的办法，这个结果对运行应用程序的所有会话都是可用的。缓存机制简单而且有效，你不必再费心设计开发自己的缓存及其管理机制。
为了打开函数的结果缓存，你只需在函数头加上RESULT_CACHE子句（如果在PACKAGE里面使用，则包头和包体中的函数声明都必须有这个子句）
当一个结果缓存函数被调用时，系统检查缓存。如果以前调用的结果存在于缓存中，参数也相同，系统直接返回结果二不是再次执行函数体。如果缓存中找不到该结果，系统执行函数体，并在把控制权交还给调用者之前，把结果加入到缓存（针对于这组参数）。

函数结果缓存通常保存在函数体中查询到的一个或多个表的数据。ORACLE需要确保这个数据的拷贝是干净的（即未被修改过）。

ORACLE通过两个机制来确保数据干净：

1. 每当表发生了修改并被提交，所有依赖于这个表的结果缓存将被清空。随后对函数的调用会导致数据再次进入缓存。

2. 一旦你在会话中修改了表，那么所有依赖于这个表的缓存将被忽略。即：缓存结果依赖于被改过的表的函数体将会被执行，一直到你提交或回滚了这些修改。

A: 两次调用参数都为NULL, ORACLE认为没有变化，直接取缓存结果而不是重新调用。
B: 同上，参数没有发生变化。
C: 参数没有发生变化，但是代码修改了 plch_foo 表，函数的缓存对当前会话不再有效；COMMIT发生后，CACHE对所有会话都不再有效。因此再次调用函数时，函数被再次执行了
D: 同上，虽然没有COMMIT, 但是该缓存对当前会话已经不可用了，所以函数会被再次执行。
   假如这个选项后面还有更多的调用：lvdummy := plch_foo (3);
   则函数体每次都会被重新执行。
E: UPDATE没有修改任何数据，因此缓存仍然有效。

=========================================
2011-11-25 包的初始化

作者：Steven Feuerstein
难度：中

为了鼓励访问者越来越多地使用我们的网站，我们针对他们的活动给予积分。我们的积分计算方法为基本点数和系数的乘积。如果活动发生在 08:00 之前，系数为3; 如果发生在 16:00 之前，系数为2; 其他的系数为1. 这个公式以如下函数实现：

CREATE OR REPLACE FUNCTION plch_multiplier
RETURN PLS_INTEGER
IS
   c_hour CONSTANT PLS_INTEGER
      := TO_NUMBER (TO_CHAR (SYSDATE, 'HH24')) ;
BEGIN
RETURN CASE
        WHEN c_hour < 8 THEN 3
        WHEN c_hour < 16 THEN 2
        ELSE 1
        END;
END;
/

然后我用这个包里的函数为活动计算积分：
CREATE OR REPLACE PACKAGE plch_pkg
IS
   FUNCTION points_for_activity (base_points_in IN INTEGER)
      RETURN PLS_INTEGER;
END;
/

先不假设我们的应用程序是状态相关或者状态无关，哪些选项实现了包体，使得计算总是正确的？
(A)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   g_multiplier   PLS_INTEGER;

   FUNCTION points_for_activity (base_points_in IN INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN base_points_in * g_multiplier;
   END;
BEGIN
   g_multiplier := plch_multiplier();
END;
/


(B)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   FUNCTION points_for_activity (base_points_in IN INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN base_points_in * plch_multiplier();
   END;
END;
/


(C)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   g_multiplier   PLS_INTEGER;

   FUNCTION points_for_activity (base_points_in IN INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      IF g_multiplier IS NULL
      THEN
         g_multiplier := plch_multiplier();
      END IF;

      RETURN base_points_in * g_multiplier;
   END;
END;
/

(D)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   g_multiplier   PLS_INTEGER := plch_multiplier();

   FUNCTION points_for_activity (base_points_in IN INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN base_points_in * g_multiplier;
   END;
END;
/

jboracle1981

有点搞晕了，还以为要指定依赖关系RELIES_ON的时候，
所依赖表的更新，对缓存结果有影响，才会重新调用

solomon_007

2011-11-25

B

newkid

2011-11-25答案B. 其他写法都是“静态”的，不能取到当前时间。
=================================================
2011-11-28 取单行数据
作者：Steven Feuerstein
难度：低

plch_employees表有一个单列主键employee_id。我需要写一个函数，声明如下：
FUNCTION plch_one_employee (
   employee_id_in IN PLS_INTEGER)
   RETURN plch_employees%ROWTYPE

它有如下行为：
1. 如果传入函数的员工ID是NULL, 抛出VALUE_ERROR异常；
2. 如果找不到这个员工，抛出NO_DATA_FOUND异常。

哪些选项符合需求？

(A)
IS
   l_return   plch_employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN l_return;
END plch_one_employee;

(B)
IS
   CURSOR one_emp_cur
   IS
      SELECT *
        FROM plch_employees
       WHERE employee_id = employee_id_in;

   l_return   one_emp_cur%ROWTYPE;
BEGIN
   OPEN one_emp_cur;
   FETCH one_emp_cur INTO l_return;
   CLOSE one_emp_cur;

   RETURN l_return;
END plch_one_employee;

(C)
IS
   l_return   plch_employees%ROWTYPE;
BEGIN
   IF employee_id_in IS NULL
   THEN
      RAISE VALUE_ERROR;
   END IF;

   SELECT *
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN l_return;
END plch_one_employee;

(D)
IS
   CURSOR one_emp_cur
   IS
      SELECT *
        FROM plch_employees
       WHERE employee_id = employee_id_in;

   l_return   one_emp_cur%ROWTYPE;
BEGIN
   IF employee_id_in IS NULL
   THEN
      RAISE VALUE_ERROR;
   END IF;

   OPEN one_emp_cur;
   FETCH one_emp_cur INTO l_return;

   IF one_emp_cur%NOTFOUND
   THEN
      CLOSE one_emp_cur;
      RAISE NO_DATA_FOUND;
   ELSE
      CLOSE one_emp_cur;
      RETURN l_return;
   END IF;
END plch_one_employee;

solomon_007

CD

jboracle1981

2011-11-28 取单行数据
=================
CD

newkid

2011-11-28答案CD, 楼上都答对了。注意用FETCH游标的方法，即使没有数据也不会报NO_DATA_FOUND错误。
=================================================
2011-11-29 11G新功能：PL/SCOPE

注：关于PL/SCOPE可参考我翻译的这篇文章：
http://www.itpub.net/thread-1336860-1-1.html

作者：Steven Feuerstein
难度：高

我在当前会话把PL/Scope打开：

ALTER SESSION SET plscope_settings='identifiers:all'
/

然后我创建了这个包头：
CREATE OR REPLACE PACKAGE plch_pkg
IS
   PROCEDURE do_stuff;
END plch_pkg;
/

下列哪些查询实现了一个包体，从而使得下列的查询：
SELECT name, usage
  FROM user_identifiers
WHERE     object_name = 'PLCH_PKG'
       AND type IN ('PROCEDURE', 'FUNCTION')
ORDER BY usage
/

会输出这些结果：
NAME       USAGE      
---------- -----------
DO_STUFF   CALL      
DO_STUFF   DECLARATION
DO_STUFF   DECLARATION
DO_STUFF   DEFINITION
DO_STUFF   DEFINITION

(A)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff (number_in IN NUMBER)
   IS
   BEGIN
      NULL;
   END do_stuff;

   PROCEDURE do_stuff
   IS
   BEGIN
      NULL;
   END do_stuff;
END plch_pkg;
/

(B)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
   BEGIN
      NULL;
   END do_stuff;
END plch_pkg;
/

(C)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff (number_in IN NUMBER)
   IS
   BEGIN
      NULL;
   END do_stuff;

   PROCEDURE do_stuff
   IS
   BEGIN
      do_stuff (1);
   END do_stuff;
END plch_pkg;
/

(D)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff (number_in IN NUMBER)
   IS
   BEGIN
      NULL;
   END do_stuff;

   PROCEDURE do_stuff
   IS
   BEGIN
      NULL;
   END do_stuff;
BEGIN
   do_stuff (1);
END plch_pkg;
/

solomon_007

D

solomon_007

PL/Scope outline:

1. PL/Scope is a new feature in oracle 11g

2. PL/Scope is intended for application developers

3. PL/Scope stores its data in SYSAUX tablespace and uses very little space

4. PL/Scope is a compile time activity not a runtime activity

5. PL/Scope init parameter in init<SID>.ora is PLSCOPE_SETTINGS ('ALL' or 'NONE')

6. PL/Scope turn on/turn off in system /session level

   ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
   ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';
   ALTER SYSTEM  SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

7. PL/Scope can compile an object with identifier collection (PROCEDURE, PACKAGE,TRIIGER, FUNCTION, TYPE, etc.)

   ALTER PACKAGE pkg_package COMPILE PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

8. PL/Scope object settings uses view

   USER_PLSQL_OBJECT_SETTINGS

9. PL/Scope gather data into views
   
   DBA/ALL/USER_IDENTIFIERS

10. PL/Scope static analysis using dictionary as following

   USER_SOURCE
   USER_DEPENDENCIES
   USER_PROCEDURES
   USER_ARGUMENTS
   USER_TYPES
   USER_TYPE_METHODS
   USER_TRIGGERS
   USER_IDENTIFIERS

11. PL/Scope dynamic analysis using packages as following

   DBMS_PROFILE
   DBMS_TRACE
   DBMS_HPROF

solomon_007

--in oracle 11g env...

CREATE OR REPLACE TYPE CODE_INFO as object (
  object_owner   VARCHAR2(30),
  object_name    VARCHAR2(30),
  object_subname VARCHAR2(30),
  object_type    VARCHAR2(30),
  object_usage   VARCHAR2(30),
  line#          NUMBER,
  column#        NUMBER);
/

CREATE OR REPLACE TYPE CODE_INFOS as table of code_info;
/


CREATE OR REPLACE PACKAGE PKG_PLSCOPE AS
  
  FUNCTION source_lookup( p_string IN VARCHAR2 )
  RETURN code_infos;
  
  FUNCTION source_lookup(
                          p_owner  IN VARCHAR2,
                          p_object IN VARCHAR2,
                          p_type   IN VARCHAR2,
                          p_line   IN NUMBER )
  RETURN VARCHAR2;
  
  FUNCTION get_impact_info(
                            p_owner  IN VARCHAR2,
                            p_object IN VARCHAR2,
                            p_unit   IN VARCHAR2 )
  RETURN code_infos;
  
  PROCEDURE print_impacts(
                            p_owner  IN VARCHAR2,
                            p_object IN VARCHAR2,
                            p_unit   IN VARCHAR2,
                            p_print_related_text IN BOOLEAN DEFAULT FALSE );
  
  PROCEDURE print_impacts(
                          p_code_infos IN code_infos,
                          p_print_related_text IN BOOLEAN );

END PKG_PLSCOPE;
/




CREATE OR REPLACE PACKAGE BODY PKG_PLSCOPE AS

  FUNCTION source_lookup( p_string IN VARCHAR2 )
  RETURN code_infos
  AS
    v_string VARCHAR2(32767) := UPPER(p_string);
    v_code_infos code_infos := code_infos();
  BEGIN
    FOR code_values IN (
                        SELECT code_info(
                                         owner,
                                         name,
                                         to_char(NULL),
                                         type,
                                         'SOURCE',
                                         line,
                                         instr(v_string, p_string)
                                         ) code_value
                          FROM all_source
                          WHERE UPPER(text) LIKE '%'||UPPER(p_string)||'%'
                        )
    LOOP   
      
      v_code_infos.extend;
      v_code_infos(v_code_infos.last) := code_values.code_value;
      
    END LOOP;  
   
    RETURN v_code_infos;
   
  END;

  FUNCTION source_lookup(
                          p_owner  IN VARCHAR2,
                          p_object IN VARCHAR2,
                          p_type   IN VARCHAR2,
                          p_line   IN NUMBER )
  RETURN VARCHAR2
  AS
    v_output VARCHAR2(32767);
  BEGIN
    SELECT text
      INTO v_output
      FROM all_source
      WHERE owner = p_owner
        AND name  = p_object
        AND type  = p_type
        AND line  = p_line;
    RETURN v_output;
  END;
  
  FUNCTION get_impact_info(
                            p_owner  IN VARCHAR2,
                            p_object IN VARCHAR2,
                            p_unit   IN VARCHAR2 )
  RETURN code_infos
  AS
    v_code_infos code_infos := code_infos();
  BEGIN
    FOR code_values IN (                       
                        SELECT code_info( ai.owner,
                                          prior_object_name,
                                          CASE
                                            WHEN ai.object_type IN ('PROCEDURE', 'FUNCTION')
                                            THEN NULL
                                          ELSE
                                            ai.name
                                          END,
                                          ai.object_type,
                                          'CALL',
                                          cnctby_vw.line,
                                          cnctby_vw.p_col
                                          ) code_value
                           FROM all_identifiers ai,
                                (
                                 SELECT usage,
                                        level,
                                        usage_id,
                                        usage_context_id,
                                        PRIOR usage_id p_usage_id,
                                        PRIOR usage_context_id prior_usagectx_id,
                                        object_name,
                                        name,
                                        object_type,
                                        type,
                                        line,
                                        PRIOR col p_col,
                                        PRIOR object_name prior_object_name,
                                        PRIOR name p_name,
                                        PRIOR object_type prior_object_type
                                   FROM all_identifiers
                                  WHERE OWNER = p_owner
                                    AND OBJECT_TYPE IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'TYPE BODY')
                                    AND USAGE NOT IN ('DECLARATION', 'ASSIGNMENT', 'DEFINITION')
                                    AND TYPE IN ('PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TYPE', 'TYPE BODY')
                                CONNECT BY PRIOR usage_id = usage_context_id
                                       AND PRIOR name = p_object
                                       AND name = p_unit
                                       AND prior usage_context_id != 0
                                )  cnctby_vw
                          WHERE ai.usage_id = cnctby_vw.prior_usagectx_id
                            AND ai.object_name = cnctby_vw.prior_object_name
                            AND ai.object_type = cnctby_vw.prior_object_type  
                      )
    LOOP

      v_code_infos.extend;
      v_code_infos(v_code_infos.last) := code_values.code_value;
      
    END LOOP;  
   
    RETURN v_code_infos;
   
  END;

  PROCEDURE chunk_output(
      p_string IN VARCHAR2,
      p_space IN VARCHAR2)
  AS
  BEGIN
    FOR i IN 1..length(p_string) / 40
    LOOP
      DBMS_OUTPUT.PUT_LINE(lpad(' ', p_space) || SUBSTR(p_string, i + ((i-1) * 40), 40));
    END LOOP;
  END;
      
  PROCEDURE print_impacts(
                          p_code_infos         IN code_infos,
                          p_print_related_text IN BOOLEAN )
  AS
    v_index PLS_INTEGER;
    v_output VARCHAR2(32767);
  BEGIN

    v_index := p_code_infos.FIRST;

    LOOP
      EXIT WHEN v_index IS NULL;
      
      DBMS_OUTPUT.PUT_LINE(
                 'Impact to ' || p_code_infos(v_index).object_type || ' ' ||
                  p_code_infos(v_index).object_owner || '.' ||
                  p_code_infos(v_index).object_name ||
                        case when p_code_infos(v_index).object_subname is not null then '.' else null end ||
                  p_code_infos(v_index).object_subname ||
                  ' at line ' || p_code_infos(v_index).line# ||
                  ' and column ' || p_code_infos(v_index).column# );
      IF p_print_related_text
      THEN
        DBMS_OUTPUT.PUT_LINE( '    TEXT: ' );
        v_output := source_lookup(
                        p_code_infos(v_index).object_owner,
                        p_code_infos(v_index).object_name,
                        p_code_infos(v_index).object_type,
                        p_code_infos(v_index).line# );
        chunk_output(v_output, 10);
       END IF;   
                  
       v_index := p_code_infos.NEXT(v_index);
      
    END LOOP;
  END;

  PROCEDURE print_impacts(
                            p_owner  IN VARCHAR2,
                            p_object IN VARCHAR2,
                            p_unit   VARCHAR2,
                            p_print_related_text IN BOOLEAN DEFAULT FALSE  )
  AS
  BEGIN
    print_impacts( get_impact_info( p_owner, p_object, p_unit ), p_print_related_text );
  END;
  
END PKG_PLSCOPE;

/