SQL 选择单个列值并将其存储在变量oracle sql中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9934605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select a single column value and store it in variable oracle sql
提问by Th3sandm4n
I want to grab a particular column value a.id and store it into a variable v_id. Then use this value to pass into a stored procedure.
我想获取特定的列值 a.id 并将其存储到变量 v_id 中。然后使用此值传递到存储过程。
DECLARE v_id a.id%TYPE;
BEGIN
SELECT id into v_id from a where a.name='test' and rownum <2 order by id desc;
Print v_id;
doSomething(v_id);
END;
/
I'm getting this error in Oracle SQL Developer:
我在 Oracle SQL Developer 中收到此错误：
Error report: ORA-06550: line 3, column 7: PLS-00103: Encountered the symbol "V_ID" when expecting one of the following:
:= . ( @ % ; The symbol ":=" was substituted for "V_ID" to continue. 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
错误报告：ORA-06550：第 3 行，第 7 列：PLS-00103：在预期以下情况之一时遇到符号“V_ID”：
：=。( @ % ; 符号 ":=" 被替换为 "V_ID" 以继续。06550. 00000 - "line %s, column %s:\n%s" *Cause: 通常是 PL/SQL 编译错误。*Action ：
回答by Ben
If you want to use rownumand order byyou haveto put the order by in a sub-query. There is no other way to guarantee that you get the correct value.
如果您想使用rownum并且order by您必须将订单放在子查询中。没有其他方法可以保证您获得正确的值。
It's also good practice to deal with the possibility that there may not be an idthat matches your query. I've added an additional begin... end;block to deal with this.
处理可能没有id与您的查询匹配的可能性也是一种很好的做法。我添加了一个额外的begin... end;块来处理这个问题。
declare
v_id a.id%type;
begin
begin
select id into v_id
from ( select id
from a
where name = 'test'
order by id desc )
where rownum < 2
;
exception when no_data_found then
v_id := null;
end;
dbms_output.put_line(v_id);
doSomething(v_id);
end;
/
As @raukh noted (whilst I was writing this!) the problem is print, which should be dbms_output.put_line()
正如@raukh 指出的（虽然我正在写这个！）问题是print，应该是dbms_output.put_line()
回答by ruakh
This:
这个：
Print v_id;
is not valid. Probably you meant:
无效。可能你的意思是：
dbms_output.put_line(v_id);
? (Note that you may need to run
? （请注意，您可能需要运行
set serveroutput on;
beforehand in order for the above to have an effect.)
事先为了使上述内容生效。）
回答by Justin Cave
PRINTisn't a valid PL/SQL command, so that's going to create a problem. Perhaps you wanted
PRINT不是有效的 PL/SQL 命令，因此会产生问题。也许你想要
DECLARE
v_id a.id%TYPE;
BEGIN
SELECT id
into v_id
from (SELECT id
FROM a
where a.name='test'
order by id desc)
where rownum < 2;
dbms_output.put_line( v_id );
doSomething(v_id);
END;
/
