这不是普通的数据库功能,但你不是第一个提出这种要求的人。
解决方案需要两个东西。第一个是数据字典;Oracle数据库不支持反射,但它提供了一组视图,可以提供有关数据库对象的元数据。在这种情况下,我们需要user_tab_columns
,它将为我们提供给定表格的列。第二个是动态SQL;这是在运行时组装SQL查询然后执行它的能力。有几种做法,但通常ref cursor足够了。
以下代码是概念验证。它需要四个参数:
- 您想搜索的表的名称
- 该表的主键列的名称
- 您想要限制的主键值
- 您想要搜索的值。
这只是个简单的示例,您可能需要编辑它以整理输出或使程序更加灵活。
create or replace procedure search_cols
(tname in user_tables.table_name%type
, pk_col in user_tab_columns.column_name%type
, pk in number
, val in number )
is
firstcol boolean := true;
stmt varchar2(32767);
result varchar2(32767);
rc sys_refcursor;
begin
stmt := 'select ';
<< projection >>
for lrec in ( select column_name from user_tab_columns
where table_name = tname
and column_name != pk_col
and data_type = 'NUMBER'
order by column_id )
loop
if not firstcol then
stmt := stmt || chr(10) || '||'',''||';
else
firstcol := false;
end if;
stmt := stmt || ' case when '|| lrec.column_name||' = '|| val ||
' then '''|| lrec.column_name || ''' else null end';
end loop projection;
stmt := stmt || chr(10)|| ' from '||tname||' where '|| pk_col || ' = '|| pk;
open rc for stmt;
fetch rc into result;
close rc;
dbms_output.put_line(tname || '::' || val || ' found in '||result);
end search_cols;
/
正如您所见,动态SQL很难阅读。调试也更加困难 :) 所以最好有一种方法来显示最终语句。
无论如何,这里是结果:
SQL> set serveroutput on size unlimited
SQL> exec search_cols('T23', 'ID', 111, 10)
T23::10 found in ,COL_B,COL_C,
PL/SQL procedure successfully completed.
SQL> exec search_cols('T23', 'ID', 222, 10)
T23::10 found in COL_A,,,
PL/SQL procedure successfully completed.
SQL>