Oracle view с параметрами

Обновлено: 15.01.2025

Можем ли мы передать параметр в представление в Microsoft SQL Server?

Пытался сделать create view так, но не получается:

Представление - это сохраненный sql-текст запроса выбора. Параметры вне обсуждения. Когда ваш сохраненный запрос возвращает столбец, в котором вы хотите выполнить фильтрацию, вы можете сделать это в вызывающем запросе. Например, "ВЫБРАТЬ * ИЗ v_emp WHERE emp_id =?" @Epicurist Parameters are out of the discussion Слишком смелое заявление. Контрпример

Как уже было сказано, вы не можете.

Возможное решение - реализовать сохраненную функцию, например:

Это позволяет вам использовать его как обычный вид с:

Каковы практические различия между этим и видом? Можете ли вы назначить пользователю права доступа только к этой функции? В MySQL вы пишете хранимую процедуру и последним оператором в процедуре будет набор результатов, который вы хотите вернуть. можем ли мы без проблем использовать этот запрос из кода JDBC в java? @MikeMurko одно важное отличие состоит в том, что можно запросить схему / метаданные о столбцах представления, если это представление. Если это хранимая процедура или функция, я думаю, базы данных могут не предоставить вам эту информацию. Если у вас есть группа пользователей, у которых есть доступ к вашей базе данных, и вы не хотите, чтобы они выполняли команду «select * from [view]» и влияли на производительность, вы можете предоставить доступ к определенным функциям, что заставит их предоставить параметры фильтра. которые, например, используют определенный набор индексов.

Есть два способа добиться того, чего вы хотите, к сожалению, ни один из них нельзя сделать с помощью представления.

Вы можете создать пользовательскую функцию с табличным значением, которая принимает нужный параметр и возвращает результат запроса.

Или вы можете сделать почти то же самое, но создать хранимую процедуру вместо определяемой пользователем функции.

хранимая процедура будет выглядеть как

Или определяемая пользователем функция будет выглядеть как

Просто имейте в виду, что вы не можете легко использовать опцию SP SELECT : прочитайте больше .

Нет, как сказал Младен Прайдич. Думайте о представлении как о «статическом фильтре» для таблицы или комбинации таблиц. Например: представление может объединять таблицы, Order и Customer поэтому вы получаете новую «таблицу» строк Order вместе с новыми столбцами, содержащими имя клиента и номер клиента (комбинация таблиц). Или вы можете создать представление, которое выбирает из Order таблицы только необработанные заказы (статический фильтр).

Затем вы выбираете из представления, как если бы вы выбирали из любой другой «нормальной» таблицы - вся «нестатическая» фильтрация должна выполняться вне представления (например, «Получить все заказы для клиентов по имени Миллер» или «Получить необработанные заказы это произошло 24 декабря »).

И мне это нужно каждый месяц, поэтому я хотел бы поместить его в представление, но, поскольку дата меняется каждый месяц, было бы здорово иметь параметр даты, который я могу передать представлению при его вызове. Есть ли способ, как я могу достичь этого? Я новичок в оракуле, ценю любую помощь. Спасибо.

4 ответа

Существуют способы «параметризации» представления, например, используя контексты Oracle, но они не часто полезны и, конечно, не для вашего случая.

Если ваш запрос на самом деле просто выбирает из одной таблицы только с датами в качестве предикатов, то представление также не добавляет особой ценности. Вы можете создать SQL скрипт (в файле, например, myquery.sql), используя переменные связывания:

Затем каждый месяц вы можете просто открыть файл и запустить его, и он предложит вам 2 даты. Или вы можете запустить скрипт как этот, и он также предложит вам:

Или если вы используете подстановочные строки '& 1.' и '& 2.' вместо:

Затем вы можете передать даты в командной строке следующим образом:

(Потому что & 1. Означает первый параметр в командной строке и т. Д.)

Начиная с 19.6 вы можете создавать параметризованные представления, используя Макросы SQL.

Другой способ сделать это - использовать функцию, которая извлекает параметры из таблицы, поэтому вам не нужно манипулировать каким-либо DDL. Идея здесь

Используя таблицу для хранения параметров, в основном вам нужно значение параметра и описание параметра.
Использование функции для получения значения этого параметра, когда вход является именем параметра
Использование вызова функции внутри представления.
Затем вы можете автоматически управлять представлением, изменяя значения таблицы параметров.

Этот запрос возвращает данные за предыдущий месяц , то есть месяц перед текущим месяцем на момент запроса (= sysdate ).

Вы используете trunc с 'MM' для получения первых месяцев и арифметику с add_months

В Oracle VIEW в сущности, представляет собой виртуальную таблицу, которая физически не существует. Скорее всего, она создается с помощью запроса соединяющего одну или несколько таблиц.

CREATE VIEW

Синтаксис

Синтаксис CREATE VIEW в Oracle:

CREATE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

Параметры или аргументы

view_name
Наименование Oracle VIEW, которое вы хотите создать.
WHERE conditions
Необязательный. Условия, которые должны быть выполнены для записей, которые будут включены в VIEW.

Пример

SELECT suppliers.supplier_id, orders.quantity, orders.price

Этот пример Oracle CREATE VIEW создаст виртуальную таблицу на основе результирующего набора SELECT. Теперь вы можете запросить VIEW Oracle следующим образом:

Update VIEW

С помощью Oracle CREATE OR REPLACE VIEW вы можете изменить определенное в Oracle VIEW не удаляя его.

Синтаксис

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;

view_name
Наименование представления Oracle, которое вы хотите создать или изменить.

Пример

SELECT suppliers.supplier_id, orders.quantity, orders.price

Этот пример Oracle CREATE OR REPLACE VIEW обновит определенное в Oracle представление sup_orders без его удаления. Если Oracle VIEW еще не существовало, то представление будет создано впервые.

Drop VIEW

После того, как VIEW было создано, вы можете удалить его с помощью DROP VIEW.

Синтаксис

view_name
Наименование представления Oracle, который вы хотите создать или заменить.

Пример

Ниже приведен пример того, как использовать Oracle DROP VIEW:

Этот пример Oracle DROP VIEW удалит представление с названием sup_orders .

Часто задаваемые вопросы

Вопрос: Можно ли обновить данные в VIEW?
Ответ: Представление в Oracle создается путем объединения одной или нескольких таблиц. При обновлении записи (ей) в VIEW, обновляются записи в базовых таблицах, которые составляют VIEW.
Так что, да, вы можете обновить данные в Oracle VIEW при наличии у вас соответствующих привилегий в таблицах базы Oracle.

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).

Преимущества использования представлений:

Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения представлений в MySQL

нельзя повесить триггер на представление,
нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
в определении представления нельзя использовать подзапрос в части FROM,
в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
таблицы и представления, присутствующие в определении представления должны существовать.
только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [ OR REPLACE]
[ALGORITHM = ]
VIEW view_name [(column_list)]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

* This source code was highlighted with Source Code Highlighter .

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
column_list — задает имена полей представления.
WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

Для избежания такой ситуации нужно явно указывать имена полей представления

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator '+' ) FROM table_name;

* This source code was highlighted with Source Code Highlighter .

Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.

В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

* This source code was highlighted with Source Code Highlighter .

Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE. Рассмотрим следующее обращение к нашему представлению:

SELECT subject, param FROM v WHERE param>1000;

* This source code was highlighted with Source Code Highlighter .

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

* This source code was highlighted with Source Code Highlighter .

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

CREATE VIEW v AS SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter .

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter .

Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;
SELECT MAX (num) FROM tmp_table;
DROP TABLE tpm_table;

* This source code was highlighted with Source Code Highlighter .

В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Обновляемость представлений

Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

punbb > CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION ;
Query OK, 0 rows affected (0.03 sec)

punbb > UPDATE v SET num_views=2003 WHERE subject= 'test' ;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

* This source code was highlighted with Source Code Highlighter .

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

punbb > UPDATE v SET num_views=1999 WHERE subject= 'test' ;
ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'

* This source code was highlighted with Source Code Highlighter .

Не все обновляемые представления позволяют добавление данных:

punbb > INSERT INTO v (subject,num_views) VALUES ( 'test1' ,4000);
ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'

* This source code was highlighted with Source Code Highlighter .

Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:

punbb > INSERT INTO v (forum_id,subject,num_views) VALUES (1, 'test1' ,4000);
ERROR 1054 (42S22): Unknown COLUMN 'forum_id' IN 'field list'

* This source code was highlighted with Source Code Highlighter .

punbb > INSERT INTO v (forum_name) VALUES ( 'TEST' );
Query OK, 1 row affected (0.00 sec)

* This source code was highlighted with Source Code Highlighter .

Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.

Представление – это виртуальная таблица. В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу, например, Oracle. Для тех, кто работает с представлением, манипулирование его данными ничем не отличается от манипулирования данными таблицы. В некоторых случаях пользователь может вводить данные в представление, как если бы оно было таблицей. Работая с представлением нужно помнить, что:

Представления добавляют уровень защиты данных (например, можно создать представление для таблицы, где пользователю, выполняющему SELECT над представлением, видны только сведения о зарплате)
Представления могут скрывать сложность данных, комбинируя нужную информацию из нескольких таблиц
Представления могут скрывать настоящие имена столбцов, порой трудные для понимания, и показывать более простые имена.

Представление создается с помощью команды CREATE VIEW. После создания представления становятся частью схемы создавшего их пользователя. Переназначить их другому пользователю можно тогда, когда у него имеется системная привилегия CREATE ANY VIEW.

Синтаксис команды CREATE VIEW Oracle

Синтаксис команды Create View

Основные ключевые слова и параметры CREATE VIEW Oracle :
OR REPLACE, FORCE, NOFORCE, Sсhema, View, Alias, AS subquery, WITH CHECK OPTION, Constraint

Преимущество использования представления вместо базовой таблицы состоит в том, что оно обновляется автоматически при изменении формирующих его таблиц. Содержимое представления не фиксируется, а повторно вычисляется всякий раз, когда вы ссылаетесь на представление в команде.

Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

представление должно включать первичный ключ таблицы
не должно содержать полей, полученных в результате применения функций агрегирования
не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
может быть определено на другом представлении, но это представление должно быть обновляемым
не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

SQL CREATE VIEW. Пример 1.
CREATE VIEW Oracle.
Простое представление, которое создается из данных одной таблицы:

CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

SQL CREATE VIEW. Пример 2.
CREATE VIEW Oracle.
При создании представления можно можно задать новые имена полей:

CREATE VIEW Rating_view(rating,number) AS SELECT rating, COUNT(*) FROM Customers GROUP BY rating;

SQL CREATE VIEW. Пример 3.
CREATE VIEW Oracle.
Представления могут получать информацию из любого количества базовых таблиц:

CREATE VIEW Nameorders AS SELECT onum, amt,a.snum, sname, cname FROM Orders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

SQL CREATE VIEW. Пример 4.
CREATE VIEW Oracle.
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

CREATE VIEW Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROMOrders c WHERE c.odate = b.odate);

SQL CREATE VIEW. Пример 5.
CREATE VIEW Oracle.

Читайте также: