Скачать или смотреть How to Insert and Return an Auto-Incrementing Primary Key in an Oracle Database

Discover how to efficiently insert a record and retrieve its auto-incremented primary key value in Oracle Database using the RETURNING clause in PL/SQL.
---
This video is based on the question https://stackoverflow.com/q/63667930/ asked by the user 'user2848242' ( https://stackoverflow.com/u/2848242/ ) and on the answer https://stackoverflow.com/a/63668004/ provided by the user 'Koen Lostrie' ( https://stackoverflow.com/u/4189814/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Insert and return value in oracle

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Inserting and Returning an Auto-Incrementing Primary Key in Oracle Database

When working with databases, a common requirement is to insert data into a table and retrieve the auto-incremented primary key generated by that operation. If you're using Oracle Database and you've encountered the challenge of how to do this efficiently in a single query, you're not alone. This guide will guide you through the process of inserting a record and returning its primary key using PL/SQL.

The Problem

Let's say you have a table named Person where the PersonId is an auto-incrementing primary key. When you insert a new record for a person (for example, someone named 'Radouane'), you want to not only insert that record but also capture the value of PersonId generated as a result of the insertion. This can be especially important for situations where you need to use the newly created ID for further processing or linking with other data.

The Solution: Using the RETURNING INTO Clause

In Oracle, you can accomplish this task using the RETURNING INTO clause. This allows you to insert a record and simultaneously retrieve the value of the generated primary key. Below, we will break down the process step-by-step.

Step-by-Step Implementation

Setting Up Your Environment: Before running the code, ensure that your SQL*Plus or PL/SQL environment is configured to display output. You can do this by setting server output on:

[[See Video to Reveal this Text or Code Snippet]]

Creating a PL/SQL Block: Next, you need to create a PL/SQL block that will handle the insertion and retrieval of the primary key. Here’s how you can structure it:

[[See Video to Reveal this Text or Code Snippet]]

Customization: In the code above, make sure you replace:

PersonId with the actual name of your primary key column if it's different.

l_id with any variable name that you wish to use for storing the ID.

Key Considerations

PL/SQL vs. SQL: The RETURNING INTO clause can only be used in PL/SQL, meaning it isn’t applicable in pure SQL statements. This distinction is important when deciding how to manipulate your database.

Output Management: Always enable server output to see the results of your DBMS_OUTPUT.PUT_LINE statements. This will help you confirm whether the ID was returned successfully.

Conclusion

In summary, inserting a record and returning its auto-incremented primary key in Oracle can be efficiently achieved using the RETURNING INTO clause in a PL/SQL block. This method not only simplifies the process but also ensures you have immediate access to the primary key, avoiding the need for a follow-up query. Whether you are handling user data, logging activities, or linking records, this technique is invaluable for efficient database management.

Now that you're equipped with this knowledge, go ahead and implement it in your own projects! Feel free to explore further examples online to enhance your understanding.