How to iterate through the result of a PLSQL Select

Question

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

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.

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

Answer 1

try using 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;