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

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

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

Есть два очень разных случая:

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

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

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

Настройка

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

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

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_OBJECTUPDATE_OBJECTDELETE_OBJECT
 s02usp_cashbook2PROCEDUREs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete
 s02usp_cashbook5PROCEDUREs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete
 s02view_cashbook2VIEWs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete

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

IDTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
 s02usp_cashbook2PROCEDUREs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete
 s02usp_cashbook5PROCEDUREs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete
 s02view_cashbook2VIEWs02.usp_cashbook2_inserts02.usp_cashbook2_updates02.usp_cashbook2_delete

Продукты 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 | DB2 | NuoDB | 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;
/

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

--#SET TERMINATOR %%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2 (
    IN ACCOUNT_ID INTEGER
    , IN ITEM_ID INTEGER
    , IN COMPANY_ID INTEGER
    )
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN NO
    LANGUAGE SQL
P1: BEGIN

    DECLARE Cursor1 CURSOR WITH RETURN 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);

    OPEN Cursor1;

END P1
%%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: 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 P1
%%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: 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 P1
%%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: BEGIN

DELETE FROM S02.CASHBOOK P WHERE P.ID = USP_CASHBOOK2_DELETE.ID;

END P1
%%

--#SET TERMINATOR ;

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

CREATE PROCEDURE S02.USP_CASHBOOK2 (
    IN ACCOUNT_ID INTEGER
    , IN ITEM_ID INTEGER
    , IN COMPANY_ID INTEGER
    )
RETURNS tmp_tab (
    ID INTEGER,
    DATE DATETIME,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS
VAR ACCOUNT_ID1 INTEGER = ACCOUNT_ID;
VAR ITEM_ID1 INTEGER = ITEM_ID;
VAR COMPANY_ID1 INTEGER = COMPANY_ID;

INSERT INTO tmp_tab
SELECT
    p.ID
    , p.DATE
    , p.ACCOUNT_ID
    , p.ITEM_ID
    , p.COMPANY_ID
    , p.DEBIT
    , p.CREDIT
FROM
    S02.CASHBOOK p
WHERE
    COALESCE(p.ACCOUNT_ID, -1) = COALESCE(ACCOUNT_ID1, p.ACCOUNT_ID, -1)
    AND COALESCE(p.ITEM_ID, -1) = COALESCE(ITEM_ID1, p.ITEM_ID, -1)
    AND COALESCE(p.COMPANY_ID, -1) = COALESCE(COMPANY_ID1, p.COMPANY_ID, -1);

END_PROCEDURE
@@

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS

INSERT INTO S02.CASHBOOK
    (DATE, ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT)
VALUES
    (DATE, ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT);

END_PROCEDURE
@@

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS

VAR ID1 INTEGER = ID;
VAR DATE1 DATE = DATE;
VAR ACCOUNT_ID1 INTEGER = ACCOUNT_ID;
VAR ITEM_ID1 INTEGER = ITEM_ID;
VAR COMPANY_ID1 INTEGER = COMPANY_ID;
VAR DEBIT1 DOUBLE = DEBIT;
VAR CREDIT1 DOUBLE = CREDIT;

UPDATE S02.CASHBOOK P
SET
    DATE = DATE1
    , ACCOUNT_ID = ACCOUNT_ID1
    , COMPANY_ID = COMPANY_ID1
    , ITEM_ID = ITEM_ID1
    , DEBIT = DEBIT1
    , CREDIT = CREDIT1
WHERE
    ID = ID1;

END_PROCEDURE
@@

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
    )
AS

VAR ID1 INTEGER = ID;

DELETE FROM S02.CASHBOOK WHERE ID = ID1;

END_PROCEDURE
@@

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

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