我的意思是希望,先找到surname的值,判断是否为空,如果不为空则继续查找firstname,为空则进入下次循环,同时firstname的处理也差不多,判断得到的值是否为空,如果为空则进入下次循环,如果不为空则将结果保留下来,各位大哥,帮帮忙把这段代码修改下啊,我实在不知道这存储过程该如何判断为空的情况。用异常处理的话,我第一次查询没查找到数据,循环就终止了,我希望的是代码能不管中间的空值,继续运行下面的循环,请问这该怎么做。 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 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;
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;
可以直接用nvl或decode函数,作为参数判断处理
也许你的要求可以用貌似以下语句来完成:一句。 select c.firstname,b.surname from a
join (select firstname,entry_id from b where b.attribute_id=4 and b.firstname is not null) b on b.entry_id=a.entry_id
join (select surname,entry_id from c where c.attribute_id=3 and c.firstname is not null) c on c.entry_id=a.entry_id
where a.entity_type=10 如果是随意一条,取rownum=1即可. 问题不是很明确,大概想法如此,也许一定要用procedure处理。
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
<<here>>
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
GOTO here; ---数据插入失败,中止这次循环
begin
dbms_output.put_line('111111');
end;
end CONVERT_PERSONNAME;
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
<<here>> --循环失败,转到这个位置
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
GOTO here; ---数据插入失败,中止这次循环
begin
dbms_output.put_line('111111');
end;
end CONVERT_PERSONNAME;