在Oracle中插入后返回标识列的值 [英] Returning the value of identity column after insertion in Oracle

问题描述

插入后如何在Oracle 12c中返回标识列(id)的值?似乎大多数方法都使用序列来获取插入项的ID.

How do I return the value of an identity column (id) in Oracle 12c after insertion? Seems like most of the approaches out there uses sequence to get back the id of the inserted item.

推荐答案

只需使用 RETURNING 子句.

例如-

RETURNING identity_id INTO variable_id;

测试用例-

SQL> set serveroutput on
SQL> CREATE TABLE t
  2    (ID NUMBER GENERATED ALWAYS AS IDENTITY, text VARCHAR2(50)
  3    );

Table created.

SQL>
SQL> DECLARE
  2    var_id NUMBER;
  3  BEGIN
  4    INSERT INTO t
  5      (text
  6      ) VALUES
  7      ('test'
  8      ) RETURNING ID INTO var_id;
  9    DBMS_OUTPUT.PUT_LINE('ID returned is = '||var_id);
 10  END;
 11  /
ID returned is = 1

PL/SQL procedure successfully completed.

SQL>

SQL> select * from t;

        ID TEXT
---------- --------------------------------------------
         1 test

SQL>

这篇关于在Oracle中插入后返回标识列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！