动态选择要在 SELECT 语句中使用的列

2022-01-23 00:00:00 sql subquery oracle oracle10g

我希望能够使用系统表(在本例中为 Oracle)来驱动在 SELECT 语句中使用哪些字段.比如:

I would love to be able to use the system tables (Oracle in this case) to drive which fields are used in a SELECT statement. Something like:

SELECT 
(
select  column_name
from    all_tab_cols
where   table_Name='CLARITY_SER'
AND     OWNER='CLARITY'
AND     data_type='DATE'
) 
FROM CLARITY_SER

此语法不起作用，因为子查询返回多行，而不是一行多列.

This syntax doesn't work, as the subquery returns multiple rows, instead of one row with multiple columns.

是否可以通过查询表架构信息来动态生成 SQL 语句以便只选择某些列?

Is it possible to generate a SQL statement dynamically by querying the table schema information in order to select only certain columns?

** 编辑 **如果可能，请在不使用函数或过程的情况下执行此操作.

** edit ** Do this without using a function or procedure, if possible.

推荐答案

你可以这样做:

declare
  l_sql varchar2(32767);
  rc sys_refcursor;
begin
  l_sql := 'select ';
  for r in
  ( select  column_name
    from    all_tab_cols
    where   table_Name='CLARITY_SER'
    AND     OWNER='CLARITY'
    AND     data_type='DATE'
  )
  loop
    l_sql := l_sql || r.column_name || ',';
  end loop;
  l_sql := rtrim(l_sql,',') || ' from clarity_ser';
  open rc for l_sql;
  ...
end;

相关文章