logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

DBMS_UTILITY

功能描述

DBMS_UTILITY包提供了很多不同的子程序工具。该内置包包含以下子程序：

子程序 描述
COMMA_TO_TABLE 用于将逗号间隔的字符串转化成数组类型。
TABLE_TO_COMMA 用于将数组类型转换成逗号间隔的字符串。
EXEC_DDL_STATEMENT 用于执行DDL语句。
FORMAT_CALL_STACK 用于格式化调用堆栈。该内置函数返回一个格式化的字符串，它显示了执行调用堆栈直至当前函数调用处的所有过程或者函数的调用顺序。该函数可在存储过程、函数或包中调用，并且以可读格式返回当前调用堆栈。
GET_HASH_VALUE 用于获得在该范围内字符串的hash值。
GET_TIME 用于记录当前时刻的时间（单位是百分之一秒）。
FORMAT_ERROR_STACK 返回与当前错误（SQLCODE的返回值)所关联的错误堆栈。
FORMAT_ERROR_BACKTRACE 用于显示异常引发点的调用堆栈。即使子程序是从外部作用域的异常处理程序调用，也可以显示。

注意事项

  • LNAME_ARRAY、UNCL_ARRAY、NAME_ARRAY类型可以用来定义变量，存放数组数据，配合函数COMMA_TO_TABLE和TABLE_TO_COMMA的出入参。它们对应的类型分别为：

    • DBMS_UTILITY.LNAME_ARRAY：对应varchar2[4000]。
    • DBMS_UTILITY.NAME_ARRAY：对应varchar2[30]。
    • DBMS_UTILITY.UNCL_ARRAY：对应varchar2[227]。

  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE和DBMS_UTILITY.FORMAT_ERROR_STACK函数仅在plpgsql的错误捕获下有实际输出，否则为空。

  • FORMAT_ERROR_BACKTRACE和FORMAT_ERROR_STACK函数功能仅在数据库兼容模式为Oracle时能够使用（即创建DB时DBCOMPATIBILITY='A'），在其他数据库兼容模式下不能使用该特性。

  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE和DBMS_UTILITY.FORMAT_ERROR_STACK函数支持版本：

    • V2.2 Build 10(Patch No.10)及以上补丁版本。
    • V2.2 Build 13及以上版本。

内置包子程序

COMMA_TO_TABLE

语法格式

DBMS_UTILITY.COMMA_TO_TABLE(
list     in VARCHAR2,
tablen   OUT BINARY_INTEGER,
tab      OUT varchar2[]);

参数说明

  • list

    VARCHAR2类型，要分隔的字符串。

  • tablen

    BINARY_INTEGER类型，分隔后的数组长度。

  • tab

    VARCHAR2[]类型，分隔后的数组。

注意事项

  • list必须是非空的逗号间隔符类型。字符串里面双引号中的逗号不计算在内。

  • 逗号分割列表中的条目不能包含多字节字符，例如连字符（-）。

  • tab里的值应为原始列表中剪裁出来的值，不能做任何转换。

示例

直接调用COMMA_TO_TABLE函数将逗号间隔的字符串转化成数组类型。

select dbms_utility.comma_to_table('a,b,c,d'::text);

返回结果为：

 comma_to_table
-----------------
 (4,"{a,b,c,d}")
(1 row)

TABLE_TO_COMMA

语法格式

DBMS_UTILITY.TABLE_TO_COMMA(
tab     in varchar2[],
tablen  OUT BINARY_INTEGER,
list    OUT VARCHAR2);

参数说明

  • tab

    VARCHAR2[]类型，输入的数组。

  • tablen

    BINARY_INTEGER类型，输入的数组长度。

  • list

    VARCHAR2类型，形成的字符串。

示例

直接调用TABLE_TO_COMMA函数将数组类型转换成逗号间隔的字符串。

select dbms_utility.table_to_comma(array['a','b','c','d']);

返回结果为：

 table_to_comma
----------------
 (4,"a,b,c,d")
(1 row)

GET_TIME

语法格式

DBMS_UTILITY.GET_TIME
return number;

注意事项

  • 如果需要换算为秒，需要除以100。
  • 该函数没有传入参数，返回值为number类型。

示例

1、创建存储过程调用GET_TIME获取当前时间值，用于计算操作的执行之间。

CREATE OR REPLACE FUNCTION get_runtime() returns number as $$
DECLARE
     start_time   NUMBER;
     finish_time  NUMBER;
     elapsed_time NUMBER;
  BEGIN
     start_time := DBMS_UTILITY.get_time();
     perform pg_sleep(1);
    perform pg_sleep(1);
    perform pg_sleep(1);
    perform pg_sleep(1);
    perform pg_sleep(1);
     finish_time := DBMS_UTILITY.get_time();
     elapsed_time := finish_time  - start_time;
     return elapsed_time;
  END
$$ language plpgsql;

2、调用函数。

select get_runtime();

返回结果为：

 get_runtime
-------------
         501
(1 row)

GET_HASH_VALUE

语法格式

dbms_utility.get_hash_value(
hash_name varchar,
hash_base integer,
hash_size integer);

参数说明

  • hash_name

    需要被转化为hash值的字符串。

  • hash_base

    hash起始位置。

  • hash_size

    hash大小，不能为0，若为正值表示从hash起始位置到加上hash大小的范围，为负值表示从integer范围排除从hash起始位置减去hash大小绝对值的范围。

示例

直接调用GET_HASH_VALUE函数。

select dbms_utility.get_hash_value('afeaw',0,1000);

返回结果为：

 get_hash_value
----------------
             84
(1 row)

EXEC_DDL_STATEMENT

语法格式

DBMS_UTILITY.EXEC_DDL_STATEMENT (
   parse_string IN VARCHAR2);

参数说明

parse_string

要被执行的DDL语句。

示例

1、创建存储过程调用EXEC_DDL_STATEMENT 函数。

CREATE OR REPLACE FUNCTION checkHexCallStack() returns text 
as $$
DECLARE
stack text;
BEGIN
select * INTO stack from dbms_utility.format_call_stack('o');
return stack;
END;
$$ LANGUAGE plpgsql;
begin
Dbms_utility.exec_ddl_statement('create table test_eds(col int);');
end;
/

2、查询test_eds表的属性。

\dti test_eds

返回结果如下，表示函数调用成功：

                                       List of relations
 Schema |   Name  | Type |  Owner |Table|                    Storage
--------+----------+-------+--------+-------+--------------------------------------------
 public | test_eds | table|vastbase|     | {orientation=row,compression=no,fillfactor=80}
(1 row)

FORMAT_CALL_STACK

语法格式

DBMS_UTILITY.FORMAT_CALL_STACK 
  RETURN VARCHAR2;

示例

示例请参考EXEC_DDL_STATEMENT示例。

FORMAT_ERROR_STACK

语法格式

DBMS_UTILITY.FORMAT_ERROR_STACK()
RETURN VARCHAR2;

示例

1、开启GUC参数serveroutput，允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、在匿名块中使用DBMS_UTILITY.FORMAT_ERROR_STACK函数。

DECLARE
v_num NUMBER(11,10) := 1;
BEGIN
BEGIN
v_num := 55;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error line=' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('Error Message=' || DBMS_UTILITY.FORMAT_ERROR_STACK);
v_num := v_num / 0;
END;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Error line=' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('Error Message=' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

返回结果显示为：

Error line=VB-P0001: at "inline_code_block", line 5

Error Message=VB-22003: numeric field overflow

Error line=VB-P0001: at "inline_code_block", line 10

Error Message=VB-22012: division by zero

ANONYMOUS BLOCK EXECUTE

FORMAT_ERROR_BACKTRACE

语法格式

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
RETURN VARCHAR2;

示例

1、创建存储过程，其中调用了DBMS_UTILITY.FORMAT_ERROR_BACKTRACE函数。

Create Or Replace Package pkg_public_back As
Procedure p_test;
End pkg_public_back;
/

Create Or Replace Package Body pkg_public_back As
Procedure p_test3 As
a Number;
Begin
a := 0 / 0;
Exception
When Others Then
Raise;
End p_test3;

Procedure p_test2 As
Begin
p_test3;
Exception
When Others Then
Raise;
End p_test2;

Procedure p_test As
Begin
p_test2;
Exception
When Others Then
dbms_output.put_line('----format_call_stack----');
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line('-------------------------');
dbms_output.put_line('----format_error_stack----');
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line('-------------------------');
dbms_output.put_line('----format_error_backtrace----');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line('-------------------------');
End p_test;

End pkg_public_back;
/

2、开启GUC参数serveroutput，允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用存储过程。

call pkg_public_back.p_test();

返回结果为：

----format_call_stack----
----- PL/pgSQL Call Stack -----
  object     line  object
  handle   number  name
       0           function anonymous object
    4e5b        7  function public.pkg_public_back.p_test()
-------------------------
----format_error_stack----
VB-22012: division by zero
VB-P0001: at "public.pkg_public_back.p_test3()", line 4
VB-P0001: at "public.pkg_public_back.p_test2()", line 3

-------------------------
----format_error_backtrace----
VB-P0001: at "public.pkg_public_back.p_test3()", line 4
VB-P0001: at "public.pkg_public_back.p_test2()", line 3
VB-P0001: at "public.pkg_public_back.p_test()", line 3

-------------------------
 p_test
--------

(1 row)