Обработка изменений ячеек хранимыми процедурами

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

Эти обработчики можно настроить в таблице xls.handlers для всех колонок или только для некоторых.

Ниже приведен пример настройки для всех колонок:

Эту настройку можно не создавать, если имя процедуры обработчика заканчивается на _change, как в приведенном примере.

Ниже приведен пример настройки для обработки только двух заданных колонок:

Детали реализации

SaveToDB и DBEdit вызывают указанные процедуры на стороне клиента.

DBGate предоставляет доступ к процедурам через метод POST и добавляет обработчики в метаданные объектов или колонок.

ODataDB создает операции ActionImport для каждой процедуры и добавляет аннотации в EntityType или его поля.

Надстройка SaveToDB поддерживает метаданные DBGate и ODataDB для настройки таких обработчиков.

Параметры хранимых процедур

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

1. Значения из колонок данных с тем же именем. Например, параметры @id и @name получают значения из колонок id и name.
2. Значения параметров запроса с тем же именем. Например, параметр @account_id может использовать значение параметра @account_id из запроса данных.
3. Значения именованных ячеек Excel. Например, параметр @customer_id может получать значение из именованной ячейки customer_id (только в надстройке SaveToDB).
4. Специальные контекстные значения, такие как @rownum или @transaction_id.

Наиболее полезные контекстные параметры:

• @column_name — имя колонки ячейки.
• @cell_value — строковое значение ячейки.
• @cell_number_value — числовое значение ячейки.
• @cell_datetime_value — значение ячейки типа datetime.

См. полный список контекстных параметров в статье Контекстные параметры.

Параметры @cell_number_value и @cell_datetime_value можно использовать для получения типизированных значений, что полезно для проверки корректности вводимых данных. Например, если типизированное значение равно NULL, а строковое — нет.

В некоторых случаях имена колонок таблицы данных могут содержать символы, которые нельзя использовать в именах параметров. Например, "customer name".

В таких случаях можно заменить запрещенные символы в именах параметров на их XML-кодировку. Например, "customer_x0020_name".

См. Соглашение по именам параметров.

Примеры хранимых процедур для обработки изменения ячеек

Ниже приведены примеры хранимых процедур для всех поддерживаемых платформ баз данных.

Используйте эти примеры для разрешения конфликтов имен параметров и полей обновляемых таблиц.

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Пример хранимой процедуры обработки изменения ячеек для Microsoft SQL Server

CREATE PROCEDURE [s02].[usp_cashbook3_change]
    @column_name nvarchar(255),
    @cell_value nvarchar(255) = NULL,
    @cell_number_value money = NULL,
    @cell_datetime_value date = NULL,
    @id int = NULL
AS
BEGIN

SET NOCOUNT ON;

IF @column_name = 'debit'
    UPDATE s02.cashbook SET debit = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'credit'
    UPDATE s02.cashbook SET credit = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'item_id'
    UPDATE s02.cashbook SET item_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'company_id'
    UPDATE s02.cashbook SET company_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'account_id'
    UPDATE s02.cashbook SET account_id = @cell_number_value WHERE id = @id;
ELSE IF @column_name = 'date'
    UPDATE s02.cashbook SET [date] = @cell_datetime_value WHERE id = @id;

END

Пример хранимой процедуры обработки изменения ячеек для MySQL

CREATE PROCEDURE s02.usp_cashbook3_change (
    column_name varchar(128),
    cell_value varchar(255),
    cell_number_value varchar(255),
    cell_datetime_value varchar(255),
    id int
)
BEGIN

IF column_name = 'date' THEN
    UPDATE s02.cashbook t SET date = cell_datetime_value WHERE t.id = id;
ELSEIF column_name = 'account_id' THEN
    UPDATE s02.cashbook t SET account_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'item_id' THEN
    UPDATE s02.cashbook t SET item_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'company_id' THEN
    UPDATE s02.cashbook t SET company_id = CAST(cell_number_value AS unsigned) WHERE t.id = id;
ELSEIF column_name = 'debit' THEN
    UPDATE s02.cashbook t SET debit = cell_number_value WHERE t.id = id;
ELSEIF column_name = 'credit' THEN
    UPDATE s02.cashbook t SET credit = cell_number_value WHERE t.id = id;
END IF;

END
//

Пример хранимой процедуры обработки изменения ячеек для PostgreSQL

CREATE OR REPLACE FUNCTION s02.usp_cashbook3_change (
    column_name varchar,
    cell_value varchar,
    cell_number_value double precision,
    cell_datetime_value date,
    id integer
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN

IF column_name = 'id' THEN
    RAISE EXCEPTION 'Do not change the id column';
    RETURN;
ELSIF column_name = 'date' THEN
    IF cell_datetime_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Date requires a date value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET date = cell_datetime_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'debit' THEN
    IF cell_number_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Debit requires a number value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET debit = cell_number_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'credit_id' THEN
    IF cell_number_value IS NULL AND cell_value IS NOT NULL THEN
        RAISE EXCEPTION 'Credit requires a number value';
        RETURN;
    END IF;
    UPDATE s02.cashbook p SET credit_id = cell_number_value WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'account_id' THEN
    UPDATE s02.cashbook p SET account_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'company_id' THEN
    UPDATE s02.cashbook p SET company_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSIF column_name = 'item_id' THEN
    UPDATE s02.cashbook p SET item_id = CAST(cell_number_value AS integer) WHERE p.id = usp_cashbook3_change.id;
ELSE
    RAISE NOTICE 'The cashbook table does not contain the % column', column_name;
    RETURN;
END IF;

IF NOT FOUND THEN
    RAISE NOTICE 'The record with the id % not found', id;
    RETURN;
END IF;

END
$$;

Пример хранимой процедуры обработки изменения ячеек для Oracle Database

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR2,
    CELL_VALUE VARCHAR2,
    CELL_NUMBER_VALUE DOUBLE PRECISION,
    CELL_DATETIME_VALUE DATE,
    ID NUMBER
)
AS
BEGIN

IF COLUMN_NAME = 'DATE' THEN
    UPDATE S02.CASHBOOK P SET "DATE" = CELL_DATETIME_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'DEBIT' THEN
    UPDATE S02.CASHBOOK P SET DEBIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'CREDIT' THEN
    UPDATE S02.CASHBOOK P SET CREDIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'ACCOUNT_ID' THEN
    UPDATE S02.CASHBOOK P SET ACCOUNT_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'COMPANY_ID' THEN
    UPDATE S02.CASHBOOK P SET COMPANY_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSIF COLUMN_NAME = 'ITEM_ID' THEN
    UPDATE S02.CASHBOOK P SET ITEM_ID = CAST(CELL_NUMBER_VALUE AS NUMBER) WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
END IF;

END;
/

Пример хранимой процедуры обработки изменения ячеек для Snowflake

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