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

Все продукты 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 | 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;
/

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

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