lunes, 18 de agosto de 2014

Oracle Database 12c valores predeterminados para una o varias columnas en una tabla.


Valores de Facto utilizando secuencias

En la nueva versión del motor de base de datos Oracle 12c, es posible ahora especificar las pseudocolumnas CURRVAL y NEXTVAL de una secuencia, como valores de facto para una o varias columnas en una tabla.

A continuación podrás ver el efecto que causa el definir estas pseudocolumnas a nivel de la tabla. Recuerda que en el "INSERT" no es necesario hacer la referencia a la columna que hemos definido con un valor de facto.

El efecto también puede modificarse utilizando la cláusula ON NULL en el ejemplo al final del artículo.

login as: root
root@10.0.0.1's password:
Last login: Wed Aug 20 04:08:19 2014
[root@dbvisit01 ~]# su - oracle
[oracle@dbvisit01 ~]$ sqlplus hr/hr

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 20 04:11:31 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 28 2014 05:06:19 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> /

TABLE_NAME           TABLE_TYPE
-------------------- -----------
REGIONS              TABLE
LOCATIONS            TABLE
JOB_HISTORY          TABLE
JOBS                 TABLE
EMPLOYEES            TABLE
EMPLEADOS2           TABLE
EMPLEADOS            TABLE
DEPARTMENTS          TABLE
COUNTRIES            TABLE
DEPARTMENTS_SEQ      SEQUENCE
EMPLOYEES_SEQ        SEQUENCE
EMP_DETAILS_VIEW     VIEW
LOCATIONS_SEQ        SEQUENCE

13 rows selected.

Creación de la secuencia.

SQL> create sequence t1_secuencia;
Sequence created.

SQL>  create table t1(consecutivo number, nombre varchar2(15), fecha date);
Table created.

SQL> insert into t1(nombre, fecha) values('JUAN',sysdate-200);
1 row created.

SQL> insert into t1(nombre, fecha) values ('GREIVIN', sysdate -100);
1 row created.

SQL> insert into t1(nombre,fecha) values ('INDRA', sysdate-50);
1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

CONSECUTIVO NOMBRE          FECHA
----------- --------------- ---------
            JUAN            01-FEB-14
            GREIVIN         12-MAY-14
            INDRA           01-JUL-14

Ahora modifiquemos la columna consecutivo, para que haga referencia a la secuencia.

SQL> alter table t1 modify (consecutivo number default t1_secuencia.nextval);
Table altered.

SQL> insert into t1(nombre, fecha) values ('JUAN', sysdate-200);
1 row created.

SQL> insert into t1(nombre, fecha) values ('GREIVIN', sysdate-100);
1 row created.

SQL> insert into t1(nombre, fecha) values('INDRA', sysdate-50);
1 row created.

SQL> select * from t1;

CONSECUTIVO NOMBRE          FECHA
----------- --------------- ---------
            JUAN            01-FEB-14
            GREIVIN         12-MAY-14
            INDRA           01-JUL-14
          1 JUAN            01-FEB-14
          2 GREIVIN         12-MAY-14
          3 INDRA           01-JUL-14

6 rows selected.

SQL>

Utilizando el valor CURRVAL de una secuencia como valor de facto en una columna de la tabla.

SQL> create sequence secuencia_maestro;

Sequence created.

SQL> create table ejemplo(id_valor_secuencia_next number default secuencia_maestro.nextval, descripcion varchar2(10),
  2  id_valor_secuencia_actual number default secuencia_maestro.currval);

Table created.

SQL> insert into ejemplo(descripcion) values ('&descripcion');
Enter value for descripcion: JUAN
old   1: insert into ejemplo(descripcion) values ('&descripcion')
new   1: insert into ejemplo(descripcion) values ('JUAN')
1 row created.

SQL> /
Enter value for descripcion: GREIVIN
old   1: insert into ejemplo(descripcion) values ('&descripcion')
new   1: insert into ejemplo(descripcion) values ('GREIVIN')
1 row created.

SQL> /
Enter value for descripcion: INDRA
old   1: insert into ejemplo(descripcion) values ('&descripcion')
new   1: insert into ejemplo(descripcion) values ('INDRA')
1 row created.

SQL> commit;
Commit complete.

SQL> select * from ejemplo;

ID_VALOR_SECUENCIA_NEXT DESCRIPCIO ID_VALOR_SECUENCIA_ACTUAL
----------------------- ---------- -------------------------
                      1 JUAN                               1
                      2 GREIVIN                            2
                      3 INDRA                              3

Uso de la clásula ON NULL con una secuencia en valor de facto de una columna.

SQL> create sequence defacto_en_nulo_secuencia;
Sequence created.

SQL> create table t2 ( id_valor number default on null defacto_en_nulo_secuencia.nextval, descripcion varchar2(10));
Table created.

SQL> insert into t2(descripcion) values( 'JUAN');
1 row created.

SQL> insert into t2 values ( 100, 'INDRA');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t2;

  ID_VALOR DESCRIPCIO
---------- ----------
         1 JUAN
       100 INDRA