I was trying to find a specific subset of result tables in my database by querying for tables by name and then querying inside those tables.
我试图通过按名称查询表然后在这些表中查询来在我的数据库中找到结果表的特定子集。
I know I can get a list of tables like this:
我知道我可以得到这样的表格列表:
SELECT table_name FROM all_tables WHERE table_name LIKE 'MY_RESULTS_%'
But what I can't figure out is how to use that list in another select statement. For example, I'd like to do something like this:
但我无法弄清楚如何在另一个select语句中使用该列表。例如,我想做这样的事情:
-- DOESN'T WORK --
SELECT table_name FROM all_tables
WHERE table_name LIKE 'OUTPUTDATA_%'
AND get_table_by_name(table_name).my_value_col = 'Special Value';
This is on an Oracle database with SQL Developer.
这是在SQL Developer的Oracle数据库中。
1 个解决方案
#1
4
Uese Dynamic SQL
with a cursor
:
带游标的Uese Dynamic SQL:
DECLARE
CURSOR all_tables IS
SELECT table_name
FROM all_tables
WHERE TABLE_NAME like 'MY_RESULTS_%'
ORDER BY TABLE_NAME ;
row_count pls_integer;
BEGIN
FOR rslt IN all_tables LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '
|| rslt.TABLE_NAME || ' where '
||rslt.TABLE_NAME||'.my_value_col= ''Special Value''' INTO row_count;
--here if the specific table.specificcolumn has the specific value print it
if row_count>=1 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(rslt.TABLE_NAME);
END;
END IF;
END LOOP;
END;
#1
4
Uese Dynamic SQL
with a cursor
:
带游标的Uese Dynamic SQL:
DECLARE
CURSOR all_tables IS
SELECT table_name
FROM all_tables
WHERE TABLE_NAME like 'MY_RESULTS_%'
ORDER BY TABLE_NAME ;
row_count pls_integer;
BEGIN
FOR rslt IN all_tables LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '
|| rslt.TABLE_NAME || ' where '
||rslt.TABLE_NAME||'.my_value_col= ''Special Value''' INTO row_count;
--here if the specific table.specificcolumn has the specific value print it
if row_count>=1 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(rslt.TABLE_NAME);
END;
END IF;
END LOOP;
END;