存储过程如何判断一条查询语句的结果为空

[tianhewulei]

我中间代码有一段是这么写的
tsql := 'select a.oid from entity_item where a.id = '||entityId;
execute immediate tsql into entityOId;

有的时候根据entityId并不能查出相应的entityOId,因为数据库中没这条记录，这个时候execute immediate into这一句就出错了，请问如何判断才能使程序正常运行，为空的时候我不做操作，不为空的时候查询出来的值保留，请不要告诉我用count（*）来判断是否有值，谢谢各位大侠了...

还有我在存储过程中写了一个if((select a.oid from entity_item where a.id = entityId) is null)这个判断，但好像存储过程并不能识别，这是怎么回事，我用的数据库是oracle。

[mantisXF]

--可以考虑扑获NO_DATA_FOUND异常事件
...
execute immediate tsql into entityOId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
...

[Quote=引用楼主 tianhewulei 的帖子:]
我中间代码有一段是这么写的
tsql := 'select a.oid from entity_item where a.id = '||entityId;
execute immediate tsql into entityOId;

有的时候根据entityId并不能查出相应的entityOId,因为数据库中没这条记录，这个时候execute immediate into这一句就出错了，请问如何判断才能使程序正常运行，为空的时候我不做操作，不为空的时候查询出来的值保留，请不要告诉我用count（*）来判断是否有值，谢谢各位大侠了...
…
[/Quote]

[bsh_ly]

DECLARE cy int;
v_sql varchar2(100);
begin
select count(1) into cy from ZAPDPF;
v_sql := 'delete from ZAPDPF';
execute immediate v_sql;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'RECORDS are influnced' );
rollback;
end;
/

[tianhewulei]

异常捕获了，但是我是要把寻找surname的这段代码放入到循环中，我第一次查询没查找到数据，循环就终止了，我希望的是代码能不管中间的空值，继续运行下面的循环，请问这该怎么做。

create or replace procedure CONVERT_PERSONNAME is
       surname varchar2(200);
       firstname varchar2(200);
       entityId number(19,0);
       tsql varchar2(500);
       num number;
       numcount number;
       cursor CursorPerson is select a.id from entity_item a where a.entity_type = 10;
begin
  if(CursorPerson%isopen = false) then
         open CursorPerson;
  end if;
  loop
      fetch CursorPerson into entityId;
      exit when CursorPerson%notfound;      
      tsql := 'select a.attribute_value from entity_item_value a where a.entity_id ='|| entityId ||' and a.attribute_id = 3';
      execute immediate tsql into surname;
      tsql := 'select a.attribute_value from entity_item_value a where a.entity_id = '||entityId||' and a.attribute_id = 4';
      execute immediate tsql into firstname;
  end loop;
  close CursorPerson;
exception when NO_DATA_FOUND then
begin 
  dbms_output.put_line('111111');
end; 
end CONVERT_PERSONNAME;

我的意思是希望，先找到surname的值，判断是否为空，如果不为空则继续查找firstname，为空则进入下次循环，同时firstname的处理也差不多，判断得到的值是否为空，如果为空则进入下次循环，如果不为空则将结果保留下来，各位大哥，帮帮忙把这段代码修改下啊，我实在不知道这存储过程该如何判断为空的情况。

[bsh_ly]

create or replace procedure CONVERT_PERSONNAME is
surname varchar2(200);
firstname varchar2(200);
entityId number(19,0);
tsql varchar2(500);
num number;
numcount number;
cursor CursorPerson is select a.id from entity_item a where a.entity_type = 10;
begin
if(CursorPerson%isopen = false) then
open CursorPerson;
end if;
loop
fetch CursorPerson into entityId;
exit when CursorPerson%notfound;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id ='|| entityId ||' and a.attribute_id = 3';
execute immediate tsql into surname;
if surname is null then
surname:='';
CONTINUE;
end if;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id = '||entityId||' and a.attribute_id = 4';
execute immediate tsql into firstname;
if firstname is null then
firstname := '';
CONTINUE;
end if;
end loop;
close CursorPerson;
end CONVERT_PERSONNAME;

[bsh_ly]

if surname is null then
surname:='';
firstname :='';
CONTINUE;
end if;



if firstname is null then
firstname := '';
surname:='';
CONTINUE;
end if;

[mantisXF]

-- TRY IT ..
CREATE OR REPLACE PROCEDURE CONVERT_PERSONNAME IS
  SURNAME   VARCHAR2(200);
  FIRSTNAME VARCHAR2(200);
  ENTITYID  NUMBER(19, 0);
  TSQL      VARCHAR2(500);
  NUM       NUMBER;
  NUMCOUNT  NUMBER;
  CURSOR CURSORPERSON IS
    SELECT A.ID FROM ENTITY_ITEM A WHERE A.ENTITY_TYPE = 10;
BEGIN
  IF (CURSORPERSON%ISOPEN = FALSE) THEN
    OPEN CURSORPERSON;
  END IF;
  LOOP
    FETCH CURSORPERSON
      INTO ENTITYID;
    EXIT WHEN CURSORPERSON%NOTFOUND;
    -- Add by mantisXF on Jan 19, 2009
    BEGIN
      TSQL := 'SELECT A.ATTRIBUTE_VALUE FROM ENTITY_ITEM_VALUE A WHERE A.ENTITY_ID =' ||
              ENTITYID || ' AND A.ATTRIBUTE_ID = 3';
      EXECUTE IMMEDIATE TSQL
        INTO SURNAME;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    -- End
  
    -- Add by mantisXF on Jan 19, 2009
    BEGIN
      TSQL := 'SELECT A.ATTRIBUTE_VALUE FROM ENTITY_ITEM_VALUE A WHERE A.ENTITY_ID = ' ||
              ENTITYID || ' AND A.ATTRIBUTE_ID = 4';
      EXECUTE IMMEDIATE TSQL
        INTO FIRSTNAME;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    -- End
  END LOOP;
  CLOSE CURSORPERSON;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Error came!!!');
    END;
END CONVERT_PERSONNAME;

[Quote=引用 3 楼 tianhewulei 的回复:]
异常捕获了，但是我是要把寻找surname的这段代码放入到循环中，我第一次查询没查找到数据，循环就终止了，我希望的是代码能不管中间的空值，继续运行下面的循环，请问这该怎么做。

SQL code
create or replace procedure CONVERT_PERSONNAME is
surname varchar2(200);
firstname varchar2(200);
entityId number(19,0);
tsql varchar2(500);
num number;
numcount number…
[/Quote]

[fosjos]

有一招很好用：
'select (select a.oid from entity_item where a.id = '||entityId||') from DUAL'

[fosjos]

如果不能确保一个值，可以加上rownum=1

[jdsnhan]

[Quote=引用 7 楼 fosjos 的回复:]
有一招很好用：
'select (select a.oid from entity_item where a.id = '||entityId||') from DUAL'
[/Quote]

这是个好主意。

[bsh_ly]

create or replace procedure CONVERT_PERSONNAME is
surname varchar2(200);
firstname varchar2(200);
entityId number(19,0);
tsql varchar2(500);
num number;
numcount number;
cursor CursorPerson is select a.id from entity_item a where a.entity_type = 10;
begin
if(CursorPerson%isopen = false) then
open CursorPerson;
end if;
loop
fetch CursorPerson into entityId;
exit when CursorPerson%notfound;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id ='|| entityId ||' and a.attribute_id = 3';
execute immediate tsql into surname;
if surname is null then
surname:='';
goto end_loop;
end if;
tsql := 'select a.attribute_value from entity_item_value a where a.entity_id = '||entityId||' and a.attribute_id = 4';
execute immediate tsql into firstname;
if firstname is null then
firstname := '';
goto end_loop;
end if;
<<end_loop>> null;
end loop;
close CursorPerson;
end CONVERT_PERSONNAME;

[sparadise1003]

[Quote=引用 7 楼 fosjos 的回复:]
有一招很好用：
'select (select a.oid from entity_item where a.id = '||entityId||') from DUAL'
[/Quote]
学习！