Сохранение данных в базе данных с помощью хранимых процедур

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

Существует два основных сценария:

1. Объект вывода данных имеет фиксированный набор колонок.
2. Объект вывода данных имеет динамический набор колонок.

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

В этой статье мы обсудим первый сценарий. Второй сценарий описан в статье Сохранение данных с использованием JSON.

Настройка

Чтобы использовать хранимые процедуры для сохранения изменений, необходимо настроить их для операций INSERT, UPDATE и DELETE.

Ниже приведен пример настройки для таблицы xls.objects:

Пример настройки для представления списка запросов:

Продукты SaveToDB автоматически создают такие конфигурации, связывая процедуры по суффиксам _insert, _update и _delete.

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

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

SaveToDB и DBEdit вызывают заданные хранимые процедуры для операций вставки, обновления и удаления.

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

ODataDB создает объекты EntitySet для представлений и FunctionImport, которые возвращают EntitySet для процедур и объектов на основе SQL-кода.

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

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

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

SaveToDB 10+, DBEdit, DBGate и ODataDB также поддерживают параметры с префиксом source_, которые получают исходные значения колонок данных.

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

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

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

См. также Контекстные параметры.

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

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

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

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Примеры хранимых процедур для Microsoft SQL Server

Обратите внимание на обязательную команду SET NOCOUNT ON, которая требуется для получения данных из хранимых процедур SQL Server в Microsoft Excel.

CREATE PROCEDURE [s02].[usp_cashbook2]
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        t.id,
        CAST(t.[date] AS datetime) AS [date],
        t.account_id,
        t.item_id,
        t.company_id,
        t.debit,
        t.credit
    FROM
        s02.cashbook t
    WHERE
        COALESCE(@account_id, t.account_id, -1) = COALESCE(t.account_id, -1)
        AND COALESCE(@item_id, t.item_id, -1) = COALESCE(t.item_id, -1)
        AND COALESCE(@company_id, t.company_id, -1) = COALESCE(t.company_id, -1);
END
GO

CREATE PROCEDURE [s02].[usp_cashbook2_insert]
    @date date = NULL,
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL,
    @debit money = NULL,
    @credit money = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO s02.cashbook
        ([date], account_id, item_id, company_id, debit, credit)
    VALUES
        (@date, @account_id, @item_id, @company_id, @debit, @credit);
END
GO

CREATE PROCEDURE [s02].[usp_cashbook2_update]
    @id int = NULL,
    @date date = NULL,
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL,
    @debit money = NULL,
    @credit money = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s02.cashbook
    SET
        [date] = @date,
        account_id = @account_id,
        item_id = @item_id,
        company_id = @company_id,
        debit = @debit,
        credit = @credit
    WHERE
        id = @id;
END
GO

CREATE PROCEDURE [s02].[usp_cashbook2_delete]
    @id int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM s02.cashbook WHERE id = @id;
END
GO

Примеры хранимых процедур для MySQL

CREATE PROCEDURE s02.usp_cashbook2 (
    account_id int,
    item_id int,
    company_id int
)
BEGIN
    SELECT
        *
    FROM
        s02.cashbook p
    WHERE
        COALESCE(account_id, p.account_id, -1) = COALESCE(p.account_id, -1)
        AND COALESCE(item_id, p.item_id, -1) = COALESCE(p.item_id, -1)
        AND COALESCE(company_id, p.company_id, -1) = COALESCE(p.company_id, -1);
END
//

CREATE PROCEDURE s02.usp_cashbook2_insert (
    date date,
    account_id int,
    item_id int,
    company_id int,
    debit double,
    credit double
)
BEGIN
    INSERT INTO s02.cashbook
        (date, account_id, item_id, company_id, debit, credit)
    VALUES
        (date, account_id, item_id, company_id, debit, credit);
END
//

CREATE PROCEDURE s02.usp_cashbook2_update (
    id int,
    date date,
    account_id int,
    item_id int,
    company_id int,
    debit double,
    credit double
)
BEGIN
    UPDATE s02.cashbook t
    SET
        t.date = date,
        t.account_id = account_id,
        t.item_id = item_id,
        t.company_id = company_id,
        t.debit = debit,
        t.credit = credit
    WHERE
        t.id = id;
END
//

CREATE PROCEDURE s02.usp_cashbook2_delete (
    id int
)
BEGIN
    DELETE FROM s02.cashbook WHERE s02.cashbook.id = id;
END
//

Примеры хранимых процедур для PostgreSQL

CREATE OR REPLACE FUNCTION s02.usp_cashbook2 (
    account integer,
    item integer,
    company integer
)
RETURNS table (
    id integer,
    date date,
    account_id integer,
    item_id integer,
    company_id integer,
    debit double precision,
    credit double precision
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.id,
        p.date,
        p.account_id,
        p.item_id,
        p.company_id,
        p.debit,
        p.credit
    FROM
        s02.cashbook p
    WHERE
        COALESCE(account, p.account_id, -1) = COALESCE(p.account_id, -1)
        AND COALESCE(item, p.item_id, -1) = COALESCE(p.item_id, -1)
        AND COALESCE(company, p.company_id, -1) = COALESCE(p.company_id, -1);
END
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_insert (
    date date,
    account_id integer,
    company_id integer,
    item_id integer,
    debit double precision,
    credit double precision
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO s02.cashbook
        (date, account_id, company_id, item_id, debit, credit)
    VALUES
        (date, account_id, company_id, item_id, debit, credit);
END
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_update (
    id integer,
    date date,
    account_id integer,
    company_id integer,
    item_id integer,
    debit double precision,
    credit double precision
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE s02.cashbook p
    SET
        date = usp_cashbook2_update.date,
        account_id = usp_cashbook2_update.account_id,
        company_id = usp_cashbook2_update.company_id,
        item_id = usp_cashbook2_update.item_id,
        debit = usp_cashbook2_update.debit,
        credit = usp_cashbook2_update.credit
    WHERE
        p.id = usp_cashbook2_update.id;
END
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_delete (
    id integer
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM s02.cashbook p WHERE p.id = usp_cashbook2_delete.id;
END
$$;

Примеры хранимых процедур для Oracle Database

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2 (
    ACCOUNT_ID IN NUMBER,
    ITEM_ID IN NUMBER,
    COMPANY_ID IN NUMBER,
    DATA OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN DATA FOR
    SELECT
        p.ID,
        p."DATE",
        p.ACCOUNT_ID,
        p.ITEM_ID,
        p.COMPANY_ID,
        p.DEBIT,
        p.CREDIT
    FROM
        S02.CASHBOOK p
    WHERE
        COALESCE(USP_CASHBOOK2.ACCOUNT_ID, p.ACCOUNT_ID, -1) = COALESCE(p.ACCOUNT_ID, -1)
        AND COALESCE(USP_CASHBOOK2.ITEM_ID, p.ITEM_ID, -1) = COALESCE(p.ITEM_ID, -1)
        AND COALESCE(USP_CASHBOOK2.COMPANY_ID, p.COMPANY_ID, -1) = COALESCE(p.COMPANY_ID, -1);
END;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    "DATE" DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE PRECISION,
    CREDIT DOUBLE PRECISION
)
AS
BEGIN
    INSERT INTO S02.CASHBOOK
        ("DATE", ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT)
    VALUES
        ("DATE", ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT);
END;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    "DATE" DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE PRECISION,
    CREDIT DOUBLE PRECISION
)
AS
BEGIN
    UPDATE S02.CASHBOOK P
    SET
        "DATE" = USP_CASHBOOK2_UPDATE."DATE",
        ACCOUNT_ID = USP_CASHBOOK2_UPDATE.ACCOUNT_ID,
        COMPANY_ID = USP_CASHBOOK2_UPDATE.COMPANY_ID,
        ITEM_ID = USP_CASHBOOK2_UPDATE.ITEM_ID,
        DEBIT = USP_CASHBOOK2_UPDATE.DEBIT,
        CREDIT = USP_CASHBOOK2_UPDATE.CREDIT
    WHERE
        P.ID = USP_CASHBOOK2_UPDATE.ID;
END;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
)
AS
BEGIN
    DELETE FROM S02.CASHBOOK WHERE ID = USP_CASHBOOK2_DELETE.ID;
END;
/

Примеры хранимых процедур для Snowflake

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