DBE_UTILITY

接口介绍

高级功能包DBE_UTILITY支持的所有接口请参见表1。

• DBE_UTILITY.FORMAT_ERROR_BACKTRACE

  函数FORMAT_ERROR_BACKTRACE返回在执行过程中出现错误时，出现错误位置的调用堆栈。DBE_UTILITY.FORMAT_ERROR_BACKTRACE函数原型为：

  DBE_UTILITY.FORMAT_ERROR_BACKTRACE()
  RETURN TEXT;

• DBE_UTILITY.FORMAT_ERROR_STACK
  函数FORMAT_ERROR_STACK返回在执行过程中出现错误时，出现错误位置的具体信息。DBE_UTILITY.FORMAT_ERROR_STACK函数原型为：

  DBE_UTILITY.FORMAT_ERROR_STACK()
  RETURN TEXT;

• DBE_UTILITY.FORMAT_CALL_STACK

  函数FORMAT_CALL_STACK设置输出函数调用堆栈。DBE_UTILITY.FORMAT_CALL_STACK函数原型为：

  DBE_UTILITY.FORMAT_CALL_STACK()
  RETURN TEXT;

• DBE_UTILITY.GET_TIME

  函数GET_TIME设置输出时间，通常用于做差，单独的返回值没有意义。DBE_UTILITY.GET_TIME函数原型为：

  DBE_UTILITY.GET_TIME()
  RETURN BIGINT;

示例

-- 示例1
create or replace procedure print_err() as
DECLARE
    a bool;
BEGIN
    a := not_exist;
exception
    when others then
        dbe_output.print_line('err_stack: ' || DBE_UTILITY.FORMAT_ERROR_STACK());
END;
/
CREATE PROCEDURE

call print_err();
-- 预期结果为：
err_stack: 50360452: column "not_exist" does not exist
 print_err 
-----------

(1 row)

-- 清理环境
drop procedure print_err;
DROP PROCEDURE

-- 示例2
create or replace procedure print_err() as
DECLARE
    a bool;
BEGIN
    a := not_exist;
exception
    when others then
        dbe_output.print_line('backtrace: ' || DBE_UTILITY.FORMAT_ERROR_BACKTRACE());
END;
/
CREATE PROCEDURE

call print_err();
-- 预期结果为：
backtrace: 50360452: PL/pgSQL function print_err() line 5 at assignment

 print_err 
-----------

(1 row)

-- 清理环境
drop procedure print_err;
DROP PROCEDURE

-- 示例3
create or replace procedure print_err() as
DECLARE
    a bool;
BEGIN
    a := not_exist;
exception
    when others then
        dbe_output.print_line('call_stack: ');
        dbe_output.print_line(DBE_UTILITY.FORMAT_CALL_STACK());
END;
/
CREATE PROCEDURE

call print_err();
-- 预期结果为：
call_stack: 
      3  dbe_utility.format_call_stack()
      9  print_err()

 print_err 
-----------

(1 row)

-- 清理环境
drop procedure print_err;
DROP PROCEDURE

-- 示例4
CREATE OR REPLACE PROCEDURE test_get_time1() 
AS
declare
    start_time  bigint;
    end_time  bigint;
BEGIN
    start_time:= dbe_utility.get_time ();
    pg_sleep(1);
    end_time:=dbe_utility.get_time ();
    dbe_output.print_line(end_time - start_time); 
END;
/
CREATE PROCEDURE

call test_get_time1();
-- 预期结果为：
101
 test_get_time1 
----------------

(1 row)

-- 清理环境
drop PROCEDURE test_get_time1;
DROP PROCEDURE

-- 示例5
-- 给表名字符串做规范
declare
    cname varchar2(50);
begin
    dbe_utility.canonicalize('seg1', cname, 50);
    dbe_output.put_line(cname);
end;
/
-- 预期结果为：
SEG1
ANONYMOUS BLOCK EXECUTE

-- 示例6
-- 将输入的字符串转换成一个表名的数组  
DECLARE
 tab_list VARCHAR2(100) := 't1,t2';
 len BINARY_INTEGER;
 tab varchar2[];
BEGIN
 dbe_output.put_line('table list is: ' || tab_list);
 dbe_utility.comma_to_table(tab_list, len, tab);
END;
/
-- 预期结果为：
table list is: t1,t2
ANONYMOUS BLOCK EXECUTE

-- 示例7
-- 查看数据库的版本号和兼容性版本号
declare
    v_version varchar2;
begin
    dbe_utility.db_version(v_version);
    v_version:=left(v_version, 8);
    dbe_output.print_line('version:' || v_version); 
end;
/
-- 预期结果为：
version:gaussdb 
ANONYMOUS BLOCK EXECUTE

-- 示例8
-- 查看当前CPU处理时间的测量值
DECLARE
    cputime NUMBER;
BEGIN
    cputime := dbe_utility.get_cpu_time();
    dbe_output.put_line('cpu time：' || cputime);
END;
/
-- 预期结果为（数值并非固定）：
cpu time：70179
ANONYMOUS BLOCK EXECUTE

-- 示例9
-- 获取数据库所在平台字节序的大小端信息
BEGIN
    dbe_output.PUT_LINE(dbe_utility.GET_ENDIANNESS());
END;
/
-- 预期结果为：
2
ANONYMOUS BLOCK EXECUTE

-- 示例10
SET system_function_version TO DEFAULT;
SET
-- 获取一个给定字符串的hash值
DECLARE
    result NUMBER(28);
BEGIN
    result := dbe_utility.get_hash_value('hello',10,10);
    dbe_output.put_line(result);
END;
/
-- 预期结果为：
15
ANONYMOUS BLOCK EXECUTE

-- 示例11
-- 判断当前数据库是否为集群模式
DECLARE
    is_cluster BOOLEAN;
BEGIN
    is_cluster := dbe_utility.IS_CLUSTER_DATABASE();
    dbe_output.put_line('CLUSTER DATABASE: ' || CASE WHEN is_cluster THEN 'TRUE' ELSE 'FALSE' END);
END;
/
-- 预期结果为：
CLUSTER DATABASE: TRUE
ANONYMOUS BLOCK EXECUTE

-- 示例12
-- 获取当前用户环境下的数据库模式名称
DECLARE
    schm varchar2(100);
BEGIN
    schm := dbe_utility.old_current_schema();
    dbe_output.put_line('current schema: ' || schm);
END;
/
-- 预期结果为（结果为当前数据库的模式名，并非固定）：
current schema: public
ANONYMOUS BLOCK EXECUTE

-- 示例13
-- 获取当前用户名称
select dbe_utility.old_current_user() from sys_dummy;
-- 预期结果为（结果为当前数据库的用户名，并非固定）：
 old_current_user 
------------------
 test
(1 row)

-- 示例14
DECLARE
    ddl_str VARCHAR2(255);
BEGIN
    dbe_output.print_line('start to test exec_ddl_statement create table.');
    ddl_str := 'CREATE TABLE test_ddl (COL1 INT)';
    dbe_utility.exec_ddl_statement(ddl_str);
END;
/
-- 预期结果为：
start to test exec_ddl_statement create table.
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'col1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CONTEXT:  SQL statement "CREATE TABLE test_ddl (COL1 INT)"
SQL statement "CALL dbe_utility.exec_ddl_statement(ddl_str)"
PL/pgSQL function inline_code_block line 5 at PERFORM
ANONYMOUS BLOCK EXECUTE

select * from test_ddl;
-- 预期结果为：
 col1 
------
(0 rows)

-- 清理环境
drop table test_ddl;
DROP TABLE

-- 示例15
create table t1 (c1 int primary key, c2 int);
insert into t1 values(1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
create view v1 as select * from t1 where c1 > 1;
create view v2 as select c1 from v1 where c2 > 1;
create view v3 as select * from v2 where c1 > 2;

declare
    in_sql clob := 'select * from public.v3';
    out_sql clob;
begin
    dbe_output.print_line('start to test expand_sql_text_proc v3 expend sql text.');
    dbe_utility.expand_sql_text_proc(in_sql, out_sql);
    dbe_output.print_line(out_sql);
end;
/
-- 预期结果为：
start to test expand_sql_text_proc v3 expend sql text.
SELECT c1 FROM (SELECT v2.c1 FROM (SELECT v1.c1 FROM (SELECT t1.c1, t1.c2 FROM public.t1 WHERE t1.c1 > 1) v1 WHERE v1.c2 > 1) v2 WHERE v2.c1 > 2) v3
ANONYMOUS BLOCK EXECUTE

-- 清理环境
drop table t1 cascade;
-- 预期结果为：
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view v1
drop cascades to view v2
drop cascades to view v3


-- 示例16
declare 
    name varchar2;
    hash raw;
    last4bytes bigint;
BEGIN
    name := '';
    -- return correctly (D41D8CD98F00B204E9800998ECF8427E, 2118318316)
    dbe_utility.get_sql_hash(name,hash,last4bytes);
    raise notice '%',hash;
    raise notice '%',last4bytes;
END;
/
-- 预期结果为：
NOTICE:  D41D8CD98F00B204E9800998ECF8427E
NOTICE:  2118318316
ANONYMOUS BLOCK EXECUTE

-- 示例17
declare
    bitchar raw(8);
begin
    bitchar := '2111111f';
    dbe_output.print('test '|| bitchar ||' bit is_bit_set value from 1 to 32 bit: ');
    for i in reverse 32 .. 1 loop
        dbe_output.print(dbe_utility.is_bit_set(bitchar, i));
    end loop;
    dbe_output.print_line('.');
end;
/
-- 预期结果为：
test 2111111F bit is_bit_set value from 1 to 32 bit: 00100001000100010001000100011111.
ANONYMOUS BLOCK EXECUTE

-- 示例18
create or REPLACE PROCEDURE p_test_pk ( -- for print result
    name in varchar2,
    type in integer
) 
as
    schema varchar2;
    part1 varchar2;
    part2 varchar2;
    dblink varchar2;
    part1_type integer;
    object_number integer;
begin
    dbe_utility.name_resolve(name,type,schema,part1,part2,dblink,part1_type,object_number);
    raise notice 'schema: % -- part1: % -- part2: % -- dblink: % -- part1_type: %', schema,part1,part2,dblink,part1_type;
end;
/
CREATE PROCEDURE

declare begin p_test_pk('a.b.c@aa',3); end;
/
-- 预期结果为：
NOTICE:  schema: a -- part1: b -- part2: c -- dblink: aa -- part1_type: 0
CONTEXT:  SQL statement "CALL p_test_pk('a.b.c@aa',3)"
PL/pgSQL function inline_code_block line 1 at PERFORM
ANONYMOUS BLOCK EXECUTE

-- 示例19
DECLARE
    name varchar;
    a    varchar;
    b    varchar;
    c    varchar;
    dblink varchar;
    nextpos INTEGER;
BEGIN
    name := '我.w#sdfsdf.CD';
    DBE_UTILITY.NAME_TOKENIZE(name, a, b, c, dblink, nextpos);
    RAISE INFO E'dbe_utility.name_tokenize parse error: name:%\na:%\nb:%\nc:%\ndblink:%',
    name, a, b, c, dblink;
    IF nextpos <> OCTET_LENGTH(name) THEN
        RAISE INFO E'dbe_utility.name_tokenize length error: name:%\nlength of name:%\nnextpos:%',
        name, OCTET_LENGTH(name), nextpos;
    END IF;
END;
/
-- 预期结果为：
INFO:  dbe_utility.name_tokenize parse error: name:我.w#sdfsdf.CD
a:我
b:W#SDFSDF
c:CD
dblink:<NULL>
ANONYMOUS BLOCK EXECUTE

-- 示例20
DECLARE
list varchar2(50) := 'aabb,ccdd,eeff,gghh';
len_list integer;
tab varchar2[];
get_list varchar2(50);
len_tab integer;
BEGIN
dbe_output.print_line('参数列表：' || list);
dbe_utility.comma_to_table(list,len_list,tab);
dbe_output.print_line('参数长度：' || len_list);
FOR i IN 1 .. len_list LOOP
    dbe_output.print_line('列表名称 ' || i || ' : ' || tab(i));
END LOOP;
dbe_output.print_line('调用table_to_comma：');
dbe_utility.table_to_comma(tab,len_tab,get_list );
dbe_output.print_line('输出结果：' || get_list );
dbe_output.print_line('数组长度：' || len_tab);
END;
/
-- 预期结果为：
参数列表：aabb,ccdd,eeff,gghh
参数长度：4
列表名称 1 : aabb
列表名称 2 : ccdd
列表名称 3 : eeff
列表名称 4 : gghh
调用table_to_comma：
输出结果：aabb,ccdd,eeff,gghh
数组长度：4
ANONYMOUS BLOCK EXECUTE

-- 示例21
declare 
    name varchar2;
    hash raw;
    last4bytes bigint;
BEGIN
    name := 'hello world';
    -- return correctly(5EB63BBBE01EEED093CB22BB8F5ACDC3, 3285015183)
    dbe_utility.get_sql_hash_func(name,hash,last4bytes);
    raise notice '%',hash;
    raise notice '%',last4bytes;
END;
/
-- 预期结果为：
NOTICE:  3285015183
NOTICE:  <NULL>
ANONYMOUS BLOCK EXECUTE