本文介绍DBMS_UTILITY包的使用。

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返回当前调用堆栈的格式化内容。
FORMAT_ERROR_STACK函数TEXT返回异常抛出位置的调用堆栈的格式化内容。
FORMAT_ERROR_BACKTRACE函数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 PostgreSQL版(兼容Oracle)的DBMS_UTILITY实施是部分实施。仅支持上表中列出的函数和存储过程。

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

公共变量数据类型说明
inv_error_on_restrictionsPLS_INTEGER1用于INVALIDATE存储过程。
lname_arrayTABLE-用于长名称列表。
uncl_arrayTABLE-用于用户和名称列表。

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
    参数描述
    methodmethod决定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逗号分隔的名称列表。
tablentab中的条目数。
tab包含list中的各个名称的表。
LNAME_ARRAYDBMS_UTILITY LNAME_ARRAY(详情请参见LNAME_ARRAY)。
UNCL_ARRAYDBMS_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('polardb.dept, polardb.emp, polardb.jobhist')

polardb.dept
polardb.emp
polardb.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: polardb 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: polardb 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:  polardb-20001: 'parse_string' must be a valid DDL statement

在此情况下,PolarDB PostgreSQL版(兼容Oracle)的行为与Oracle不同,Oracle接受无效的parse_string而不会生成错误。

FORMAT_CALL_STACK

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

DBMS_UTILITY.FORMAT_CALL_STACK return TEXT

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

FORMAT_ERROR_BACKTRACE

FORMAT_ERROR_BACKTRACE函数返回异常抛出位置的调用堆栈的格式化内容。

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE return TEXT

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

FORMAT_ERROR_STACK

FORMAT_ERROR_STACK函数返回异常抛出位置的调用堆栈的格式化内容。

DBMS_UTILITY.FORMAT_ERROR_STACK return TEXT

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

说明 PolarDB PostgreSQL版(兼容Oracle)与Oracle的FORMAT_ERROR_STACK行为有所不同,Oracle的此函数返回的字符串包含SQLCODE和SQLERRM信息;而PolarDB PostgreSQL版(兼容Oracle)的此函数与FORMAT_ERROR_BACKTRACE功能相同。

GET_CPU_TIME

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

参数

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

示例

以下SELECT命令用于检索当前CPU时间,即百分之6.03秒或0.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)

参数

参数描述
typename的对象类型。有效值为INDEX、PACKAGE、PACKAGE BODY、SEQUENCE、TABLE、TRIGGER、TYPE和VIEW。
schemaname所在的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

令牌化名称polardb.list_emp:

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

name   : polardb.list_emp
a      : polardb
b      : LIST_EMP
c      :
dblink :
nextpos: 12

令牌化名称 "polardb"."Emp_Admin".update_emp_sal:

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

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

令牌化名称polardb.emp@polardb_dblink:

BEGIN
    name_tokenize('polardb.emp@polardb_dblink');
END;

name   : polardb.emp@polardb_dblink
a      : polardb
b      : EMP
c      :
dblink : polardb_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_ARRAYDBMS_UTILITY LNAME_ARRAY(详情请参见LNAME_ARRAY)。
UNCL_ARRAYDBMS_UTILITY UNCL_ARRAY(详情请参见UNCL_ARRAY)。
tablenlist中的条目数。
listtab中名称的逗号分隔的列表。

示例

以下示例先使用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('polardb.dept, polardb.emp, polardb.jobhist')

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