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

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

Все продукты SaveToDB поддерживают получение данных их хранимых процедур.

Для хранимых процедур создаются элементы для ввода и изменения значений параметров.

Разработчики могут настроить списки значений параметров. См. Списки значений параметров.

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

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

Мастера подключения отображают только процедуры, которые возвращают данные.

В некоторых случаях, продукты могут неправильно определять наличие SELECT.

Возможны два случая:

  • Мастер показывает процедуру, которая не возвращает результат
  • Мастер не показывает процедуру, которая возвращает результат

В обоих случаях используйте таблицу xls.objects.

Используйте тип HIDDEN, чтобы скрыть процедуру, и тип PROCEDURE, чтобы показать ее.

Специфические возможности DBGate и ODataDB

ODataDB публикует хранимые процедуры, которые возвращают данные, как FunctionImport, которые возвращают коллекцию EntitySet.

Если хранимая процедура не поддерживает сохранение изменений, ODataDB помечает EntitySet как доступный только для чтения.

DBGate и ODataDB не поддерживают постраничную выдачу для хранимых процедур и возвращают полный набор данных.

Если процедура возвращает очень большой набор данных, вы можете использовать параметры @top и @skip для реализации выдачи страниц внутри процедуры.

SaveToDB и DBEdit игнорируют эти параметры, всегда передавая NULL.

Ниже приведен пример использования параметров в процедуре Microsoft SQL Server:

CREATE PROCEDURE [s01].[usp_cashbook]
    @account nvarchar(50) = NULL
    , @item nvarchar(50) = NULL
    , @company nvarchar(50) = NULL
    , @top int = NULL
    , @skip int = NULL
AS
BEGIN
SET NOCOUNT ON

SELECT
    t.id
    , CAST(t.[date] AS datetime) AS [date]
    , t.account
    , t.item
    , t.company
    , t.debit
    , t.credit
FROM
    s01.cashbook t
WHERE
    COALESCE(t.account, '') = COALESCE(@account, t.account, '')
    AND COALESCE(t.item, '') = COALESCE(@item, t.item, '')
    AND COALESCE(t.company, '') = COALESCE(@company, t.company, '')
ORDER BY
    t.id
OFFSET COALESCE(@skip, 0) ROWS FETCH NEXT COALESCE(@top, 1000000) ROWS ONLY

END

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

Microsoft Excel имеет специфические требования для получения данных из хранимых процедур с использованием драйверов OLEDB и ODBC.

Используйте образцы кода ниже, если другие варианты выдают ошибки получения данных в Microsoft Excel.

Также, вы можете использовать предложенные решения для разрешения конфликта имен параметров и полей таблиц.

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

Пример хранимой процедуры для получения данных из 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
//

Пример хранимой процедуры для получения данных из 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
$$;

Пример хранимой процедуры для получения данных из 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;
/

Пример хранимой процедуры для получения данных из 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
%%

--#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
@@

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

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