DBMS_UTILITY_内置包使用_兼容Oracle数据库开发指南_云数据库PolarDB_阿里云技术文档

DBMS_UTILITY 包支持以下各种实用程序：


函数/存储过程	类型	返回类型	说明
`ANALYZE_DATABASE(method [, estimate_rows [, estimate_percent [, method_opt ]]])`	存储过程	n/a	分析数据库表。
`ANALYZE_PART_OBJECT(schema, object_name [, object_type [, command_type [, command_opt [, sample_clause ]]]])`	存储过程	n/a	分析已分区表。
`ANALYZE_SCHEMA(schema, method [, estimate_rows [, estimate_percent [, method_opt ]]])`	存储过程	n/a	分析 schema 表。
`CANONICALIZE(name, canon_name OUT, canon_len)`	存储过程	n/a	规范化字符串。例如，去掉空格。
`COMMA_TO_TABLE(list, tablen OUT, tab OUT)`	存储过程	n/a	将逗号分隔的名称列表转换为名称表。
`DB_VERSION(version OUT, compatibility OUT)`	存储过程	n/a	获取数据库版本。
`EXEC_DDL_STATEMENT(parse_string)`	存储过程	n/a	执行 DDL 语句。
`FORMAT_CALL_STACK`	函数	TEXT	设置当前调用堆栈的格式。
`GET_CPU_TIME`	函数	NUMBER	获取当前 CPU 时间。
`GET_DEPENDENCY(type, schema, name)`	存储过程	n/a	获取依赖于给定对象的对象。
`GET_HASH_VALUE(name, base, hash_size)`	函数	NUMBER	计算散列值。
`GET_PARAMETER_VALUE(parnam, intval OUT, strval OUT)`	存储过程	BINARY_INTEGER	获取数据库初始化参数设置。
`GET_TIME`	函数	NUMBER	获取当前时间。
`NAME_TOKENIZE(name, a OUT, b OUT, c OUT, dblink OUT, nextpos OUT)`	存储过程	n/a	将给定名称解析为其组成部分。
`TABLE_TO_COMMA(tab, tablen OUT, list OUT)`	存储过程	n/a	将名称表转换为逗号分隔的列表。

与Oracle版本相比，POLARDB for Oracle的DBMS_UTILITY实施是部分实施。仅支持上表中列出的函数和存储过程。

下表列出了DBMS_UTILITY包中可用的公共变量。


公共变量	数据类型	值	说明
inv_error_on_restrictions	PLS_INTEGER	1	由 INVALIDATE 存储过程使用。
lname_array	TABLE	-	用于长名称列表。
uncl_array	TABLE	-	用于用户和名称列表。

LNAME_ARRAY

LNAME_ARRAY用于存储长名称（包括完全限定名称）的列表。

TYPE lname_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

UNCL_ARRAY

UNCL_ARRAY 用于存储用户和名称列表。

TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

ANALYZE_DATABASE、ANALYZE SCHEMA 和 ANALYZE PART_OBJECT

可使用ANALYZE_DATABASE()、ANALYZE_SCHEMA()和ANALYZE_PART_OBJECT()存储过程收集数据库中表的统计信息。在执行ANALYZE 语句时，Postgres会对表中的数据进行采样并在pg_statistics系统表中记录分布统计信息。

ANALYZE_DATABASE、ANALYZE_SCHEMA和ANALYZE_PART_OBJECT之间的主要区别在于处理的表的数量：

ANALYZE_DATABASE 分析当前数据库中所有 schema 中的所有表。
ANALYZE_SCHEMA 分析给定 schema（位于当前数据库中）中的所有表。
ANALYZE_PART_OBJECT 分析单个表。

ANALYZE命令的语法为：

ANALYZE_DATABASE(method VARCHAR2 [, estimate_rows NUMBER
  [, estimate_percent NUMBER [, method_opt VARCHAR2 ]]])

ANALYZE_SCHEMA(schema VARCHAR2, method VARCHAR2
  [, estimate_rows NUMBER [, estimate_percent NUMBER
  [, method_opt VARCHAR2 ]]])

ANALYZE_PART_OBJECT(schema VARCHAR2, object_name VARCHAR2
  [, object_type CHAR [, command_type CHAR
  [, command_opt VARCHAR2 [, sample_clause ]]]])

参数

ANALYZE_DATABASE 和 ANALYZE_SCHEMA


参数	描述
method	method 决定 ANALYZE 存储过程是填充 pg_statistics 表还是从 pg_statistics 表中删除条目。如果指定 DELETE 方法，则 ANALYZE 存储过程将从 pg_statistics 中删除相关行。如果指定 COMPUTE 或 ESTIMATE 方法，则 ANALYZE 存储过程将分析一个表（或多个表）并在 pg_statistics 中记录分布信息。COMPUTE 和 ESTIMATE 之间没有区别；这两种方法都执行 Postgres ANALYZE 语句。所有其他参数会进行验证，然后被忽略。
estimate_rows	估计统计信息所依据的行数。如果方法为 ESTIMATE，则必须指定 estimate_rows 或 estimate_percent。此参数被忽略，但为了实现兼容性而提供。
estimate_percent	估计统计信息所依据的行百分比。如果方法为 ESTIMATE，则必须指定 estimate_rows 或 estimate_percent。此参数被忽略，但为了实现兼容性而提供。
method_opt	要分析的对象类型。以下项的任意组合： `[ FOR TABLE ] [ FOR ALL [ INDEXED ] COLUMNS ] [ SIZE n ] [ FOR ALL INDEXES ]` 此参数被忽略，但为了实现兼容性而提供。

ANALYZE_PART_OBJECT


参数	描述
schema	要分析其对象的 schema 的名称。
object_name	要分析的已分区对象的名称。
object_type	要分析的对象的类型。有效值为：T – 表，I – 索引。此参数被忽略，但为了实现兼容性而提供。
command_type	要执行的分析功能的类型。有效值为： E - 根据 sample_clause 子句中的指定行数或行百分比收集估计统计信息； C - 计算精确的统计信息； V – 验证分区的结构和完整性。此参数被忽略，但为了实现兼容性而提供。
command_opt	对于 command_type C 或 E，可以是以下项的任意组合： `[ FOR TABLE ] [ FOR ALL COLUMNS ] [ FOR ALL LOCAL INDEXES ]` 对于 command_type V，可以是 CASCADE（如果 object_type 为 T）。此参数被忽略，但为了实现兼容性而提供。
sample_clause	如果 command_type 为 E，则包含以下子句以指定估计值所依据的行数或行百分比。 `SAMPLE n { ROWS \| PERCENT }` 此参数被忽略，但为了实现兼容性而提供。

CANONICALIZE

CANONICALIZE存储过程对输入字符串执行以下操作：

如果字符串未包含在双引号中，则验证它是否使用合法标识符的字符。如果未使用，则将引发异常。如果字符串包含在双引号中，则允许使用所有字符。
如果字符串未包含在双引号中且不包含句点，则将所有字母字符设置为大写形式并删除前导空格和尾随空格。
如果字符串包含在双引号中且不包含句点，则删除双引号。
如果字符串包含句点，并且字符串的任何部分均未包含在双引号中，则将字符串的每个部分设置为大写形式并用双引号将每个部分引起来。
如果字符串包含句点且字符串的部分包含在双引号中，则按原样返回包含在双引号中的部分（包括双引号），并将未包含在双引号中的部分以带双引号的大写形式返回。

CANONICALIZE(name VARCHAR2, canon_name OUT VARCHAR2,
  canon_len BINARY_INTEGER)

参数


参数	描述
name	要规范化的字符串。
canon_name	规范化的字符串。
canon_len	要规范化的 name 中的字节数（从第一个字符开始）。

示例

以下过程对其输入参数应用 CANONICALIZE 存储过程并显示结果。

CREATE OR REPLACE PROCEDURE canonicalize (
    p_name      VARCHAR2,
    p_length    BINARY_INTEGER DEFAULT 30
)
IS
    v_canon     VARCHAR2(100);
BEGIN
    DBMS_UTILITY.CANONICALIZE(p_name,v_canon,p_length);
    DBMS_OUTPUT.PUT_LINE('Canonicalized name ==>' || v_canon || '<==');
    DBMS_OUTPUT.PUT_LINE('Length: ' || LENGTH(v_canon));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

EXEC canonicalize('Identifier')
Canonicalized name ==>IDENTIFIER<==
Length: 10

EXEC canonicalize('"Identifier"')
Canonicalized name ==>Identifier<==
Length: 10

EXEC canonicalize('"_+142%"')
Canonicalized name ==>_+142%<==
Length: 6

EXEC canonicalize('abc.def.ghi')
Canonicalized name ==>"ABC"."DEF"."GHI"<==
Length: 17

EXEC canonicalize('"abc.def.ghi"')
Canonicalized name ==>abc.def.ghi<==
Length: 11

EXEC canonicalize('"abc".def."ghi"')
Canonicalized name ==>"abc"."DEF"."ghi"<==
Length: 17

EXEC canonicalize('"abc.def".ghi')
Canonicalized name ==>"abc.def"."GHI"<==
Length: 15

COMMA_TO_TABLE

COMMA_TO_TABLE存储过程将逗号分隔的名称列表转换为名称表。列表中的每个条目都将成为表条目。必须将名称格式化为有效的标识符。

COMMA_TO_TABLE(list VARCHAR2, tablen OUT BINARY_INTEGER,
  tab OUT { LNAME_ARRAY | UNCL_ARRAY })

参数


参数	描述
list	逗号分隔的名称列表。
tablen	tab 中的条目数。
tab	包含 list 中的各个名称的表。
LNAME_ARRAY	DBMS_UTILITY LNAME_ARRAY（详情请参见LNAME_ARRAY）。
UNCL_ARRAY	DBMS_UTILITY UNCL_ARRAY（详情请参见UNCL_ARRAY）。

示例

以下过程使用 COMMA_TO_TABLE 存储过程将名称列表转换为表。随后将显示表条目。

CREATE OR REPLACE PROCEDURE comma_to_table (
    p_list      VARCHAR2
)
IS
    r_lname     DBMS_UTILITY.LNAME_ARRAY;
    v_length    BINARY_INTEGER;
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list,v_length,r_lname);
    FOR i IN 1..v_length LOOP
        DBMS_OUTPUT.PUT_LINE(r_lname(i));
    END LOOP;
END;

EXEC comma_to_table('edb.dept, edb.emp, edb.jobhist')

edb.dept
edb.emp
edb.jobhist

DB_VERSION

DB_VERSION存储过程返回数据库的版本号。

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

参数


参数	描述
version	数据库版本号。
compatibility	数据库的兼容性设置（其含义由实施定义）。

示例

以下匿名块显示数据库版本信息。

DECLARE
    v_version       VARCHAR2(150);
    v_compat        VARCHAR2(150);
BEGIN
    DBMS_UTILITY.DB_VERSION(v_version,v_compat);
    DBMS_OUTPUT.PUT_LINE('Version: '       || v_version);
    DBMS_OUTPUT.PUT_LINE('Compatibility: ' || v_compat);
END;

Version: EnterpriseDB 10.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 32-bit
Compatibility: EnterpriseDB 10.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.220080704 (Red Hat 4.1.2-48), 32-bit

EXEC_DDL_STATEMENT

可使用 EXEC_DDL_STATEMENT 执行 DDL 命令。

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

参数


参数	描述
parse_string	要执行的 DDL 命令。

示例

以下匿名块创建 job 表。

BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
        'CREATE TABLE job (' ||
          'jobno NUMBER(3),' ||
          'jname VARCHAR2(9))'
    );
END;

如果 parse_string 未包含有效的 DDL 语句，则 Advanced Server 会返回以下错误：

#  exec dbms_utility.exec_ddl_statement('select rownum from dual');
ERROR:  EDB-20001: 'parse_string' must be a valid DDL statement

在此情况下，POLARDB for Oracle的行为与 Oracle 不同；Oracle 接受无效的 parse_string 而不会生成错误。

FORMAT_CALL_STACK

FORMAT_CALL_STACK 函数返回当前调用堆栈的格式化内容。

DBMS_UTILITY.FORMAT_CALL_STACKreturn VARCHAR2

可以在存储过程、函数或包中，使用此函数以可读格式返回当前调用堆栈。此功能对于实现调试目的非常有用。

GET_CPU_TIME

GET_CPU_TIME 函数返回任意时间点的 CPU 时间（以百分之一秒为单位）。

参数


参数	描述
cputime	CPU 时间的百分之一秒数。

示例

以下 SELECT 命令检索当前 CPU 时间，即百分之 6.03 秒或 .0603 秒。

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

get_cpu_time
--------------
          603

GET_DEPENDENCY

可使用 GET_DEPENDENCY 存储过程列出依赖于指定对象的对象。GET_DEPENDENCY 不显示函数或存储过程的依赖项。

GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2,
  name VARCHAR2)

参数


参数	描述
type	name 的对象类型。有效值为 INDEX、PACKAGE、PACKAGE BODY、SEQUENCE、TABLE、TRIGGER、TYPE 和 VIEW。
schema	name 所在的 schema 的名称。
name	要获取其依赖项的对象的名称。

示例

以下匿名块查找 EMP 表的依赖项。

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

DEPENDENCIES ON public.EMP
------------------------------------------------------------------
*TABLE public.EMP()
*   CONSTRAINT c public.emp()
*   CONSTRAINT f public.emp()
*   CONSTRAINT p public.emp()
*   TYPE public.emp()
*   CONSTRAINT c public.emp()
*   CONSTRAINT f public.jobhist()
*   VIEW .empname_view()

GET_HASH_VALUE

可使用 GET_HASH_VALUE 函数计算给定字符串的散列值。

hash NUMBER GET_HASH_VALUE(name VARCHAR2, base NUMBER,
  hash_size NUMBER)

参数


参数	描述
name	要计算其散列值的字符串。
base	要生成散列值的起始值。
hash_size	所需散列表的散列值的数量。
hash	生成的散列值。

示例

以下匿名块使用 emp 表的 ename 列创建散列值表，然后显示键以及散列值。散列值从 100 开始，最多 1024 个不同的值。

DECLARE
    v_hash          NUMBER;
    TYPE hash_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    r_hash          HASH_TAB;
    CURSOR emp_cur IS SELECT ename FROM emp;
BEGIN
    FOR r_emp IN emp_cur LOOP
        r_hash(r_emp.ename) :=
            DBMS_UTILITY.GET_HASH_VALUE(r_emp.ename,100,1024);
    END LOOP;
    FOR r_emp IN emp_cur LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(r_emp.ename,10) || ' ' ||
            r_hash(r_emp.ename));
    END LOOP;
END;

SMITH      377
ALLEN      740
WARD       718
JONES      131
MARTIN     176
BLAKE      568
CLARK      621
SCOTT      1097
KING       235
TURNER     850
ADAMS      156
JAMES      942
FORD       775
MILLER     148

GET_PARAMETER_VALUE

可使用 GET_PARAMETER_VALUE 存储过程检索数据库初始化参数设置。

status BINARY_INTEGER GET_PARAMETER_VALUE(parnam VARCHAR2,
  intval OUT INTEGER, strval OUT VARCHAR2)

参数


参数	描述
parnam	要返回其值的参数的名称。pg_settings 系统视图中列出了这些参数。
intval	整数参数的值或 strval 的长度。
strval	字符串参数的值。
status	如果参数值为 INTEGER 或 BOOLEAN，则返回 0。如果参数值为字符串，则返回 1。

示例

以下匿名块显示两个初始化参数的值。

DECLARE
    v_intval        INTEGER;
    v_strval        VARCHAR2(80);
BEGIN
    DBMS_UTILITY.GET_PARAMETER_VALUE('max_fsm_pages', v_intval, v_strval);
    DBMS_OUTPUT.PUT_LINE('max_fsm_pages' || ': ' || v_intval);
    DBMS_UTILITY.GET_PARAMETER_VALUE('client_encoding', v_intval, v_strval);
    DBMS_OUTPUT.PUT_LINE('client_encoding' || ': ' || v_strval);
END;

max_fsm_pages: 72625
client_encoding: SQL_ASCII

GET_TIME

可使用 GET_TIME 函数返回当前时间（以百分之一秒为单位）。

参数


参数	描述
time	从程序启动后经历的百分之一秒数。

示例

以下示例显示对 GET_TIME 函数的调用。

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

 get_time
----------
  1555860

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

 get_time
----------
  1556037

NAME_TOKENIZE

NAME_TOKENIZE 存储过程将名称解析为其组成部分。将未包含在双引号中的名称设置为大写形式。对于带双引号的名称，将删除双引号。

NAME_TOKENIZE(name VARCHAR2, a OUT VARCHAR2,   b OUT VARCHAR2,c OUT VARCHAR2, dblink OUT VARCHAR2,   nextpos OUT BINARY_INTEGER)

参数


参数	描述
name	包含以下格式的名称的字符串： `a[.b[.c]][@dblink ]`
a	返回最左侧组成部分。
b	返回第二个组成部分（如果有）。
c	返回第三个组成部分（如果有）。
dblink	返回 database link 名称。
nextpos	名称中解析的最后一个字符的位置。

示例

以下存储过程用于显示不同名称的 NAME_TOKENIZE 存储过程的返回参数值。

CREATE OR REPLACE PROCEDURE name_tokenize (
    p_name          VARCHAR2
)
IS
    v_a             VARCHAR2(30);
    v_b             VARCHAR2(30);
    v_c             VARCHAR2(30);
    v_dblink        VARCHAR2(30);
    v_nextpos       BINARY_INTEGER;
BEGIN
    DBMS_UTILITY.NAME_TOKENIZE(p_name,v_a,v_b,v_c,v_dblink,v_nextpos);
    DBMS_OUTPUT.PUT_LINE('name   : ' || p_name);
    DBMS_OUTPUT.PUT_LINE('a      : ' || v_a);
    DBMS_OUTPUT.PUT_LINE('b      : ' || v_b);
    DBMS_OUTPUT.PUT_LINE('c      : ' || v_c);
    DBMS_OUTPUT.PUT_LINE('dblink : ' || v_dblink);
    DBMS_OUTPUT.PUT_LINE('nextpos: ' || v_nextpos);
END;

令牌化名称 emp：

BEGIN
    name_tokenize('emp');
END;

name   : emp
a      : EMP
b      :
c      :
dblink :
nextpos: 3

令牌化名称 edb.list_emp：

BEGIN
    name_tokenize('edb.list_emp');
END;

name   : edb.list_emp
a      : EDB
b      : LIST_EMP
c      :
dblink :
nextpos: 12

令牌化名称 "edb"."Emp_Admin".update_emp_sal：

BEGIN
    name_tokenize('"edb"."Emp_Admin".update_emp_sal');
END;

name   : "edb"."Emp_Admin".update_emp_sal
a      : edb
b      : Emp_Admin
c      : UPDATE_EMP_SAL
dblink :
nextpos: 32

令牌化名称 edb.emp@edb_dblink：

BEGIN
    name_tokenize('edb.emp@edb_dblink');
END;

name   : edb.emp@edb_dblink
a      : EDB
b      : EMP
c      :
dblink : EDB_DBLINK
nextpos: 18

TABLE_TO_COMMA

TABLE_TO_COMMA 存储过程将名称表转换为逗号分隔的名称列表。每个表条目均变成一个列表条目。必须将名称格式化为有效的标识符。

TABLE_TO_COMMA(tab { LNAME_ARRAY | UNCL_ARRAY },
  tablen OUT BINARY_INTEGER, list OUT VARCHAR2)

参数


参数	描述
tab	包含名称的表。
LNAME_ARRAY	DBMS_UTILITY LNAME_ARRAY（详情请参见LNAME_ARRAY）。
UNCL_ARRAY	DBMS_UTILITY UNCL_ARRAY（详情请参见UNCL_ARRAY）。
tablen	list 中的条目数。
list	tab 中名称的逗号分隔的列表。

示例

以下示例先使用 COMMA_TO_TABLE 存储过程将逗号分隔的列表转换为表。随后，TABLE_TO_COMMA 存储过程将表转换回显示的逗号分隔的列表。

CREATE OR REPLACE PROCEDURE table_to_comma (
    p_list      VARCHAR2
)
IS
    r_lname     DBMS_UTILITY.LNAME_ARRAY;
    v_length    BINARY_INTEGER;
    v_listlen   BINARY_INTEGER;
    v_list      VARCHAR2(80);
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list,v_length,r_lname);
    DBMS_OUTPUT.PUT_LINE('Table Entries');
    DBMS_OUTPUT.PUT_LINE('-------------');
    FOR i IN 1..v_length LOOP
        DBMS_OUTPUT.PUT_LINE(r_lname(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-------------');
    DBMS_UTILITY.TABLE_TO_COMMA(r_lname,v_listlen,v_list);
    DBMS_OUTPUT.PUT_LINE('Comma-Delimited List: ' || v_list);
END;

EXEC table_to_comma('edb.dept, edb.emp, edb.jobhist')

Table Entries
-------------
edb.dept
edb.emp
edb.jobhist
-------------
Comma-Delimited List: edb.dept, edb.emp, edb.jobhist

DBMS_UTILITY_内置包使用_兼容Oracle数据库开发指南_云数据库PolarDB

DBMS_UTILITY