Обработка баз данных на Visual Basic®.NET

Создание хранимой процедуры с помощью программы 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-узле Издательского дома "Вильяме" по адресу: находится текстовый файл 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×10⁹…2,1×10⁹).

Бизнес-ситуация 3.1: создание триггера для поиска созвучных слов

Брэд Джонс, президент компании Jones Novelties Incorporated, одобрил предварительную работу своего разработчика базы данных. Теперь он готов приступить к следующей проблеме, связанной с базой данных, и создать запросы для извлечения информации о клиентах по введенному созвучному имени (например, имена произносятся одинаково, но пишутся по-разному) с учетом случайных опечаток в нем. Как организовать поиск клиента, если вы не помните точное написание его имени, а только его произношение: Smith или Smyth, McManus или MacManus? Каждому человеку с необычной фамилией наверняка приходилось сталкиваться с ее неверной записью на слух.