Листинг 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?)
Листинг 21. Создание таблицы и представления.
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?)