sys.dbms_utility包的使用:

最新推荐文章于 2024-04-01 08:58:11 发布

转载最新推荐文章于 2024-04-01 08:58:11 发布 · 567 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/caroline/archive/2012/05/16/2502757.html

文章标签：

#数据库

本文介绍了Oracle数据库中的多种实用技巧，包括获取毫秒级时间的方法、使用set timing命令测量SQL执行时间、处理逗号分隔字符串的转换、依赖项查询及批量编译模式对象等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考文档: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 :可以用来估算一个函数的执行所花费的时间

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

 1 DECLARE
 2   Time_Before BINARY_INTEGER;
 3   Time_After  BINARY_INTEGER;
 4   TYPE t IS TABLE OF VARCHAR2(100);
 5   T1 t;
 6 BEGIN
 7   Time_Before := Dbms_Utility.Get_Time;
 8   SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
 9   Time_After := Dbms_Utility.Get_Time;
10   Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
11   Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
12 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串的拆分。

 1 DECLARE
 2   t_Vararray    Dbms_Utility.Lname_Array;
 3   Vc_Stringlist VARCHAR2(4000);
 4   n_Idx         BINARY_INTEGER;
 5 BEGIN
 6   --comma to table
 7   Vc_Stringlist := 'dkf,dddl,fewe,klkj';
 8 
 9   Dbms_Utility.Comma_To_Table(Vc_Stringlist, n_Idx, t_Vararray);
10   Dbms_Output.Put_Line('Total Num : ' || To_Char(n_Idx));
11 
12   FOR i IN 1 .. n_Idx LOOP
13     Dbms_Output.Put_Line(t_Vararray(i));
14     t_Vararray(i) := '[' || t_Vararray(i) || ']';
15   END LOOP;
16 
17   --table to comma  
18   Dbms_Utility.Table_To_Comma(t_Vararray, n_Idx, Vc_Stringlist);
19   Dbms_Output.Put_Line('');
20   Dbms_Output.Put_Line('' || Vc_Stringlist);
21 
22 END;

 1 DECLARE
 2   t_Vararray    Dbms_Utility.Lname_Array;
 3   Vc_Stringlist VARCHAR2(4000);
 4   n_Idx         BINARY_INTEGER;
 5   Time_Before   BINARY_INTEGER;
 6   Time_After    BINARY_INTEGER;
 7   TYPE t IS TABLE OF VARCHAR2(100);
 8   T1 t;
 9 BEGIN
10 
11   Time_Before := Dbms_Utility.Get_Time;
12   SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
13   --comma to table
14   Vc_Stringlist := 'dkf111,dddl,fewe,klkj';
15 
16   Dbms_Utility.Comma_To_Table(Vc_Stringlist, n_Idx, t_Vararray);
17   Dbms_Output.Put_Line('Total Num : ' || To_Char(n_Idx));
18 
19   FOR i IN 1 .. n_Idx LOOP
20     Dbms_Output.Put_Line(t_Vararray(i));
21     t_Vararray(i) := '[' || t_Vararray(i) || ']';
22   END LOOP;
23 
24   --table to comma  
25   Dbms_Utility.Table_To_Comma(t_Vararray, n_Idx, Vc_Stringlist);
26   Dbms_Output.Put_Line('');
27   Dbms_Output.Put_Line('' || Vc_Stringlist);
28   Time_After := Dbms_Utility.Get_Time;
29   Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
30   Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
31 END;
32 
33 DECLARE
34   Time_Before BINARY_INTEGER;
35   Time_After  BINARY_INTEGER;
36   i_Idstr     VARCHAR2(32767) := '1,2,3,4';
37   TYPE t IS TABLE OF VARCHAR2(100);
38   T1 t;
39 BEGIN
40   Time_Before := Dbms_Utility.Get_Time;
41   SELECT o.Object_Name BULK COLLECT INTO T1 FROM All_Objects o;
42   FOR Cr IN (SELECT Substring FROM TABLE(Splitstr(i_Idstr, ','))) LOOP
43     Dbms_Output.Put_Line(Cr.Substring);
44   END LOOP;
45   Time_After := Dbms_Utility.Get_Time;
46   Dbms_Output.Put_Line('执行的毫秒数:' || (Time_After - Time_Before));
47   Dbms_Output.Put_Line('执行的秒数:' || ((Time_After - Time_Before)) / 100);
48 END;

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

 1 CREATE OR REPLACE PACKAGE parse AS
 2   /*
 3   || Package of utility procedures for parsing delimited or fixed position strings into tables
 4   || of individual values, and vice versa.
 5   */
 6   TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
 7   PROCEDURE delimstring_to_table
 8     ( p_delimstring IN VARCHAR2
 9     , p_table OUT varchar2_table
10     , p_nfields OUT INTEGER
11     , p_delim IN VARCHAR2 DEFAULT ','
12     );
13   PROCEDURE table_to_delimstring
14     ( p_table IN varchar2_table
15     , p_delimstring OUT VARCHAR2
16     , p_delim IN VARCHAR2 DEFAULT ','
17     );
18 END parse;
19 /
20 CREATE OR REPLACE PACKAGE BODY parse AS
21   PROCEDURE delimstring_to_table
22     ( p_delimstring IN VARCHAR2
23     , p_table OUT varchar2_table
24     , p_nfields OUT INTEGER
25     , p_delim IN VARCHAR2 DEFAULT ','
26     )
27   IS
28     v_string VARCHAR2(32767) := p_delimstring;
29     v_nfields PLS_INTEGER := 1;
30     v_table varchar2_table;
31     v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
32     v_delimlen PLS_INTEGER := LENGTH(p_delim);
33   BEGIN
34     WHILE v_delimpos > 0
35     LOOP
36       v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
37       v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
38       v_nfields := v_nfields+1;
39       v_delimpos := INSTR(v_string, p_delim);
40     END LOOP;
41     v_table(v_nfields) := v_string;
42     p_table := v_table;
43     p_nfields := v_nfields;
44   END delimstring_to_table;
45   PROCEDURE table_to_delimstring
46     ( p_table IN varchar2_table
47     , p_delimstring OUT VARCHAR2
48     , p_delim IN VARCHAR2 DEFAULT ','
49     )
50   IS
51     v_nfields PLS_INTEGER := p_table.COUNT;
52     v_string VARCHAR2(32767);
53   BEGIN
54     FOR i IN 1..v_nfields
55     LOOP
56       v_string := v_string || p_table(i);
57       IF i != v_nfields THEN
58         v_string := v_string || p_delim;
59       END IF;
60     END LOOP;
61     p_delimstring := v_string;
62   END table_to_delimstring;
63 END parse;
64 /

使用例子:

 1 DECLARE
 2   v_Tab     Parse.Varchar2_Table;
 3   v_Nfields INTEGER;
 4   v_String  VARCHAR2(1000) := '1000,Smith,John,13-May-1970';
 5 BEGIN
 6   Parse.Delimstring_To_Table(v_String, v_Tab, v_Nfields);
 7   FOR i IN 1 .. v_Nfields LOOP
 8     Dbms_Output.Put_Line('Field(' || i || ') = ' || v_Tab(i));
 9   END LOOP;
10 END;

DBMS_UTILITY.GET_DEPENDENCY的用法:

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

函数说明:

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

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

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

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

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

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

1 DECLARE
2   TYPE Name2varchar2 IS TABLE OF VARCHAR2(100);
3   v_Name2varchar2 Name2varchar2;
4 BEGIN
5   SELECT Au.Username BULK COLLECT INTO v_Name2varchar2 FROM All_Users Au;
6   FOR i IN v_Name2varchar2.First .. v_Name2varchar2.Last LOOP
7     --Dbms_Utility.Compile_Schema(v_Name2varchar2(i));
8   END LOOP;
9 END;

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

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

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

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