Procedure Coding question 1

[cranebill]

I have written a procedure and I need a little help or ideas.

I keep coming up with the error "no data found" I know why I am getting this error, however I am a little new to coding so I need a work around.


select longitude, latitude
into bus_long, bus_lat
from zip.zipcodes
where zip_code = c.zip_5;

this is ran on each record in the procedure so when it encounters a zipcode that is not in one or the other tables i get ther error.

I have found that alot of records where zipcode was null therefore i did an if to check for a null value

Ie if c.zip_5 is not null then

select longitude, latitude
into bus_long, bus_lat
from zip.zipcodes
where zip_code = c.zip_5;

end if;

now this works for nulls but there are other zipcodes not located in the zip_codes table.

How can I verify that the zipcode is a match to the table prior to running the select statement.

 

[SimonSellick]

There's no need - you can wrap the section of code in its own block, trap the exception that gets raised when there is no data, and deal with it then:

.
.
  begin
    select longitude, latitude
    into bus_long, bus_lat
    from zip.zipcodes
    where zip_code = c.zip_5;
  exception
    when no_data_found then
    ..do something
  end
.
.

HTH

 

[cranebill]

ok can i do that within a procedure...? Im learning here so bear with me..

it would be like this then?

begin;
Blah
blah
balh
begin
select longitude, latitude
into bus_long, bus_lat
from zip.zipcodes
where zip_code = c.zip_5;
exception
when no_data_found then
..do something
end
continue blah
end;

 

[SimonSellick]

Yes, that's right. PL/SQL is block structured and you can nest blocks to any number of levels. Each block comprises:

[ declare
(declarations) ]
begin
(code)
[ exception
(handlers) ]
end;

Where the sections within [] are optional.

This method of hadling exceptions is very useful but takes a little getting used to if you are used to always testing for errors immediately after the statements that might generate them.

It's usual to indent each block, to make it clear where each starts and ends (just as with IF..END IF etc).

 

[cranebill]

ok cool... got it and I got it working, still needs a little tweaking but I can handle that

stars for you