oracle 是否可以将 sql%rowcount 用于 SELECT？

Is it possible to use sql%rowcount for SELECT?

提问by user1

The code below may return more than one row. Will sql%rowcountreturn the number of rows fetched?

下面的代码可能返回多于一行。会sql%rowcount返回获取的行数吗？

select * from emp where empname = 'Justin' and dept='IT'
if sql%rowcount>0
    ...

This is my sample proc; am I using sql%rowcountin correct way?

这是我的示例程序；我是否sql%rowcount以正确的方式使用？

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;

采纳答案by Alex Poole

Based on your comment

根据您的评论

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;

---

---

The exceptionhandler applies to the whole block. If any of the selectstatements find no rows, the no_data_foundexception will be handled by that block and will set outretvalueto -12.

该exception处理器适用于整个块。如果任何select语句未找到任何行，则no_data_found异常将由该块处理并设置outretvalue为-12。

If you want a different outretvaluefor each selectthen you can wrap them in sub-blocks, each with their own exception handling section:

如果您想要outretvalue每个都不同，select那么您可以将它们包装在子块中，每个子块都有自己的异常处理部分：

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;

You only need to do that if the caller needs to know which selectfailed, 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_foundand decide what to do. Depends what the exception condition means to you and your application though.

如果调用者需要知道哪个select失败了，并且您从未真正期望它们中的任何一个失败，那么您只需要这样做，那么根本不捕获异常并让调用者查看原始数据no_data_found并决定做什么可能更常见. 取决于异常条件对您和您的应用程序意味着什么。

回答by Codo

Yes, you can use SQL%ROWCOUNT. It's valid in PL/SQL.

是的，您可以使用SQL%ROWCOUNT. 它在 PL/SQL 中有效。

However, in PL/SQL the result of your query needs to go somewhere e.g. into a PL/SQL table. PL/SQL will never send the result to the output(terminal, window etc.). So SELECT * FROMwon't work.

但是，在 PL/SQL 中，您的查询结果需要放在某个地方，例如放入 PL/SQL 表中。PL/SQL 永远不会将结果发送到输出（终端、窗口等）。所以SELECT * FROM不会工作。

Your code could look like this:

您的代码可能如下所示：

DECLARE
  TYPE emp_t ...;
  emp_tab emp_t;

BEGIN
  SELECT *
  BULK COLLECT INTO emp_tab
  FROM emp
  WHERE empname = 'Justin' AND dept='IT';

  IF sql%rowcount > 0 THEN
    .. do something ...
  END IF;
END;
/

Update:

更新：

The updated questions suggests that you're looking for something else.

更新的问题表明您正在寻找其他东西。

Option 1: Use exceptions

选项 1：使用异常

If there are 0 rows or more than 1 row, these cases are handled separately (as errors):

如果有 0 行或多于 1 行，则分别处理这些情况（作为错误）：

BEGIN
  select PORT_NUMBER,STATIC_IP into outport, outIP
  from TINST
  where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    outretvalue := -12;
    RETURN;

  WHEN TOO_MANY_ROWS THEN
    outretvalue := -13;
    RETURN;
END;

Option 2: Use aggregations

选项 2：使用聚合

Using aggregations, the query will always return exactly one row. If now source row matched the WHERE clause, then both result values will be NULL. If there WHERE clause matched more than one row, the maximum will be taken.

使用聚合，查询将始终只返回一行。如果现在源行与 WHERE 子句匹配，则两个结果值都将为 NULL。如果 WHERE 子句匹配多于一行，则取最大值。

Note that this query might return a port number and an IP address that originally were not on the same row.

请注意，此查询可能会返回最初不在同一行上的端口号和 IP 地址。

select MAX(PORT_NUMBER), MAX(STATIC_IP) into outport, outIP
from TINST
where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y';

IF outport IS NULL OR outIP IS NULL THEN
    outretvalue := -12;
    RETURN;
END IF;

Option 3: Use ROWNUM

选项 3：使用 ROWNUM

This query returns at most one row. If no row matched the WHERE clause, an exception is thrown and needs to be handled:

此查询最多返回一行。如果没有行匹配 WHERE 子句，则抛出异常并需要处理：

BEGIN
  select PORT_NUMBER, STATIC_IP into outport, outIP
  from TINST
  where INST_ID = in_Hid AND IP_PORT_STATUS = 'Y'
  AND ROWNUM = 1;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    outretvalue := -12;
    RETURN;

END;