是否可以将sql%rowcount用于SELECT? [英] Is it possible to use sql%rowcount for SELECT?
问题描述
下面的代码可能返回多个行. sql%rowcount
会返回获取的行数吗?
The code below may return more than one row. Will sql%rowcount
return the number of rows fetched?
select * from emp where empname = 'Justin' and dept='IT'
if sql%rowcount>0
...
这是我的示例处理;我是否以正确的方式使用sql%rowcount
?
This is my sample proc; am I using sql%rowcount
in correct way?
CREATE PROCEDURE Procn(in_Hid IN VARCHAR2,outInststatus OUT VARCHAR2,outSockid IN NUMBER,outport OUT VARCHAR2,outIP OUT VARCHAR2,outretvalue OUT NUMBER)
AS
BEGIN
select INST_STATUS into outInststatus from TINST_child where INST_ID = in_Hid and INST_STATUS = 'Y';
if outInststatus = 'Y' then
select PORT_NUMBER,STATIC_IP into outport,outIP from TINST where INST_ID = in_Hid and IP_PORT_STATUS = 'Y';
if sql%rowcount >= 1 then
select SOCK_ID into outSockid from TINST where PORT_NUMBER = outport AND STATIC_IP = outIP;
outretvalue := 0;
else
outretvalue := -12;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outretvalue := -13;
end if;
END;
推荐答案
根据您的评论
如果第二个选择"查询返回多个行,我想获取第一行并进行处理
If 2nd 'select' query returns more than one row i want to take the first one and process with it
...这应该可行,但可能不符合您的期望,因为您尚未定义第一个"的含义.
... this ought to work, but perhaps not quite as you expect, as you haven't defined what the 'first one' means.
CREATE PROCEDURE Procn(in_Hid IN VARCHAR2, outInststatus OUT VARCHAR2,
outSockid IN NUMBER, outport OUT VARCHAR2, outIP OUT VARCHAR2,
outretvalue OUT NUMBER)
AS
BEGIN
select INST_STATUS into outInststatus
from TINST_child
where INST_ID = in_Hid and INST_STATUS = 'Y';
-- no need to check if outInstatus is Y, that's all it can be here
-- restricting with `rownum` means you'll get at most one row, so you will
-- not get too_many_rows. But it will be an arbitrary row - you have no
-- criteria to determine which of the multiple rows you want. And you can
-- still get no_data_found which will go to the same exception and set -12
select PORT_NUMBER, STATIC_IP into outport, outIP
from TINST
where INST_ID = in_Hid and IP_PORT_STATUS = 'Y'
and rownum < 2;
-- no need to check sql%rowcount; it can only be 1 here
-- not clear if this can return multiple rows too, and what should happen
-- if it can; could use rownum restriction but with the same caveats
select SOCK_ID into outSockid
from TINST
where PORT_NUMBER = outport AND STATIC_IP = outIP;
outretvalue := 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outretvalue := -12;
END;
exception
处理程序适用于整个块.如果任何select
语句均未找到任何行,则no_data_found
异常将由该块处理,并将outretvalue
设置为-12
.
The exception
handler applies to the whole block. If any of the select
statements find no rows, the no_data_found
exception will be handled by that block and will set outretvalue
to -12
.
如果每个select
都希望使用不同的outretvalue
,则可以将它们包装在子块中,每个子块都有自己的异常处理部分:
If you want a different outretvalue
for each select
then you can wrap them in sub-blocks, each with their own exception handling section:
CREATE PROCEDURE Procn(in_Hid IN VARCHAR2, outInststatus OUT VARCHAR2,
outSockid IN NUMBER, outport OUT VARCHAR2, outIP OUT VARCHAR2,
outretvalue OUT NUMBER)
AS
BEGIN
BEGIN
select INST_STATUS into outInststatus
from TINST_child
where INST_ID = in_Hid and INST_STATUS = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
outretvalue := -12;
END;
BEGIN
select PORT_NUMBER, STATIC_IP into outport, outIP
from TINST
where INST_ID = in_Hid and IP_PORT_STATUS = 'Y'
and rownum < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outretvalue := -13;
END;
BEGIN
select SOCK_ID into outSockid
from TINST
where PORT_NUMBER = outport AND STATIC_IP = outIP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outretvalue := -14;
END;
outretvalue := 0;
END;
仅在调用方需要知道哪个select
失败的情况下才需要这样做,并且如果您从未真正期望它们中的任何一个失败,则可能更常见的是根本不捕获异常并让调用方看到raw no_data_found
并决定要怎么做.但这取决于异常条件对您和您的应用程序意味着什么.
You only need to do that if the caller needs to know which select
failed, and if you never really expect any of them to fail then it's probably more common not to catch the exception at all and let the caller see the raw no_data_found
and decide what to do. Depends what the exception condition means to you and your application though.
这篇关于是否可以将sql%rowcount用于SELECT?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!