oracle – 是否可以行使sql％rowcount举办SELECT？

下面的代码也许会返回多行.叨教sql％rowcount是否返回获取的行数？

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

这是我的样本进程;我以正确的方法行使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;

办理要领

If 2nd ‘select’ query returns more than one row i want to take the first one and process with it

……这应该有用,但大概并不像你祈望的那样,由于你没有界说“第一个”的寄义.

CREATE PROCEDURE Procn(in_Hid IN 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,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;

非常处理赏罚措施合用于整个block.假如任何select语句没有找到任何行,则no_data_found非常将由该块处理赏罚,并将outretvalue配置为-12.

假如你想为每个select选择差异的outretvalue,那么你可以将它们包装在子块中,每个子块都有本身的非常处理赏罚部门：

CREATE PROCEDURE Procn(in_Hid IN 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,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;

假如挪用者必要知道哪个选择失败,你只必要这样做,假如你真的没有想到它们中的任何一个失败那么也许更常见的是基础不捕捉非常并让挪用者看到原始的no_data_found并抉择什么去做.取决于非常前提对您和您的应用措施的意义.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!