Help?? about SQL stored procedure in Oracle?

[youcan1314]

I have a SQL stored procedure :

create procedure test @coun char(10)
as
select * from employees where country=@coun

In Oracle,How should I write??

Thanks!!

 

[JamesGordon]

create procedure test @coun char(10)
as
select * from employees where country=@coun

Create or replace procedure test(coun varchar2) as
begin
select * from employees where country=coun;
end;
/

or

Create or replace procedure test(coun varchar2) as
cursor c1 (p_coun varchar2) is
select *
from employees
where country = p_coun;
begin
for c_row in c1 loop
... Do something with the returned row
end loop;
end;
/
The first doesn't really do anything. Was it mean't to return the rows of data, trying to remember when I did SQL in SQL Server. IF it was then you might have to create a PL/SQL table, populate it and return it to someting that can handle the data.

 

[rtefft]

I'm not sure what info you wanted from EMPLOYEES... as a nexample, if you want it to return the number of employees for the specified country, try it as a function:

create or replace function COUN (
p_coun in varchar2)
return number as
--
v_result number;
begin
select count(*) into v_result
from employees
where country = p_coun;
return v_result;
end;

This will let you use it in a SELECT, such as:

select country, COUN(country) from employees;

To do the same as a procedure, try this:

create or replace function COUN (
p_coun in varchar2,
p_result out number) as
--
begin
select count(*) into p_result
from employees
where country = p_coun;
end;

Hope this helps,
Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[OraCool]

I think JamesGordon's second solution should read as:

begin
for c_row in c1(coun) loop
... Do something with the returned row
end loop;
end;