Informix，初始化存储过程中的变量

Question

我在 Informix 数据库上有一个存储过程。我想从该过程的执行中获取一些变量。

我不知道如何创建存储过程。

这是我到目前为止所拥有的：

CREATE PROCEDURE foo()
RETURN somebar;
END PROCEDURE;

当我运行它时，我收到一个错误：

DEFINE somebar

我想这样做：

execute procedure foo() into bar;

如何执行过程 foo() 并将返回值放入 bar？

Answer 1

这是一个例子。基本上，RETURNING 子句列出类型（可选地后跟 AS 名称 - 但该名称不声明变量）；然后在过程的主体中，您“照常”定义变量，然后返回它们。

这个特定的示例是 Oracle NEXT_DAY 函数的模拟，但不包括全球化。该文件包含自测试：

示例过程

-- @(#)$Id: next_day.spl,v 1.1 2004/10/05 21:39:18 jleffler Exp $
--
-- @(#)Create procedure equivalent to Oracle's NEXT_DAY() function.
-- Bugs: not internationalized.

CREATE PROCEDURE 'oracle'.next_day(dateval DATE, dayname CHAR(3))
    RETURNING DATE AS next_date;

    DEFINE rv DATE;     -- Return value.
    DEFINE dw INTEGER;  -- Weekday corresponding to dayname.
    DEFINE wd INTEGER;  -- Weekday corresponding to dateval.
    DEFINE dn CHAR(3);

    LET rv = NULL;

    IF dateval IS NOT NULL THEN
        LET dw = NULL;
        LET dn = UPPER(dayname);
        IF   dn = 'SUN' THEN LET dw = 0;
        ELIF dn = 'MON' THEN LET dw = 1;
        ELIF dn = 'TUE' THEN LET dw = 2;
        ELIF dn = 'WED' THEN LET dw = 3;
        ELIF dn = 'THU' THEN LET dw = 4;
        ELIF dn = 'FRI' THEN LET dw = 5;
        ELIF dn = 'SAT' THEN LET dw = 6;
        END IF;
        IF dw IS NOT NULL THEN
            LET wd = WEEKDAY(dateval);
            LET rv = dateval - wd + dw;
            IF wd >= dw THEN
                LET rv = rv + 7;
            END IF;
        END IF;
    END IF;

    RETURN rv;

END PROCEDURE;

自测试代码

--     February 2001
--   S  M Tu  W Th  F  S
--               1  2  3
--   4  5  6  7  8  9 10
--  11 12 13 14 15 16 17
--  18 19 20 21 22 23 24
--  25 26 27 28

CREATE TEMP TABLE test_next_day
(
    ref_date    DATE NOT NULL,
    nxt_day     CHAR(9) NOT NULL,
    exp_date    DATE NOT NULL
);

INSERT INTO test_next_day VALUES(MDY(02,01,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,03,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,04,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,05,2001), 'Tuesday',   MDY(02,06,2001));
INSERT INTO test_next_day VALUES(MDY(02,06,2001), 'Tuesday',   MDY(02,13,2001));
INSERT INTO test_next_day VALUES(MDY(02,07,2001), 'Tuesday',   MDY(02,13,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Monday',    MDY(02,05,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Wednesday', MDY(02,07,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Thursday',  MDY(02,08,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Friday',    MDY(02,09,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Saturday',  MDY(02,03,2001));
INSERT INTO test_next_day VALUES(MDY(02,02,2001), 'Sunday',    MDY(02,04,2001));

SELECT ref_date, nxt_day, WEEKDAY(ref_date) AS ref_dow,
       exp_date, WEEKDAY(exp_date) AS exp_dow,
       NEXT_DAY(ref_date, nxt_day) AS got_date
    FROM test_next_day
    INTO TEMP test_next_day2;

SELECT *
    FROM test_next_day2
    ORDER BY ref_date, nxt_day;

SELECT "**FAILED**" AS failed, *
    FROM test_next_day2
    WHERE (exp_date IS NOT NULL AND got_date IS     NULL)
       OR (exp_date IS     NULL AND got_date IS NOT NULL)
       OR (exp_date IS NOT NULL AND got_date IS NOT NULL AND exp_date != got_date)
    ORDER BY ref_date, nxt_day;