时间戳

首页 > 解决方案 > 在 Oracle 中，我如何创建存储过程以在 2 个表中插入值

oracle - 在 Oracle 中，我如何创建存储过程以在 2 个表中插入值

问题描述

我正在尝试在两个表中插入以下详细信息。但它显示错误。我哪里错了？

create or replace PROCEDURE ADD_customer_order( 
customer_id in varchar, Shipping_id_arg in number,
order_date_arg in date, Total_price_arg in decimal,
inventory_id_arg in number, order_quantity_arg in number) 
AS
BEGIN
INSERT INTO customer_order (customer_id,Shipping_id,Order_date,total_price) VALUES(customer_id_arg,Shipping_id_arg,order_date_arg, total_price_arg); 
insert into order_details (inventory_id,order_quantity) values(scope_identity(),inventory_id_arg,order_quantity_arg); 
END;

标签： oraclestored-proceduresmultiple-insert

解决方案

如果你很好地格式化它会有所帮助。

create or replace procedure add_customer_order( 
  customer_id      in varchar2, shipping_id_arg in number,
  order_date_arg   in date    , total_price_arg in decimal,
  inventory_id_arg in number  , order_quantity_arg in number) 
as
begin
  insert into customer_order 
  (customer_id    , shipping_id    , order_date    , total_price) 
  values
  (customer_id_arg, shipping_id_arg, order_date_arg, total_price_arg); 

  insert into order_details 
  (                  inventory_id    , order_quantity) 
  values
  (scope_identity(), inventory_id_arg, order_quantity_arg); 
end;

这样做，您很容易注意到第二个INSERT是无效的，因为您将 3 个值插入到 2 列中：

  insert into order_details 
  (                  inventory_id    , order_quantity) 
  values
  (scope_identity(), inventory_id_arg, order_quantity_arg);

删除scope_identity()（它是什么？），或在要插入的列列表中包含其他列。

阅读您的评论后，似乎该returning条款可能会有所帮助。请参阅以下示例（比您的示例简单一些；不想输入那么多）。触发器用于自动增加 ORDER_ID列。在CUSTOMER_ORDER表中（我在 11g XE 上；这里没有标识列）。

SQL> create table customer_order (order_id number, customer_id number);

Table created.

SQL> create table order_details (order_id number, inventory_id number);

Table created.

SQL> create sequence seqo;

Sequence created.

SQL> create or replace trigger trg_co
  2    before insert on customer_order
  3    for each row
  4  begin
  5    :new.order_id := seqo.nextval;
  6  end;
  7  /

Trigger created.

程序：注意第 4 行中声明的局部变量和returning第 7 行中的子句：

SQL> create or replace procedure p_test
  2    (par_customer_id in number, par_inventory_id in number)
  3  is
  4    l_order_id   customer_order.order_id%type;
  5  begin
  6    insert into customer_order (customer_id) values (par_customer_id)
  7    returning order_id into l_order_id;
  8
  9    insert into order_details (order_id, inventory_id)
 10    values (l_order_id, par_inventory_id);
 11  end;
 12  /

Procedure created.

测试：

SQL> exec p_test(100, 200);

PL/SQL procedure successfully completed.

SQL> exec p_test (235, 2230);

PL/SQL procedure successfully completed.

SQL> select * From customer_order;

  ORDER_ID CUSTOMER_ID
---------- -----------
         1         100
         2         235

SQL> select * From order_details;

  ORDER_ID INVENTORY_ID
---------- ------------
         1          200
         2         2230

SQL>

两个表中使用相同的ORDER_ID值。

推荐阅读