ORACLE 检索某列包含特定字符串的数据表工具存储过程

使用示例：

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;