在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屋！