一、查询任意表的任意VARCHAR2列
先建立嵌套表类型:
create or replace type strings_table is table of varchar2(4000);
创建函数:
CREATE OR REPLACE FUNCTION all_strings
(
tab_in IN VARCHAR2
,col_in IN VARCHAR2
,where_in IN VARCHAR2 := NULL
) RETURN strings_table
--查询任意表中任意varchar2类型的列
IS
dynstr VARCHAR2(2000);
l_strings strings_table := strings_table();
BEGIN
dynstr := 'BEGIN
SELECT ' || col_in || ' BULK COLLECT INTO :strings ' ||
' FROM ' || tab_in || ' WHERE ' || NVL(where_in, '1 = 1') || ';
END;';
EXECUTE IMMEDIATE dynstr
USING OUT l_strings;
RETURN l_strings;
END;
使用:
14:07:31 SCOTT@orcl> select * from table(all_strings('emp','empno'));
COLUMN_VALUE
------------------------------------------------------------------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
二、通用的分隔函数(VARCHAR2版本)
create or replace function split
(
p_string varchar2,
p_sep varchar2 := ',' --默认按逗号分隔
) return type_split pipelined --pipelined关键字指定返回结果集使用管道输出,输出为表类型
is
v_l pls_integer;
v_string varchar2(30000) := p_string;
begin
loop
v_l := instr(v_string,p_sep);
if v_l > 0 then
pipe row(substr(v_string,1,v_l-1)); --使用管道函数
v_string := substr(v_string,v_l+length(p_sep));
else
pipe row(v_string);
exit;
end if;
end loop;
return;
end split;
用之前记得创建一个PL/SQL表类型用来保存输出的结果:
create or replace type type_split is table of varchar2(1000)
用法:
select * from table(split('12,aa,qwwq,',','));
结果
COLUMN_VALUE
12
aa
qwwq
NULL
通用的分隔函数(CLOB版本):
CREATE OR REPLACE FUNCTION splitCLOB(p_string IN CLOB,
p_delimiter IN VARCHAR2)
RETURN type_split
PIPELINED AS
v_length NUMBER;
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
v_length := dbms_lob.getlength(p_string);
WHILE (v_start <= v_length) LOOP
v_index := dbms_lob.instr(p_string, p_delimiter, v_start);
IF v_index = 0 THEN
PIPE ROW(dbms_lob.substr(p_string, v_length - 1, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(dbms_lob.substr(p_string, v_index - v_start, v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitCLOB;
用法同上,唯一要注意的是要建立CLOB数组来保存输出:
create or replace type type_split is table of CLOB
三、取得当前函数或存储过程的名称(一般用于错误处理)
CREATE OR REPLACE FUNCTION f_getprocname RETURN VARCHAR2
--获取函数或者存储过程自身的名称及调用者
IS
l_owner VARCHAR2(30);
l_name VARCHAR2(30);
l_lineno NUMBER;
l_type VARCHAR2(30);
BEGIN
OWA_UTIL.who_called_me(l_owner, l_name, l_lineno, l_type);
RETURN l_owner || '.' || l_name;
END;
用法
create or replace procedure p_test
as
EX_P01 EXCEPTION;
...
begin
...
EXCEPTION
WHEN EX_P01 THEN
RAISE_APPLICATION_ERROR(-20001,
'在' || f_getprocname || '中出现错误:('||sqlcode||')--'||sqlerrm||',请检查!');
...
end p_test;