Возможно ли в PLSQL выбрать все поля в таблице, кроме 1 или 2, без необходимости указывать нужные поля?
Например, таблица employee имеет поля:
Можно ли написать запрос, похожий на
select * from employee
оставив поле hobbies без необходимости писать что-то вроде этого?
select id, firstname, lastname from employee
Нет - либо вы получите все поля (*), либо укажите нужные поля.
Если вы хотите избежать спада писателя, вы можете использовать SQL Developer и создать для него список столбцов:
select column_name||','
from all_tab_columns
where table_name = 'YourTableName'
А затем просто выньте одну или две колонки, которые вам не нужны.
Вы также можете использовать
SELECT listagg(column_name, ',') within group (order by column_name) columns
FROM all_tab_columns
WHERE table_name = 'TABLE_NAME'
GROUP BY table_name;
Работаете ли вы на Oracle 12c?
Если да, рассмотрите, соответствует ли это вашим потребностям:
alter table mytable modify column undesired_col_name INVISIBLE;
В этом случае столбец undesired_col_name будет полностью использоваться, но он будет исключен из любых операторов SELECT * и т.п. (например, %ROWTYPE), как будто его не было.
Старый поток, но, да... в 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
Чтобы понять, как работает PIVOT и почему он решает вопрос, давайте рассмотрим лучший пример для нашей таблицы с примерами employee:
select *
from employee
pivot
(
max(id) foo,
max(1) bar
for (hobbies)
IN ('2' as two, '3' as three)
)
Результат здесь:
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
Точно такой же вывод можно получить с помощью этого более простого для понимания запроса:
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
Таким образом, hobbies столбцов никогда не выбираются, так же как и id столбца, оба указаны в предложении PIVOT. Все остальные столбцы сгруппированы и выбраны.
Что ж, возвращаясь к первому запросу, он работает по двум причинам:
1- вы не потеряете ни одной строки в процессе группировки, поскольку столбец идентификатора уникален и столбцы для агрегатов не указаны;
2-, поскольку сводная диаграмма генерирует N * M новых столбцов, где N = количество значений в предложении IN и M = количество указанных агрегаций, поэтому без фильтров и этой единственной безвредной агрегации будет получено 0 * 1 = 0 новых столбцов и удалит те, которые указаны в предложении PIVOT, это просто хобби.
ОТВЕТИТЬ НА КОММЕНТАРИЙ 1
Первая строка этого вопроса гласит: "... без указания полей, которые вы хотите". Во всех остальных ответах предложенные запросы указывают нужные поля в предложении SELECT, за исключением моего, фактически.
Также в названии вопроса написано "... без писательской судороги". Ну, а какую правильную меру выявляет писательская судорога? Я приложил бы все усилия, чтобы предвидеть хороший стандарт SQL для этой проблемы и сравнить с моим ответом. На самом деле, я думаю, что этот "стандарт" может быть чем-то вроде SELECT * NOT IN ([col1], [col2],...).
Теперь я могу видеть в обоих запросах:
Это означает, что вам нужно написать немного больше в моем подходе, так как вам нужна ложная агрегация и предложение PIVOT... но на самом деле на несколько символов больше...
query_generator - это функция PL/SQL, которая возвращает строку выбора для таблицы (1-й параметр), но исключая некоторые столбцы (2-й параметр).
stringlist и putil.join взяты из PL/SQL Commons.
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
Пример использования:
exec dbms_output.put_line(query_generator('all_tables', stringlist('segment_created', 'result_cache')))
То, что искали OP, было примерно таким:
SELECT * MINUS hobbies from...
Лучшее, что можно сделать, чтобы избежать многого ввода (и получить правильные имена столбцов), - это открыть описание таблицы и вырезать и вставить все имена столбцов и удалить те, которые вам не нужны, запятая разделяет оставшихся и поставить их на одну или две строки.
Это легко, быстро, точно, и вы не будете путать следующего человека, который должен работать над вашим кодом.
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
чтобы создать представление: -
создать view view_name как выберите id, first_name, last_name от сотрудника где id в ('', '', '')
note: - это как виртуальная таблица в вашей базе данных, но она может влиять на значения в фактической таблице.
Полиморфные табличные функции Oracle 18c позволяют выбирать все из таблицы и исключать список столбцов:
select * from everything_but(employee, columns(hobbies));
ID FIRSTNAME LASTNAME
-- --------- --------
1 John Smith
Для создания этой функции требуется следующий пакет, который скопирован с веб-сайта Тима Холла 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;
/
Я также создал эту простую функцию-обертку, чтобы дать ей лучшее имя. И создал простой пример таблицы.
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;
Вот еще один вариант получения списка полей, который позволяет указать разделитель:
select listagg(column_name, ', ') WITHIN GROUP (ORDER BY rownum)
from all_tab_columns
where table_name='table'
вот решения... мне нужны все столбцы, кроме пароля
(выберите column_name || ',' из user_tab_columns, где table_name = 'USERS' и column_name < > 'PASSWORD')