Creating stored procedure with select statements

[dpman39]

Gurus,

I'm new to oracle and I'm trying to right an SP that returns certain values.

Please let me know how this could be written better

CREATE OR REPLACE
procedure CATTRAC_PROC
as
begin
select stbkeyi from stb where
STBBEZC = ' ';

select a.STBBEZC from stb a,
bld b
where
b.bldfnmc = '12345' and b.bldfnmc = '32'
and b.BLDSTBKEYI = a.STBKEYI;
commit;
END;

[PAVB]

1 -- You do not have to commit a select statement

2 -- Where are you supposed to store the results of your select statements?
For single result you can select into a variable
For multiple results you can load a cursor
After you have the data there... what do you want to do with it?

3 -- Do you expect column STBBEZC to be populated with a zero-lenght string or with a NULL value?
In the second case... "stbbezc is Null" would do the trick.

Hope this helps to get you jump started

[dpman39]

1. I will need to return mutiple values, can you give me an example of how to use a cursor in this situation?

[PAVB]

Follows example including just first query... you might have to load a second cursor for your second query.

CREATE OR REPLACE procedure CATTRAC_PROC
as
CURSOR MyCursor(w_stbkeyi put_here_stbkeyi_datatype) IS
select stbkeyi
from stb
where STBBEZC = ' ';
BEGIN

Here you can loop into MyCursor to retrieve returned values...

END;