DBMS_UTILITY

提供各种实用程序子程序。

函数名	参数类型	结果类型	描述
format_call_stack	text	text	格式化当前调用堆栈
comma_to_table	list varchar,OUT tablen integer,OUT tab varchar[]	record	把字符串以逗号分割后形成数组
table_to_comma	tab varchar[],OUT tablen integer,OUT list varchar	record	把数组合并成以逗号分割的字符串
get_time	null	numeric	返回一个单位为百分之一秒的当前时间值

类型	对应类型	描述
LNAME_ARRAY	VARCHAR2(4000)[]	配合comma_to_table和table_to_comma两个函数的出入参
UNCL_ARRAY	VARCHAR2(227)[]
NAME_ARRAY	VARCHAR2(30)[]

do language plpgsql $$   
declare 
V_ALERTNAME VARCHAR2(30) := 'alert1';
BEGIN
DBMS_ALERT.SIGNAL('alert1', 'hello, this   is   sending   process!');
 RAISE NOTICE '%',dbms_utility.format_call_stack();
end;  
$$; 

atlasdb$# $$; 
NOTICE:  ----- PL/pgSQL Call Stack -----
  object     line  object
  handle   number  name
       0        6  function anonymous object

vastbase=# declare
   t_vararray dbms_utility.lname_array;
   vc_stringlist varchar2(4000);
   n_idx binary_integer;
begin
   vc_stringlist := 'lname,ARRAY';
   dbms_utility.comma_to_table(vc_stringlist, n_idx,t_vararray);
   dbms_output.put_line('Total Num : '||to_char(n_idx));
   for i in 1..n_idx loop
       dbms_output.put_line(t_vararray[i]);
       t_vararray(i) := '['||t_vararray[i]||']';
   dbms_output.put_line('<<'||t_vararray[i]);
   end loop;
   n_idx := n_idx -1;
   --table to comma
   dbms_utility.table_to_comma(t_vararray, n_idx, vc_stringlist);
   dbms_output.put_line('>>>>>');
   dbms_output.put_line(''||vc_stringlist);
end;
/

select dbms_output.serveroutput(true);
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('oracle.dept, oracle.emp, oracle.jobhist');
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('oracle.dept, oracle.emp, oracle.jobhist');

VastbaseG100

DBMS_UTILITY

弹框头部