在SELECT语句中使用声明的变量 [英] Use a declared variable in a SELECT statement
问题描述
我正在使用Oracle 10g,需要在 SELECT 的where子句中使用变量;例如.
I'm using Oracle 10g and need to use a variable in a where clause of a SELECT; eg.
DECLARE
v_blah NUMBER;
BEGIN
v_blah := 13;
SELECT * FROM PEOPLE p WHERE p.LuckyNumber = v_blah;
END;
但出现错误提示
PLS-00428:此SELECT语句中应包含一个INTO子句
PLS-00428: an INTO clause is expected in this SELECT statement
它在DELETE或INSERT语句中似乎可以正常工作,所以我不确定为什么它在这里不起作用.
It seems to work fine in a DELETE or INSERT statement, so I'm not sure why it wouldn't work here.
推荐答案
正确的语法是:
DECLARE
v_blah NUMBER := 13;
v_people_rec PEOPLE%ROWTYPE;
BEGIN
SELECT * INTO v_people_rec FROM PEOPLE p WHERE p.LuckyNumber = v_blah;
END;
PL/SQL中的 select语句需要一个存储查询结果的地方.在此示例中,该位置是v_people_rec
变量.
The select statement in PL/SQL requires a place where store the query result. In this example the place is v_people_rec
variable.
上面的示例期望恰好返回一行.在其他情况下,它将引发异常NO_DATA_FOUND
或TOO_MANY_ROWS
.
The example above expects exactly one row to be returned. In other cases it will throw exceptions NO_DATA_FOUND
or TOO_MANY_ROWS
.
这篇关于在SELECT语句中使用声明的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!