Опубликован: 25.11.2008 | Уровень: для всех | Доступ: платный
Лекция 12:

Встроенный SQL

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

Текст процедуры в этом случае будет иметь вид:

/* проверка существования в системном каталоге объекта 
 с данным именем и типом, созданного владельцем БД */
if exists (select * from sysobjects 
            where id = object_id('dbo.NEW_BOOKS')
              and sysstat & 0xf = 4)
/* если объект существует, то сначала 
 его удалим из системного каталога */
drop procedure dbo.NEW_BOOKS
CREATE PROCEDURE NEW_BOOKS 
( @ISBN       varchar(12),
  @TITL       varchar(255),
  @AUTOR      varchar(30),
  @COAUTOR    varchar(30),
  @YEARIZD    int,
  @PAGES      INT,
  @NUM_EXEMPL INT)
/* процедура ввода новой книги с указанием количества
 экземпляров данной книги параметры
 @ISBN       varchar(12)  шифр книги
 @TITL       varchar(255) название
 @AUTOR      varchar(30)  автор
 @COAUTOR    varchar(30)  соавтор
 @YEARIZD    int          год издания
 @PAGES      INT          количество страниц
 @NUM_EXEMPL INT          количество экземпляров */ 
AS                      
DECLARE @TEK int declare @INV int
INSERT INTO BOOKS 
  VALUES(@ISBN,@TITL,@AUTOR,@COAUTOR,@YEARIZD,@PAGES)
/* назначение значения текущего счетчика осташихся к вводу экземпляров*/
SELECT @TEK = @NUM_EXEMPL
/* определение максимального значения инвентарного номера в библиотеке */
SELECT @INV = SELECT MAX( ID_EXEMPLAR) FROM EXEMPLAR
/* организуем цикл для ввода новых экземпляров данной книги */
WHILE @TEK>0
/* пока количество оставшихся экземпляров больше нуля */
 BEGIN
  insert into EXEMPLAR (ID_EXEMPLAR,ISBN,DATA_IN,DATA_OUT,EXIST)
         VALUES (@INV,@ISBN,GETDATE(),GetDate(), TRUE)
  /* изменение текущих значений счетчика и инвентарного номера */
  SELECT @TEK = @TEK - 1
  SELECT @INV = @INV + 1
 End
/* конец цикла ввода данных о экземпляре книги*/
GO

Хранимые процедуры могут вызывать одна другую. Создадим хранимую процедуру, которая возвращает номер читательского билета для конкретного читателя.

if exists (select * from sysobjects 
            where id = object_id('dbo. CK_READER') 
              and sysstat & 0xf = 4)
/* если объект существует, то сначала его удалим из системного каталога */
drop procedure dbo.CK_READER
/* Процедура возвращает номер читательского билета, 
 если читатель есть и 0 в противном случае. 
 В качестве параметров передаем фамилию и дату рождения */
CREATE PROCEDURE CK_READER 
( @FIRST_NAME varchar(30),
  @BIRTH_DAY  varchar(12))
AS
/*опишем переменную, в которой будет храниться 
 номер читательского билета*/
DECLARE @NUM_READER INT
/* определение наличия читателя */
 select @NUM_READER = select NUM_READER from READERS
  WHERE FIRST_NAME = @ FIRST_NAME AND
    AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY 
RETURN COALESCE(@NUM_READER,0)

Мы здесь использовали функцию преобразования типа данных dataTime в тип данных varchar(8). Это было необходимо сделать для согласования типов данных при выполнении операции сравнения. Действительно, входная переменная @BIRTH_DAY имеет символьный тип ( varchar ), а поле базы данных BIRTH_DAY имеет тип SmallDateTime.

Хранимые процедуры допускают наличие нескольких выходных параметров. Для этого каждый выходной параметр должен после задания своего типа данных иметь дополнительное ключевое слово OUTPUT. Рассмотрим пример хранимой процедуры с несколькими выходными параметрами.

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

/* проверка наличия данной процедуры в нашей БД*/
if exists (select * from sysobjects 
            where id = object_id(N'[dbo].[NEW_READER]') 
			  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NEW_READER]
GO
/* процедура проверки существования читателя с 
 заданными значениями вводимых параметров
 Процедура возвращает новый номер читательского билета, 
 если такого читателя не было сообщает старый номер 
 и количество книг, которое должен читатель в противном случае */
CREATE PROCEDURE NEW_READER 
( @NAME_READER varchar(30),
  @ADRES       varchar(40),
  @HOOM_PHONE  char(9),    
  @WORK_PHONE char(9),
  @BIRTH_DAY   varchar(8),
  @NUM_READER  int OUTPUT,
  /* выходной параметр, определяющий номер читательского билета*/
  @Y_N int OUTPUT,
  /* выходной параметр, определяющий был ли читатель ранее записан в библиотеку*/
  @COUNT_BOOKS int OUTPUT
  /* выходной параметр, определяющий количество книг, которое числится за читателем*/
) AS
/* переменная, в которой будет храниться номер читательского билета, 
 если читатель уже был записан в библиотеку */
 DECLARE @N_R int
 /* определение наличия читателя */
 EXEC @N_R = CK_READER @NAME_READER,@BIRTH_DAY
 IF @N_R= 0 Or @N_R Is Null
 /* если читатель с заданными характеристиками не найден, 
  т. е. переменной @N_R присвоено значение нуль или ее значение неопределено, 
  перейдем к назначению для нового читателя нового номера читательского билета */
  BEGIN
  /* так как мы номер читательского билета определили как инкрементное поле, 
   то в операторе ввода мы его не указываем система сама назначит новому читателю 
   очередной номер */
   INSERT INTO READER(NAME_READER,ADRES,HOOM_PHONE,
                      WORK_PHONE, BIRTH_DAY)
          VALUES (@NAME_READER,@ADRES,@HOOM_PHONE,
		          @WORK_PHONE,Convert(smalldatetime, @BIRTH_DAY,4) )
   /* в операторе INSERT мы должны преобразовать символьную переменную @BIRTH_DAY 
    в тип данных smalldatetime, который определен для поля дата рождения BIRTH_DAY. 
	Это преобразование мы сделаем с помощью встроенной функции Transact SQL Convert */
   /* теперь определим назначенный номер читальского билета */
   select @NUM_READER = NUM_READER FROM READER
    WHERE NAME_READER = @NAME_READER
      AND convert(varchar(8),BIRTH_DAY,4)=@BIRTH_DAY
   /* здесь мы снова используем функцию преобразования типа, но в этом случае нам 
    необходимо преобразовать поле BIRTH_DAY из типа smalldatetime к типу varchar(8), 
	в котором задан входной параметр @BIRTH_DAY */
   Select @Y_N =0
   /* присваиваем выходному параметру @Y_N значение 0 (ноль), что соответствует тому,
    что данный читатель ранее в нашей библиотеке не был записан */
   Select @COUNT_BOOKS = 0
   /* присваиваем выходному параметру, хранящему количество книг, 
    числящихся за читателем значение ноль */
   Return 1
  END
 else
  /* если значение переменной @N_R не равно нулю, то читатель с заданными характеристиками 
   был ранее записан в нашей библиотеке */
  BEGIN
  /* определение количества книг у читателя с найденным номером читательского билета */
   select @COUNT_BOOKS = COUNT(INV_NUMBER) FROM EXEMPLAR WHERE NUM_READER = @N_R
   select @Count_books = COALESCE( @COUNT_BOOKS,0)
   /* присваиваем выходному параметру @COUNT_BOOKS значение, равное количеству книг, 
    которые числятся за нашим читателем, если в предыдущем запросе @COUNT_BOOKS было 
    присвоено неопределенное значение, то мы заменим его на ноль, используя для этого 
    встроенную функцию COALESCE(@COUNT_BOOKS,0), которая возвращает первое определенное 
    значение из списка значений, заданных в качестве ее параметров */
   Select @Y_N = 1
   /* присваиваем выходному параметру @Y_N значение 1, что соответствует тому, что данный 
    читатель ранее в нашей библиотеке был записан */
   Select @NUM_READER = @N_R
   /* присваиваем выходному параметру @NUM_READER определенный ранее номер читательского билета */
   return 0
 end

Теперь посмотрим, как работает наша новая процедура, для этого в режиме интерактивного выполнения запросов (то есть в QueryAnalyzer MS SQL Server 7.0) запишем следующую последовательность команд:

-- пример использования выходных параметров при вызове процедуры
-- new reader
-- зададим необходимые нам переменные
Declare @K int, @N int, @B int
exec NEW_READER 'Пушкин В.В.','Лиговский 22-90',
'333-55-99','444-66-88','01.06.83',@NUM_READER =@K OUTPUT, 
@Y_N = @N OUTPUT,@COUNT_BOOKS = @B OUTPUT
-- теперь выведем результаты работы нашей процедуры используя ранее
-- определенные нами переменные
Select 'номер билета',@K,'да-нет',@N,'кол-во книг',@B

Мы получим результат:

Номер билета  да-нет  кол-во книг 18                         0     0

Если же мы снова запустим нашу процедуру с теми же параметрами, то есть повторим выполнение подготовленных выше операторов, то получим уже иной ответ:

Номер билета  да-нет  кол-во книг 18                         1     0

и это означает, что господин Пушкин В. В. уже записан в нашей библиотеке, но он не успел взять ни одной книги, поэтому за ним числится 0 (ноль) книг.

Теперь обратимся к оценке эффективности применения хранимых процедур.

Если рассмотреть этапы выполнения одинакового текста части приложения, содержащего SQL-операторы, самостоятельно на клиенте и в качестве хранимой процедуры, то можно отметить, что на клиенте выполняются все 5 этапов выполнения SQL-операторов, а хранимая процедура может храниться в БД в уже скомпилированном виде, и ее исполнение займет гораздо меньше времени (см. рис. 12.2).

Кроме того, хранимые процедуры, как уже упоминалось, могут быть использованы несколькими приложениями, а встроенные операторы SQL должны быть включены в каждое приложение повторно.

Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедуры

Рис. 12.2. Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедуры

Хранимые процедуры также играют ключевую роль в повышении быстродействия при работе в сети с архитектурой "клиент—сервер".

На рис. 12.3 показан пример выполнения последовательности операторов SQL на клиенте, а на рис. 12.4 показан пример выполнения той же последовательности операторов SQL, оформленных в виде хранимой процедуры. В этом случае клиент обращается к серверу только для выполнения команды запуска хранимой процедуры. Сама хранимая процедура выполняется на сервере. Объем пересылаемой по сети информации резко сокращается во втором случае.

Сетевой трафик при выполнении встроенных SQL-операторов

Рис. 12.3. Сетевой трафик при выполнении встроенных SQL-операторов
Сетевой трафик при выполнении хранимой процедуры на сервере

Рис. 12.4. Сетевой трафик при выполнении хранимой процедуры на сервере
Александр Егай
Александр Егай
Александра Каева
Александра Каева