Компания Microsoft заявила, что будущие версии SQL Server позволят создавать хранимые процедуры на любом языке, который совместим с платформой .NET (например, Visual Basic .NET), а не только на языке Transact SQL. Это позволит разработчикам легко переходить от одного аспекта программирования к другому, не изучая синтаксиса нового языка программирования.
В этом разделе не ставится задача подробного описания всех команд, которые доступны программисту при создании хранимых процедур, а только дается общее описание принципов работы хранимых процедур, достоинств и способов применения в приложениях на основе SQL Server.
Создание хранимых процедур с помощью программы SQL Server Enterprise Manager
Для создания хранимой процедуры с помощью SQL Server Enterprise Manager выполните ряд действий.
1. В окне Microsoft SQL Servers программы SQL Server Enterprise Manager щелкните правой кнопкой мыши на папке Stored Procedures рабочей базы данных. В нашем примере это база данных pubs.
2. Из контекстного меню выберите команду New Stored Procedure (Создать хранимую процедуру). Появится диалоговое окно Stored Procedure Properties (Свойства хранимой процедуры).
НА ЗАМЕТКУ
Хотя диалоговое окно Stored Procedure Properties выглядит как окно с фиксированными размерами, его размер можно изменить, перетаскивая один из краев или уголков окна, как это делается с другими диалоговыми окнами. Таким образом можно изменить размер окна для более удобного отображения всего введенного текста.
3. Введите текст хранимой процедуры, который показан на рис. 3.18.
РИС. 3.18. Диалоговое окно создания новой хранимой процедуры Stored Procedure Properties в программе SQL Server Enterprise Manager
4. После выполнения этих действий щелкните на кнопке OK в нижней части диалогового окна Stored Procedure Properties.
Запуск хранимых процедур в окне программы SQL Query Analyzer
Для запуска хранимых процедур (а также представлений и других команд SQL) можно воспользоваться программой SQL Query Analyzer. Таким образом можно протестировать созданную хранимую процедуру или представление. Для запуска хранимой процедуры в окне программы SQL Query Analyzer выполните приведенные ниже действия.
1. В окне программы SQL Server Enterprise Manager выберите команду Tools→SQL Query Analyzer (Сервис→SQL Query Analyzer); запустится программа SQL Query Analyzer.
2. В диалоговом окне Query введите имя хранимой процедуры, которую нужно запустить. Например, для запуска хранимой процедуры, описанной в предыдущем разделе, введите procEmployeesSorted.
3. Выполните запрос, нажав клавишу <F5> или щелкнув на кнопке Execute Query. Хранимая процедура будет выполнена и возвратит (при наличии данных в таблице) результирующий набор во вкладке Grids (или во вкладке Results).
4. В окне Microsoft SQL Servers программы SQL Server Enterprise Manager выберите папку Stored Procedures для проверки наличия только что созданной хранимой процедуры. Здесь для обновления содержимого консольного окна, возможно, понадобится щелкнуть на кнопке Refresh (Обновить).
Конечно, хранимую процедуру можно запустить, непосредственно запуская программу SQL Query Analyzer, и для этого совсем не обязательно запускать ее изнутри программы SQL Server Enterprise Manager.
Создание хранимой процедуры с помощью программы SQL Query Analyzer
Процесс создания хранимой процедуры с помощью SQL Query Analyzer практически не отличается от аналогичного процесса в окне программы SQL Server Enterprise Manager.
НА ЗАМЕТКУ
Убедитесь в том, что хранимая процедура создается в базе данных Novelty. Довольно часто разработчики забывают переключиться на нужную базу данных (с помощью команды USE или списка баз данных в программе SQL Query Analyzer) до выполнения команд по отношению к ней. Создание хранимых процедур с помощью программы SQL Server Enterprise Manager только усугубляет последствия этой ошибки.
Для создания хранимой процедуры с помощью программы SQL Query Analyzer используйте команду CREATE PROCEDURE.
1. В окне программы SQL Query Analyzer введите следующий код:
CREATE PROCEDURE GetCustomerFromID @custID int
AS
SELECT * FROM tblCustomer
WHERE ID = @custID
2. В этом коде создается хранимая процедура GetCustomerFromID, которая принимает аргумент @custID и возвращает запись, в поле ID которой находится значение, совпадающее со значением аргумента @custID (поскольку поле ID в таблице tblCustomer является первичным ключом, эта процедура всегда будет возвращать либо нуль, либо одну запись).
3. Выполните введенную команду для создания хранимой процедуры.
4. Теперь необходимо протестировать созданную хранимую процедуру в окне Query. Для этого попробуйте извлечь запись из таблицы, введя следующую команду:
GetCustomerFromID 22
5. Сервер вернет запись клиента с идентификационным номером 22 (рис. 3.19). Использование другого идентификационного номера в качестве параметра этой хранимой процедуры позволит вернуть другую запись с данными о другом клиенте.
Созданная процедура вернет данные только в том случае, если они есть в таблице.
РИС. 3.19. Запись клиента возвращается с помощью хранимой процедуры GetCustomerFromID
НА ЗАМЕТКУ
Теперь можно приступить к загрузке данных в таблицу. В прилагаемых к книге файлах (их вы можете найти на Web-узле Издательского дома "Вильяме" по адресу: http://www.williamspublishing.com) находится текстовый файл CustomerData.sql, который предназначен для загрузки данных о клиенте в базу данных Novelty. Кроме него, этому адресу располагаются и другие сценарии загрузки данных в таблицы базы данных Novelty.
Отображение текста существующих представлений или хранимых процедур
Для отображения кода представлений или хранимых процедур можно использовать хранимую процедуру sp_helptext. Для того чтобы отобразить эти данные, необходимо ввести команду sp_helptext, а затем имя интересующего вас объекта базы данных. После этого SQL Server возвратит полный текст представления или хранимой процедуры. Рассмотрим пример отображения текста представления Employee_view, создание которого описано в предыдущих разделах.
1. В окне Query программы SQL Query Analyzer введите следующую команду:
sp_helptext Employee_view
2. Выполните введенную команду, нажав клавишу <F5> или щелкнув на кнопке Execute Query панели инструментов программы SQL Query Analyzer. В результате выполнения этой команды SQL Server вернет текст хранимой процедуры во вкладке Grids (рис. 3.20).
РИС. 3.20. Отображение текста представления с помощью хранимой процедуры sp_helptext
Создание триггеров
Триггер (trigger) — это особый тип хранимой процедуры, который выполняется при доступе к данным в таблице. Понятие триггера в SQL Server аналогично понятию процедуры события в Visual Basic: триггер выполняется при обновлении, удалении или вставке данных в таблицу.
Триггер обычно используется при сложном доступе к данным, например: сохранение в файле журнала информации об обновлениях базы данных или же создание в новой записи сложного значения поля (по умолчанию) на основе запроса к одной или нескольким таблицам.
Не нужно использовать триггеры для поддержки ссылочной целостности; для этого лучше обратиться к встроенным средствам SQL Server. Старайтесь при работе с базами данных использовать средства, предоставляемые SQL Server.
Например, можно использовать триггеры, чтобы обеспечить уникальное значение в столбце для сохранения первичного ключа. Этот способ характерен для программы Microsoft Access Upsizing Tools; в ней генерируется случайное значение первичного ключа каждой записи с помощью триггера. (Для этого можно использовать уникальное поле, как уже упоминалось ранее в главе.) Пример такого кода генерации первичного ключа приведен ниже.
CREATE TRIGGER tblCustomer_ITrig ON dbo.tblCustomer
FOR INSERT
AS
DECLARE @randc int, @newc int
SELECT @randc = (SELECT convert(int, rand () * power(2, 30)))
SELECT @newc = (SELECT ID FROM inserted)
UPDATE tblCustomer SET ID = @randc WHERE ID = @newc
НА ЗАМЕТКУ
Для корректной работы каждого из этих триггеров и обновления идентификационного поля нужно переустановить значения этого поля таким образом, чтобы оно не считалось идентификационным. Для этого перейдите в диалоговое окно Design Table и задайте для свойства Identity(Идентификационное поле) значение No.
Создание первичного ключа записи на основе случайного значения – самый простой способ уникальной идентификации записи. Однако такой способ имеет два недостатка.
Во-первых, первичный ключ генерируется в произвольном порядке. В некоторых случаях это не очень существенная проблема, однако если использовать первичный ключ для нумерации выписываемых счетов, то может случиться так, что счет с номером 20010 будет выписан раньше, чем счет с номером 20009.
Во-вторых, существует потенциальная проблема, состоящая в том, что сгенерированный уникальный ключ на самом деле не будет уникальным, т.е. при создании ключа не выполняется проверка существования записи с таким же значением первичного ключа. Конечно, вероятность того, что будет сгенерировано два одинаковых значения, очень мала, но она все же существует (тип данных integer в SQL Server имеет длину 4 бита, т.е. диапазон возможных значений: -2,1×109…2,1×109).
