sys.dbms_utility包的使用:

参考文档:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm

             http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

　　　　http://www.dbforums.com/oracle/887432-dbms_utility-comma_to_table-giving-problem.html

1、

  (1)、Oralce中获取毫秒级别的时间:dbms_utility.get_time :可以用来估算一个函数的执行所花费的时间

--  Find out the current elapsed time in 100th's of a second.
--  Output:
--      The returned elapsed time is the number of 100th's
--      of a second from some arbitrary epoch.
--  Related Function(s): "get_cpu_time" [See below].
SELECT Dbms_Utility.Get_Time FROM Dual;

 

DECLARE
  Time_Before BINARY_INTEGER;
  Time_After  BINARY_INTEGER;
  TYPE t IS TABLE OF VARCHAR2(100);
  T1 t;
BEGIN
  Time_Before := Dbms_Utility.Get_Time;
  SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
  Time_After := Dbms_Utility.Get_Time;
  Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
  Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
END;

 

（2）、也可以使用Oracle 提供的set timing on命令来实现该功能：

Timing SQL Commands

    Oracle provides a TIMING command for measuring the running time of SQL commands. To activate this feature, type

set timing on;

    Then, Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently. Note that timing data may be affected by external factors such as system load, etc. To turn off timing, type

set timing off;

You can also create and control multiple timers; type HELP TIMING in sqlplus for details.

 

 comma_to_table和Table_To_Comma的使用:

方法说明:

comma_to_table:Convert a comma-separated list of names into a PL/SQL table of names This uses aname_parse to figure out what are names and what are commas This is an overloaded version for supporting fully-qualified attribute names.

 table_To_Comma:Convert a PL/SQL table of names into a comma-separated list of names

 该方法的使用注意事项:Trying to pass numbers, elements greater than 30 characters, reserved words etc. will not work, i.e.(不可以传递数字开头的，传递的元素的大小必须小于必须小于30，保留关键字将不起作用，只接受逗号分隔的元素格式，当为其他形式的分隔符是，可以使用replace方法进行替换)

结论:在真正的开发过程中，一半是传递ID串，但是每个ID的长度都为32位，所以就会出现问题。不推荐使用该方法进行ID串的拆分。

DECLARE
  t_Vararray    Dbms_Utility.Lname_Array;
  Vc_Stringlist VARCHAR2(4000);
  n_Idx         BINARY_INTEGER;
BEGIN
  --comma to table
  Vc_Stringlist := 'dkf,dddl,fewe,klkj';

  Dbms_Utility.Comma_To_Table(Vc_Stringlist, n_Idx, t_Vararray);
  Dbms_Output.Put_Line('Total Num : ' || To_Char(n_Idx));

  FOR i IN 1 .. n_Idx LOOP
    Dbms_Output.Put_Line(t_Vararray(i));
    t_Vararray(i) := '[' || t_Vararray(i) || ']';
  END LOOP;

  --table to comma  
  Dbms_Utility.Table_To_Comma(t_Vararray, n_Idx, Vc_Stringlist);
  Dbms_Output.Put_Line('');
  Dbms_Output.Put_Line('' || Vc_Stringlist);

END;

DECLARE
  t_Vararray    Dbms_Utility.Lname_Array;
  Vc_Stringlist VARCHAR2(4000);
  n_Idx         BINARY_INTEGER;
  Time_Before   BINARY_INTEGER;
  Time_After    BINARY_INTEGER;
  TYPE t IS TABLE OF VARCHAR2(100);
  T1 t;
BEGIN

  Time_Before := Dbms_Utility.Get_Time;
  SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
  --comma to table
  Vc_Stringlist := 'dkf111,dddl,fewe,klkj';

  Dbms_Utility.Comma_To_Table(Vc_Stringlist, n_Idx, t_Vararray);
  Dbms_Output.Put_Line('Total Num : ' || To_Char(n_Idx));

  FOR i IN 1 .. n_Idx LOOP
    Dbms_Output.Put_Line(t_Vararray(i));
    t_Vararray(i) := '[' || t_Vararray(i) || ']';
  END LOOP;

  --table to comma  
  Dbms_Utility.Table_To_Comma(t_Vararray, n_Idx, Vc_Stringlist);
  Dbms_Output.Put_Line('');
  Dbms_Output.Put_Line('' || Vc_Stringlist);
  Time_After := Dbms_Utility.Get_Time;
  Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
  Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
END;

DECLARE
  Time_Before BINARY_INTEGER;
  Time_After  BINARY_INTEGER;
  i_Idstr     VARCHAR2(32767) := '1,2,3,4';
  TYPE t IS TABLE OF VARCHAR2(100);
  T1 t;
BEGIN
  Time_Before := Dbms_Utility.Get_Time;
  SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
  FOR Cr IN (SELECT Substring FROM TABLE(Splitstr(i_Idstr, ','))) LOOP
    Dbms_Output.Put_Line(Cr.Substring);
  END LOOP;
  Time_After := Dbms_Utility.Get_Time;
  Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
  Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
END;

 针对上述问题:当前可以使用一个包，来实现上述的功能:

CREATE OR REPLACE PACKAGE parse AS
  /*
  || Package of utility procedures for parsing delimited or fixed position strings into tables
  || of individual values, and vice versa.
  */
  TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ','
    );
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ','
    );
END parse;
/
CREATE OR REPLACE PACKAGE BODY parse AS
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ','
    )
  IS
    v_string VARCHAR2(32767) := p_delimstring;
    v_nfields PLS_INTEGER := 1;
    v_table varchar2_table;
    v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
    v_delimlen PLS_INTEGER := LENGTH(p_delim);
  BEGIN
    WHILE v_delimpos > 0
    LOOP
      v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
      v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
      v_nfields := v_nfields+1;
      v_delimpos := INSTR(v_string, p_delim);
    END LOOP;
    v_table(v_nfields) := v_string;
    p_table := v_table;
    p_nfields := v_nfields;
  END delimstring_to_table;
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ','
    )
  IS
    v_nfields PLS_INTEGER := p_table.COUNT;
    v_string VARCHAR2(32767);
  BEGIN
    FOR i IN 1..v_nfields
    LOOP
      v_string := v_string || p_table(i);
      IF i != v_nfields THEN
        v_string := v_string || p_delim;
      END IF;
    END LOOP;
    p_delimstring := v_string;
  END table_to_delimstring;
END parse;
/

使用例子:

DECLARE
  v_Tab     Parse.Varchar2_Table;
  v_Nfields INTEGER;
  v_String  VARCHAR2(1000) := '1000,Smith,John,13-May-1970';
BEGIN
  Parse.Delimstring_To_Table(v_String, v_Tab, v_Nfields);
  FOR i IN 1 .. v_Nfields LOOP
    Dbms_Output.Put_Line('Field(' || i || ') = ' || v_Tab(i));
  END LOOP;
END;

 

 DBMS_UTILITY.GET_DEPENDENCY的用法:

查询出所有和输入对象有关联的对象

函数说明:

procedure get_dependency (type   IN VARCHAR2,
                          schema IN VARCHAR2,
                          name   IN VARCHAR2);
 -- This procedure will show all the dependencies on the object passed in.
 -- The inputs are
 --   type: The type of the object, for example if the object is a table
 --         give the type as 'TABLE'.
 --   schema: The schema name of the object.
 --   name: The name of the object.

 测试:注意该过程没有输出参数:只是在后台打印出和它相关的对象。

BEGIN
  DBMS_UTILITY.GET_DEPENDENCY('TABLE','SCOTT','EMP');
END;

 

DBMS_UTILITY.COMPILE_SCHEMA的使用:
编译指定schema的所有的过程、函数、包、触发器

  procedure compile_schema(schema varchar2, compile_all boolean default TRUE,
                           reuse_settings boolean default FALSE);
  --  Compile all procedures, functions, packages and triggers in the specified
  --  schema.  After calling this procedure you should select from view
  --  ALL_OBJECTS for items with status of 'INVALID' to see if all objects
  --  were successfully compiled.  You may use the command "SHOW ERRORS
  --  <type> <schema>.<name>" to see the errors assocated with 'INVALID'
  --  objects.
  --  Input arguments:
  --    schema
  --      Name of the schema.
  --    compile_all
  --      This is a boolean flag that indicates whether we should compile all
  --      schema objects or not, regardless of whether the object is currently
  --      flagged as valid or not. The default is to support the previous
  --      compile_schema() behaviour and compile ALL objects.
  --    reuse_settings
  --      This is a boolean flag that indicates whether the session settings in
  --      the objects should be reused, or whether the current session settings
  --      should be picked up instead.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this schema.
  --    ORA-20001: Cannot recompile SYS objects.

测试:编译所有用户下的对象:(不推荐使用该方法-因为编译所有对象下的用户，会耗费大量的时间):

DECLARE
  TYPE Name2varchar2 IS TABLE OF VARCHAR2(100);
  v_Name2varchar2 Name2varchar2;
BEGIN
  SELECT Au.Username BULK COLLECT INTO v_Name2varchar2 FROM All_Users Au;
  FOR i IN v_Name2varchar2.First .. v_Name2varchar2.Last LOOP
    --Dbms_Utility.Compile_Schema(v_Name2varchar2(i));
  END LOOP;
END;

可以使用该语句限制用户数量:

SELECT Username
  FROM All_Users
 WHERE Lower(Username) IN ('a', 'b''c')
 ORDER BY Decode(Lower(Username), 'apps', Chr(1), Lower(Username));

也可以自己指定一系列的用户:

DECLARE
  TYPE NAME2VARCHAR2 IS TABLE OF VARCHAR2(100);
  --初始化指定用户名
  V_NAME2VARCHAR2 NAME2VARCHAR2 := NAME2VARCHAR2('SCOTT', 'DONGYJ');
BEGIN
  FOR I IN V_NAME2VARCHAR2.FIRST .. V_NAME2VARCHAR2.LAST LOOP
    DBMS_UTILITY.COMPILE_SCHEMA(V_NAME2VARCHAR2(I));
  END LOOP;
END;