假设我有一个名为"People"
的表,带有两个列"FirstName"
和"LastName"
,以及以下函数,引用了该表(:
CREATE FUNCTION PUBLIC."SelectPeopleByFirstName" (
IN "FirstName" VARCHAR(100)
)
RETURNS TABLE (
"FirstName" VARCHAR(100)
, "LastName" VARCHAR(100)
)
READS SQL DATA
RETURN TABLE (
SELECT
"People"."FirstName"
, "People"."LastName"
FROM
PUBLIC."People"
WHERE
"People"."FirstName" = "FirstName"
)
问题是,在SELECT
语句的WHERE
子句中,标识符"FirstName"
被视为对表列"People"."FirstName"
而不是例程参数"FirstName"
。
现在,Google告诉我,我不是第一个遇到此问题的人,并且从我能找到的不同建议的解决方案中,我发现最令人满意的解决方案是用日常名称限定标识符,例如:
WHERE
"People"."FirstName" = "SelectPeopleByFirstName"."FirstName"
不幸的是,这似乎与HSQLDB不起作用,因为我遇到以下错误:
用户缺乏特权或找不到的对象:selectpeoplebyfirstname.firstname/错误代码:-5501/state:42501
当然,我可以给常规参数一个不同的,独特的名称,但是我认为这比解决方案更像是解决方法,因为那样的是,例程的功能将取决于"人"列的名称与例程参数相同,这是可以在不知道的情况下改变的东西。
所以我在这里做/理解错误的事情,还是我真的被迫诉诸于解决这个难题?
您需要为变量使用不同的名称。用户通常使用命名约定作为参数,在此示例中显示了与 _P
一起附加到名称的HSQLDB指南。
CREATE PROCEDURE test_proc(INOUT val_p INT, IN lastname_p VARCHAR(20))
MODIFIES SQL DATA
BEGIN ATOMIC
SET val_p = 0;
for_label: FOR SELECT * FROM customer WHERE lastname = lastname_p DO
IF val_p > 0 THEN
DELETE FROM customer WHERE customer.id = id;
END IF;
SET val_p = val_p + 1;
END FOR for_label;
END
和HSQLDB不允许您重命名例程中使用的表列,因此以后更改不会出错。
从弗雷德(Fredt(的答案中得知不可能限定参数名称后,我提出了一个不太优雅的构造,尽管如此,它仍然解决了我在问题中解决的问题。我不是直接从"People"
表中选择返回表,该表可以具有任何例行程序不知道的任意名称的任何数量的列,而是创建一个子查询,作为"People"
表的"管道",并从此子查询中进行选择。:
CREATE FUNCTION PUBLIC."SelectPeopleByFirstName" (
IN "FirstName_Param" VARCHAR(100)
)
RETURNS TABLE (
"FirstName" VARCHAR(100)
, "LastName" VARCHAR(100)
)
READS SQL DATA
RETURN TABLE (
SELECT
"FirstName"
, "LastName"
FROM (
SELECT
"FirstName"
, "LastName"
FROM
PUBLIC."People"
)
WHERE
"FirstName" = "FirstName_Param"
)
现在,WHERE
子句中的标识符"FirstName_Param"
将始终引用例程参数,即使出于某种不可思议的原因,某人在表"People"
中添加了名为"FirstName_Param"
的列,因为表"People"
在表中不超出范围。WHERE
子句。