在用户定义的表类型HANA中的OUT参数中插入多行

I am stuck at a place.

我被困在一个地方。

There is a procedure that checks for something and inserts into an table type upon successful determination of that condition.

在成功确定该条件后，有一个过程检查某些内容并插入到表类型中。

But i can insert only once in the table type. Is there a way to insert again and again into the table type.

但我只能在表格类型中插入一次。有没有办法一次又一次地插入表格类型。

    PROCEDURE "hello"."helloWorld.db::sampleException" (OUT TRACE_RECORD "hello"."LogTrace" )
    LANGUAGE SQLSCRIPT AS
BEGIN

DECLARE i int;

select count(*) into i from "hello"."REGION";
IF :i > 1 then 
TRACE_RECORD = SELECT '1' AS "LogID", '1' AS "TraceID" FROM DUMMY;
 end if;
IF :i > 2 then 
TRACE_RECORD = SELECT '2' AS "LogID", '2' AS "TraceID" FROM DUMMY;
end if;

END;

What i get on executing the procedure is only the last record "2,2".

我执行该程序所得到的只是最后一条记录“2,2”。

How can i insert both the records 1,1 and 2,2.

我如何插入记录1,1和2,2。

Note: I do not want to use Temporary Tables.

注意：我不想使用临时表。

Any help on this..

对此有任何帮助..

Thanks.!

谢谢。！

Editing the Question a bit:

编辑问题：

-I have to use Table TYPE (till the time there is no optimal way better than it)

- 我必须使用表格类型（直到没有比它更好的最佳方式）

-I have to insert more than 20-30 records in the table type.

- 我必须在表格类型中插入超过20-30条记录。

1 个解决方案

#1

Do you have to write this as a procedure? A table-valued function seems more suitable:

你必须把它写成一个程序吗？表值函数似乎更合适：

CREATE FUNCTION f_tables4 (in_id INTEGER)
RETURNS TABLE (
    "LogID" VARCHAR(400),
    "TraceID" VARCHAR(400)
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    RETURN 
    SELECT t."LogID", t."TraceID"
    FROM (
        SELECT 1 AS i, '1' AS "LogID", '1' AS "TraceID" FROM DUMMY
        UNION ALL
        SELECT 2 AS i, '2' AS "LogID", '2' AS "TraceID" FROM DUMMY
    ) t
    JOIN (SELECT count(*) AS cnt FROM "hello"."REGION") c
    ON c.cnt > t.i
END