oracle - 调用带参数的plsql过程

Question

我试图调用这个程序，但有一些问题

CREATE OR REPLACE Procedure InsertNewReservation
   ( flight_number IN varchar2,passenger_id IN number,flight_price in number )

   IS
   s2 number;

BEGIN
   insert into Reservations (flightNumber,passengerId) values (flight_number,passenger_id) 
                                                       returning ID into s2;

   insert into Payments (value,reservationId) values (flight_price,s2);
END;
/

我如何调用程序？我试过

BEGIN
  InsertNewReservation('FL100',1,120);
END;
/

但它没有用：/

谢谢！

标签： oraclestored-proceduresplsql

Answer 1

因为没有ID列，但passengerId在 Reservations 表中：

CREATE OR REPLACE Procedure InsertNewReservation
                           ( 
                            flight_number Reservations.flightNumber%type,
                            passenger_id  Reservations.passengerId%type,
                            flight_price  Payments.value%type 
                           )
IS
   s2 Payments.reservationId%type;
BEGIN
   insert into Reservations (flightNumber,passengerId) 
   values(flight_number,passenger_id) returning passengerId into s2;

   insert into Payments (value,reservationId) Values (flight_price,s2);
END;
/

顺便说一句，替换变量以使其在表中符合它们各自的类型，并且使用IN运算符对于过程的参数类型是多余的，因为它是默认值。

Demo