SQL: Display results from table in a stored procedure

Question

I have to implement a packaging system. I have the following tables and insertions in the table :

--a
create table client(
IdClient number(5),
NameClient varchar2(20),
Address varchar2(20),
primary key(IdClient)
);

create table tpackage(
IdPackage number(5),
IdClient number(5),
WLocation varchar2(20),
Courier varchar2(20),
DeliveryTime date,
Status varchar2(20),
primary key(IdPackage),
constraint fk_package foreign key(IdClient) references client(IdClient)
);

insert into client values(1,'Robb','Dimitrie Cantemir 1');
insert into client values(2,'Ion','Aleea Cascadei 15');
insert into client values(3,'Popan','Progresului 1');
insert into client values(4,'Pop','Carei 15');
insert into client values(5,'Grozavescu','Vasile Pirvan 1');
insert into client values(6,'Popa','Orsova 11');

insert into tpackage values(01,1,'Diminetii 2','Ilie','22-Jan-2017','in-transit');
insert into tpackage values(02,6,'Liege 1','Popescu','23-NOV-2016','delivered');
insert into tpackage values(03,4,'Crisana 3','Alexandru','20-Jan-2017','in-transit');
insert into tpackage values(04,3,'Torontal 14','Stefan','18-Dec-2016','delivered');
insert into tpackage values(05,2,'Carei 15','Ionescu','23-Feb-2017','in-transit');

I have to implement a stored procedure that displays all package info for the in transit packages for clients given as parameters. This is what I have done so far:

create or replace procedure displayPackages(i_IdClient number) as 
begin
--SELECT t.Status from tpackage as t inner join client as c on t.IdClient = c.IdClient where t.Status='in-transit' and c.IdClient = IdClient and t.IdClient = IdClient and c.IdClient = t.IdClient;
select t.status,c.NameClient from tpackage t,client c where t.Status='in-transit' and c.IdClient = i_IdClient and c.IdClient = t.IdClient;
end displayPackages;

But this is not working. Thanks in advance!

It would be best to change those joins to proper ANSI style joins, you're using the outdated format. — Rich Benner, Dec 19 '16 at 09:43
Define "not working". I'm guessing you get a PLS-00428 compilation error telling you an `into` clause was expected, but we should not have to guess. If you are on 12c then look at implicit result sets, as in my reply here: http://stackoverflow.com/questions/351489/is-it-possible-to-output-a-select-state-from-a-pl-sql-block/40360471#40360471 — William Robertson, Dec 19 '16 at 10:23

score 0 · Accepted Answer · answered Dec 19 '16 at 09:43

In PL/SQL you can't just select. You need to specify variables that will store result of your query.

create or replace procedure displayPackages(i_IdClient number) as 
tstatus tpackage.status%type;
cname client.NameClient%type;
begin
select t.status,c.NameClient into tstatus, cname 
  from tpackage t inner join client c on (c.IdClient = t.IdClient)
  where t.Status='in-transit' and c.IdClient = i_IdClient;
--after selecting data into variables you can do your calculations or other operations on variables
end displayPackages;

SQL: Display results from table in a stored procedure

1 Answers1