对PLSQL中的每个表执行 [英] Execute For Each Table in PLSQL
问题描述
我想要所有表中符合特定名称条件的记录数.这是我建立的SQL
I want to the the number of records in all tables that match a specific name criteria. Here is the SQL I built
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;
/
但是出现以下错误:
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?
更新:
根据收到的答案,我尝试了以下操作,但在DBMS_OUTPUT中什么都没得到
Based on an answer received, I tried the following but I do not get anything in the 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;
/
推荐答案
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;
/
如果您要从 all_tables
中进行选择,则不能指望获得从表名中进行选择所必需的授予.因此,您应该检查是否出现 ORA-00942:表或视图不存在
错误.
If you're selecting from all_tables
you 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 exist
error thrown.
关于错误原因:之所以会出现此错误,是因为select语句返回的结果集包含多行(每个表一个),并且您无法将这样的结果集分配给varchar2.
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.
在执行此块之前,请确保在 SET SERVEROUT ON
上启用dbms_output.
By the way, make sure you enable dbms_output with SET SERVEROUT ON
before executing this block.
这篇关于对PLSQL中的每个表执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!