Firebird 2.1 存储过程用于连接多行文本

发布于 2024-10-04 01:51:42 字数 725 浏览 2 评论 0原文

我正在尝试编写一个存储过程来将多行文本连接在一起，以将其作为单个字符串返回。例如：

CREATE TABLE TEST (
 ID INTEGER,
 SEQ INTEGER,
 TEXT VARCHAR(255));

COMMIT;

INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 1, "LINE 1");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 2, "LINE 2");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 3, "LINE 3");

COMMIT;

SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
  RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
  CONCAT = '';
  FOR SELECT TEXT FROM TEST where id=:iID INTO :name
  DO BEGIN
    CONCAT = CONCAT || name;
  END
END!!
SET TERM ;!!

commit;

但是当我运行时：

select concat from concat_names(1);

它总是返回零行。

有什么想法吗？

原文

I am trying to write a stored procedure to concatenate multiple rows of text together to return it as a single string. For example:

CREATE TABLE TEST (
 ID INTEGER,
 SEQ INTEGER,
 TEXT VARCHAR(255));

COMMIT;

INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 1, "LINE 1");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 2, "LINE 2");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 3, "LINE 3");

COMMIT;

SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
  RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
  CONCAT = '';
  FOR SELECT TEXT FROM TEST where id=:iID INTO :name
  DO BEGIN
    CONCAT = CONCAT || name;
  END
END!!
SET TERM ;!!

commit;

However when I run:

select concat from concat_names(1);

It always returns zero rows.

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往日 2024-10-11 01:51:42

你忘记了暂停。您的过程应该如下所示：

SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
  RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
  CONCAT = '';
  FOR SELECT TEXT FROM TEST where id=:iID INTO :name
  DO BEGIN
    CONCAT = CONCAT || name;
  END
  SUSPEND;
END!!
SET TERM ;!!

您可以在没有存储过程的情况下获得相同的结果。使用LIST聚合函数：

SELECT LIST(text, '') FROM TEST where id=:iID

LIST的第二个参数是分隔符。如果仅使用字段名称调用 LIST，则将使用逗号“,”来分隔值。

You forget for SUSPEND. Your proc should look like this:

SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
  RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
  CONCAT = '';
  FOR SELECT TEXT FROM TEST where id=:iID INTO :name
  DO BEGIN
    CONCAT = CONCAT || name;
  END
  SUSPEND;
END!!
SET TERM ;!!

You can achieve the same result without stored proc. Use LIST aggregate function:

SELECT LIST(text, '') FROM TEST where id=:iID

Second parameter of LIST is a delimiter. If you call LIST with only field name, then comma ',' will be used to separate values.

回复收藏 0 原文

梦巷 2024-10-11 01:51:42

如果字段 TEST 可以为 null 并且您不想将整个结果设置为 null，则可以使用：

 CONCAT = CONCAT || coalesce(name,'');

而不是

CONCAT = CONCAT || name;

In the case the field TEST can ben null and you don't want to set to null the whole result it is useful to use:

 CONCAT = CONCAT || coalesce(name,'');

instead of

CONCAT = CONCAT || name;

回复收藏 0 原文

幸福不弃 2024-10-11 01:51:42

如果不使用存储过程并使用 Firebird 2.5 版本，LIST 聚合函数将返回“列中非 NULL 值的逗号分隔字符串串联”*。使用前面提到的 TEST 表，SQL

SELECT LIST(TEXT)
    FROM TEST

LINE 1,LINE 2,LINE 3

这可能会引起一些兴趣。

*取自 Firebird 参考页面此处

Without utilizing a Stored Proc and using version Firebird 2.5, the LIST aggregation function will return "Comma-separated string concatenation of non-NULL values in the column"*. Using the aforementioned TEST table, the SQL