Informix程序-如何返回空表? [英] Informix procedure — how to return an empty table?

问题描述

如果我发现一些值，我需要创建一个Informix过程以返回带有行的表，如果找不到值，则返回一个空表.

I need to create an Informix procedure to return a table with rows if I found some value and an empty table if no value found.

我知道如何返回带有行的表，但是我不知道如何返回空表；有人可以帮忙吗?

I know how to return a table with rows, but I don't know how to return the empty table; can anyone help?

CREATE row type AType (
  id VARCHAR(255),
  name VARCHAR(255)
);


CREATE PROCEDURE getmeasurement (p_date DATETIME YEAR TO SECOND)
RETURNING MULTISET(AType NOT NULL);

    DEFINE AType_TABLE MULTISET (AType NOT NULL);
    DEFINE v_id VARCHAR(255);
    DEFINE v_name VARCHAR(255);
    ....
    IF( FoundValue ) THEN
        -- INSERT INTO TABLE
        INSERT INTO TABLE (AType_TABLE) VALUES (ROW(v_id,v_name)::AType);     
    ELSE
        // how to initial a AType_TABLE instance with empty records.
    END IF
    ....
    RETURN AType_TABLE;

END PROCEDURE;

推荐答案

尽管在

Despite what it says in the SQL Syntax Manual, SPL (Stored Procedure Language) procedures can return collection types (COLLECT, SET, MULTISET or LIST). I've reported a documentation bug against that — which misled me earlier this week.

在Mac OS X 10.7.5上，我无法在Informix 11.70.FC6上进行编译，但是出现的错误是:

I'm not able to get this procedure to compile under Informix 11.70.FC6 on Mac OS X 10.7.5, but the error it comes up with is:

SQL -9905: [Internal] No extended type information for domain.

尝试上述代码的各种变体时，我遇到了各种各样的问题.看来，在多集中的DELETE上不能有WHERE子句(不同的错误消息).如果回滚类型的创建，然后重试，也会遇到问题.

I've run into various issues trying various variants of the above code. You can't have a WHERE clause on a DELETE from a multiset, it seems (different error message). You can also run into problems if you rollback the creation of the type and then try again.

但是，我能够在Linux(RHEL 5，x86/64)上使用11.70.FC8的预发行版对其进行测试，并获得了所需的输出:

However, I was able to test it with a pre-release of 11.70.FC8 on Linux (RHEL 5, x86/64) and got the desired output:

CREATE ROW TYPE IF NOT EXISTS AType
(
    id   VARCHAR(255),
    name VARCHAR(255)
);

CREATE PROCEDURE getmeasurement (p_date DATETIME YEAR TO SECOND)
        RETURNING MULTISET(AType NOT NULL);

    DEFINE AType_TABLE MULTISET(AType NOT NULL);
    DEFINE v_id VARCHAR(255);
    DEFINE v_name VARCHAR(255);

    LET v_id = "Polynomial - " || p_date;
    LET v_name = "Euclid's Geometry of the Plane";
    INSERT INTO TABLE (AType_TABLE) VALUES(ROW(v_id, v_name)::AType);
    IF 1 = 1 THEN
        -- how TO initial a AType_TABLE instance WITH empty records.
        DELETE FROM TABLE(AType_TABLE);
    END IF

    RETURN AType_TABLE;

END PROCEDURE;

EXECUTE PROCEDURE getmeasurement(CURRENT);

ROLLBACK;

输出为:

MULTISET{}

这是一个空的多集.如果不先将某些内容插入多集中，则删除将失败.这是一件小事.

which is an empty multiset. If you don't insert something into the multiset first, the delete fails. This is a trifle odd.

您可能会对Informix 11.70.xC7感到满意；它可能也可以在其他版本上使用.但是可能有很多版本的Informix无法使用.

You may be OK with Informix 11.70.xC7; it may work on some other versions too. But there are likely to be a lot of versions of Informix where it does not work.

这篇关于Informix程序-如何返回空表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！