Get primary key value of record inserted in the table inside the stored procedure in Oracle

Question

I have a table with the name users where my userId is primary key and incremental what I am trying to do is write a stored procedure that would accept user data and since userId is incremental it would return the userId created after insertion of that record.

Here is my stored procedure code:

CREATE OR REPLACE PROCEDURE sp_register_new_user 
    (firstname     IN VARCHAR2,
     lastname      IN VARCHAR2,
     phone_name    IN VARCHAR2,
     user_password IN VARCHAR2,
     email_id      IN VARCHAR2,
     userrole      IN NUMBER,
     udid          IN VARCHAR2,
     res           OUT NUMBER) 
AS
BEGIN
    INSERT INTO users (
        first_name,
        last_name,
        login,
        isactive,
        role_id,
        password,
        uuid,
        phonenumber
    ) VALUES (
        firstname,
        lastname,
        email_id,
        1,
        userrole,
        user_password,
        udid,
        phone_name
    );

  SELECT LAST_INSERT_ID();
END sp_register_new_user;

I took some references where they say I need to write this line to get the value of the last record

SELECT LAST_INSERT_ID();

but I get an error.

Also, I wanted to do this with the help of transactions but am not sure how to achieve it since new to MySQL, can somebody help me solve this problem

Answer 1

If you want the solution in Oracle, You can use RETURNING keyword there to return last inserted ID -

 CREATE OR REPLACE PROCEDURE sp_register_new_user (firstname     IN VARCHAR,
                                                   lastname      IN VARCHAR,
                                                   phone_name    IN VARCHAR,
                                                   user_password IN VARCHAR,
                                                   email_id      IN VARCHAR,
                                                   userrole      IN NUMBER,
                                                   udid          IN VARCHAR,
                                                   res           IN NUMBER
                                       ) AS

BEGIN
    INSERT INTO users (
        first_name,
        last_name,
        login,
        isactive,
        role_id,
        password,
        uuid,
        phonenumber
    ) VALUES (
        firstname,
        lastname,
        email_id,
        1,
        userrole,
        user_password,
        udid,
        phone_name
    ) RETURNING id INTO res;

END sp_register_new_user;