ms sql view с параметрами
Представления и табличные объекты
Представления
Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.
Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.
Например, пусть у нас есть три связанных таблицы:
Теперь добавим в базу данных, в которой содержатся данные таблицы, следующее представление:
То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:
Теперь используем созданное выше представление для получения данных:
При создании представлений следует учитывать, что представления, как и таблицы, должны иметь уникальные имена в рамках той же базы данных.
Представления могут иметь не более 1024 столбцов и могут обращаться не более чем к 256 таблицам.
Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.
Также при создании представления можно определить набор его столбцов:
Изменение представления
Например, изменим выше созданное представление OrdersProductsCustomers:
Удаление представления
Для удаления представления вызывается команда DROP VIEW :
Также стоит отметить, что при удалении таблиц также следует удалить и представления, которые используют эти таблицы.
Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом. Как и таблица, представление состоит из ряда именованных столбцов и строк данных. Пока представление не будет проиндексировано, оно не существует в базе данных как хранимая совокупность значений. Строки и столбцы данных извлекаются из таблиц, указанных в определяющем представление запросе и динамически создаваемых при обращениях к представлению.
Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. Определяющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлениях текущей или других баз данных. Кроме того, для определения представлений с данными из нескольких разнородных источников можно использовать распределенные запросы. Это полезно, например, если нужно объединить структурированные подобным образом данные, относящиеся к разным серверам, каждый из которых хранит данные конкретного отдела организации.
Представления обычно используются для направления, упрощения и настройки восприятия каждым пользователем информации базы данных. Представления могут использоваться как механизмы безопасности, давая возможность пользователям обращаться к данным через представления, но не предоставляя им разрешений на непосредственный доступ к базовым таблицам, лежащим в основе представлений. Представления могут использоваться для обеспечения интерфейса обратной совместимости, моделирующего таблицу, которая существует, но схема которой изменилась. Представления могут также использоваться при прямом и обратном копировании данных в SQL Server для повышения производительности и секционирования данных.
Типы представлений
Кроме основных определяемых пользователем представлений, выполняющих стандартные роли, в SQL Server предусмотрены следующие типы представлений, которые соответствуют специальным назначениям в базе данных.
Индексированные представления
Индексированным называется материализованное представление. Это означает, что определение представления вычисляется, а результирующие данные хранятся точно так же, как и таблица. Индексировать представление можно, создав для него уникальный кластеризованный индекс. Индексированные представления могут существенно повысить производительность некоторых типов запросов. Индексированные представления эффективнее всего использовать в запросах, группирующих множество строк. Они не очень хорошо подходят для часто обновляющихся базовых наборов данных.
Системные представления
Системные представления предоставляют доступ к метаданным каталога. Системные представления можно использовать для получения сведений об экземпляре SQL Server или объектах, определенных в экземпляре. Например, получить сведения об определяемых пользователем базах данных, доступных в экземпляре, можно через представление каталога sys.databases. Дополнительные сведения см. в разделе Системные представления (Transact-SQL).
Общие задачи работы с представлениями
В следующей таблице приведены ссылки на общие задачи, связанные с созданием или изменением представления.
Получение сведений о представлении
Получить сведения об определении или свойствах представления в SQL Server можно с помощью SQL Server Management Studio или Transact-SQL. Возможность просмотреть определение представления может понадобиться, чтобы понять, как его данные извлекаются из исходных таблиц, или чтобы увидеть данные, определенные представлением.
Если имя объекта, на который ссылается представление, было изменено, необходимо изменить представление так, чтобы в его тексте использовалось новое имя. Следовательно, прежде чем переименовывать объект, следует отобразить зависимости объекта, чтобы определить, повлияет ли предполагаемое изменение на какие-либо представления.
В этом разделе
Перед началом работы
Получение сведений о представлении с помощью следующих средств:
Перед началом
безопасность
Permissions
Использование среды SQL Server Management Studio
Получение свойств представления с помощью обозревателя объектов
В обозревателе объектов нажмите на значок плюса рядом с базой данных, содержащей представление, свойства которого необходимо просмотреть, а затем нажмите значок плюса, чтобы развернуть папку Представления.
Щелкните правой кнопкой представление, свойства которого необходимо просмотреть, и выберите пункт Свойства.
В диалоговом окне Свойства представления отображаются следующие свойства:
База данных
Имя базы данных, содержащей это представление.
Server
Имя текущего экземпляра сервера.
Пользователь
Имя пользователя этого соединения.
Дата создания
Отображает дату создания представления.
имя;
Имя текущего представления.
Схема
Схема, которой принадлежит представление.
Системный объект
Указывает, является ли представление системным объектом. Возможные значения: True и False.
Значения NULL по стандарту ANSI
Указывает, был ли объект создан с параметром ANSI NULL.
Зашифрована
Указывает, зашифровано ли представление. Возможные значения: True и False.
Заключенный в кавычки идентификатор
Показывает, был ли объект создан с параметром «заключенный в кавычки идентификатор».
Привязка к схеме
Указывает, является ли представление привязанным к схеме. Возможные значения: True и False. Сведения о представлениях, привязанных к схеме, см. в подразделе SCHEMABINDING раздела CREATE VIEW (Transact-SQL).
Получение свойств представления с помощью конструктора представлений
Щелкните правой кнопкой мыши представление, свойства которого необходимо просмотреть, и выберите пункт Конструктор.
Правой кнопкой мыши щелкните пустое пространство в области диаграмм и выберите Свойства.
На панели Свойства отображаются следующие свойства.
(Имя)
Имя текущего представления.
Имя базы данных
Имя базы данных, содержащей это представление.
Описание
Краткое описание текущего представления.
Схема
Схема, которой принадлежит представление.
Имя сервера
Имя текущего экземпляра сервера.
Привязка к схеме
Предотвращает такие изменения пользователями базовых объектов, задействованных в представлении, в результате которых определение представления становится недействительным.
Детерминированное
Указывает, может ли тип данных для выбранного столбца быть точно определен
Различные значения
Указывает, что запрос будет отфильтровывать повторения в представлении. Этот параметр полезен при использовании только некоторых столбцов из таблицы, притом что эти столбцы могут содержать повторяющиеся значения, или если обработка соединения двух или более таблиц приводит к появлению повторяющихся строк в результирующем наборе. Выбор этого параметра эквивалентен вставке ключевого слова DISTINCT в инструкцию на панели SQL.
Расширение GROUP BY
Указывает, что для представлений доступны дополнительные параметры, основанные на агрегатных запросах.
Выводить все столбцы
Указывает, возвращаются ли в выбранном представлении все столбцы. Этот параметр задается во время создания представления.
Комментарий SQL
Показывает описание инструкций SQL. Чтобы просмотреть описание целиком или изменить его, выберите описание, а затем нажмите на меню с многоточием (…) справа от свойства. Комментарии могут содержать, например, сведения о том, кто использует это представление и когда оно используется.
(Верх)
Указывает, что представление будет иметь предложение TOP, возвращающее только первые n строк или первые n процентов строк из результирующего набора. По умолчанию представление возвращает первые 10 строк из результирующего набора. Это поле позволяет изменить число возвращаемых строк или указать другой процент.
Выражение
Указывает, какой процент (если для параметра Процент выбрано значение Да) или какое количество записей (если для параметра Процент выбрано значение Нет) возвращает представление.
(Обновление с использованием правил представления.)
Указывает, что все обновления и вставки в представлении будут преобразованы с помощью Microsoft Data Access Components (MDAC) в инструкции SQL, которые ссылаются на представление, а не в инструкции SQL, которые ссылаются непосредственно на базовые таблицы представления.
В некоторых случаях компоненты MDAC представляют операции обновления и вставки представления в виде операций обновления и вставки в базовых таблицах представления. Выбор Обновление с использованием правил представления позволяет гарантировать создание MDAC операций обновления и вставки для самого представления.
Параметр проверки
Указывает, что при открытии этого представления и изменении панели Результаты источник данных проверяет соответствие добавленных или измененных данных предложению WHERE определения представления. Если изменение не соответствует предложению WHERE, отобразится сообщение об ошибке с дополнительными сведениями.
Получение зависимостей представления
Щелкните правой кнопкой мыши представление, свойства которого необходимо просмотреть, и выберите пункт Просмотреть зависимости.
Выберите Объекты, зависящие от [имя_представления] для отображения объектов, которые ссылаются на представление.
Использование Transact-SQL
Получение определения и свойств представления
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте один из следующих примеров и вставьте его в окне запроса, а затем нажмите Выполнить.
Получение зависимостей представления
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
Представления (VIEW) в MySQL
В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
Ограничения представлений в MySQL
Создание представлений
view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении
CREATE VIEW v AS SELECT a.id, b.id FROM a,b;
CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;
CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;
CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator ‘+’ ) FROM table_name;
Алгоритмы представлений
Существует два алгоритма, используемых 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;
SELECT subject, param FROM v WHERE param>1000;
SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;
Если в определении представления используются групповые функции (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;
SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;
Выполнение этого запроса приводит к ошибке «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;
Обновляемость представлений
Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.
Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.
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
punbb > SELECT subject, num_views FROM topics WHERE subject= ‘test’ ;
+———+————+
| subject | num_views |
+———+————+
| test | 2003 |
+———+————+
1 rows IN SET (0.01 sec)
Однако, если мы попробуем установить значение 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’
Не все обновляемые представления позволяют добавление данных:
Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:
punbb > INSERT INTO v (forum_name) VALUES ( ‘TEST’ );
Query OK, 1 row affected (0.00 sec)
Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.
Создание SQL View с параметрами
У меня есть запрос SQL, который принимает параметры. Теперь, когда я пытаюсь включить этот запрос в view, я сталкиваюсь с ошибкой, потому что view не может содержать параметры так же, как SP или функция.
Следовательно, если бы мне пришлось создать view, который должен был содержать параметры, есть ли какой-то способ, которым это возможно?
5 ответов
У меня есть форма, которую пользователь заполняет, а затем направляется на view, содержащий введенные данные. Как я могу создать этот view так, чтобы он использовал переменные, инициализированные вводом пользователя. Как должен выглядеть view. Я читал о передаче параметров в view, но мне нужен.
У меня есть пользовательское выражение SQL с некоторыми параметрами. Я правильно добавил его в поле Explorer. Но я не смог найти пример синтаксиса для использования предопределенной параметризованной команды SQL с моими собственными параметрами, не являющимися параметрами по умолчанию. Например.
Создает виртуальную таблицу, содержимое которой (столбцы и строки) определяется запросом. Используйте эту инструкцию для создания view данных в одной или нескольких таблицах базы данных. Например, a view можно использовать для следующих целей:
Чтобы сфокусировать, упростить и настроить восприятие базы данных каждым пользователем.
В качестве механизма безопасности, позволяя пользователям получать доступ к данным через view, без предоставления пользователям разрешений на прямой доступ к базовым таблицам.
Чтобы обеспечить обратную совместимость интерфейса для эмуляции таблицы, схема которой изменилась.
Определенно нет. Рассмотрим View как таблицу, содержащую скомпилированный набор данных(хотя на самом деле view отличается от таблицы), и, следовательно, он никогда не может иметь никакого входного параметра, такого как sp или функция.
Вы, ребята, никогда этого не делали, так как VIEW-это совершенно другая таблица, созданная запросом, и вы можете включить параметры в эту query. it, которая также чертовски проста
создайте свой view, как и любой другой, с параметрами.
теперь назовите это view вот так:
Довольно простой и очень мощный, поскольку вы можете использовать view для предоставления информации UNALTERABLE подмножеству таблицы или объединению таблиц.
view-это NOT стол, и поэтому есть вещи, которые вы иногда не можете сделать.
Но это те же самые вещи, которые вы не можете сделать, когда вы формируете очень сложное соединение/объединение в query. been, используя приведенное выше с 1999 года, поэтому я знаю, что это работает.
Похожие вопросы:
У меня есть хранимая процедура SQL, которую мне нужно выполнить несколько раз с разными параметрами. Можно ли выполнить какой-то SQL-скрипт, который будет выполняться несколько раз с таким же.
Правильно ли я предполагаю, что невозможно создать view с параметрами? У меня есть запрос с подзапросом,и в его предложении where мне нужно указать параметр. Указание, где пункт view не поможет. Я.
Я работаю над кодированием первой техники с EF4.1, WCF webservice и Sql azure. Чтобы повысить производительность, я хотел создать View для извлечения данных на стороне сервера. но в соответствии с.
У меня есть форма, которую пользователь заполняет, а затем направляется на view, содержащий введенные данные. Как я могу создать этот view так, чтобы он использовал переменные, инициализированные.
У меня есть пользовательское выражение SQL с некоторыми параметрами. Я правильно добавил его в поле Explorer. Но я не смог найти пример синтаксиса для использования предопределенной.
У меня есть запрос SQL, хранящийся в таблице, содержащей имена параметров. Мне нужно знать, как правильно выполнить его в хранимой процедуре. Это мой код SQL в процедуре PROCEDURE [spMassUpdateSKUs].
Просто интересно, можно ли создать view из нескольких представлений? Я пытаюсь создать view (Data_extract_C) из 2 представлений (Data_extract_A и Data_Extract_B) с помощью Left Join. Прошел уже час.