Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Newbie question regarding PL/SQL
"Lisa Adcock" <ladcock_at_ford.com> wrote in message
news:8musan$fqq3_at_eccws12.dearborn.ford.com...
> I have a cursor and I would like to do some data validation on each row in
> the cursor before the "real" processing begins. I would like to organize
my
> as follows but am having problems finding the right language
> construct/reserved word.
>
> for emp_record in cur_employees loop
>
> if this row has a data problem then
> continue onto next row in cursor
> end if
>
> -- start real processing here
>
> end
>
> I can solve my problem by putting the "real" processing in the elsif part
of
> the if statement, but the processing logic already involves three nested
ifs
> and I don't want to indent them another level by putting them in the
elsif.
>
> Any ideas would be appreciated!
>
>
>
>
If you are not doing anything regarding your 'invalid' rows your select
shouldn't fetch them. IMO, it also better to put complex validation in
triggers and/or functions, that would at least make sure they are in *one*
place. (I'm currently working at a site where they didn't do that resulting
in noone having a complete overview of what is validated where, we also have
front-end and server validation written).
That said, in your current setup the easiest way is to setup this structure
declare
invalid_record exception;
for emp_record in cur_employees loop
begin
if 'invalid record' then
raise invalid_record;
end if;
<continue normal processing>;
exception
when invalid_record then dbms_output.put_line('a suitable message'); -- or instead the line above just null; -- do nothing
Hth,
Sybrand Bakker, Oracle DBA Received on Thu Aug 10 2000 - 00:00:00 CDT