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

发布于 2024-11-29 10:14:32 字数 354 浏览 0 评论 0原文

我希望能够使用系统表（本例中为 Oracle）来驱动 SELECT 语句中使用哪些字段。类似于：

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

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

是否可以通过查询表模式信息动态生成 SQL 语句，以便仅选择某些列？

** 编辑 ** 如果可能的话，不要使用函数或过程来执行此操作。

原文

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

做个ˇ局外人 2024-12-06 10:14:32

你可以这样做：

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;

You can do this:

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;

回复收藏 0 原文

烟花易冷人易散 2024-12-06 10:14:32

不，不可能在 SQL 中动态指定列列表。您需要使用过程语言来运行第一个查询，使用它构建第二个查询，然后运行第二个查询。

回复收藏 0 原文

作妖 2024-12-06 10:14:32

您可以使用动态 SQL。创建一个函数，该函数接受表名称、所有者、数据类型，执行内部查询并返回以逗号分隔的列名称列表，或者返回数组表（如果您愿意）。然后构造外部查询并使用 executemedia 执行它。

CREATE FUNCTION get_column_list(
      table_name IN varchar2,
      owner_name IN varchar2,
      data_type  IN varchar2) 
   RETURN varchar2 
   IS 
BEGIN 
...... (get columns and return comma-separated list)
 END;
/

如果你的函数返回一个以逗号分隔的列表，你可以内联它：

execute immediate 'select ' || get_column_list(table_name, owner_name, datatype) || ' from ' || table_name

不可否认，自从我使用 oracle 以来已经很长时间了，所以我可能有点偏离，但我很确定这是完全可行的。

You could use dynamic SQL. Create a function that takes the table name, owner, data type, executes the inner query and returns a comma-separated list of column names, or an array table if you prefer. Then construct the outer query and execute it with execute immediate.

CREATE FUNCTION get_column_list(
      table_name IN varchar2,
      owner_name IN varchar2,
      data_type  IN varchar2) 
   RETURN varchar2 
   IS 
BEGIN 
...... (get columns and return comma-separated list)
 END;
/

If your function returns a comma-separated list you can inline it:

execute immediate 'select ' || get_column_list(table_name, owner_name, datatype) || ' from ' || table_name

Admittedly it's a long time since I played with oracle so I may be a bit off but I'm pretty sure this is quite doable.

回复收藏 0 原文

清秋悲枫 2024-12-06 10:14:32

在 SQLPlus 中你可以这样做：

COLUMN cols NEW_VALUE cols

SELECT max( ltrim( sys_connect_by_path( column_name, ',' ), ',' ) )  cols
FROM
(
select rownum rn, column_name
from    all_tab_cols
where   table_Name='CLARITY_SER'
and     OWNER='CLARITY'
AND      data_type='DATE'
)
start with rn = 1 connect by rn = prior rn +1
;

select &cols from clarity.clarity_ser;

In SQLPlus you could do this:

COLUMN cols NEW_VALUE cols

SELECT max( ltrim( sys_connect_by_path( column_name, ',' ), ',' ) )  cols
FROM
(
select rownum rn, column_name
from    all_tab_cols
where   table_Name='CLARITY_SER'
and     OWNER='CLARITY'
AND      data_type='DATE'
)
start with rn = 1 connect by rn = prior rn +1
;

select &cols from clarity.clarity_ser;

回复收藏 0 原文

~没有更多了~