Campo autonumerico en Oracle

Oracle no dispone de un campo autonumérico. Para conseguir emular un campo autonumérico necesitaremos una secuencia y un trigger. A continuación los pasos.

1. Crear la tabla

CREATE TABLE NAMES
(
ID       NUMBER (10) NOT NULL,
NAME     NVARCHAR2 (200) NOT NULL
CONSTRAINT PK_NAMES PRIMARY KEY (ID)
)

CREATE TABLE NAMES

(

ID NUMBER (10) NOT NULL,

NAME NVARCHAR2 (200) NOT NULL

CONSTRAINT PK_NAMES PRIMARY KEY (ID)

)

2. Crear la secuencia

CREATE SEQUENCE NAMES_SEQ
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE SEQUENCE NAMES_SEQ

START WITH 1

INCREMENT BY 1

CACHE 20;

3. Crear el trigger

CREATE OR REPLACE TRIGGER TRG_NAMES_SEQ BEFORE INSERT OR UPDATE ON NAMES
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
BEGIN
  IF INSERTING THEN
    SELECT CAFILELOG_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    :NEW.ID := v_newVal;
  END IF;
END;

CREATE OR REPLACE TRIGGER TRG_NAMES_SEQ BEFORE INSERT OR UPDATE ON NAMES

FOR EACH ROW

DECLARE

v_newVal NUMBER(12) := 0;

BEGIN

IF INSERTING THEN

SELECT CAFILELOG_SEQ.NEXTVAL INTO v_newVal FROM DUAL;

:NEW.ID := v_newVal;

END IF;

END;

Hasta aqui perfecto. Ahora puedes insertar un registro en la tabla NAMES sin preocuparte de asignarle un identificador. Ahora bien, si necesitas averiguar el identificar asignado utiliza NAMES_SEQ.CURRVAL:

INSERT INTO NAMES (NAME) VALUES ("My name");
SELECT NAMES_SEQ.CURRVAL FROM DUAL;

1 2	INSERT INTO NAMES (NAME) VALUES ("My name"); SELECT NAMES_SEQ.CURRVAL FROM DUAL;

Con PL/SQL puedes averiguar el identificador mediante RETURNING INTO. Por ejemplo con .NET queda así:

static decimal InsertName(string name)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
decimal id = 0;
connection.Open();
OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
OracleCommand cmd = new OracleCommand("INSERT INTO NAMES (NAME) VALUES (:name) RETURNING ID INTO :myId", connection);
cmd.Parameters.Add(new OracleParameter("name", name));
cmd.Parameters.Add(new OracleParameter("myId", OracleDbType.Decimal, ParameterDirection.Output));
cmd.ExecuteNonQuery();                            
id = ((OracleDecimal)(cmd.Parameters["myId"].Value)).Value;             
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
}
finally
{
connection.Close();
}
return id;
}
}

static decimal InsertName(string name)

{

using (OracleConnection connection = new OracleConnection(connectionString))

{

decimal id = 0;

connection.Open();

OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

try

{

OracleCommand cmd = new OracleCommand("INSERT INTO NAMES (NAME) VALUES (:name) RETURNING ID INTO :myId", connection);

cmd.Parameters.Add(new OracleParameter("name", name));

cmd.Parameters.Add(new OracleParameter("myId", OracleDbType.Decimal, ParameterDirection.Output));

cmd.ExecuteNonQuery();

id = ((OracleDecimal)(cmd.Parameters["myId"].Value)).Value;

transaction.Commit();

}

catch (Exception e)

{

transaction.Rollback();

}

finally

{

connection.Close();

}

return id;

}

Con Entity Framework con Code First, esto se hace con la opción DatabaseGeneratedOption.Identity de la data annotation DatabaseGenerated:

[Table("FILE", Schema = "MI")]
public class File
{
    [Key]
    [Column("ID")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public decimal Id { get; set; }

    [Column("FILENAME")]
    public string Filename {get; set;}

    public virtual ICollection<Lines> Lines { get; set; }
}

[Table("FILE", Schema = "MI")]

public class File

{

[Key]

[Column("ID")]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public decimal Id { get; set; }

[Column("FILENAME")]

public string Filename {get; set;}

public virtual ICollection<Lines> Lines { get; set; }

}

3ENGINE

Programación y otros cachivaches

Campo autonumerico en Oracle

Enlaces relacionados

David Miró

Acerca 3Engine

Categorías

Etiquetas

Archivos