ORACLE 检索某列包含特定字符串的数据表工具存储过程
使用示例：
1、初始化环境
delete APPS.FIND_RESULT;
set serveroutput on
2、执行查找特定检索字符串
declare
v_ret varchar(200);
begin
apps.sp_findstring('HopeBridge.LIS', 1, 0, v_ret); --检索数值则用如下 apps.sp_findstring('2000', 0, 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;
IN_IGNORECASE NUMBER;
OUT_RETURN VARCHAR2(200);
BEGIN
IN_DATA := 'HopeBridge.LIS';
IN_FLAG := 1;
IN_IGNORECASE := 0;
APPS.SP_FINDSTRING(
IN_DATA => IN_DATA,
IN_FLAG => IN_FLAG,
IN_IGNORECASE => IN_IGNORECASE,
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 1 --0:数字；1：字符串
, in_ignoreCase IN NUMBER DEFAULT 1 --0:正常比较；1：忽略大小写（对字符串查找有效）
, out_return OUT VARCHAR2 )
/**************************************************************
说明：字符串对于VARCHAR2, NVARCHAR2, NCLOB字段类型均可查
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_diffColName VARCHAR2(200);
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 := CASE in_ignoreCase WHEN 1 THEN Upper(in_data) ELSE in_data END;
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_diffColName := CASE in_ignoreCase WHEN 1 THEN 'Upper("' || v_cloumn_name || '")' ELSE '"' || v_cloumn_name || '"' END;
v_sql := 'select count(*) from "' || v_owner_name || '"."' || v_table_name || '" where ' || v_diffColName || ' LIKE ''%' || v_data || '%''';
--dbms_output.put_line(v_sql);
execute immediate v_sql into v_count;
--如果查询出来的条数大于，则将查询中的表名，字段名和输入的内容插入到我建的那个表中
if v_count > 0 then
--dbms_output.put_line(v_sql);
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 APPS.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;
