blocks|key|3461763|text|通常，只为已存在的记录做工作更像SQL。换句话说，您可以在每次匹配时执行任务，如果没有匹配，则不执行该任务。因此，您甚至不需要IF-ELSE构造。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3461764|我不建议使用游标来完成这项工作，因为这将与我最初建议的更像SQL的做法背道而驰。但是如果你必须这样做，那么光标可能会做你想做的事情。|3461765|是的，我知道这并没有直接回答你的问题。|3461766|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

It is generally more SQL-like to just do the work for the records that exist. In other words, you could perform your task for each occurrence of the match, and if there are no occurrences you don't do it. So you would not even need the IF-ELSE construct.

I won't recommend using a cursor to do the work, because that would be counter to my first suggestion that you do it more SQL-like. But if you have to do it this way, then a cursor might do what you want.

And yes, I realize that does not answer your question directly.

blocks|key|3248472|text|使用异常处理程序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3248473|Begin
++select+column
++into+variable
++from+table
++where+...;

++--+Do+something+with+your+variable

exception
+when+no_data_found+then
++++--+Your+query+returned+no+rows+--

+when+too_many_rows
++++--+Your+query+returned+more+than+1+row+--

end;|code-block|syntax|javascript|3248474|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use an exception handler

<pre><code>Begin
 select column
 into variable
 from table
 where ...;

 -- Do something with your variable

exception
 when no_data_found then
 -- Your query returned no rows --

 when too_many_rows
 -- Your query returned more than 1 row --

end;
</code></pre>

blocks|key|3461802|text|我首先想到的也是异常处理，但如果您不想处理所有不同的情况，我倾向于使用select+count(*)+from。count(*)的好处是它总是返回一些东西(假设你的查询是合法的)。在这种情况下，您可以计数以查看它是否返回0(无匹配)或更多(在这种情况下，您可以执行某些操作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3461803|你可以得到类似这样的东西：|3461804|declare
++v_count+number+:=+0;
begin
++select+count(*)+into+v_count+from+table+where+condition;

++if+v_count+=+0+then
++++++--do+something
++else
++++++--do+something+else
++end+if;
end;|code-block|syntax|javascript|3461805|entityMap^0|Z|K|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Exception handling would be the first thing I think of too, but if you don't want to burden yourself with handling all the different cases, I tend to use a <code>select count(*) from</code>. The nice thing with count(*) is that it ALWAYS returns something (assuming your query is legal). In this case you could count to see if it returns 0 (no matches) or more (in which case you can do something.

You could get something like this:

<pre><code>declare
 v_count number := 0;
begin
 select count(*) into v_count from table where condition;

 if v_count = 0 then
 --do something
 else
 --do something else
 end if;
end;
</code></pre>

blocks|key|413141|text|首先捕获不想要的条件并使用count(1)，因为count(*)实际上尝试计数并添加rownum=1，您只需要一个first+not+catch条件。我使用这个语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|413142|+++++++declare
++++++v_check+number;
++++begin
++++++select+count(1)+into+v_check+
from+table
+where+condition(something+not+wanted)+AND+rownum=1;

++++++if+v_check+=+0+then+
+++++++++++--do+something+else
++++++elsif+v_check+=+1+--dont+want+theat
+++++++++rise+some+error+or+more..
++++++end+if;
++++end;|code-block|syntax|javascript|413143|对你来说只是|413144|select+count(1)+into+v_check+from+dual+where+exists+(select+count(1)+
++++from+table
+++++where+condition+AND+rownum=1);

if+v_check+=+0+then+--nothing+found
+++++++++++++++++something...
++++++++++elsif+v_check+=+1+--found+something
++++++++++++something...
++++++++++end+if;
++++++++end;|413145|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

catch first not wanted condition and use count(1) because count(*) actually trying to count something and add rownum=1, you need only one first not matching condition. i use this statement.

<pre><code> declare
 v_check number;
 begin
 select count(1) into v_check 
from table
 where condition(something not wanted) AND rownum=1;

 if v_check = 0 then 
 --do something else
 elsif v_check = 1 --dont want theat
 rise some error or more..
 end if;
 end;
</code></pre>

For you just 

<pre><code>select count(1) into v_check from dual where exists (select count(1) 
 from table
 where condition AND rownum=1);

if v_check = 0 then --nothing found
 something...
 elsif v_check = 1 --found something
 something...
 end if;
 end;
</code></pre>

I'm writing a procedure, and i need to check whether my select query returned an empty record or not.
(In this example whether there is no x,y shelf)

How can i do that?

I tried this:

<pre><code>temp shelves.loadability%TYPE := NULL;
BEGIN

select loadability into temp from shelves where rownumber = x and columnnumber = y;
IF temp IS NOT NULL THEN
/* do something when it's not empty */
ELSE
/* do the other thing when it's empty */
END IF;
</code></pre>

But the second branch of the if never works...

EDIT:

Oh, it was so easy...

<pre><code>temp shelves.loadability%TYPE;
BEGIN

select count(*) into temp from shelves where rownumber = x and columnnumber = y;
IF temp != 0 THEN
/* do something when it's not empty */
ELSE
/* do the other thing when it's empty */
END IF;

END;
</code></pre>

PL/SQL check if query returns empty

我正在编写一个过程，我需要检查我的select查询是否返回了空记录。(在此示例中是否没有x，y工具架)我该怎么做呢？我试过这个：temp shelves.loadability%TYPE := NULL;BEGINselect loadability into temp from shelves where rownu...

问PL/SQL检查查询是否返回空
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL/SQL检查查询是否返回空EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL/SQL检查查询是否返回空
EN