oracle 除了 1 或 2 个字段之外，您可以选择所有内容而不会出现作家抽筋吗？

Question

提问by Steve

Is it possible, in PLSQL, to select all of the fields in a table except for 1 or 2, without having to specify the fields you want?

在 PLSQL 中，是否可以选择表中除 1 或 2 之外的所有字段，而无需指定所需的字段？

Example, the employee table has the fields:

例如，employee 表具有以下字段：

id
firstname
lastname
hobbies

ID
名
姓
爱好

Is it still possible to write a query similar to

是否仍然可以编写类似于

select * from employee

while leaving the field hobbieswithout with having to write something like this?

离开现场hobbies而不必写这样的东西？

select id, firstname, lastname from employee

Answer 1

采纳答案by Yahia

No - you either get all fields (*) OR specify the fields you want.

不，您要么获取所有字段 ( *)，要么指定所需的字段。

Answer 2

回答by Michael Fredrickson

If you want to avoid the writer's cramp, you can use SQL Developer and have it generate the column list for you:

如果你想避免作者的抽筋，你可以使用 SQL Developer 并让它为你生成列列表：

select column_name||','
from all_tab_columns
where table_name = 'YourTableName'

And then just take out the one or two columns that you don't want.

然后取出您不想要的一两列。

You can also use

你也可以使用

SELECT listagg(column_name, ',') within group (order by column_name) columns
FROM all_tab_columns
WHERE table_name = 'TABLE_NAME'
GROUP BY table_name;

Answer 3

回答by Matthew McPeak

Are you running on Oracle 12c?

您是否在 Oracle 12c 上运行？

If so, consider whether this meets your needs:

如果是这样，请考虑这是否满足您的需求：

alter table mytable modify column undesired_col_name INVISIBLE;

In that case column undesired_col_namewill be completely usable, but it will be excluded from any SELECT *statements and the like (e.g., %ROWTYPE) as though it didn't exist.

在这种情况下，列undesired_col_name将完全可用，但它将被排除在任何SELECT *语句等（例如，%ROWTYPE）之外，就好像它不存在一样。

Answer 4

回答by Felypp Oliveira

An old thread but, yes... there is a way to do it in Oracle:

一个旧线程但是，是的......有一种方法可以在Oracle中做到这一点：

with

employee(id, firstname, lastname, hobbies) as
(
  select 1, 'a', 'b', '1' from dual union 
  select 2, 'a', 'b', '2' from dual union 
  select 3, 'a', 'b', '3' from dual union 
  select 4, 'c', 'd', '3' from dual union 
  select 5, 'e', 'f', '2' from dual  
)

select * 
from employee 
pivot
( 
  max(1) -- fake  
  for (hobbies) -- put the undesired columns here
  IN () -- no values here...
) 
where 1=1 -- and your filters here...
order by id

To understand how the PIVOTworks and why it solves the question, lets take a better example for our employeesample table:

要了解PIVOT 的工作原理以及为什么它可以解决问题，让我们为我们的employee示例表举一个更好的例子：

select * 
from employee 
pivot
(
  max(id) foo,
  max(1)  bar
  for (hobbies) 
  IN ('2' as two, '3' as three)
)

The result here is:

这里的结果是：

FIRSTNAME | LASTNAME | TWO_FOO | TWO_BAR | THREE_FOO | THREE_BAR
    c          d         null      null        4           1
    e          f           5        1         null        null
    a          b           2        1          3           1

The exact same output can be achieved using this easier to understand query:

使用这个更容易理解的查询可以实现完全相同的输出：

select 
  firstname,
  lastname,
  max(case when hobbies = '2' then id end) two_foo,
  max(case when hobbies = '2' then 1  end) two_bar,
  max(case when hobbies = '3' then id end) three_foo,
  max(case when hobbies = '3' then 1  end) three_bar
from employee 
group by
  firstname,
  lastname

So, the column hobbiesis never selected, just as the column id, both specified inside the PIVOTclause. All other columns are grouped and selected.

因此，hobbies永远不会选择column ，就像 column 一样id，两者都在PIVOT子句中指定。所有其他列都被分组和选择。

Well, returning to the first query, it works for two reasons:
1- you will not lose any row in the grouping process because the idcolumn is unique and no columns were specified for aggregations;
2- as the pivot generates N * M new columns, where N = number of values of the INclause and M = number of aggregations specified, so having no filters and that single harmless aggregation will produce 0 * 1 = 0 new columns and will remove the ones specified in the PIVOTclause, which is just the hobbies.

好吧，回到第一个查询，它有两个原因：
1- 在分组过程中不会丢失任何行，因为id列是唯一的，并且没有为聚合指定任何列；
2- 由于数据透视生成 N * M 个新列，其中 N = IN子句的值数，M = 指定的聚合数，因此没有过滤器和单个无害聚合将产生 0 * 1 = 0 个新列，并将删除PIVOT子句中指定的那些，这只是hobbies。

ANSWER TO COMMENT 1

回答评论 1

The first line of this question says: "... without having to specify the fields you want". In all other answers the proposed queries specifies the desired fields in the SELECTclause, except in mine, actually.

这个问题的第一行说：“...而不必指定您想要的字段”。在所有其他答案中，建议的查询在SELECT子句中指定了所需的字段，实际上我的除外。

Also, in the question title says "... without writer's cramp". Well, what's the correct measure to identify a writer's cramp? My best effort would be to foresee a good SQL standard to this problem and compare with my answer. Actually, I think this "standard" could be something like SELECT * NOT IN ([col1], [col2], ...).

此外，在问题标题中说“......没有作家的抽筋”。那么，识别作家抽筋的正确方法是什么？我最大的努力是为这个问题预见一个好的 SQL 标准并与我的答案进行比较。实际上，我认为这个“标准”可能类似于SELECT * NOT IN ([col1], [col2], ...)。

Now, I can see in both queries:

现在，我可以在两个查询中看到：

a list of undesired columns;
an INclause;
a three characters clause - FORand NOT;

不需要的列的列表；
一个IN条款;
三个字符的子句 - FOR和NOT;

It means that you need to write a bit more in my approach as you need a fake aggregation and the PIVOTclause... but it's really few characters more...

这意味着你需要在我的方法中多写一点，因为你需要一个假的聚合和PIVOT子句......但它真的只有几个字符......

Answer 5

回答by Jon Heller

Oracle 18c polymorphic table functions make it possible to select everything from a table and exclude a list of columns:

Oracle 18c 多态表函数可以从表中选择所有内容并排除列列表：

select * from everything_but(employee, columns(hobbies));

ID   FIRSTNAME   LASTNAME
--   ---------   --------
1    John        Smith

Creating that function requires the below package, which is copied from Tim Hall's website https://oracle-base.com/articles/18c/polymorphic-table-functions-18c. Note that the package does not contain anything specific to the table - this solution will work for any Oracle table.

创建该函数需要以下包，该包是从 Tim Hall 的网站https://oracle-base.com/articles/18c/polymorphic-table-functions-18c复制的。请注意，该包不包含特定于该表的任何内容 - 此解决方案适用于任何 Oracle 表。

CREATE OR REPLACE PACKAGE poly_pkg AS

  FUNCTION everything_but(tab IN TABLE,
                          col IN COLUMNS)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t;

END poly_pkg;
/


CREATE OR REPLACE PACKAGE BODY poly_pkg AS

  FUNCTION describe (tab IN OUT DBMS_TF.table_t,
                     col IN     dbms_tf.columns_t)
    RETURN DBMS_TF.describe_t
  AS
  BEGIN
    -- Loop through all the table columns.
    FOR i IN 1 .. tab.column.count() LOOP
      -- Loop through all the columns listed in the second parameter.
      FOR j IN 1 .. col.count() LOOP
        -- Set pass_through to true for any columns not in the exclude list.
        tab.column(i).pass_through := (tab.column(i).description.name != col(j));
        -- Exit inner loop if you find a column that shouldn't be included.
        EXIT WHEN NOT tab.column(i).pass_through;
      END LOOP;
    END LOOP;

    RETURN NULL;
  END;

END poly_pkg;
/

I also created this simple wrapper function to give it a better name. And created a simple example table.

我还创建了这个简单的包装函数来给它一个更好的名字。并创建了一个简单的示例表。

CREATE OR REPLACE FUNCTION everything_but(tab IN TABLE, col in COLUMNS)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;
/

create table employee as
select 1 id, 'John' firstname, 'Smith' lastname, 'fishing' hobbies from dual;

Answer 6

回答by user2785110

What the OP was looking for was something like:

OP正在寻找的是这样的：

SELECT * MINUS hobbies from...

The best thing to do to avoid a lot of typing (and get all the column names correct) is to open the table description and cut and paste all the column names and delete the ones you don't want, comma separate the remaining ones and put them on a singe line or two.

避免大量输入（并正确输入所有列名）的最佳做法是打开表描述并剪切并粘贴所有列名并删除您不需要的列名，用逗号分隔剩余的列名和把它们放在一两条线上。

It is easy, fast, accurate and you won't confuse the next person who has to work on your code.

它简单、快速、准确，您不会混淆下一个必须处理您的代码的人。

Answer 7

回答by user272735

query_generatoris a PL/SQL function that returns a select stringfor a table (1st parameter) but excluding some columns (2nd parameter).

query_generator是一个 PL/SQL 函数，它返回表（第一个参数）的选择字符串，但不包括某些列（第二个参数）。

stringlistand putil.joinare from PL/SQL Commons.

stringlist并且putil.join来自PL/SQL Commons。

stringlistis a simple list of strings: create type StringList as table of varchar2(32767);and putil.joinis just a normal join function.

stringlist是一个简单的字符串列表：create type StringList as table of varchar2(32767);并且putil.join只是一个普通的连接函数。

create or replace function quote_list(p_list in stringlist)
return stringlist as
  v_list stringlist := stringlist();
begin
  v_list.extend(p_list.last);
  for i in p_list.first .. p_list.last loop
    v_list(i) := '''' || p_list(i) || '''';
  end loop;

  return v_list;
end;
/
show errors

create or replace function query_generator(
  p_table in varchar2,
  p_exclude in stringlist
) return varchar2 as
  v_table constant varchar2(31) := upper(p_table);
  v_exclude constant varchar2(32676) :=
    upper(putil.join(quote_list(p_exclude), ','));
  v_stmt_str constant varchar2(32676) :=
    'select column_name from all_tab_columns where table_name = ''' ||
    v_table || ''' and column_name not in (' || v_exclude ||
    ') order by column_id';
  type stmt_cur_t is ref cursor;
  v_stmt_cur stmt_cur_t;
  v_column_name varchar2(31);
  v_query varchar2(32676) := 'select ';
begin
  open v_stmt_cur for v_stmt_str;

  loop
    fetch v_stmt_cur into v_column_name;
    exit when v_stmt_cur%notfound;
    v_query := v_query || lower(v_column_name) || ', ';
  end loop;

  close v_stmt_cur;

  select rtrim(v_query, ', ') into v_query from dual;

  v_query := v_query || ' from ' || p_table || ';';

  return v_query;
end;
/
show errors

Usage example:

用法示例：

exec dbms_output.put_line(query_generator('all_tables', stringlist('segment_created', 'result_cache')))

Answer 8

回答by deepak_giri_goswami

to create view :-

创建视图：-

create view view_name as select id,first_name,last_name from employee where id in ('','','')

创建视图 view_name 作为 select id,first_name,last_name from employee where id in ('','','')

note:- this is like virtual table in your database but , it can effect values in actual table.

注意：- 这就像数据库中的虚拟表，但是，它会影响实际表中的值。

Answer 9

回答by clq

WITH O AS
(
SELECT 'SELECT ' || rtrim('NULL AS "Dummy",' || LISTAGG('"'||column_name || '"', ',' ) within group (ORDER BY COLUMN_NAME),',')|| ' FROM "'||TABLE_NAME||'"' AS SQL, TABLE_NAME  FROM USER_TAB_COLUMNS  GROUP BY (TABLE_NAME)
)
SELECT DBMS_XMLGEN.GETXMLTYPE ((SELECT REPLACE(SQL,',COLUMNNAME','') FROM O WHERE TABLE_NAME = 'TABLENAME')) FROM DUAL

Answer 10

回答by David L

Here is another option to get a list of the fields that allows you to specify the delimiter:

这是获取允许您指定分隔符的字段列表的另一个选项：

select listagg(column_name, ', ') WITHIN GROUP (ORDER BY rownum)
from all_tab_columns
where table_name='table'

oracle 除了 1 或 2 个字段之外，您可以选择所有内容而不会出现作家抽筋吗？

提问by Steve

采纳答案by Yahia

回答by Michael Fredrickson

回答by Matthew McPeak

回答by Felypp Oliveira

回答by Jon Heller

回答by user2785110

回答by user272735

回答by deepak_giri_goswami

回答by clq

回答by David L

相关推荐

最近更新

标签

oracle 除了 1 或 2 个字段之外，您可以选择所有内容而不会出现作家抽筋吗？

提问by Steve

采纳答案by Yahia

回答by Michael Fredrickson

回答by Matthew McPeak

回答by Felypp Oliveira

回答by Jon Heller

回答by user2785110

回答by user272735

回答by deepak_giri_goswami

回答by clq

回答by David L

相关推荐

oracle Oracle中如何声明和显示变量

启动 Oracle SQL*Plus 时出现 TNS 协议适配器错误

如何在 Oracle 表中插入/更新更大的数据？

如何使用 SYS_REFCURSOR 处理 NO DATA FOUND - ORACLE

相关推荐

最近更新

标签