CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
dreamerdiver
dreamerdiver

Reputation: 69

Setting up a stored procedure in Oracle

I'm working to create a stored procedure that takes input of an id and a start and end date, then returns trips that fall within that range. I've been looking over the oracle documentation and I think I'm close, but getting a few errors yet: 

 CREATE or replace PROCEDURE chg_per_aircraft 
  (p_aircraft_id IN RCC_AIRCRAFT.aircraft_id,
   p_start_date IN date,
   p_end_date IN date, 
   p_ttl_chg_per_acft OUT INTEGER)

AS                                             
BEGIN
  SELECT RCC_AIRCRAFT.aircraft_id, 
         SUM(RCC_CHARTER.distance * RCC_MODEL.charge_per_mile) ttl_chg
  INTO
         p_aircraft_id,
         p_ttl_chg_per_acft
FROM RCC_AIRCRAFT
full join RCC_CHARTER
on RCC_CHARTER.aircraft_id = RCC_AIRCRAFT.aircraft_id
left join RCC_MODEL
on RCC_MODEL.model_code = RCC_AIRCRAFT.model_code
Where RCC_CHARTER.trip_date > p_start_date and RCC_CHARTER.trip_date < p_end_date
group by RCC_AIRCRAFT.aircraft_id;


SYS.DBMS_OUTPUT.PUT_LINE(ttl_chg);
 end;

Upvotes: 0

Views: 578

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Your first error is the parameter definition:

p_aircraft_id IN RCC_AIRCRAFT.aircraft_id

should be 

p_aircraft_id IN RCC_AIRCRAFT.aircraft_id%TYPE

But then you're selecting INTO p_aircraft_id, which is declared as an IN parameter, so you can't set it to a new value. Is that a variable you want to pass in, or a value you want to get out? It makes more sense as something the caller supplies along with the dates, but then you'd need to use it as a filter in the select statement. If there was more than one aircraft ID - likely if it's only restricted by date - then you'd get multiple results back, which would be a too_many_rows error anyway.

Your output will only be visible to a session that is set up to handle it, so that would perhaps make more sense for the caller to do; but in any case should be:

DBMS_OUTPUT.PUT_LINE(p_ttl_chg_per_acft);

... as ttl_chg only exists as a column alias, not a PL/SQL variable.

If you are passing in the aircraft ID, you might want something like this:

CREATE or replace PROCEDURE chg_per_aircraft 
  (p_aircraft_id IN RCC_AIRCRAFT.aircraft_id%TYPE,
   p_start_date IN date,
   p_end_date IN date, 
   p_ttl_chg_per_acft OUT INTEGER)
AS                                             
BEGIN
  SELECT SUM(RCC_CHARTER.distance * RCC_MODEL.charge_per_mile) ttl_chg
  INTO p_ttl_chg_per_acft
  FROM RCC_AIRCRAFT
  JOIN RCC_CHARTER
  ON RCC_CHARTER.aircraft_id = RCC_AIRCRAFT.aircraft_id
  JOIN RCC_MODEL
  ON RCC_MODEL.model_code = RCC_AIRCRAFT.model_code
  WHERE RCC_CHARTER.trip_date > p_start_date
  AND RCC_CHARTER.trip_date < p_end_date
  AND RCC_AIRCRAFT.aircraft_id = p_aircraft_id
  GROUP BY RCC_AIRCRAFT.aircraft_id;

  -- just to debug!
  DBMS_OUTPUT.PUT_LINE(p_ttl_chg_per_acft);
END;
/

I've also changed to inner joins as it doesn't seem useful to make them outer joins. This would also make more sense as a function than a procedure; though wrapping a single query in a stored program may be unnecessary anyway - though this looks like an assignment.

Upvotes: 2

Related Questions