在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:

我正在创建一个存储过程,它接受id和开始和结束日期的输入,然后返回落在该范围内的行程。我一直在查看oracle文档,我认为我很接近,但是还有一些错误:

 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;

1 个解决方案

#1

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.

但是,您选择INTO p_aircraft_id,它被声明为IN参数,因此您无法将其设置为新值。这是您要传入的变量,还是您想要获取的值?它更像是调用者提供的日期和日期,但是你需要在select语句中将它用作过滤器。如果有多个飞机ID - 可能只是受到日期的限制 - 那么你会得到多个结果,无论如何都会出现太多的错误。

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.

...因为ttl_chg仅作为列别名存在,而不是PL / SQL变量。

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

如果你传递飞机ID,你可能想要这样的东西:

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.

我也改为内连接,因为它们使外连接似乎没有用。这也比程序更有意义;虽然在存储的程序中包装单个查询可能是不必要的 - 尽管这看起来像一个赋值。

#1