Algunas veces sucede que uno da por hecho que todo mundo sabe de lo que estamos hablando, refiriéndome a “todo mundo” por todo el equipo de trabajo de Oracle en mi actual proyecto, y mas hablando de cosas sencillas como un trigger a nivel de tabla, pero a veces resulta que no, hay cosas que uno sabe que otros no y viceversa.

Discutiendo la solución a un problema, algunos llegábamos a la conclusión de utilizar un trigger a nivel de tabla, unos estaban en contra y resulta que otros no sabían que existían, no hablare de eso, mas bien para los que no sepan, les hablare de cómo crearlos y para que sirven.

Un trigger a nivel de tabla sirve para que Oracle ejecute x o y cosa que deseemos, claro utilizando PL/SQL, al realizar algún DDL sobre una tabla (insert, delete, update) puede ser antes o después del DDL y se puede especificar una, dos o las tres acciones, incluso en la versión 11g ya se puede especificar el orden en que se desee se ejecuten los triggers, en caso de haber mas de uno sobre la misma tabla, se puede especificar si se desea la ejecución sobre cada registro procesado o que se realice por cada transacción y varias cosas mas.

La sintaxis es la siguiente:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE OR REPLACE TRIGGER <trigger_name>
[<ENABLE | DISABLE>]
<BEFORE | AFTER> <ACTION> [OR <ACTION> OR <ACTION>]
ON <table_name>
 
DECLARE
 <variable definitions>
BEGIN
  <trigger_code>
EXCEPTION
  <exception clauses>
END <trigger_name>;
/

Un ejemplito sencillo que ya había utilizado en un post anterior:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TRIGGER tr_pk_employees 
  before INSERT ON employees
  FOR each ROW
DECLARE
 
BEGIN
 
  SELECT NVL(MAX(employee_id),0) + 1
    INTO :NEW.employee_id
    FROM employees;
 
END;

También podemos diferenciar la acción realizada dentro del trigger:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE TRIGGER tr_pk_employees 
  before INSERT OR UPDATE OR DELETE
  ON employees
  FOR each ROW
DECLARE
 
BEGIN
 
  IF INSERTING THEN
    DBMS_OUTPUT.put_line('Inserting');
  ELSIF UPDATING THEN
    DBMS_OUTPUT.put_line('Updating');
  ELSIF DELETING THEN
    DBMS_OUTPUT.put_line('Deleting');
  END IF;
 
END;

El único detalle a destacar es que para referenciar las columnas de la tabla se utiliza el “:OLD” y el “:NEW”, por ejemplo en un “update”, “:old” tendrá el valor antes de la actualización y “:new” el valor después, en un “insert” no existe el valor “:old” y en un delete no existe el valor “:new”.

Hace algunos días, en mi actual proyecto, me pidieron que revisara una consulta, el problema era que sus tiempos se estaban elevando mucho, y considerando que con el tiempo la tabla iría creciendo aun mas, estamos hablando de millones de registros, era necesario aplicarle un poco de tunning al SQL.

No hablare de cómo optimizar una consulta en este momento, de planes de ejecución, uso de índices o de hints, etc. de lo que quería hablar era de algo mas censillo, del uso del “case” en una consulta.

En versiones anteriores a la 9i (creo que es esa versión la que incluye por primera vez el uso del “case”) no había otra forma de realizar una consulta condicionando el resultado de una columna mas que usando un “decode”, aunque funcionaba, no era una buena solución, no se podían condicionar rangos de valores, para cada valor posible se tenia que definir en el “decode”, resultaba mucho menos claro el código y varias cosas mas en contra.

Al revisar la consulta me doy cuenta que hace un uso extensivo de “decodes”, que aunque funcionan como deben, no es la mejor manera de hacerlo, aparte de que hace el SQL mas difícil de entender y hasta podría decir que también le están afectando a los tiempos de respuesta, no voy a incluir la consulta como tal, mejor dejo unos ejemplos de su uso para aquellos que todavía usan “decodes” en sus consultas.

Supongamos que tenemos una tabla con un campo llamado “TIPO”, los valores para este campo van desde la “A” a la “E”, a la vieja usanza, con un “decode” tendríamos algo así:

1
2
3
4
5
6

SELECT DECODE(TIPO,'A','TIPO A',
                   'B','TIPO B',
                   'C','TIPO C', 
                   'D','TIPO D',
                   'E','TIPO E', 'TIPO NO IDENTIFICADO') TIPO
  FROM TABLA

Ahora modificamos la consulta usando el case:

1
2
3
4
5
6
7
8
9

SELECT CASE TIPO 
          WHEN 'A' THEN 'TIPO A'
          WHEN 'B' THEN 'TIPO B'
          WHEN 'C' THEN 'TIPO C'
          WHEN 'D' THEN 'TIPO D'
          WHEN 'E' THEN 'TIPO E'
          ELSE 'TIPO NO IDENTIFICADO'
       END TIPO
  FROM TABLA

Hasta aquí pues a la vista no varia mucho, las dos las entiendo prácticamente igual, pero este es un ejemplo sencillo, ya verán como se dificultan las cosas cuando hablamos de millones de registros, aparte de que con el “case” ya estas haciendo las cosas como se deben hacer, bien hechas, ahora veamos las ventajas del case, ahora me piden simplemente identificar los valores conocidos y los no conocidos, con un “decode” no se podría tan fácilmente, igual se tendría que usar un “IN” o algo así, pero con el “case” es muy fácil:

1
2
3
4
5
6

SELECT CASE 
          WHEN TIPO BETWEEN 'A' AND 'E' THEN 
             'TIPO IDENTIFICADO'
          ELSE 'TIPO NO IDENTIFICADO'
       END TIPO
  FROM TABLA

Ahora resulta que me piden identificar los valores “A” y “E” para procesarlos de una manera, los valores “B”, “C” y “D” para procesarlos de otra y todos los demás se quedan sin procesar:

1
2
3
4
5
6
7
8

SELECT CASE 
          WHEN TIPO IN ('A','E') THEN 
             'PROCESO A y E'
          WHEN TIPO IN ('B','C','D') THEN 
             'PROCESO B, C y D'
          ELSE 'NO PROCESO'
       END TIPO
  FROM TABLA

El como realizar un autoincremental en oracle del que platique en mi post anterior surgió más bien de la necesidad de obtener ese mismo número al realizar un “insert” a la tabla, pero aproveche para hablar específicamente de el sin mezclar temas.

Ya platicaba en ese post de que al hacer el insert ya no es necesario especificar el campo del identificador por que el trigger lo generaría automáticamente, pero entonces ahora surge el problema, que pasa si en realidad se necesita durante el proceso conocer ese número ya que, por ejemplo, se trata de una tabla “maestro” y después se inserta en otra tabla “detalle” otra información incluyendo ese identificador único.

Mi respuesta hasta ayer hubiera sido realizar otro “select” a la tabla obteniendo el ultimo identificador generado, o de menos, que el mismo proceso realice la misma acción del trigger para obtener el identificador, ninguna de las dos es muy limpia solución que digamos, pero no se me ocurría otra manera de hacerlo hasta hoy.

El día de hoy me comentaban de una nueva funcionalidad, al menos para mi (no se exactamente en que versión de Oracle fue introducida) que te permite obtener el valor de los campos de una tabla después de un DML (insert, delete, update) sin necesidad de generar otra consulta.

Para este ejemplo utilizare la tabla “employees” que generé en el post anterior por lo que regresen al post anterior antes de comenzar a probar, considerando que ya se tiene esa tabla creada, con su llave primaria y su trigger “emulador” de autoincremental veamos el ejemplo del returning.

En este ejemplo se utilizo un “type” aunque en el segundo ejemplo se ve que no es necesario, todo depende de la necesidad en especifico, al realizar el insert junto con la cláusula “returning into” se obtiene el valor que se genero para el identificador sin necesidad de otra cosa:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SQL> DECLARE
  2  
  3     TYPE EmpRec IS RECORD (employee_id   employees.employee_id%TYPE);
  4  
  5     emp_info EmpRec;
  6  
  7  BEGIN
  8  
  9     INSERT INTO employees VALUES (NULL,'otro mas','otro',6000)
 10        RETURNING employee_id INTO emp_info;
 11  
 12     DBMS_OUTPUT.PUT_LINE('Se inserto nuevo registro con id ' || emp_info.employee_id);
 13  
 14  
 15  END;
 16  /
Se inserto nuevo registro con id 1
 
PL/SQL PROCEDURE successfully completed.

Este ejemplo es utilizando directamente el “%type”:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL> DECLARE
  2  
  3     emp_info employees.employee_id%TYPE;
  4  
  5  BEGIN
  6  
  7     INSERT INTO employees VALUES (NULL,'otro mas','otro',6000)
  8        RETURNING employee_id INTO emp_info;
  9  
 10     DBMS_OUTPUT.PUT_LINE('Se inserto nuevo registro con id ' || emp_info);
 11  
 12  
 13  END;
 14  /
Se inserto nuevo registro con id 2
 
PL/SQL PROCEDURE successfully completed.

Y solo para probar que todo funcionó correctamente:

1
2
3
4
5
6

SQL> SELECT * FROM employees;
 
EMPLOYEE_ID FIRTS_NAME           LAST_NAME                SALARY
----------- -------------------- -------------------- ----------
          1 otro mas             otro                       6000
          2 otro mas             otro                       6000

Hace algún tiempo me preguntaban que como se realizaba un autoincremental en Oracle, para lo que saben poco, no es más que un atributo que se utiliza para generar un identificador único para cada nueva fila de una tabla, yo se que existe en Mysql simplemente especificando “AUTO_INCREMENT” en el campo al crear la tabla, generalmente en su llave primaria.

Mi respuesta fue, que hasta donde yo se, no existe este atributo en Oracle, aunque con un pequeño trigger se puede “emular” fácilmente la lógica de este atributo en oracle, pero veamos como, claro con un ejemplo sencillo para los menos versados en el tema.

Primero creamos la clásica tabla de pruebas llamada “employees”:

1
2
3
4
5

CREATE TABLE employees (
  employee_id      NUMBER  NOT NULL,
  firts_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  salary           NUMBER);

Le creamos su llave primaria en el campo “employee_id”:

1

ALTER TABLE employees add constraint "PKemployees" primary key (employee_id);

Creamos el susodicho trigger que generara automáticamente el identificador para cada nueva fila, por supuesto, también lo pudimos haber realizado en base a una secuencia pero este caso, por facilidad, lo crearemos con un simple max + 1:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TRIGGER tr_pk_employees 
  before INSERT ON employees
  FOR each ROW
DECLARE
 
BEGIN
 
  SELECT NVL(MAX(employee_id),0) + 1
    INTO :NEW.employee_id
    FROM employees;
 
END;

Es todo, ahora al realizar un insert a la tabla solo necesitamos los valores para los demás campos, si lo queremos hacer sin especificar campos tendremos que ponerle “null” o en su defecto doble comilla simple:

1
2
3

INSERT INTO employees VALUES (NULL, 'pancho',  'lopez',1000);
INSERT INTO employees VALUES (NULL,   'juan',  'penas',2000);
INSERT INTO employees VALUES (NULL,  'nacho','vazquez',3000);

O especificando los campos sin necesidad del campo “employee_id”:

1
2
3

INSERT INTO employees (firts_name, last_name, salary) VALUES ('guicho','sanchez',4000);
INSERT INTO employees (firts_name, last_name, salary) VALUES ('rolando', 'calles',5000);
INSERT INTO employees (firts_name, last_name, salary) VALUES ('juan','gabriel',6000);

Y para corroborar que todo funcionó correctamente, revisamos los datos:

1
2
3
4
5
6
7
8
9
10

SQL> SELECT * FROM employees;
 
EMPLOYEE_ID FIRTS_NAME           LAST_NAME                SALARY
----------- -------------------- -------------------- ----------
          1 pancho               lopez                      1000
          2 juan                 penas                      2000
          3 nacho                vazquez                    3000
          4 guicho               sanchez                    4000
          5 rolando              calles                     5000
          6 juan                 gabriel                    6000

Hace algún tiempo recuerdo haber necesitado generar una contraseña aleatoria en Oracle pero al no encontrar una solución fácil nos fuimos por ponerle de contraseña el mismo nombre de usuario, o sea, si era el usuario “fulanito” le pone la contraseña “fulanito”, esto desde un modulo clásico de “olvide mi contraseña”.

Hoy buscando en Internet la solución para otro problema me tope con el paquete dbms_random de oracle, que entre sus funcionalidades esta la generación de una cadena con caracteres aleatorios, hubiera quedado perfecto para el problema que comento, y no solo sirve para eso, sirve también para generar números aleatorios, pudiendo seleccionar el rango en el que estén esos números.

Pero la opción que considero mas importante es que incluso lo puedo utilizar para seleccionar algún registro aleatorio de una tabla. Me recuerda el “ORDER BY RAND()” de Mysql, aunque no es tan “natural” acá en Oracle como lo es en Mysql, les dejo unos ejemplos de su uso, seguro a mas de uno le ayudara a resolver algo.

Una simple consulta para generar un número aleatorio:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.random FROM dual;
 
    RANDOM
----------
-267030442

Pero resulta que nos puede generar números negativos, fácil, usamos el abs:

1
2
3
4
5

SQL> SELECT ABS(DBMS_RANDOM.random) FROM dual;
 
ABS(DBMS_RANDOM.RANDOM)
-----------------------
              705640851

Bueno pero queremos especificar el rango en el que estén esos números:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.VALUE(1,10) FROM dual;
 
DBMS_RANDOM.VALUE(1,10)
-----------------------
             4.41966315

Pero resulta que me regresa números con decimales, fácil, usamos el round:

1
2
3
4
5

SQL> SELECT ROUND(DBMS_RANDOM.VALUE(1,10)) FROM dual;
 
ROUND(DBMS_RANDOM.VALUE(1,10))
------------------------------
                             4

Podemos generar una cadena aleatoria:

1
2
3
4
5

SQL> SELECT DBMS_RANDOM.string('A', 12) FROM dual;
 
DBMS_RANDOM.STRING('A',12)
--------------------------------------------------
mQKXSkedaGpK

Aquí la explicación del primer parámetro, el segundo solo es la longitud de la cadena:

1
2
3
4
5

'a','A'  alpha characters only (mixed CASE)
'l','L'  LOWER CASE alpha characters only
'p','P'  ANY printable characters
'u','U'  UPPER CASE alpha characters only
'x','X'  ANY alpha-numeric characters (UPPER)

Y por ultimo la mejor, podemos obtener un registro aleatorio de una tabla, solo es necesario sustituir “id_campo” por el campo llave de su tabla y por supuesto “mitabla” por el nombre real de su tabla:

1
2
3
4
5

SELECT id_campo
  FROM (SELECT id_campo
          FROM mitabla
      ORDER BY DBMS_RANDOM.VALUE)
 WHERE ROWNUM = 1

Pero bueno, esa consulta solo me regresa un id, quiero todo el registro, fácil:

1
2
3
4
5
6
7

SELECT *
  FROM mitabla mit,
       (SELECT id_campo
          FROM mitabla
      ORDER BY DBMS_RANDOM.VALUE) idv
 WHERE mit.id_campo = idv.id_campo
   AND ROWNUM = 1