Sources
Delphi Russian Knowledge Base
DRKB - база знаний по Дельфи в рунете, составленная Виталием Невзоровым
Home About Authors Links Download
DRKB » Базы данных » MS SQL Server » Особенности работы с БД на MS SQL Server 2000 из Delphi 5 (статья) » Особенности реализации серверной части

Особенности реализации серверной части

01.06.2001
Тенцер А. Л. tolik@katren.nsk.ru, ICQ UIN 15925834

MS SQL Server имеет встроенный язык программирования Transact SQL, являющийся процедурным расширением стандарта ANSI SQL 92 entry level. T-SQL имеет полный набор средств для написания хранимых процедур и триггеров. Кроме этого реализованы некоторые расширения стандартного SQL, которые необходимо знать разработчику.

SELECT

В ранних версиях внешнее объединение таблиц задавалось выражением *= и =* во фразе WHERE. Этот синтаксис поддерживается, но не рекомендуется и будет исключен в будущих версиях. Начиная с версии 6.5, сервер поддерживает стандартный синтаксис

{LEFT|RIGHT|FULL} [OUTER] JOIN

Функция IDENTITY(data_type[, seed, increment] позволяет при выполнении SELECT в таблицу (SELECT INTO) создать в этой таблице автоинкрементное поле IDENTITY и заполнить его. При помощи этой функции и временных таблиц можно пронумеровать результаты запроса.

SELECT IDENTITY(INTEGER, 1, 1) AS Counter, Name
INTO #Temp
FROM MyTable
ORDER BY Name<

SELECT * FROM #Temp

Начиная с версии 7.0, оператор SELECT имеет модификаторы TOP n [PERSENT] [WITH TIES], позволяющие вывести первые n записей или n процентов записей. Указав WITH TIES можно заставить сервер включить в результат все записи с таким же значением сортируемого поля, как и у последней из n записи. Если SELECT не имеет фразы ORDER BY, то набор записей не обязательно будет один и тот же.

В качестве одной из таблиц в запросе можно использовать вложенный запрос:

SELECT A.Name, A.Population, B.AvgPop
  FROM City A INNER JOIN
   (SELECT Country, AVG(Population) AS AvgPop 
      FROM City GROUP BY Country ) AS B
    ON A.Country = B.Country

Этот запрос для каждого города выведет его название, количество жителей и среднее количество жителей на город в стране, в которой он находится.

Функции OPENQUERY и OPENROWSET позволяют использовать в качестве одной из таблиц в запросе выборку из любого OLE DB совместимого источника данных.

В MSSQL 2000 можно в запросе указать выражение FOR XML, в результате чего будет возвращена строка, содержащая XML представление выборки. Например, запрос:

SELECT O.OrderID, O.CustomerID, O.OrderDate, 
  O.ShipName, O.ShipAddress, O.ShipCity, O.ShipRegion, 
  P.ProductName, OD.UnitPrice, OD.Quantity
  FROM Orders O
    INNER JOIN [Order Details] OD ON O.OrderId = OD.OrderId
    INNER JOIN Products P ON OD.ProductId = P.ProductId
WHERE O.OrderId = '10248' 
FOR XML AUTO

Вернет результат:

<O OrderID="10248" 
   CustomerID="VINET" 
   OrderDate="1996-07-04T00:00:00" 
   ShipName="Vins et alcools Chevalier" 
   ShipAddress="59 rue de l&apos;Abbaye" 
   ShipCity="Reims">
   <P ProductName="Queso Cabrales">
     <OD UnitPrice="14.0000" Quantity="12"/>
   </P>
   <P ProductName="Singaporean Hokkien Fried Mee">
     <OD UnitPrice="9.8000" Quantity="10"/>
   </P>
   <P ProductName="Mozzarella di Giovanni">
     <OD UnitPrice="34.8000" Quantity="5"/>
   </P>
</O>

Возможно как автоматическое форматирование XML результатов запроса, так и задание способа форматирования программистом.

Кроме этого возможно использование XML данных в качестве таблицы в запросе. В качестве примера рассмотрим хранимую процедуру, выдающую данные по заранее неизвестному количеству записей. Идентификаторы записей передаются в неё в виде XML-документа

CREATE PROCEDURE XMLParam 
 @Ids VARCHAR(8000)
AS
 DECLARE @idoc int
 EXEC sp_xml_preparedocument @idoc OUTPUT, @Ids
 SELECT O.* 
   FROM Orders O
    INNER JOIN OPENXML (@idoc, '/ROOT/Ids', 1)
      WITH (ID INT) AS T ON 
 .OrderId = T.Id
 EXEC sp_xml_removedocument @idoc
GO

Вызов этой процедуры выглядит следующим образом:

DECLARE @S VARCHAR(8000)

SET @S = '<ROOT>
<Ids ID="10250"/>
<Ids ID="10257"/>
<Ids ID="10258"/>
</ROOT>'

EXECUTE XMLParam @S

Очевидно, что соответствующая строка параметров может быть легко построена и клиентским приложением.

INSERT, UPDATE и DELETE

INSERT

В дополнение к стандартным возможностям, MS SQL Server позволяет вставить в таблицу набор данных, полученный в результате выполнения хранимой процедуры, при помощи синтаксиса:

INSERT author_sales EXECUTE get_author_sales

UPDATE и DELETE

Сервер поддерживает расширенный синтаксис

UPDATE MyTable 
  SET Name = 'Иванов'
 FROM MyTable T INNER JOIN AnotherTable A ON T.Id = A.MyTableId
  AND A.SomeField = 20

CREATE TABLE и типы данных

В версии 7.0 поддерживаются следующие типы данных:

В версии SQL 2000 дополнительно появились:

Поддерживается создание вычисляемых полей

CREATE TABLE MyTable (
  Direction BIT NOT NULL,
  Amount MONEY,
  CASE Direction 
   WHEN 1 THEN Amount
   ELSE -Amount
  END AS SignedAmount
)

Выражение не должно содержать подзапросов. В версии MSSQL2000 по вычисляемому полю может быть построен индекс.

Написание триггеров

Триггеры в MS SQL Server срабатывают после обновления и один раз на оператор (а не на каждую обновленную запись). Количество триггеров на таблицу неограниченно. В триггере доступна обновленная таблица и две виртуальных таблицы Inserted и Deleted.

В них находятся:

Command Inserted Deleted
INSERT Вставленные записи Нет записей
UPDATE Новые версии записей Старые версии записей
DELETE Нет записей Удаленные записи

Триггер может, основываясь на содержании этих таблиц осуществить дополнительную модификацию данных, либо отменить транзакцию, вызвавшую этот оператор. Например:

CREATE TRIGGER T1 ON MyTable FOR INSERT, UPDATE 
AS BEGIN
  -- Заносим в поля:
  --   LastUserName – имя пользователя, последним обновившего запись
  --   LastDateTime – дату и время последнего обновления
  UPDATE MyTable
    SET LastUserName = SUSER_NAME(),
        LastDateTime = GETDATE()
    FROM Inserted I INNER JOIN MyTable T ON I.Id = T.Id
END

CREATE TRIGGER T2 ON MyTable FOR DELETE
AS BEGIN
  -- Этот триггер откатывает и снимает всю транзакцию
  -- вызвавшую ошибку
  IF EXISTS (SELECT * FROM Deleted 
              WHERE Position = 'Boss') BEGIN
    RAISERROR('Нельзя удалять начальника', 16, 1)
    ROLLBACK
  END
END

CREATE TRIGGER T3 ON MyTable FOR DELETE
AS BEGIN
  -- А этот просто не дает удалить запись
  -- позволяя продолжить транзакцию
  IF EXISTS (SELECT * FROM Deleted 
              WHERE Position = 'Programmer') BEGIN
    INSERT INTO MyTable 
      SELECT * FROM Deleted 
       WHERE Position = 'Programmer'
    RAISERROR('Программиста удалить тоже не получится', 16, 1)
  END
END

Поскольку триггер срабатывает после обновления таблицы в нем нельзя реализовывать каскадные обновления данных при наличии FOREIGN KEY. Например, если есть две таблицы:

CREATE TABLE Main (
  Id INTEGER PRIMARY KEY
)

CREATE TABLE Child (
  Id INTEGER PRIMARY KEY,
  MainId INTEGER NOT NULL REFERENCES Main(Id)
)

То при удалении записи из Main, на которую имеются ссылки в Child, триггер на Main не сработает. Чтобы обойти эту проблему рекомендуется создать хранимую процедуру

CREATE PROCEDURE DeleteFromMain
 @Id INTEGER
AS BEGIN
  DECLARE @Result INTEGER
  BEGIN TRANSACTION
    SAVE TRANSACTION DeleteFromMain
      DELETE Child WHERE MainId = @Id
      DELETE Main WHERE Id = @Id
    SET @Result = @@ERROR
    IF @Result <> 0
      ROLLBACK TRANSACTION DeleteFromMain
  COMMIT
END

Другим способом является реализация ограничений ссылочной целостности только при помощи триггеров.

Кроме этого в версии MSSQL 2000 возможно создание INSTEAD OF триггеров. Такие триггеры выполняются вместо вызвавшей их операции. При этом ответственность за запись данных в таблице полностью лежит на программисте. Такие триггеры могут быть созданы на представлениях (VIEW), что позволяет сделать обновляемым любое представление, независимо от его сложности.

Пакеты команд

Операторы могут отправляться на сервер не поодиночке, а пакетами. Пакетом (batch) называется группа команд, отправленная клиентским приложением на сервер одновременно. Весь пакет компилируется в единый план исполнения. Такая техника позволяет уменьшить сетевой трафик и увеличить эффективность приложения. Типичный пакет может выглядеть следующим образом:

BEGIN TRANSACTION
INSERT One (SomeField) VALUES (:1)
INSERT Two (AnotherField) VALUES (:2)
IF @@ERROR = 0
  COMMIT
ELSE
  ROLLBACK

Внутри пакета возможно объявление переменных. Область их видимости ограничена пакетом, в котором они объявлены.

В случае синтаксической ошибки в любом из операторов пакета весь пакет не выполняется. Однако в случае ошибки выполнения любого оператора остальные операторы продолжают исполняться до окончания пакета.

Разделителем пакетов команд служит оператор GO.

Обработка ошибок

Для того, чтобы проинформировать клиентское приложение об ошибке MS SQL Server использует функцию RAISERROR. При этом необходимо помнить, что:

При возникновении ошибки в каком-либо из операторов внутри пакета выполнение пакета продолжается, а функция @@ERROR возвращает код ошибки, который можно обработать.

INSERT MyTable (Name) VALUES ('Петров')
IF @@ERROR != 0
  PRINT 'Ошибка вставки'.

После успешного оператора @@ERROR возвращает 0, поэтому, если значение ошибки может понадобиться впоследствии - его надо сохранить в переменной.

DECLARE @ErrCode INTEGER

SET @ErrCode = 0

BEGIN TRANSACTION
INSERT MyTable (Name) VALUES ('Иванов')
IF @@ERROR != 0
  @ErrCode = @@ERROR

INSERT MyTable (Name) VALUES ('Петров')
IF @@ERROR != 0
  @ErrCode = @@ERROR

IF @ErrCode = 0
  COMMIT
ELSE BEGIN
  ROLLBACK
  RAISERROR('Не удалось обновить данные', 16, 1)
END

Если оператор обновления данных не нашел ни одной записи - ошибки не возникает. Проверить эту ситуацию можно при помощи функции @@ROWCOUNT, которая возвращает количество записей, обработанных последним оператором.

UPDATE MyTable
  SET Name = 'Сидоров'
WHERE Name = 'Петров'

IF @@ROWCOUNT = 0
  PRINT 'Петров не найден'

Блокировки

MS SQL Server поддерживает блокировку на уровне записи для всех операций модификации данных. Если оптимизатор решит, что количество блокируемых записей в таблице слишком велико, то он может произвести эскалацию блокировок на страницы или всю таблицу. Это происходит, например, при одновременном обновлении значительного количества записей. В этом случае гораздо быстрее заблокировать таблицу (или группу страниц в ней) выполнить модификацию, а затем разблокировать её, чем накладывать блокировку на каждую запись. Сервер не предоставляет средств для управления эскалацией блокировок и делает её автоматически.

Другим важным вопросом является модель обеспечения уровней изоляции транзакций REPEATABLE READ и SERIAZABLE. При выполнении транзакции с этим уровнем изоляции сервер блокирует диапазоны значений полей, по которым осуществляется выборка данных для предотвращения вставки «фантомных» значений. Например, если в транзакции с уровнем изоляции SERIAZABLE будет выполнен запрос

SELECT * FROM MyTable
WHERE Name BETWEEN 'A' AND 'C'

сервер наложит блокировку по записи (Shared Lock) на диапазон значений, попавших в результат запроса, предотвращая тем самым вставку «фантомных» записей другими транзакциями. Блокировка будет удерживаться до конца транзакции. На измененные транзакцией записи накладывается блокировка по чтению (Exclusive Lock), предотвращающая чтение их другими транзакциями. Поэтому транзакции с высокими уровнями изоляции необходимо тщательно планировать и делать максимально короткими.

Обработка транзакций

В MS SQL Server поддерживаются все определенные стандартом ANSI SQL 92 уровни изоляции транзакций:

Для установки текущего уровня изоляции используется оператор

SET TRANSACTION ISOLATION LEVEL
    {
        READ COMMITTED 
         READ UNCOMMITTED 
         REPEATABLE READ 
         SERIALIZABLE
    }

Момент начала транзакции регулируется установкой

SET IMPLICIT_TRANSACTION ON|OFF

По умолчанию она установлена в ON, и каждый оператор выполняется в отдельной транзакции. По его завершении неявно выполняется COMMIT. Если необходимо выполнить транзакцию, состоящую из нескольких операторов её надо явно начать командой BEGIN TRANSACTION. Заканчивается транзакция операторами COMMIT или ROLLBACK.

Например:

INSERT MyTable VALUES (1)
-- Выполнился внутри отдельной транзакции
BEGIN TRANSACTION
-- Начали явную транзакцию
INSERT MyTable VALUES (2) 
INSERT MyTable VALUES (3) 
COMMIT
-- завершили явную транзакцию

Если выдать команду

SET IMPLICIT_TRANSACTION OFF

то сервер начинает новую транзакцию, если она еще не начата и выполнился один из следующих операторов:

ALTER TABLE    FETCH       REVOKE    CREATE
SELECT         DELETE      INSERT    GRANT
DROP           OPEN        UPDATE    TRUNCATE TABLE

Транзакция продолжается до тех пор, пока не будет выдана команда COMMIT или ROLLBACK.

Возможно создание вложенных транзакций. При этом функция @@TRANCOUNT показывает глубину вложенности транзакции. Например:

BEGIN TRANSACTION
 SELECT @@TRANCOUNT  -- Выдаст 1
 BEGIN TRANSACTION
 SELECT @@TRANCOUNT  -- Выдаст 2
 COMMIT
 SELECT @@TRANCOUNT  -- Выдаст 1
COMMIT
SELECT @@TRANCOUNT  -- Выдаст 0

Вложенный BEGIN TRANSACTION не начинает новую транзакцию. Он лишь увеличивает @@TRANCOUNT на 1. Аналогично, Вложенный оператор COMMIT не завершает транзакцию, а лишь уменьшает @@TRANCOUNT на 1. Реальное завершение транзакции происходит, когда @@TRANCOUNT становится равным 0. Такой механизм позволяет писать хранимые процедуры, содержащие транзакцию, например:

CREATE PROCEDURE Foo 
AS BEGIN
  BEGIN TRANSACTION
  INSERT MyTable VALUES (1)
  INSERT MyTable VALUES (1)
  COMMIT
END

При запуске вне контекста транзакции процедура выполнит свою транзакцию. Если она запущена внутри транзакции - внутренние BEGIN TRANSACTION и COMMIT просто увеличат и уменьшат счетчик транзакций.

Поведение ROLLBACK отличается от вышеописанного. ROLLBACK всегда, независимо от текущего уровня вложенности устанавливает @@TRANCOUNT в 0 и отменяет все изменения, начиная с начала самой внешней транзакции. Если в хранимой процедуре возможен откат её действий, исходя из какого-то условия, можно использовать точки сохранения (savepoint)

CREATE PROCEDURE Foo 
AS BEGIN
  BEGIN TRANSACTION
  -- Этот оператор не может быть отменен вне контекста
  -- основной транзакции
  INSERT MyTable VALUES (1)
   SAVE TRANSACTION InsideFoo
    -- Операторы, начиная отсюда могут быть отменены
    -- без отката основной транзакции
    INSERT MyTable VALUES (2)
    INSERT MyTable VALUES (3)
    IF (SELECT COUNT(*) FROM MyTable) > 3
      ROLLBACK TRANSACTION InsideFoo
      -- Отменяем изменения, внесенные после
      -- последнего savepoint
  COMMIT
END

Отдельного обсуждения заслуживает ROLLBACK вызванный в триггере.

В этом случае не только откатывается транзакция, в рамках которой произошло срабатывание триггера, но и прекращается выполнение пакета команд, внутри которого это произошло. Все операторы, следующие за оператором, вызвавшим триггер, не будут выполнены. Рассмотрим эту ситуацию на примере:

CREATE TABLE MyTable (Id INTEGER)

GO

CREATE TRIGGER MyTrig ON MyTable FOR INSERT 
AS BEGIN
  IF (SELECT MAX(Id) FROM Inserted) >= 2 BEGIN
    ROLLBACK
    RAISERROR('Id >= 2', 17, 1)
  END
END

GO

INSERT MyTable VALUES (1)
INSERT MyTable VALUES (2) -- Вызовет ROLLBACK в триггере
-- Операторы, начиная отсюда не выполнятся
INSERT MyTable VALUES (3)
INSERT MyTable VALUES (4)

Соответствие стандарту ANSI SQL 92

В MS SQL Server имеются настройки, позволяющие изменять степень соответствия сервера стандарту ANSI SQL 92.

SET ANSI_NULLS {ON\|OFF} - регулирует результат сравнения значений, содержащих NULL. Если ANSI_NULLS = OFF, то запрос

SELECT * FROM MyTable
WHERE MyField = NULL

Вернет все строки, в которых MyField установлено в NULL. Если ANSI_NULLS = OFF, то, в соответствии со стандартом ANSI SQL92 сравнение с NULL возвращает UNKNOWN. Другие установки, на которые следует обратить внимание:

SET CURSOR_CLOSE_ON_COMMIT - Устанавливает режим закрытия курсоров по завершению транзакции

SET ANSI_NULL_DFLT_ON и SET ANSI_NULL_DFLT_OFF - Устанавливают nullability полей по умолчанию при создании таблицы

SET IMPLICIT_TRANSACTIONS - Устанавливает режим Autocommit

SET ANSI_PADDING - Устанавливает режим «отсечения» концевых пробелов для вновь создаваемых полей

SET QUOTED_IDENTIFIER - Разрешает выделение идентификаторов двойными кавычками

SET ANSI_WARNINGS - Устанавливает реакцию на математические ошибки

Рассмотрение этих параметров выходит за рамки книги, однако необходимо обратить на них внимание при чтении документации.

Установка SET ANSI_DEFAULTS устанавливает режим максимальной совместимости с ANSI SQL92. При установке SET ANSI_DEFAULTS ON устанавливаются в ON следующие параметры:

SET ANSI_NULLS, SET CURSOR_CLOSE_ON_COMMIT, SET ANSI_NULL_DFLT_ON, SET IMPLICIT_TRANSACTIONS, SET ANSI_PADDING, SET QUOTED_IDENTIFIER и SET ANSI_WARNINGS

По умолчанию ANSI_DEFAULTS = ON для клиентов ODBC и OLE DB (ADO) и OFF для клиента DB-Library (BDE). Поскольку предпочтительным (и поддерживаемым в будущем) методом доступа является OLE DB, то при разработке клиентской части, использующей BDE, рекомендуется явно устанавливать SET ANSI_DEFAULTS ON.

С разностью этой установки связана и проблема, возникающая при разработке запросов при помощи Query Analyzer. Если в нем и в клиентском приложении имеются разные настройки совместимости с ANSI - одни и те же запросы могут выдавать разные результаты. Поэтому рекомендуется проверять настройки Query Analyzer на предмет соответствия их тем, что предполагается иметь в клиенте.

Previous page:
Особенности реализации клиентской части 		Top:
DRKB 		Next page:
Модель безопасности
Powered by yoCMS v.0.9.6, Ex.time: 0.03931s. Copyright © 2001-2026, DRKB.ru All rights reserved.