使用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_VALUEFunction Compute給定字串的雜湊值。

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