java - 如何在不使用 seq.currval 的情况下检索序列的当前 ID

Question

我需要插入到persons中的最后一个id，以便在java中进行一些操作。我有两个插入，然后如果我使用 getGenerateKeys 我会收到“不允许操作”错误。我使用选择查询来检索序列的当前值，但它会导致“需要一个 into 子句”错误。我怎样才能设法访问最后一个人的ID。查询:

    begin
        insert into Persons  
           (Id,First_Name,Last_Name)  
           values (person_seq.nextval  ,?  ,?  );
        insert into Relations (relation_id, person_id) 
            values (relation_seq.nextval,person_seq.currval); 
        SELECT person_seq.currval FROM DUAL;
    end;

java代码:

    stmt = connectionManager.getConnection().prepareStatement(query);//,new String[] {"ID"});
    stmt.setString(1, family.getFName());
    stmt.setString(2, family.getLName());
    ResultSet resultSet = stmt.executeQuery();
    ret = resultSet.getLong(1);

Answer 1

您可以使用 RETURNING INTO 子句声明一个变量来存储新的 id:

declare
   new_id number; --or your id row type
begin
        insert into Persons  
           (Id,First_Name,Last_Name)  
           values (person_seq.nextval  ,?  ,?  )
           returning id into new_id;
        insert into Relations (relation_id, person_id) 
            values (relation_seq.nextval,person_seq.currval); 
end;