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

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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;

Method 2

I did this

    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;

and it worked

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0