为什么此SQL存储过程需要创建一个临时表才能使其正常工作(返回结果)?

问题描述

IBM Informix Dynamic Server版本11.50.FC6

IBM Informix Dynamic Server Version 11.50.FC6

我正在研究一个小的存储过程，该过程将从表中获取名称字段，并将其解析为最多8个字符的用户名".

I was working on a small stored procedure that would take name fields from a table and parse them into "user names" with a maximum of 8 chars.

这是我正在尝试的代码:

This is the code I was trying:

CREATE PROCEDURE build_jics_user (pid INT)
    RETURNING CHAR(8) AS username;
    SELECT LOWER((SUBSTR(firstname,0,1))||(SUBSTR(lastname,0,7))) username
    FROM id_rec
    WHERE id = pid;
END PROCEDURE;

执行时返回的错误是:

  659: INTO TEMP table required for SELECT statement.
Error in line 5
Near character position 15

我不明白召集临时表的意义是什么，并且我也找不到任何类似的简单示例，可以在没有错误的情况下正常工作.

I don't understand what the point of summoning a temporary table is, and I also couldn't find any similarly simple examples online that would work without error.

有人知道我想念什么吗?

Does anyone know what I'm missing?

推荐答案

您要说的是这样:

CREATE PROCEDURE build_jics_user (pid INT)
    RETURNING CHAR(8);
    DEFINE username CHAR(8);
    SELECT LOWER((SUBSTR(firstname,0,1))||(SUBSTR(lastname,0,7))) INTO username
      FROM id_rec
      WHERE id = pid;
    RETURN username;
END PROCEDURE;

...并像这样执行它:

... and execute it like this:

EXECUTE PROCEDURE build_jics_user(42);

更新

如果此操作的目的是作为一个函数，而在其他一些SQL中则需要此函数，则可以执行以下操作:

If the purpose of this is to be a function, where it's required inside some other SQL, then you might do the following:

CREATE FUNCTION jics_user(fname VARCHAR(255), lname VARCHAR(255))
    RETURNING CHAR(8);
    RETURN LOWER(SUBSTR(fname,0,1) || SUBSTR(lname,0,7));
END FUNCTION;

...并像这样执行它:

... and execute it like this:

SELECT id, firstname, lastname, jics_user(firstname, lastname) AS jics_user, ...
  FROM id_rec;

PROCEDURE和FUNCTION之间没有真正的技术区别，更多地是关于其用法的断言.

There's no real technical difference between a PROCEDURE and a FUNCTION, it's more an assertion as to how it's used.

这篇关于为什么此SQL存储过程需要创建一个临时表才能使其正常工作(返回结果)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！