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; 解决方法根据您的评论
……这应该有效,但也许并不像你期望的那样,因为你没有定义“第一个”的含义. 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并决定什么去做.取决于异常条件对您和您的应用程序的意义. (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |