Обработка изменений ячеек хранимыми процедурами
Специальное предложение: скидка 50% (115 200 рублей) на ODataDB Enterprise до 31 мая 2024

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

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

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

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

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02usp_cashbook3Changes02usp_cashbook3_changePROCEDURE

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

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

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02usp_cashbook3item_idChanges02usp_cashbook3_changePROCEDURE
 s02usp_cashbook3company_idChanges02usp_cashbook3_changePROCEDURE

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

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

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

ODataDB создает операции ActionImport для каждой процедуры и добавляет аннотации в EntityType или поля 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 | DB2 | NuoDB | 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;
/

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

--#SET TERMINATOR %%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR(255)
    , CELL_VALUE VARCHAR(255)
    , CELL_NUMBER_VALUE DOUBLE
    , CELL_DATETIME_VALUE DATE
    , ID INTEGER
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: BEGIN

IF COLUMN_NAME = 'DATE' THEN
    UPDATE S02.CASHBOOK P SET DATE = CELL_DATETIME_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF COLUMN_NAME = 'DEBIT' THEN
    UPDATE S02.CASHBOOK P SET DEBIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF COLUMN_NAME = 'CREDIT' THEN
    UPDATE S02.CASHBOOK P SET CREDIT = CELL_NUMBER_VALUE WHERE P.ID = USP_CASHBOOK3_CHANGE.ID;
ELSEIF 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;
ELSEIF 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;
ELSEIF 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;
END IF;

END P1
%%

--#SET TERMINATOR ;

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

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK3_CHANGE (
    COLUMN_NAME VARCHAR(255)
    , CELL_VALUE VARCHAR(255)
    , CELL_NUMBER_VALUE DOUBLE
    , CELL_DATETIME_VALUE DATE
    , ID INTEGER
    )
AS

VAR ID1 INTEGER = ID;

IF (COLUMN_NAME = 'DATE')
    UPDATE S02.CASHBOOK SET DATE = CELL_DATETIME_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'DEBIT')
    UPDATE S02.CASHBOOK SET DEBIT = CELL_NUMBER_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'CREDIT')
    UPDATE S02.CASHBOOK SET CREDIT = CELL_NUMBER_VALUE WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'ACCOUNT_ID')
    UPDATE S02.CASHBOOK SET ACCOUNT_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'COMPANY_ID')
    UPDATE S02.CASHBOOK SET COMPANY_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

IF (COLUMN_NAME = 'ITEM_ID')
    UPDATE S02.CASHBOOK SET ITEM_ID = CAST(CELL_NUMBER_VALUE AS INTEGER) WHERE ID = ID1;
    RETURN;
END_IF;

END_PROCEDURE
@@

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

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