oracle 对PLSQL中的每个表执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5142696/
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
Execute For Each Table in PLSQL
提问by Raj More
I want to the the number of records in all tables that match a specific name criteria. Here is the SQL I built
我想要匹配特定名称条件的所有表中的记录数。这是我构建的 SQL
Declare SQLStatement VARCHAR (8000) :='';
BEGIN
SELECT 'SELECT COUNT (*) FROM ' || Table_Name || ';'
INTO SQLStatement
FROM All_Tables
WHERE 1=1
AND UPPER (Table_Name) LIKE UPPER ('MSRS%');
IF SQLStatement <> '' THEN
EXECUTE IMMEDIATE SQLStatement;
END IF;
END;
/
But I get the following error:
但我收到以下错误：
Error at line 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 3
Script Terminated on line 1.
How do I modify this so that it runs for all matching tables?
如何修改它以便它对所有匹配的表运行？
Update:
更新：
Based on an answer received, I tried the following but I do not get anything in the DBMS_OUTPUT
根据收到的答案，我尝试了以下操作，但在 DBMS_OUTPUT 中没有得到任何信息
declare
cnt number;
begin
for r in (select table_name from all_tables) loop
dbms_output.put_line('select count(*) from CDR.' || r.table_name);
end loop;
end;
/
回答by René Nyffenegger
declare
cnt number;
begin
for r in (select owner, table_name from all_tables
where upper(table_name) like ('%MSRS%')) loop
execute immediate 'select count(*) from "'
|| r.owner || '"."'
|| r.table_name || '"'
into cnt;
dbms_output.put_line(r.owner || '.' || r.table_name || ': ' || cnt);
end loop;
end;
/
If you're selecting from all_tablesyou cannot count on having been given the grants necessary to select from the table name. You should therefore check for the ORA-00942: table or view does not existerror thrown.
如果您正在选择，all_tables您不能指望获得了从表名中进行选择所需的授权。因此，您应该检查ORA-00942: table or view does not exist抛出的错误。
As to the cause for your error: You get this error because the select statement returns a result set with more than one row (one for each table) and you cannot assign such a result set to a varchar2.
至于您的错误原因：您收到此错误是因为 select 语句返回的结果集有多于一行（每个表一个），并且您不能将这样的结果集分配给 varchar2。
By the way, make sure you enable dbms_output with SET SERVEROUT ONbefore executing this block.
顺便说一句，请确保SET SERVEROUT ON在执行此块之前启用 dbms_output 。
