获取最后插入的行的ID，并将其用于插入存储过程中的另一个表中 [英] Get ID of last inserted row and use it to insert into another table in a stored procedure

问题描述

我有以下存储过程，可用于将数据插入表中

I have the following stored procedure which we use to insert data into a table:

CREATE OR REPLACE PROCEDURE mySproc
(
 invoiceId IN NUMBER
 customerId IN NUMBER
)
IS
BEGIN 
    INSERT INTO myTable (INVOICE_ID) 
    VALUES (invoiceId);
END mySproc;
/

我想做的是获取最后插入的ID(这是myTable上的主键字段，并使用序列自动递增)并将其插入到另一个表中，我尝试了以下操作，但无法使其正常工作:

What I am trying to do is to get the last inserted ID (this is the primary key field on myTable and auto incremented using a sequence) and insert it into another table, I have tried the following but could not get it working:

CREATE OR REPLACE PROCEDURE mySproc
(
 invoiceId IN NUMBER
 customerId IN NUMBER
)
IS
BEGIN 
    INSERT INTO myTable (INVOICE_ID) 
    VALUES (invoiceId)

    returning id into v_id;

    INSERT INTO anotherTable (ID, customerID) 
    VALUES (v_id, customerId);  
END mySproc;
/

我遇到此错误: [错误] PLS-00049(59:26):PLS-00049:错误的绑定变量'V_ID'在BEGIN语句之后，但是又给出了另一个错误.

I am getting this error: [Error] PLS-00049 (59: 26): PLS-00049: bad bind variable 'V_ID' I think I need to declare v_id somewhere but I tried before and after the BEGIN statement but that gave another error.

关于如何执行此操作的任何想法?

Any ideas as to how to do this?

谢谢

推荐答案

将过程更改为

CREATE OR REPLACE PROCEDURE mySproc
(
 invoiceId IN NUMBER,  -- Added comma
 customerId IN NUMBER
)
IS
    v_id  NUMBER;  -- ADDED
BEGIN 
    INSERT INTO myTable (INVOICE_ID) 
    VALUES (invoiceId)
    returning id into v_id;

    INSERT INTO anotherTable (ID, customerID) 
    VALUES (v_id, customerId);  
END mySproc;

分享并享受.

这篇关于获取最后插入的行的ID，并将其用于插入存储过程中的另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！