使用示例：
1、初始化環境
delete APPS.FIND_RESULT;
set serveroutput on
2、執行查找特定檢索字元串
declare
v_ret varchar(200);
begin
apps.sp_findstring('IP2000', 1, v_ret); --檢索數值則用如下 apps.sp_findstring('2000', 0, v_ret);
DBMS_OUTPUT.PUT_LINE('OUT_RETURN = "' || v_ret || '"');
end;
3、查詢檢查結果
select * from apps.find_result order by TABLE_NAME, COLUMN_NAME;
create table apps.find_result(table_name varchar2(255), column_name varchar2(255), data_value varchar2(255), rowversion date);
DELETE apps.find_result;
select * from apps.find_result order by table_name;
DECLARE
IN_DATA VARCHAR2(200);
IN_FLAG NUMBER;
OUT_RETURN VARCHAR2(200);
BEGIN
IN_DATA := 'ip2000';
IN_FLAG := 0;
SP_FINDSTRING(
IN_DATA => IN_DATA,
IN_FLAG => IN_FLAG,
OUT_RETURN => OUT_RETURN
);
DBMS_OUTPUT.PUT_LINE('OUT_RETURN = ' || OUT_RETURN);
END; 工具存儲過程：
create or replace PROCEDURE APPS."SP_FINDSTRING" ( in_data IN VARCHAR2 --被檢索的字元串
, in_flag IN NUMBER DEFAULT 0 --0:數字；1：字元串
, out_return OUT VARCHAR2 )
/**************************************************************
TEST SAMPLE:
======================================
delete APPS.FIND_RESULT;
set serveroutput on;
declare
v_ret varchar(200);
begin
apps.sp_findstring('第一階段', 1, v_ret);
DBMS_OUTPUT.PUT_LINE('OUT_RETURN = "' || v_ret || '"');
end;
select * from apps.find_result;
**************************************************************/
IS
errorException exception; --聲明異常
errorCode number; --異常編碼
errorMsg varchar2(1000); --異常資訊
v_flag varchar2(10);
v_owner_name varchar2(128);
v_table_name varchar2(128);
v_cloumn_name varchar2(128);
v_count int;
v_data VARCHAR2(500);
v_sql varchar2(2000);
v_data_type varchar2(100);
v_rows_found int;
--按照如下條件取出遊标，其中USERS是表空間，那個地方你可以改
cur_data SYS_REFCURSOR;
begin--判斷是數字
v_flag := 'true';
v_rows_found := 0;
if ( Lower(in_data) = Upper(in_data) AND in_flag = 0 ) then
v_sql :=' SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, A.data_type from DBA_TAB_COLUMNS A, DBA_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.STATUS = ''VALID'''
|| ' AND A.OWNER = B.OWNER AND A.OWNER NOT IN (''SYS'', ''SYSTEM'', ''SYSMAN'', ''APPQOSSYS'', ''APEX_030200'', ''CTXSYS'', ''MDSYS'''
|| ' , ''OLAPSYS'', ''ORDSYS'', ''OWBSYS'', ''EXFSYS'', ''WMSYS'', ''DBSNMP'', ''OUTLN'', ''XDB'')'
|| ' AND A.data_type = ''NUMBER'' ORDER BY A.OWNER, A.TABLE_NAME, A.COLUMN_NAME';
open cur_data for v_sql;--打開遊标
loop
fetch cur_data into v_owner_name, v_table_name, v_cloumn_name, v_data_type;
exit when cur_data%notfound;
--取出遊标裡的各個變量，拼成動态sql語句
if v_data_type = 'NUMBER' then
v_sql := 'select count(*) from ' || v_owner_name || '.' || v_table_name || ' where ' || v_cloumn_name || ' = ' ||in_data;
end if;
execute immediate v_sql into v_count;
--如果查詢出來的條數大于，則将查詢中的表名，字段名和輸入的内容插入到我建的那個表中
if v_count > 0 then
v_sql := 'insert into APPS.FIND_RESULT values (''' || v_owner_name || '.' || v_table_name ||
''',''' || v_cloumn_name || ''',''' || in_data || ''',sysdate)';
execute immediate v_sql;
commit;
v_rows_found := v_rows_found + 1;
end if;
end loop;
close cur_data;
else--不是數字
v_data := Upper(in_data);
v_sql :=' SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, A.data_type from DBA_TAB_COLUMNS A, DBA_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.STATUS = ''VALID'''
|| ' AND A.OWNER = B.OWNER AND A.OWNER NOT IN (''SYS'', ''SYSTEM'', ''SYSMAN'', ''APPQOSSYS'', ''APEX_030200'', ''CTXSYS'', ''MDSYS'''
|| ' , ''OLAPSYS'', ''ORDSYS'', ''OWBSYS'', ''EXFSYS'', ''WMSYS'', ''DBSNMP'', ''OUTLN'', ''XDB'')'
|| ' AND (A.data_type = ''VARCHAR2'' OR A.data_type = ''NVARCHAR2'' OR A.data_type = ''NCLOB'') ORDER BY A.OWNER, A.TABLE_NAME, A.COLUMN_NAME';
--dbms_output.put_line(v_sql);
open cur_data for v_sql;
loop
fetch cur_data into v_owner_name, v_table_name, v_cloumn_name, v_data_type;
exit when cur_data%notfound;
--取出遊标裡的各個變量，拼成動态sql語句，
v_sql := 'select count(*) from ' || v_owner_name || '."' || v_table_name || '" where Upper("' || v_cloumn_name || '") LIKE ''%' || v_data || '%''';
--if v_table_name = 'MESSAGEMETADATA' and v_cloumn_name = 'DESCRIPTION' then
-- dbms_output.put_line(v_sql);
--end if;
execute immediate v_sql into v_count;
--如果查詢出來的條數大于，則将查詢中的表名，字段名和輸入的内容插入到我建的那個表中
if v_count > 0 then
v_sql := 'insert into APPS.FIND_RESULT values (''' || v_owner_name || '.' || v_table_name || ''',''' || v_cloumn_name || ''',''' || in_data || ''',sysdate)';
execute immediate v_sql;
commit;
dbms_output.put_line('found in: ' || v_owner_name || '."' || v_table_name || '"."' || v_cloumn_name || '"');
v_rows_found := v_rows_found + 1;
end if;
end loop;
close cur_data;
end if;
out_return := 'found ' || v_rows_found || ' (rows).' ;
exception --異常捕捉，不要把有需要的代碼放在異常捕捉後面，有異常才會執行異常代碼下所有代碼，沒有異常不會執行
when errorException then
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
v_flag := 'false';
out_return := 'flag=' || v_flag || ', errorCode=' || errorCode || ', errorMsg=' || errorMsg;
when others then
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
v_flag := 'false';
out_return := 'flag=' || v_flag || ', errorCode=' || errorCode || ', errorMsg=' || errorMsg;
--dbms_output.put_line(out_return);
end sp_findstring; 分割字元串函數（以管道形式輸出）：
CREATE OR REPLACE TYPE SPLITSTRING_TYPE IS TABLE OF VARCHAR2 (4000);
/
CREATE OR REPLACE FUNCTION APPS.FN_SPLITSTRING(
-- 使用上面的函數前需要先建立一個類型
-- CREATE OR REPLACE TYPE SPLITSTRING_TYPE IS TABLE OF VARCHAR2 (4000);
-- Usage: SELECT * FROM TABLE(APPS.FN_SPLITSTRING('1,2,3,4,5'));
p_value in varchar2, --待分割的字元串
p_split varchar2 := ',' --分割标志
)
return SPLITSTRING_TYPE
pipelined is
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;
begin
loop
v_idx := instr(v_strs_last, p_split);
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
pipe row(v_str);
end loop;
pipe row(v_strs_last);
return;
end FN_SPLITSTRING;