why won't this work in a stored procedure?

[AWI]

CREATE OR REPLACE PROCEDURE RetrieveContractStaff(CemployeeID varchar2) as
BEGIN
Select * from ContractStaff, PartTimeStaff, EMPLOYEE
Where PartTimeStaff.PemployeeID = ContractStaff.CemployeeID
AND PartTimeStaff.PemployeeID = EMPLOYEE.employeeID
and ContractStaff.CemployeeID = CemployeeID;
END;

It works fine when it's not in a procedure... what is going on?
I need to implement this generic method and I can't figure out how
to do it in a stored procedure!!!! Help !!!
thanks heaps,

Antony

[Klaus Zeuch]

Reason 1:
Database columns must be bound to variables of the programming language

Reason 2:
Queries retrieving multiple rows have to be placed inside cursors

Solution in PL/SQL for lazy programmers

create or replace......(v_id varchar2) is
begin
for v_c1 in (select * from....where ContractStaff.CemployeeID = v_id
and.....) loop
-- do processing referencing db-columns as v_c1.cemployeeid and so on)
end loop;
end;

if you are not lazy:

create...
cursor c1 is select.....;
v_tmp c1%rowtype;
begin
open c1;
loop
fetch c1 into v_tmp;
exit when c1%notfound;
-- do processing referencing columns as v_tmp.cemployeeid, ...
end loop;
close c1;
end;

Klaus
<AWI> schrieb in im Newsbeitrag: 39d0...@naylor.cs.rmit.edu.au...

[Andy Hardy]

<AWI> wrote in message news:39d0...@naylor.cs.rmit.edu.au...

>
> CREATE OR REPLACE PROCEDURE RetrieveContractStaff(CemployeeID varchar2) as
> BEGIN
> Select * from ContractStaff, PartTimeStaff, EMPLOYEE
> Where PartTimeStaff.PemployeeID = ContractStaff.CemployeeID
> AND PartTimeStaff.PemployeeID = EMPLOYEE.employeeID
> and ContractStaff.CemployeeID = CemployeeID;
> END;
>
> It works fine when it's not in a procedure... what is going on?

You need to go back to your PL/SQL manuals, I'm afraid.

Basically, your procedure runs a query but doesn't do anything with the
results. PL/SQL does not display to the screen, etc.

You need to answer questions including:
1. How do you think that you are going to use this procedure?
2. Why are you using a procedure and not SQL or a VIEW?
3. What do you mean by generic?

PL/SQL is more than SQL within a procedure.

Andy

--
Opinions are mine and may not reflect those of BG Technology Ltd