Microsoft SQL Server 2000. Новые возможности для разработчика приложений. Часть 1

Листинг 1. Пример объявления с типом BIGINT.

declare @bi0 as bigint, @bi1 as bigint
set @bi0 = power(cast(-2 as bigint), 63)
set @bi1 = -(@bi0 + 1)
select @bi0 as 'Нижняя граница bigint', @bi1 as 'Верхняя
граница bigint'

Листинг 2. Пример использования BIGINT для типизации.

drop table #tblTypesTest
create table #tblTypesTest (ID bigint identity 
(9223372036854775806, 1) not null, [Название] 
nvarchar(100) null) 
GO
insert #tblTypesTest ([Название]) values('ааа') - @bi1 - 1
insert #tblTypesTest ([Название]) values('bbb') - @bi1
select * from #tblTypesTest

Листинг 3. Пример объявления переменной типа <таблица>.

declare @t table (CustID nchar(5) NOT NULL primary key, 
Company nvarchar(40) NOT NULL unique, 
ContactName nvarchar(30) NULL, 
ContactTitle nvarchar(30) NULL, 
Country nvarchar(15) NULL, 
City nvarchar(15) NULL)

Листинг 4. Пример переноса записей из таблицы в переменную.

declare @CustID as nchar(5), @Company as nvarchar(40),
@ContactName as nvarchar(30)
declare @ContactTitle as nchar(30), @Country as
nvarchar(15), @City as nvarchar(15)
select top 1 @CustID = CustomerID, @Company =
CompanyName,
@ContactName = ContactName,
@ContactTitle = ContactTitle, 
@Country = Country, @City = City
from Customers order by CustomerID
while rowcount_big() > 0 begin
insert @t values (@CustID, @Company, @ContactName,
@ContactTitle, 
@Country, @City)
select top 1 @CustID = CustomerID, @Company =
CompanyName,
@ContactName = ContactName,
@ContactTitle = ContactTitle, 
@Country = Country, @City = City
from Customers where CustomerID > @CustID order by
CustomerID 
end

Листинг 5. Пример операций с переменной типа <таблица>.

update @t set ContactName = 'Don Pedro' where CustID =
'ALFKI'
delete from @t where CustID = 'ANATR'
select * from @t

Листинг 6. Пример определения переменных типа SQL_VARIANT.

declare @i as bigint, @d as decimal(15,3), @f as float(25),
@c as nvarchar(10), @dt as datetime, @m as money, @u
as uniqueidentifier
select @i = 9223372036854775807, @d =
1234567890.123, @f = 1.23456789E123, @c = 'abcde',
@dt = getdate(), @m = 12345.678, @u = newid()

Листинг 7. Пример использования переменных типа SQL_VARIANT.

create function fnShowVariantProps(@x as sql_variant)
returns table as
return (select @x as 'Переменная', 
sql_variant_property(@x, 'BaseType') as 'Базовый тип', 
sql_variant_property(@x, 'Precision') as 'Длина в цифрах', 
sql_variant_property(@x, 'Scale') as 'После запятой',
sql_variant_property(@x, 'TotalBytes') as 'Размер в байтах',
sql_variant_property(@x, 'Collation') as 'Unicode Collation',
sql_variant_property(@x, 'MaxLength') as 'Макс.длина строки')

Листинг 8. Пример создания таблицы #MyObjects для хранения объектов класса.

create table #MyObjects (ObjectID uniqueidentifier default
NewID() NOT NULL, Property1 int NULL, Property2 decimal(15, 3) NULL,
Property3 float NULL, Property4 nvarchar(10) NULL,
Property5 datetime NULL, Property6 smallmoney NULL)

Листинг 9. Пример работы с таблицей #MyObjects.

insert #MyObjects (Property1) values (5)
insert #MyObjects (Property5) values ('2000-06-07')
insert #MyObjects (Property2, Property3, Property6) values (2.72, -1.2E-4, 1000)
insert #MyObjects (Property1, Property4) values (-10, 'abc')
insert #MyObjects (Property2) values (3.14)
select * from #MyObjects

Листинг 10. Использование типа SQL_VARIANT для экономии пространства.

create table #MyObjects (ObjectID uniqueidentifier default NewID() NOT NULL,
PropertyName varchar(10) NOT NULL, 
PropertyValue sql_variant NOT NULL)
declare @id as uniqueidentifier
insert #MyObjects (PropertyName, PropertyValue) values 
('Property1', cast(5 as int))
insert #MyObjects (PropertyName, PropertyValue) values 
('Property5', cast('2000-06-07' as smalldatetime))
set @id = NewID()
insert #MyObjects (ObjectID, PropertyName, PropertyValue) values 
(@id, 'Property2', cast(2.72 as decimal(15, 3)))
insert #MyObjects (ObjectID, PropertyName, PropertyValue) values
(@id, 'Property3', cast(-1.2E-4 as float))
select ObjectID, PropertyName, PropertyValue, sql_variant_property
(PropertyValue, 'BaseType') from #MyObjects

Листинг 11. Функция (скалярного типа) подсчета слов в строке и пример ее использования.

create function fnNumToken (@InputString as nvarchar(4000),
@Delimiters as varchar(100)) returns int
as begin
declare @i as int, @i_prev as int, @n as int
set @Delimiters = @Delimiters + char(1)
set @InputString = @InputString + right(@Delimiters, 1) 
select @i = 1, @n = 0, @i_prev = 0 
while @i <= len(@InputString) begin
if charindex(substring(@InputString, @i, 1), @Delimiters)
<> begin
if @i > @i_prev + 1 set @n = @n + 1
set @i_prev = @i
end
set @i = @i + 1
end
return @n
end 
select dbo.fnNumToken('(095) 967-85-85', '() -')
select CompanyName, 'Кол-во слов в поле' = dbo.fnNumToken(CompanyName,
' -') from Northwind..Customers

Листинг 12. Табличная функция и пример ее использования.

create function fnTokens (@InputString as nvarchar(4000),
@Delimiters as varchar(100))
returns @Tokens table (id int identity (1, 1) NOT NULL
primary key clustered, Token nvarchar(4000) collate Cyrillic_General_CI_AS NULL)
as begin
declare @c as nvarchar(1), @Token as nvarchar(4000)
declare @i as int
set @Delimiters = @Delimiters + char(1) 
set @InputString = @InputString + right(@Delimiters, 1) 
select @i = 1, @Token = ''
while @i <= len(@InputString) begin
set @c = substring(@InputString, @i, 1)
if charindex(@c, @Delimiters) = 0
set @Token = @Token + @c
else
if @Token > '' begin 
insert @Tokens (Token) values (@Token)
set @Token = ''
end
set @i = @i + 1
end 
return
end

select * from fnTokens('(095) 967-85-85', '() -')
select CompanyName, ContactName, ContactTitle, City from Customers 
inner join fnTokens('ANTON, BERGS, DUMON, FOLKO, OCEAN', ', ') as Tkn
on Customers.CustomerID = Tkn.Token

Листинг 13. Подготовка отчета на основе данных из БД Northwind с помощью обычного представления.

if exists (select * from dbo.sysobjects where id = object_id('vwSalesByProductCategory')
and OBJECTPROPERTY(id, 'IsView') = 1)
drop view vwSalesByProductCategory
GO
create view dbo.vwSalesByProductCategory as
select c.CategoryName, p.ProductName, sum((od.UnitPrice - od.Discount) * od.Quantity) as
'Продажи'
from [Order Details] as od 
inner join Products as p on od.ProductID = p.ProductID
inner join Categories as c on p.CategoryID = c.CategoryID
group by p.ProductName, c.CategoryName
having c.CategoryName like 'Grains%'

Листинг 14. Подготовка отчета на основе данных из БД Northwind с помощью функции in-line.

if exists (select * from dbo.sysobjects where id
object_id('fnSalesByProductCategory') and xtype in ('FN','IF','TF'))
drop function [dbo].[fnSalesByProductCategory]
GO
create function fnSalesByProductCategory(@Category as varchar(30))
returns table as
return (select c.CategoryName, p.ProductName, 
sum((od.UnitPrice - od.Discount) * od.Quantity) as 'Продажи'
from [Order Details] as od 
inner join Products as p on od.ProductID = p.ProductID
inner join Categories as c on p.CategoryID = c.CategoryID
group by p.ProductName, c.CategoryName
having c.CategoryName like (@Category + '%'))

Листинг 15. Пример использования операторов ON DELETE и ON UPDATE.

select CustomerID as CustID, CompanyName as Company,
ContactName as Name, ContactTitle as Title, City, Country
into Cust from Customers where (CustomerID < N'B')
alter table Cust add constraint PK_Cust primary key (CustID)
select OrderID as OrdID, CustomerID as CustID, OrderDate
as OrdDate into Ords from Orders where CustomerID in
(select CustID from Cust)
alter table Ords add constraint PK_Ords primary key (OrdID),
constraint FK_Ords foreign key (CustID) references Cust
(CustID) on delete cascade on update cascade
select OrderID as OrdID, ProductID as ProdID, UnitPrice,
Quantity into OrdDet from [Order Details] where OrderID
in (select OrdID from Ords)
alter table OrdDet add constraint FK_OrdDet foreign key
(OrdID) references Ords (OrdID) on delete cascade on
update cascade

Листинг 16. Каскадное удаление.

select Cust.CustID, Ords.OrdDate, Ords.OrdID, OrdDet.ProdID, OrdDet.Quantity
from Ords inner join Cust on Ords.CustID = Cust.CustID 
inner join OrdDet on Ords.OrdID = OrdDet.OrdID
order by Cust.CustID
delete from Cust where CustID = 'ALFKI'
select * from Ords order by CustID
select * from OrdDet where OrdID in (10643, 10692, 10702, 10835, 10952, 11011)

Листинг 17. Ошибочное каскадное удаление.

alter table OrdDet drop constraint FK_OrdDet 
alter table OrdDet add constraint FK_OrdDet foreign key
(OrdID) references Ords (OrdID) on delete no action on update cascade
delete from Cust where CustID = 'ANATR'

Листинг 18. Новые параметры в OBJECTPROPERTY и два новых поля в INFORMATION._SCHEMA.REFERENTIAL_CONSTRAINTS.

select objectproperty(object_id('FK_OrdDet'), 'CnstIsDeleteCascade')
select objectproperty(object_id('FK_OrdDet'), 'CnstIsUpdateCascade')
select constraint_name, update_rule, delete_rule
from information_schema.referential_constraints

Листинг 19. Таблица с триггерами.

select top 5 CustomerID as CustID, CompanyName as Company,
ContactName as Name, ContactTitle as Title, Country into Cust from Customers
create trigger CustTr1 on Cust after update as 
if update(Company) select 'Поле Company было модифицировано'
go
create trigger CustTr2 on Cust after update as 
if update(Name) select 'Поле Name было модифицировано'
go
create trigger CustTr3 on Cust after update as 
if update(Title) select 'Поле Title было модифицировано'
go
create trigger CustTr4 on Cust after update as 
if update(Country) select 'Поле Country было модифицировано'
go
update Cust set Company = 'Здравствуйте, я - ваша тетя',
Name = 'Donna Rosa', Title = 'Your aunt', Country = 'Brazil' where CustID =
'ANATR'

Листинг 20. Задание последовательность вызова триггеров.

exec sp_settriggerorder @triggername = 'CustTr4', @order = 'first', @stmttype =
'update'
exec sp_settriggerorder @triggername = 'CustTr1', @order = 'last', @stmttype = 'update'
select objectproperty(object_id('CustTr4'), 'ExecIsFirstUpdateTrigger') 
select objectproperty(object_id('CustTr1'), 'ExecIsLastUpdateTrigger')

if exists (select * from information_schema.tables 
where table_type = 'BASE TABLE' and table_name = 'Empl')
drop table Empl
go
select * into Empl from Employees
create view vwEmpl as select EmployeeID as ID, FirstName
+ ' ' + LastName as Name, Title from Empl

Листинг 22. Создание триггеров INSTEAD OF.

create trigger trEmplIns on vwEmpl instead of insert as
if @@rowcount = 0 return
insert Empl (FirstName, LastName, Title)
select left(Name, charindex(' ', Name, 1) - 1), right(Name, len(Name)
- charindex(' ', Name, 1)), Title from inserted
go
create trigger trEmplUpd on vwEmpl instead of update as
if not update(Name) return
update Empl set FirstName = left(i.Name, charindex(' ',
i.Name, 1) - 1), LastName = right(i.Name, len(i.Name)
- charindex(' ', i.Name, 1)), Title = i.Title from inserted
i inner join Empl on i.ID = Empl.EmployeeID

Листинг 23. Использование триггеров INSTEAD OF.

insert vwEmpl (ID, Name, Title) values(0, 'Donna Rosa', 'Your aunt') 
update vwEmpl set Name = 'Don Pedro', Title = 'What a man he was' where ID = 1
select * from Empl order by EmployeeID
select * from vwEmpl order by ID

Листинг 24. Получение метаданных и определение типа триггера.

exec sp_help 'trEmplUpd' -> тип объекта
exec sp_helptext 'trEmplUpd' -> текст триггера (если не with
encryption)
exec sp_depends 'trEmplUpd' -> на чем определен
select objectproperty(object_id('CustTr4'), 'ExecIsAfterTrigger')
select objectproperty(object_id('trEmplUpd'), 'ExecIsInsteadOfTrigger')

Листинг 25. Поиск триггеров для таблицы/представления.

exec sp_helptrigger 'vwEmpl' -> список триггеров на объект
select objectproperty(object_id('vwEmpl'), 'HasInsteadOfTrigger')
select objectproperty(object_id('vwEmpl'), 'HasAfterTrigger')
select objectproperty(object_id('vwEmpl'), 'HasUpdateTrigger')
select objectproperty(object_id('vwEmpl'), 'HasInsertTrigger')