您想说的是:
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;
…并像这样执行它:
EXECUTE PROCEDURE build_jics_user(42);
如果此操作的目的是成为一个函数(在其他一些sql中需要此函数),则可以执行以下操作:
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;
…并像这样执行它:
SELECT id, firstname, lastname, jics_user(firstname, lastname) AS jics_user, ...
FROM id_rec;
PROCEDURE和FUNCTION之间没有真正的技术区别,更多地是关于如何使用它的断言。