Oracle 数据库搜索所有表中的字符串，返回行数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12921792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle database search all tables for string, return row data
提问by user1750939
I need to search all tables in an oracle database for a specific string and return all the records where that string is found. I am working with SQL Developer. There are several really useful scripts and stored procedures that have been posted online that provide a way to search the entire database and they all seem to be able to return the table name and the column name where the string is found. I actually want to see the rows like a select * statement but I would like it to be for all the tables in my database. I want to note that I am very much a beginner and I'm not familiar with advanced PL/SQL scripting and database concepts so though I've tried and tried I can't seem to figure it out so any input would be appreciated.
我需要在 oracle 数据库中的所有表中搜索特定字符串并返回找到该字符串的所有记录。我正在与 SQL Developer 合作。网上发布了几个非常有用的脚本和存储过程，它们提供了一种搜索整个数据库的方法，它们似乎都能够返回找到字符串的表名和列名。我实际上希望看到像 select * 语句一样的行，但我希望它适用于我数据库中的所有表。我想指出的是，我是一个初学者，我不熟悉高级 PL/SQL 脚本和数据库概念，所以尽管我已经尝试过，但我似乎无法弄清楚，所以任何输入都将不胜感激。
Here is a script that I was trying to modify (someone else's solution)
这是我试图修改的脚本（其他人的解决方案）
declare
l_count number := 0;
l_str varchar2(20) := '%test%';
begin
for rec in (select table_name, column_name
from user_tab_columns
where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' ) ) loop
execute immediate 'select count(*) from '||rec.table_name||
' where '||rec.column_name||' like '''||l_str||'''' into l_count;
if l_count > 0 then
dbms_output.put_line('Table : '||rec.table_name||' '||rec.column_name);
end if;
end loop;
end;
/
采纳答案by Luke Liu
Are you trying to build a full database search based on a key word? You can get table names , table column names and row counts by following code. But you cannot get row data within same code, you can get row data based on search results.
您是否正在尝试基于关键字构建完整的数据库搜索？您可以通过以下代码获取表名、表列名和行数。但是你不能在同一个代码中获取行数据，你可以根据搜索结果获取行数据。
--Set output size
SET serveroutput ON size 1000000
DECLARE
-- var table Name for cursor loop.
t_c1_tname user_tab_columns.table_name%TYPE;
-- var column name for dynamic sql statement.
t_c1_cname user_tab_columns.column_name%TYPE;
-- var string for dynamic sql statement.
t_command VARCHAR2(200);
-- var for your search key word.
l_str varchar2(20) := '%test%';
-- var for dynamic cursor.
t_cid INTEGER;
-- var for total row counts.
t_total_records NUMBER(10);
-- var for stat of executing dynamic sql statement.
stat INTEGER;
--var for each loop row counts.
row_count INTEGER;
-- var for minimum search result, here I set value = 0;
t_limit INTEGER := 0; -- Only show tables with more rows
-- cursor gets all table name, column name.
CURSOR c1 IS select table_name, column_name
from user_tab_columns
where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' );
BEGIN
t_limit := 0;
OPEN c1;
LOOP
FETCH c1 INTO t_c1_tname,t_c1_cname;
EXIT WHEN c1%NOTFOUND;
-- Here create dynamic sql statement.
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname || ' where ' || t_c1_cname ||' like '''|| l_str||'''';
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
-- Here execute dynamic sql statement.
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
-- Here get total row counts for each loop.
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
IF t_total_records > t_limit THEN
--Here output results
DBMS_OUTPUT.PUT_LINE(RPAD(t_c1_tname,55,' ')||RPAD(t_c1_cname,55,' ')||
TO_CHAR(t_total_records,'99999999')||' record(s)');
-- here you can insert results into your table.
--INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records);
END IF;
DBMS_SQL.CLOSE_CURSOR(t_cid);
END LOOP;
CLOSE c1;
-- COMMIT if you have any insert statement.
-- COMMIT;
END;
/
