when no_data_found [message #405713] |
Fri, 29 May 2009 03:48  |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
declare
v_empid number;
v_dept_id number;
Begin
Select empid into v_empid from emp where dept_id = v_dept_id;
exception
when NO_DATA_FOUND then
Flag :=0
end;
if flag <> 0 then
< proceed ...do some action >
else
null;
end if;
end;
I want to know if this is the correct way of coding. Thanks
|
|
|
|
|
Re: when no_data_found [message #405718 is a reply to message #405713] |
Fri, 29 May 2009 04:04   |
|
Did u mean like below by any chance, because the design that posted would result errors
DECLARE
v_empid NUMBER;
v_dept_id NUMBER;
BEGIN
SELECT empno
INTO v_empid
FROM emp
WHERE dept_id = v_dept_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
flag := 0;
IF flag <> 0
THEN
< proceed ...do some action >
ELSE
< proceed ...do some action >
END IF;
END;
|
|
|
Re: when no_data_found [message #405721 is a reply to message #405713] |
Fri, 29 May 2009 04:09   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It's a way of coding it.
You are making the assumption that you will never get two rows from EMP for the same Dept_id - you might want to consider trapping the Too_Many_Rows exception as well.
I would be more likely to code it like this, assuming that no rows or too many rows for the specified Id wasn't a condition that needed handling or reporting on:DECLARE
v_empid NUMBER;
v_dept_id NUMBER;
BEGIN
SELECT empno
INTO v_empid
FROM emp
WHERE dept_id = v_dept_id;
<do some action>
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN TOO_MANY_ROWS THEN
null;
END;
|
|
|
Re: when no_data_found [message #405722 is a reply to message #405717] |
Fri, 29 May 2009 04:10   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
When no data found I want to set flag as 0 and pass it main block and based on the condition whther to process or not to process.Would it be wrong if I write the code this way?
DECLARE
v_empid number;
v_dept_id number;
flag number;
BEGIN
<......... >
BEGIN
SELECT empid INTO v_empid FROM emp
WHERE dept_id = v_dept_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
flag:=0;
END;
IF flag <> 0 THEN
< proceed ...do some action >
ELSE
NULL;
END IF;
END;
[Updated on: Fri, 29 May 2009 04:19] Report message to a moderator
|
|
|
|
Re: when no_data_found [message #405739 is a reply to message #405722] |
Fri, 29 May 2009 04:46   |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
DECLARE
v_empid number;
v_dept_id number;
flag number;
BEGIN
<......... >
BEGIN
SELECT empid INTO v_empid FROM emp
WHERE dept_id = v_dept_id;
< proceed ...do some action >
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
END;
END;
can this be alternative to
DECLARE
v_empid number;
v_dept_id number;
flag number;
BEGIN
<......... >
BEGIN
SELECT empid INTO v_empid FROM emp
WHERE dept_id = v_dept_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
flag:=0;
END;
IF flag <> 0 THEN
< proceed ...do some action >
ELSE
NULL;
END IF;
END;
or is it a bad coding approach?
|
|
|
Re: when no_data_found [message #405749 is a reply to message #405739] |
Fri, 29 May 2009 05:42   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
prachij593 wrote on Fri, 29 May 2009 11:46 | or is it a bad coding approach?
|
It is your choice to create longer and less transparent code.
Just one observation: as FLAG is not assigned anything in case of success, it will hold NULL value. So the condition (FLAG <> 0) will have NULL value and <some action> will not be performed. Seems like bug to me.
|
|
|
Re: when no_data_found [message #405764 is a reply to message #405739] |
Fri, 29 May 2009 06:35  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | can this be alternative to
|
If you don't understand the first one do it as you want but abvoce all do it that is
1/ compile
2/ fit the requirements.
Regards
Michel
|
|
|