SELECT SCOPE_IDENTITY () in Oracle

Question

SELECT SCOPE_IDENTITY () in Oracle

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

This PROCEDURE is done in the MSSQL database engine and I need to pass it to ORACLE 11g:

CREATE PROCEDURE [dbo].[Insert_User]
   @Username NVARCHAR(20),
   @Password NVARCHAR(20),
   @Email NVARCHAR(30)
AS
BEGIN
   SET NOCOUNT ON;
   IF EXISTS(SELECT UserId FROM Users WHERE Username = @Username)
   BEGIN
          SELECT -1 AS UserId -- Username exists.
   END
   ELSE IF EXISTS(SELECT UserId FROM Users WHERE Email = @Email)
   BEGIN
          SELECT -2 AS UserId -- Email exists.
   END
   ELSE
   BEGIN
          INSERT INTO [Users]
                    ([Username]
                    ,[Password]
                   ,[Email]
                    ,[CreatedDate])
          VALUES
                    (@Username
                    ,@Password
                    ,@Email
                    ,GETDATE())

          SELECT SCOPE_IDENTITY() AS UserId -- UserId
          --No he encontrado un sucedáneo para esta línea...       
 END
END

I have already transformed the majority of the body of the Procedure, I only need the indicated part of SELECT SCOPE_IDENTITY ()

sql sql-server oracle

asked by Luis Nox 14.11.2018 в 16:14

source

2 answers

Handling lists in python AmCharts - Decimal Rounding of the Mean

score 2 · Answer 1

First of all, the 11g version does not have columns of type IDENTITY , so I imagine that you will have solved this in particular with a SEQUENCE and a TRIGGER . Something like this:

CREATE TABLE TABLA (
  ID           NUMBER(10)    NOT NULL
);

ALTER TABLE TABLA ADD (
  CONSTRAINT TABLA_pk PRIMARY KEY (ID));

CREATE SEQUENCE TABLA_seq START WITH 1;

CREATE OR REPLACE TRIGGER TABLA_bir 
BEFORE INSERT ON TABLA 
FOR EACH ROW

BEGIN
  SELECT TABLA_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

And then: How to get the last ID inserted in the table, which is definitely what a scope_identity() does? What you can do is get the last id inserted as follows:

insert into TABLA (...) values (...)
returning id into v_id;

score 0 · Answer 2

It remains until now like this:

CREATE PROCEDURE Insert_User
   @Username VARCHAR2(20),
   @Password VARCHAR2(20),
   @Email VARCHAR2(30)
AS
BEGIN
    IF EXISTS(SELECT UserId FROM Users WHERE Username = @Username)
     BEGIN
      SELECT -1 AS UserId -- Username exists.
     END
    ELSE IF EXISTS(SELECT UserId FROM Users WHERE Email = @Email)
     BEGIN
      SELECT -2 AS UserId -- Email exists.
     END
    ELSE
     BEGIN
      INSERT INTO Users
                (UserId
                ,Username
                ,Password
                ,Email
                ,CreatedDate)
      VALUES
                (SEQ_USERS.NEXTVAL
                ,@Username
                ,@Password
                ,@Email
                ,SYSDATE)

      --SELECT SCOPE_IDENTITY() AS UserId -- UserId
      --No he encontrado un sucedáneo para esta línea...
      RETURNING ID INTO v_id;
      SELECT v_id AS UserId; --Algo así?
  END
END