选择一个列值并将其存储在变量oracle sql中 [英] Select a single column value and store it in variable oracle sql

问题描述

我想获取特定的列值a.id并将其存储到变量v_id中.然后使用此值传递到存储过程.

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.

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;
/

我在Oracle SQL Developer中遇到此错误:

I'm getting this error in Oracle SQL Developer:

错误报告:ORA-06550:第3行，第7列:PLS-00103:遇到了 预期以下之一时，输入符号"V_ID":

Error report: ORA-06550: line 3, column 7: PLS-00103: Encountered the symbol "V_ID" when expecting one of the following:

:=. (@％;将符号:="替换为"V_ID" 继续. 06550.00000-％s行，％s列:\ n％s" *原因:通常是PL/SQL编译错误. *动作:

:= . ( @ % ; 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:

推荐答案

如果要使用rownum和order by，则必须在子查询中下达订单.没有其他方法可以保证您获得正确的值.

If you want to use rownum and order by you have to put the order by in a sub-query. There is no other way to guarantee that you get the correct value.

处理可能没有与您的查询匹配的id的可能性也是一种很好的做法.我添加了一个额外的begin... end;块来解决这个问题.

It's also good practice to deal with the possibility that there may not be an id that matches your query. I've added an additional begin... end; block to deal with this.

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;
/

正如@raukh所指出的(虽然我正在写这篇文章！)问题是print，应该是dbms_output.put_line()

As @raukh noted (whilst I was writing this!) the problem is print, which should be dbms_output.put_line()

这篇关于选择一个列值并将其存储在变量oracle sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！