Бизнес-ситуация 3.2: SQL-сценарий для создания базы данных
Для запуска примеров кода требуется запустить сценарий NoveltyDB.sql, который предназначен для создания и запуска базы данных Novelty. (Наверное, вы уже заглядывали в него и интересовались тем, что он делает.)
Поскольку структура базы данных может изменяться в процессе создания приложения, разработчику нужно периодически создавать сценарий для документирования текущей структуры базы данных, а также для автоматического создания (или воссоздания) базы данных. Хотя разработчик может обладать полным контролем и вручную создавать такие сценарии, но гораздо лучше применять для этого программу SQL Server Enterprise Manager. По крайней мере она прекрасно подходит для создания основы сценария и его последующего редактирования. Этот подход позволяет гораздо быстрее добиться решения поставленной задачи с меньшей вероятностью возникновения ошибок.
Разработчик базы данных для компании Jones Novelties Incorporated решил применить этот подход для создания корпоративной базы данных. Созданный им сценарий позволяет просто инсталлировать объекты базы данных на компьютере, на котором она даже не определена. Иначе говоря, сценарий выполняет все необходимые действия для создания и инсталляции базы данных и ее объектов на совершенно "пустом" компьютере, что обычно требуется при инсталляции системы на новом рабочем месте. На рис. 3.28 показана схема базы данных Novelty в диалоговом окне программы SQL Server Enterprise Manager.
РИС. 3.28. Структура базы данных Novelty компании Jones Novelties Incorporated в диалоговом окне программы SQL Server Enterprise Manager
Сценарий создания базы данных выполняется в программе SQL Query Analyzer. Чтобы сократить до минимума ошибки, этот сценарий удаляет все прежние объекты базы данных. Это значит, что при любом изменении структуры базы данных в режиме конструктора целостность базы данных может быть восстановлена с самого начала просто за счет повторного выполнения сценария. Этот подход гарантирует, что все изменения структуры базы данных учитываются каждый раз при ее повторном создании.
Недостатком этого подхода является то, что если разработчик будет недостаточно аккуратен, то сценарий удалит все таблицы в базе данных вместе с содержащимися в них данными. Поэтому все такие сценарии нужно удалить после переноса создаваемой системы на рабочее место. При этом, как обычно, большое значение имеет резервное копирование базы данных еще до внесения в нее каких-либо существенных изменений для восстановления в случае непредвиденных сбоев.
Для генерации сценариев создания базы данных для компании Jones Novelties Incorporated выполните перечисленные ниже действия.
1. Откройте программу SQL Server Enterprise Manager и щелкните правой кнопкой мыши на базе данных Novelty.
2. Выберите команду All Tasks→Generate SQL Script (Все задачи→Генерировать сценарий SQL) из контекстного меню для открытия диалогового окна Generate SQL Scripts.
3. Щелкните на кнопке Show All для отображения всех доступных объектов выбранной базы данных.
4. Теперь у вас есть возможность выбора одного или нескольких объектов сценария. Установите флажок Script all objects (Включить в сценарий все объекты), чтобы выбрать все объекты базы данных, как показано на рис. 3.29.
РИС. 3.29. Выбор всех объектов базы данных Novelty для включения в сценарий в диалоговом окне Generate SQL Scripts
5. Выберите вкладку Formatting (Форматирование). Помимо установленных по умолчанию флажков параметров установите флажок Include descriptive headers in the Script files (Включить описательные заголовки в файлы сценариев).
СОВЕТ
Не забудьте установить во вкладке Formatting флажок Include descriptive headers in the script files, потому что он приводит к автоматическому созданию строки заголовка с указанием времени и даты генерации сценария. Это очень полезное усовершенствование позволит вам впоследствии легко определить самую последнюю (или наиболее правильную) версию сценария.
6. Если вам нужен сценарий только для создания объектов базы данных, то на этом можно остановиться. Однако для генерации сценария создания физической базы данных нужно продолжить работу, выбрав вкладку Options (Параметры).
7. В группе параметров Security Scripting Options (Параметры сценария безопасности) установите флажок Script (Включить в сценарий базу данных). Кроме него, можно также установить флажки других параметров для пользователей базы данных, ролей и учетных записей, но здесь мы этого делать не будем, считая это отдельной задачей администрирования базы данных.
8. В группе параметров Table Scripting Options (Параметры сценария таблицы) установите флажки Script indexes (Включить в сценарий индексы), Script triggers (Включить в сценарий триггеры) и Script PRIMARY keys, FOREIGN keys, defaults, and check constraints (Включить в сценарий первичные ключи, внешние ключи, принимаемые по умолчанию значения и ограничения целостности), как показано на рис. 3.30.
РИС. 3.30. Вкладка Options диалогового окна Generate SQL Scripts
9. Щелкните на кнопке OK для запуска процесса генерации сценария. При этом на экране появится стандартное диалоговое окно Save As (Сохранить как), в котором следует ввести имя файла (с расширением .sql) и указать путь к нему. Сделайте это и щелкните на кнопке Save. После успешного создания сценария на экране появится диалоговое окно с сообщением об этом. Щелкните на кнопке OK для его удаления.
НА ЗАМЕТКУ
Созданный вами сценарий может отличаться от приведенного ниже из-за использования другого компьютера, сервера или параметров базы данных. Однако сейчас об этом не следует беспокоиться.
Приведенный далее сценарий содержит множество строк с командой GO, которая предназначена для принудительного выполнения предыдущих команд еще до перехода к следующим командам сценария. Например, довольно часто можно встретить такие блоки команд:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
или
SET QUOTED IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Они гарантируют корректную (временную) конфигурацию базы данных для выполнения нужной операции и переустановку параметров для выполнения следующих операций.
Далее созданный сценарий предлагается в виде отдельных фрагментов, но на практике его рекомендуется создавать и применять в виде целого файла. В таком случае после создания рабочего сценария можно вставить его текст в окно Query программы SQL Query Analyzer (либо загружая его из тестового файла, либо копируя и вставляя с помощью буфера обмена) и щелкнуть на кнопке Execute для запуска. Можно также использовать отдельные фрагменты сценария или создать на основе единого сценария несколько отдельных сценариев и выполнять каждый из них. При создании сценариев SQL с помощью программы SQL Query Analyzer отдельные команды SQL можно редактировать в диалоговом окне Query, тестировать и проверять результаты их выполнения, а затем сохранять их в файле после завершения этих операций.
Сначала нужно создать физическую базу данных, с помощью сценария, который представлен в листинге 3.1.
Листинг 3.1. Сценарий создания физической базы данных Novelty
/****** Object: Database Novelty Script Date: 10-Jul-02 12:41:09 PM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Novelty')
DROP DATABASE [Novelty]
GO
CREATE DATABASE [Novelty] ON (NAME = N'novelty_Data',
FILENAME = N'c:\program files\microsoft sql server\mssql\data\Novelty_Data.mdf',
SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'novelty_Log',
FILENAME = N'c:\program files\microsoft sql server\mssql\data\Novelty_Log.LDF', SIZE = 3, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AI
GO
Перед попыткой создания любого нового объекта сценарий всегда проверяет его существование, а затем удаляет его. Поэтому в данном сценарии после проверки и удаления существующей базы данных Novelty создается новая база данных.
Код в листинге 3.2 задает параметры базы данных, описание которых приводится в официальной справочной документации SQL Server Books Online.
Листинг 3.2. Сценарий указания параметров базы данных Novelty
exec sp_dboption N'Novelty', N'autoclose', N'false'
GO
exec sp_dboption N'Novelty', N'bulkcopy', N'false'
GO
exec sp_dboption N'Novelty', N'trunc.log', N'false'
GO
exec sp_dboption N'Novelty', N'torn page detection', N'true'
GO
exec sp_dboption N'Novelty', N'read only', N'false'
GO
exec sp_dboption N'Novelty', N'dbo use', N'false'
GO
exec sp_dboption N'Novelty', N'single', N'false'
GO
exec sp_dboption N'Novelty', N'autoshrink', N'false'
GO
exec sp_dboption N'Novelty', N'ANSI null default', N'false'
GO
exec sp_dboption N'Novelty', N'recursive triggers', N'false'
GO
exec sp_dboption N'Novelty', N'ANSI nulls', N'false'
GO
exec sp_dboption N'Novelty', N'concat null yields null', N'false'
GO
exec sp_dboption N'Novelty', N'cursor close on commit', N'false
GO
exec sp_dboption N'Novelty', N'default to local cursor', N'false'
GO
exec sp_dboption N'Novelty', N'quoted identifier', N'false'
GO
exec sp_dboption N'Novelty', N'ANSI warnings', N'false'
GO
exec sp_dboption N'Novelty', N'auto create statistics', N'true'
GO
exec sp_dboption N'Novelty', N'auto update statistics', N'true'
GO
Теперь после создания базы данных можно приступить к работе с ней, т.е. начать выполнять команды по отношению к ней. Для выполнения команд SQL с другой базой данных NoveltyTest (например, для ее тестирования на том же сервере) нужно просто указать другую базу данных с помощью команды USE.
USE [NoveltyTest]
GO
Далее в сценариях создания объектов базы данных также проверяется их наличие и удаление (если они имеются). Это относится к ограничениям, триггерам, хранимым процедурам, представлениям и таблицам. Этот порядок имеет большое значение, потому что таблицу нельзя удалить, если существует какой-то из связанных с ней объектов. Код выполнения этих операций приведен в листинге 3.3.
Листинг 3.3. Сценарий удаления существующих объектов в базе данных Novelty
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblOrder_tblCustomer]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblOrder] DROP CONSTRAINT FK_tblOrder_tblCustomer
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblEmployee_tblDepartment]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEmployee] DROP CONSTRAINT FK_tblEmployee_tblDepartment
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblOrderItem_tblInventory]')
and OBJECTPROPERTY(id,N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblOrderltem] DROP CONSTRAINT FK_tblOrderItem_tblInventory
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblOrderItem_tblOrder]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblOrderltem] DROP CONSTRAINT FK_tblOrderItem_tblOrder
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblCustomer_tblRegion] ')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblCustomer]
DROP CONSTRAINT FK_tblCustomer_tblRegion
GO
/****** object: Trigger dbo.trCustomerI Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trCustomerI]')
and OBJECTPROPERTY(id,N'IsTrigger') =1)
drop trigger [dbo].[trCustomerI]
GO
/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteEmployee]')
and OBJECTPPOPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteEmployee]
GO
/****** object: Stored Procedure Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects dbo.sysobjects where id = object_id(N'[dbo].[GetCustomerFromID]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCustomerFromID]
GO
/****** Object: Stored Procedure dbo.insertEmployee Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertEmployee]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertEmployee]
GO
/****** Object: Stored Procedure dbo.InsertEmployeeOrg Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id (N'[dbo].[InsertEmployeeOrg]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertEmployeeOrg]
GO
/****** Object: Stored Procedure dbo.LastNameLookup Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LastNameLookup]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LastNameLookup]
GO
/****** object: Stored Procedure Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectEmployees]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectEmployees]
GO
/****** object: Stored Procedure dbo Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployee]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateEmployee]
GO
/****** object: Stored Procedure dbo.procEmployeesSorted Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procEmployeesSorted]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procEmployeesSorted]
GO
/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmployeeDepartment_view]')
and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[EmployeeDepartment_view]
GO
/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryEmployee_view]')
and OBJECTPROPERTY(id,N'IsView') = 1)
drop view [dbo].[qryEmployee_view]
GO
/****** Object: Table [dbo].[tblCustomer] Script Date:10-Jul-0212:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCustomer]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCustomer]
GO
/****** Object: Table [dbo].[tblDepartment] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDepartment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDepartment]
GO
/****** Object: Table [dbo].[tblEmployee] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEmployee]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO
/****** Object: Table [dbo].[tblInventory] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N' [dbo].[tblInventory]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInventory]
GO
/****** Object: Table [dbo].[tblOrder] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOrder]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOrder]
GO
/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblOrderItem]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblOrderltem]
GO
/****** object: Table [dbo].[tblRegion] Script Date: 10-Jul-02 12:41:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegion]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegion]
GO
Теперь можно приступать к созданию новых объектов базы данных. Начнем с создания таблиц; сценарий этих операций приведен в листинге 3.4.
Листинг 3.4. Сценарий создания таблиц базы данных Novelty
/****** Object: Table [dbo].[tblCustomer] Script Date: 10-Jul-02 12:41:10 PM ******/
CREATE TABLE [dbo].[tblCustomer] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[FirstName] [varchar] (20) COLLATE Latin1_General_CI_AI NULL,
[LastName] [varchar] (30) COLLATE Latinl_General_CI_AI NULL,
[Company] [varchar] (50) COLLATE Latin1_General_CI_AI NULL,
[Address] [varchar] (50) COLLATE Latin1_General_CI_AI NULL,
[City] [varchar] (30) COLLATE Latin1_General_CI_AI_NULL,
[State] [char] (2) COLLATE Latin1_General_CI_AI_NULL,
[PostalCode] [varchar] (9) COLLATE Latin1_General_CI_AI NULL,
[Phone] [varchar] (15) COLLATE Latinl_General_CI_AI NULL,
[Fax] [varchar] (15) COLLATE Latinl_General_CI_AI NULL,
[Email] [varchar] (100) COLLATE Latinl_General_CI_AI NULL,
[LastNameSoundex] [varchar] (4) COLLATE Latinl_General_CI_AI NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo].[tblDepartment] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblDepartment] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[DepartmentName] [varchar] (75) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblEmployee] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblEmployee] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AI NOT NULL,
[LastName] [varchar] (70) COLLATE Latin1_General_CI_AI NOT NULL,
[DepartmentID] [int] NULL,
[Salary] [money] NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo]. [tblInventory] Script Date: 10-Jul-02 12:41:11 PM ******/
CREATE TABLE [dbo].[tblInventory] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[ProductName] [varchar] (75) COLLATE Latin1_General_CI_AI NOT NULL
[WholesalePrice] [money] NULL,
[RetailPrice] [money] NULL,
[Description] [ntext] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrder] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE [dbo].[tblOrder] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[CustomerlD] [int] NULL,
[OrderDate] [datetime] NULL,
[Amount] [money] NULL
) ON [PRIMARY]
GO
/****** object: Table [dbo].[tblOrderItem] Script Date: 10-Jul-02 12:41:12 PM ******/
CREATE TABLE] (
[ID] [int] IDENTITY (1, 1) NOT NULL, [OrderID] [int] NOT NULL,
[int] NOT NULL, [Quantity] [int] NULL,
[Cost] [money] NULL
) ON [PRIMARY]
GO
/******* object: Table [dbo].[tblRegion] Script Date: 10-Jul -02 12:41:12 PM *******/
CREATE TABLE [dbo]. [tblRegion] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[State] [char] (2) COLLATE Latin1_General_CI_AI NOT NULL,
[RegionName] [varchar] (25) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]
GO
Далее следует создать ограничения, как показано в листинге 3.5.
Листинг 3.5. Сценарий создания ограничений для базы данных Novelty
ALTER TABLE [dbo].[tblCustomer] WITH NOCHECK ADD
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDepartment ] WITH NOCHECK ADD
CONSTRAINT [tblDepartment_IDPK] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo]. [tblEmployee] WITH NOCHECK ADD
CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblInventory] WITH NOCHECK ADD
CONSTRAINT [PK_tblInventory] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblOrder] WITH NOCHECK ADD
CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblOrderltem] WITH NOCHECK ADD
CONSTRAINT [PK_tblOrderItem] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo]. [tblRegion] WITH NOCHECK ADD
CONSTRAINT [PK_tblRegion] PRIMARY KEY CLUSTERED (
[ID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_tblRegion] ON [dbo]. [tblRegion] ([State]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCustomer] ADD
CONSTRAINT [FK_tblCustomer_tblRegion] FOREIGN KEY ([State])
references [dbo].[tblRegion] (
[State]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblEmployee] ADD
CONSTRAINT [FK_tblEmployee_tblDepartment] FOREIGN KEY ([DepartmentID])
REFERENCES [dbo].[tblDepartment] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo]. [tblOrder] ADD
CONSTRAINT [FK_tblOrder_tblCustomer] FOREIGN KEY ( [CustomerID])
REFERENCES [dbo].[tblCustomer] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblOrderItem] ADD
CONSTRAINT [FK_tblOrderItem_tblInventory] FOREIGN KEY ([ItemID])
REFERENCES [dbo].[tblInventory] ([ID])
ON DELETE CASCADE ON UPDATE CASCADE,
constraint [FK_tblOrderItem_tblOrder] foreign key ([OrderID])
REFERENCES [dbo].[tblOrder] ([ID])
ON DELETE CASCADE ON UPDATE CASCADE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Наконец, для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.
Листинг 3.6. Сценарий создания представлений, хранимых процедур и триггеров
/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE view EmployeeDepartment_view as
select e.ID, FirstName, LastName, DepartmentName
from tblEmployee e, tblDepartment t
where e.DepartmentID = t.ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:13 PM ******/
create view qryEmployee_view as
SELECT ID, FirstName, LastName, DepartmentID from tblEmployee
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.DeleteEmployee (@Original_ID int)
AS
SET NOCOUNT OFF;
DELETE FROM tblEmployee WHERE (ID = @Original_ID)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.GetCustomerFromID Script Date: 10-Jul-02 12:41:13 PM ******/
create procedure GetCustomerFromID @custID int
as
select * from tblCustomer where ID = @custID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.InsertEmployee (
@FirstName varchar(50),
@LastName varchar(70),
@DepartmentID int,
@Salary money)
AS
SET NOCOUNT OFF;
if (@Salary = 0 or @Salary is null) begin
-– Do complicated salary calculations
set @Salary = @DepartmentID * 10000
end
INSERT INTO tblEmployee(FirstName, LastName, Salary) VALUES
(@FirstName, @LastName, @DepartmentID, @Salary)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.InsertEmployeeOrg Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.InsertEmployeeOrg (
@FirstName varchar(50),
@LastName varchar(70),
@DepartmentID int,
@Salary money
)
AS
SET NOCOUNT OFF;
INSERT INTO tblEmployee(FirstName, LastName, DepartmentID, Salary) VALUES
(@FirstName, @LastName, @DepartmentID, @Salary)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.LastNameLookup Script Date: 10-Jul-02 12:41:13 PM ******/
create proc LastNameLookup
@name varchar(40) as
select * from tblCustomer where soundex(gname) = LastNameSoundex
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.SelectEmployees Script Date: 10-Jul-02 12:41:13 PM ******/
create procedure dbo.SelectEmployees
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department ID, Salary, ID FROM tblEmployee
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.UpdateEmployee Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.UpdateEmployee (
@FirstName varchar(50),
@LastName varchar(70),
@DepartmentID int,
@Salary money,
@Original_ID int)
AS
SET NOCOUNT OFF;
UPDATE tblEmployee SET FirstName = @FirstName, LastName = @LastName, DepartmentID = @DepartmentID, Salary = @Salary WHERE (ID = @Original_ID)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.procEmployeesSorted Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE procEmployeesSorted
AS
select * from tblEmployee
order by LastName, FirstName return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.trCustomerT Script Date: 10-Jul-02 12:41:14 PM ******/
create trigger trCustomerT
on dbo.tblCustomer
for insert, update
as
update tblCustomer
set tblCustomer.LastNameSoundex = soundex(tblCustomer.LastName)
from inserted
where tblCustomer.ID = inserted.ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Хотя сценарий, созданный автоматически с помощью программы SQL Server Enterprise Manager, достаточно хорош для запуска его даже в том состоянии, в котором он находится, его можно отредактировать нужным вам образом. Учтите, что при повторной генерации сценария внесенные вручную изменения будут утрачены.
Полезной модификацией сценария может стать включение команды PRINT в стратегически важных местах сценария, например для отображения некоторых фрагментов текста в диалоговом окне Messages программы SQL Query Analyzer. Это позволит отслеживать прогресс выполнения сценария, а также использовать выводимые сообщения для отладки сценария: поиска и устранения замеченных ошибок. Использование команды PRINT совсем не обязательно и не имеет прямой связи с процессом создания базы данных.
Помните, что при использовании подобных пакетов команд SQL их можно запускать повторно нужное количество раз. Дело в том, что он создан так, чтобы полностью удалить и воссоздать повторно базу данных. При загрузке тестовых данных для проверки работоспособности базы данных не следует беспокоиться о повреждении или утрате данных при выполнении такого сценария. Кроме того, создавая базу данных с помощью пакета команд SQL, можно легко переносить структуру базы данных на другие cерверы. Это позволяет создавать две совершенно разные физические базы данных: одну для создания приложения, а другую для работы.
