create user jyc identified by jyc;
grant dba to jyc;
grant select any table to jyc;
grant select any view to jyc;
create table jyc.find_result(table_name varchar2(255), column_name varchar2(255), data_value varchar2(255), rowversion date);
select * from jyc.find_result order by table_name;
truncate table jyc.find_result;
用法:
set serveroutput on
declare
v_ret varchar(200);
begin
jyc.sp_findstring('关键字', 1, v_ret);
DBMS_OUTPUT.PUT_LINE('OUT_RETURN = "' || v_ret || '"');
end;
/
存储过程:
create or replace PROCEDURE "JYC"."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 SYS.DBA_TAB_COLUMNS A, SYS.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'' AND A.TABLE_NAME NOT LIKE ''%Column%'' and A.TABLE_NAME not like ''%Modifi%'' 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 jyc.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 SYS.DBA_TAB_COLUMNS A, SYS.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'' AND A.TABLE_NAME NOT LIKE ''%Column%'' and A.TABLE_NAME not like ''%Modifi%'' 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 jyc.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 jyc.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;
/