DBMS_UTILITY包函数用法示例详解-云原生数据库 PolarDB-阿里云

本文介绍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_restrictions	PLS_INTEGER	1	用于INVALIDATE存储过程。
lname_array	TABLE	-	用于长名称列表。
uncl_array	TABLE	-	用于用户和名称列表。

DBMS_UTILITY数据类型

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;

执行如下语句，对输入参数应用CANONICALIZE存储过程并显示结果。
```
EXEC canonicalize('"_+142%"');
```
返回结果如下：
```
Canonicalized name ==>_+142%<==
Length: 6
```

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('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: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)
Compatibility: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)

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:  '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时间（以百分之一秒为单位）。

参数

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

示例

执行如下SELECT语句，用于检索当前CPU时间。

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

返回结果如下，即百分之6.03秒或0.0603秒。

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('client_encoding', v_intval, v_strval);
    DBMS_OUTPUT.PUT_LINE('client_encoding' || ': ' || v_strval);
END;

返回结果如下：

client_encoding: UTF8

GET_TIME

语法说明

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

参数

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

示例

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

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

返回结果如下：

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

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: 16

令牌化名称 "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: 36

令牌化名称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: 26

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('polardb.dept, polardb.emp, polardb.jobhist');

返回结果如下：

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