Базовая процедура SQL Server

Для разработки базовой процедуры приведена таблица с базовыми типами данных SQL Server и соответствующими внутренними типами платформы:

Подробное описание типов SQL Server можно найти в описании Data Types, Data Type Synonyms.

Базовый табличный тип

По стандартному языку базы данных создаются типы, таблицы и процедуры. Базовый табличный тип объявляется с помощью синтаксиса:

CREATE TYPE <наименование типа> AS TABLE (
    <имя столбца> <тип столбца>
);

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

Базовая хранимая процедура

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

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

• функции не могут изменять данные на диске, даже если внутри функции вызвана процедура.

Для реализации базовых хранимых процедур неудобно использовать функции, так как из-за их ограничения не получится реализовать сценарии с изменением данных. Требуется реализовывать базовые хранимые процедуры на основе процедур. Если есть процедура «some_proc», то должен быть определен базовый табличный тип с именем «some_proc_return_type» для определения структуры выходной таблицы.

Связанный табличный тип для процедуры – это базовый табличный тип, имя которого отличается от имени связанной процедуры суффиксом «_return_type». Этот тип обозначает тип выходной таблицы процедуры.

Базовая хранимая процедура в MS SQL – это совокупность хранимой процедуры и связанного табличного типа. Требования к процедуре повторяют требования к базовой хранимой процедуре, кроме пункта про возвращаемую структуру.

Пример

Рассмотрим пример добавления новой записи в таблицу фруктов, в которой содержатся атрибуты:

• id (int). Идентификатор записи;

• name (varchar). Название фрукта.

Добавление новой записи в таблицу осуществляется с помощью хранимых процедур, которые необходимо создать в базе данных. Для выполнения хранимых процедур используется API-метод rpc. В качестве входных параметров для процедур задаются атрибуты таблицы, которые передаются в табличном или скалярном виде.

Примеры выполнения процедур в зависимости от вида передачи входных параметров:

CREATE PROCEDURE [fruits_add_table_struct]
@upsert_rows fruits_struct READONLY
AS
    BEGIN
 
   SET NOCOUNT ON;
   SET IDENTITY_INSERT [fruits_struct] ON;
 
   INSERT INTO  [fruits_struct](id, name)
   SELECT id, name FROM @upsert_rows
   WHERE id NOT IN (
      SELECT id FROM [fruits_struct]
   ) AND id IS NOT NULL;
 
   SET IDENTITY_INSERT [fruits_struct] OFF;
 
   INSERT INTO [fruits_struct](name)
   SELECT name FROM @upsert_rows WHERE id IS NULL;
 
   DECLARE @id NVARCHAR(max);
   DECLARE [cursor] CURSOR LOCAL READ_ONLY
   FOR    (
      SELECT id FROM @upsert_rows
   );
 
   OPEN [cursor];
   FETCH NEXT FROM [cursor] INTO @id;
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATE [fruits_struct]
      SET NAME = (
         SELECT NAME    FROM @upsert_rows WHERE id = @id
      )
      WHERE id = @id;
      FETCH NEXT FROM [cursor] INTO @id;
   END;
 
   CLOSE [cursor];
   DEALLOCATE [cursor];
   end
 
    SELECT * FROM fruits_struct WHERE id = (SELECT id from @upsert_rows)
go

create type fruits_add_table_struct_return_type as table
(
    id   int,
    name nvarchar(max)
)
go

CREATE PROCEDURE [fruits_add_enum]
@id INT,
@name NVARCHAR (max)
 
as begin
    Set nocount on;
    SET IDENTITY_INSERT [fruits_struct] ON;
    IF NOT EXISTS(select * from fruits_struct where id=@id)
    INSERT INTO fruits_struct (id,name) VALUES (@id,@name);
    ELSE IF EXISTS(select * from fruits_struct where id=@id)
    UPDATE [fruits_struct] SET name = @name
    WHERE id=@id;
 
   SELECT * from fruits_struct WHERE id=@id
 
end
go

create type fruits_add_enum_return_type as table
(
    id   int,
    name nvarchar(max)
)
go

После выполнения одной из процедур в таблицу фруктов будет добавлена новая запись, если значение заданного идентификатора записи (атрибут id) отличается от существующих значений в таблице. Если значение заданного идентификатора записи совпадает с существующим значением в таблице, то название фрукта (атрибут name) будет изменено на название, переданное в запросе.

См. также:

Настройка интеграции с табличными источниками данных | Базовая процедура SOAP/XML

Копировать ссылку на страницу

Наверх

Нашли ошибку? Выделите текст с ошибкой и нажмите кнопку "Сообщить об ошибке" или CTRL+ENTER.

Справочная система на версию 23.05 от 03/07/2023, © ООО «ФОРСАЙТ»,