如何遍历PLSQL Select的结果 [英] How to iterate through the result of a PLSQL Select

问题描述

我正在尝试查找Oracle数据库中特定列中有多少次出现特定值.该列用于数十个表中，并且我将不得不多次运行查询，因此我不想单独查询每个表.我可以通过类似以下内容获取要搜索的表的列表:

I am trying to find how many occurrences of a particular value there are in a particular column in an Oracle database. The column is used in dozens of tables and I'm going to have to run the queries many times, so I don't want to query each table individually. I can get a list of the tables to search with something like:

Select table_name from all_tab_cols
   join all_tables using (table_name)
   where column_name = 'EmployeeId' and num_rows > 0

下一步是遍历表名列表，并输出在EmployeeId列中包含特定值的每个表.例如，输出可能类似于:

The next step is to iterate through that list of table names and output each table that contains a particular value in the EmployeeId column. For example, output might be something like:

**Table Name   Column_name   # Rows for EmployeeId = '123456'**
Table 1        EmployeeId                    1
Table 2        EmployeeId                   12
etc.

我不是开发人员，也没有在SQL脚本中使用游标的经验，因此将不胜感激.

I'm not a developer and don't have experience using cursors in SQL scripts, so any help would be greatly appreciated.

推荐答案

尝试使用CURSOR FOR LOOP.

可能看起来如下图所示(未尝试).

Probably it may look as shown below (not tried).

BEGIN
FOR item IN
(Select table_name,column_name,num_rows  from all_tab_cols
   join all_tables using (table_name)
   where column_name = 'EmployeeId' and num_rows > 0)
LOOP
DBMS_OUTPUT.PUT_LINE
(item.table_name || '    ' || item.column_name ||'    '||item.num_rows);
END LOOP;
END;

这篇关于如何遍历PLSQL Select的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！