Перекрестное соединение формирует результат, называемый декартовым произведение двух таблиц. В предложении JOIN каждая строка из первой таблицы соединяется с каждой строкой из второй таблицы. Этот тип соединения применяется лишь в исключительных ситуациях. Синтаксическая запись оператора CROSS JOIN такая же, как у других типов соединений за исключением того, что при этом соединении не требуется указывать условия соединения. Для того, чтобы получить выборку всех возможных комбинаций покупателей и продавцов, необходимо выполнить следующий запрос: [2]
SELECT B.Name AS BuyerName,
E.Name AS EmployeeName
FROM Buyer AS B CROSS JOIN Employee AS E
Представления
Некоторые из создаваемых вами инструкций SELECT используются для решения конкретной одноразовой задачи, а другие инструкции SELECT применяются в вашей рабочей среде снова и снова. Некоторые запросы, многократно используемые в вашей рабочей среде, содержат сложную бизнес-логику и сложный программный код на языке T-SQL, которые вы не хотите воспроизводить каждый раз, когда понадобится запрос. SQL сервер позволяет сохранять инструкцию SELECT в базе данных с помощью объекта, называемого представлением[1].
Представление – это простая инструкция SELECT с заданным именем, хранящаяся в базе данных. Главное преимущество представления состоит в том, что после создания оно действует как таблица в отношении всех остальных инструкций SELECT, которые вы захотите написать [1].
Синтаксис создания представления имеет следующий вид:
CREATE VIEW [schema_name.] view_name [ (column [,... n])]
[ WITH <view_attribute> [,... n]]
AS select_statement
[ WITH CHECK OPTION] [;]
Определяемая для представления инструкция SELECT может ссылаться на таблицы, другие представления и функции, но не может делать следующего:
- содержать предложение COMPUTE или COMPUTE BY;
- создавать постоянную или временную таблицу с помощью ключевого слова INTO;
- применять предложение OPTION;
- ссылаться на временную таблицу;
- ссылаться на переменную любого типа;
- содержать переложение ORDER BY, если не задан параметр TOP.
Представление может содержать множественные инструкции SELECT, пока вы применяете операторы UNION или UNION ALL. [3]
Хранимые процедуры
Хранимые процедуры представляют уровень абстракции, ограждающий приложение от низкоуровневой структуры базы данных. Будучи главной опорой любого приложения SQL Sеrver, хранимые процедуры позволяют изменять структуру баз данных и управлять производительностью без переписывания приложений и распространения их обновлений [2].
Хранимая процедура – это одна или несколько инструкций, которым дано имя и которые хранятся в базе данных. Почти любая команда языка T-SQL может быть включена в хранимую процедуру, что делает процедуры подходящими для приложений и для решения административных задач. В хранимых процедурах не могут применяться лишь следующие команды:
- USE <database_name>;
- SET SHOWPLAN_TEXT;
- SET SHOWPLAN_ALL;
- SET PARSEONLY;
- SET SHOWPLAN_XML;
- CREATE AGGREGATE;
- CREATE RULE;
- CREATE DEFAULT;
- CREATE SCHEMA;
- CREATE FUNCTION или ALTER FUNCTION;
- CREATE TRIGGER или ALTER TRIGGER;
- CREATE PROCEDURE или ALTER PROCEDURE;
- CREATE VIEW или ALTER VIEW.
При первом обращении к хранимой процедуре SQL Server генерирует планы компиляции и выполнения, которые хранятся в кэше запроса и повторно используются последующим выполнением. Следовательно, применяя хранимые процедуры, вы можете получить небольшой выигрыш в производительности, т.к. при последующих выполнениях хранимой процедуры исчезнет необходимость в синтаксическом анализе, компиляции и генерации плана запроса. Но главное назначение хранимой процедуры – создание защищенного слоя и прикладного интерфейса для ваших баз данных, которые отделяют приложение от изменения в структуре базы данных.
Далее приведена синтаксическая запись общего вида для создания хранимой процедуры [2].
CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[ {@parameter [type_schema_name.]data_type}
[VARYING] [= default] [OUT | OUTPUT] [READONLY]
] [,... n]
[ WITH <procedure_option> [,... n]]
[ FOR REPLICATION]
AS {<sql_statement> [;][,... n] | <method_specifier>} [;]
Содержание работы.
1. Создайте запрос, который будет формировать выборку данных о записях чека с включением данных о проданных товарах. В выборку включить поля: BillID, BillItemID, ProductID, Product.Name, Number, Price, Cost, Bill.Date, BillItem.Date. Используйте внутреннее соединение таблиц (INNER JOIN). Примените условие фильтрации согласно своему варианту.
2. Создайте запрос, который будет формировать выборку данных с информацией о чеках, проданных товарах и покупателях со следующими полями: BillID, BillItemID, ProductID, BuyerID, Product.Name, Number, Price, Cost, Bill.Date, BillItem.Date, Buyer.Name, Photo. Используйте внешнее левое соединение от таблицы Bill к таблице Buyer (LEFT JOIN).
Объясните, что делает полученный запрос? Почему в этом случае используется внешнее соединение, а не внутреннее, как в предыдущем примере?
3. Создайте запрос, который будет формировать выборку данных с информацией о чеках, проданных товарах, покупателях и сотрудниках, расширив перечень полей в запросе из п.2. Используйте внешнее правое соединение от таблицы Bill к таблице Employee (RIGHT JOIN).
Объясните, что делает данный запрос?
4. Создайте запрос, который будет формировать выборку с данными по чекам и покупателям со следующими полями: BuyerID, BillID, Name, Date. Ограничьте выборку данных, используя фильтр по дате в таблице чеков (используйте фильтр в разделе WHERE). Используйте внешнее полное соединение таблиц Buyer и Bill (FULL JOIN).
Объясните, что делает этот запрос?
5. Создайте запрос, который будет формировать выборку, содержащую все возможные пары (сотрудник, покупатель) со следующими полями: EmployeeID, BuyerID, Buyer.Name, Employee.Name, Employee.Post. Используйте перекрестное соединение таблиц Employee и Buyer (CROSS JOIN).
6. Сохраните запрос из пункта 1 в виде представления. В среде Microsoft SQL Management Studio в разделе Views используйте команду New View… Назовите представление BillAndProduct. Проверьте результат командой: SELECT * FROM BillAndProduct.
7. Сохраните запросы из пунктов 2 и 3 аналогичным образом.
8. Запрос из пункта 4 сохраните в виде хранимой процедуры с одним параметром – датой. Для создания процедуры используйте команду CREATE PROCEDURE или воспользуйтесь командой New Stored Procedure в разделе Programmability/Stored Procedures. Назовите хранимую процедуру BillAndBuyer. Проверьте работоспособность процедуры, используя ее вызов: exec BillAndProduct @date=’2011-07-05’.
9. Сохраните запрос из пункта 5 в хранимой процедуре.
10. Подготовьте материал для включения в отчетную презентацию по курсу Базы данных: специальный курс.
Варианты заданий:
1. Фильтр по отдельному названию товара. Используйте одиночное условие фильтрации «=». Название товара выберите самостоятельно.
2. Фильтр по множеству названий товаров. Используйте условие фильтрации in. Перечень товаров выберите самостоятельно.
3. Фильтр по отдельной дате. Используйте одиночное условие фильтрации «=». Дату чека выберите самостоятельно.
4. Фильтр с ограничением диапазона по датам чека. Используйте фильтрацию по диапазону between или совместно «<=», «=>». Диапазон дат выберите самостоятельно.
5. Фильтр по полуоткрытому интервалу дат чека. Используйте фильтрацию по условию «<=» или «=>». Диапазон дат выберите самостоятельно.