Технология Microsoft ADO.NET


Создание хранимых процедур в SQL Query Analyzer - часть 2


USE Northwind exec sp_stored_procedures

Можно было, конечно, указать название и в выпадающем списке

. База Northwind содержит 38 хранимых процедур (рис. 5.3), большая часть из которых - системные. Для просмотра списка в других базах следует вызвать для них название этой же процедуры.

Вывод списка хранимых процедур базы данных Northwind

Рис. 5.3.  Вывод списка хранимых процедур базы данных Northwind

Перейдем к созданию своих собственных процедур. Скопируйте базу BDTur_firm.mdb из лекции 1, назовите ее "BDTur_firm2.mdb". Открываем ее в Microsoft Access и в названиях таблиц и полей удаляем все пробелы. Например, таблица "Информация о туристах" будет теперь называться так: "Информацияотуристах", а поле "Код туриста" станет полем "Кодтуриста". Затем конвертируем базу в формат Microsoft SQL и присоединяем ее к локальному серверу2). Запускаем SQL Query Analyzer, открываем чистый бланк и вводим запрос3):

create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы

Здесь create procedure - оператор, указывающий на создание хранимой процедуры, proc1 - ее название, далее после оператора as следует обычный SQL-запрос. Запускаем его - появляется сообщение:

The COMMAND(s) completed successfully.

Это означает, что мы все сделали правильно и команда создала процедуру proc1. Для просмотра результата вызываем ее:

exec proc1

Появляется уже знакомое нам извлечение всех записей таблицы "Туристы" со всеми записями (рис. 5.4):

Результат запуска процедуры proc1

Рис. 5.4.  Результат запуска процедуры proc1

Как видите, создание содержимого хранимой процедуры не отличается ничем от создания обычного SQL-запроса. В таблице 5.1 приведены примеры хранимых процедур:

Таблица 5.1. Примеры хранимых процедур

№SQL-конструкция для созданияКоманда для извлеченияОписание
1

create procedure proc1 as select Кодтуриста, Фамилия, Имя, Отчество from Туристы

exec proc1Вывод всех записей таблицы Туристы
Результат запуска
2

create procedure proc2 as select top 3 Фамилия from туристы

exec proc2

Вывод первых трех значений поля Фамилия таблицы Туристы

Результат запуска
3

create procedure proc3 as select * from туристы where Фамилия = 'Андреева'

exec proc3

Вывод всех полей таблицы Туристы, содержащих в поле Фамилия значение " Андреева "

Результат запуска
4

create procedure proc4 as select count (*) from Туристы

exec proc4

Подсчет числа записей таблицы Туристы

Результат запуска
5

create procedure proc5 as select sum(Сумма) from Оплата

exec proc5

Подсчет значений поля Сумма таблицы Оплата

Результат запуска
6

create procedure proc6 as select max(Цена) from Туры

exec proc6

Вывод максимального значения поля Цена таблицы Туры

Результат запуска
7

create procedure proc7 as select min(Цена) from Туры

exec proc7

Вывод минимального значения поля Цена таблицы Туры

Результат запуска
8

create procedure proc8 as select * from Туристы where Фамилия like '%и%'

exec proc8 Вывод всех записей таблицы Туристы, содержащих в значении поля Фамилия букву "и" (в любой части слова)
Результат запуска
9

create procedure proc9 as select * from Туристы inner join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста

exec proc9

Операция inner join объединяет записи из двух таблиц, если поле (поля), по которому связаны эти таблицы, содержат одинаковые значения. Общий синтаксис выглядит следующим образом: from таблица1 inner join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2

Результат запуска
10

create procedure proc10 as select * from Туристы left join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста

exec proc10

Прежде чем создать эту процедуру и затем ее извлечь, запускаем программу SQL Server Enterprise Manager, выделяем таблицу "Туристы" базы данных " BDTur_firm2". Щелкаем на ней правой кнопкой и в появившемся меню выбираем Open Table - Return all rows. Теперь добавляем запись - "Корнеев Глеб Алексеевич". В результате в таблице "Туристы" у нас получилось 6 записей, а в связанной с ней таблице "Информацияотуристах" - 5. В SQL Query Analyzer создаем хранимую процедуру и запускаем ее. Операция left join используется для создания так называемого левого внешнего соединения. С помощью объединения выбираются все записи первой (левой) таблицы, даже если они не соответствуют записям во второй (правой) таблице. Общий синтаксис имеет вид: from таблица1 left join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2.

Здесь в таблице "Информацияотуристах" нет связанной записи для туриста "Корнеев Глеб Алексеевич", поэтому соответствующие поля заполняются значениями null

Результат запуска
11

create procedure proc11 as select * from Туристы right join Информацияотуристах on Туристы.КодТуриста= Информацияотуристах.КодТуриста

exec proc11

Перед созданием этого запроса нам снова придется изменить таблицы. В SQL Server Enterprise Manager удаляем шестую запись в таблице "Туристы", добавляем шестую запись в таблицу " Информацияотуристах"(значения полей - см. на рисунке). Операция right join используется для создания правого внешнего соединения. С его помощью выбираются все записи второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице. Общий синтаксис имеет вид: from таблица1 right join таблица2 on таблица1.поле1 оператор_сравнения таблица2.поле2.

Результат запуска
<


Начало  Назад  Вперед



Книжный магазин