使用示例：

delete APPS.FIND_RESULT;

set serveroutput on

declare

    v_ret varchar(200);

begin 

   apps.sp_findstring('IP2000', 1, v_ret);

    DBMS_OUTPUT.PUT_LINE('OUT_RETURN = "' || v_ret || '"');

end;

select * from apps.find_result; 

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 )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'' 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语句，        if v_data_type = 'VARCHAR2' then           v_sql := 'select count(*) from ' || v_owner_name || '.' || v_table_name || ' where Upper("' || v_cloumn_name || '") LIKE ''%' || v_data || '%''';        else           v_sql := 'select count(*) from APPS.FIND_RESULT where 1=2';        end if;        --dbms_output.put_line(v_sql);        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;   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;