Oracle 23c - Constructor de Tabla a partir de Valores

Otra mejora interesante en el lenguaje SQL de Oracle Database 23c es la posibilidad de "convertir" un conjunto de valores en una tabla en forma muy rápida, sin necesidad de tener que usar sentencias SELECT con la tabla DUAL.

Esta mejora al constructor VALUES (el cual solemos utilizar en las sentencias INSERT para proveer una lista de valores) ahora acepta múltiple filas en una misma sentencia, y se puede usar no solo para insertar sino en diversas sentencias SQL, como mostraremos en este artículo.

Sintaxis

La sintaxis del constructor VALUES con múltiples filas es la siguiente:

VALUES ( <ValorA1>, <ValorB1>, <ValorC1> ) 
     [,( <ValorA2>, <ValorB2>, <ValorC2> )...]

Uso en sentencias INSERT

Podemos usarlo en una sentencia INSERT para insertar múltiples filas en una única sentencia. Primero vamos a crear una tabla vacía para poder insertar los datos en la misma:

CREATE TABLE tValues (ID NUMBER(10) NOT NULL CONSTRAINT PK_Values PRIMARY KEY,
                      Valor VARCHAR2(20) NOT NULL);

Luego, usaremos VALUES para insertar tres filas en la nueva tabla en una única sentencia SELECT:

INSERT INTO tValues
VALUES (1, 'Valor 1'), (2, 'Valor 2'), (3, 'Valor 3');

Y vemos el resultado de la operación:

Uso en sentencias SELECT

Así como lo usamos para insertar múltiples valores en una única sentencia, podemos usar el constructor para devolver múltiple filas en una sentencia SELECT. La única diferencia es que debemos usar el mismo en la cláusula FROM dentro de paréntesis y debemos proporcionar un alias tanto para la "tabla" como para cada una de las columnas:

SELECT *
FROM ( VALUES (4, 'Valor 4'), (5, 'Valor 5'), (6, 'Valor 6') ) t (ID, Valor);

Esta sentencia devuelve el siguiente resultado:

Uso en Cláusulas WITH

Este constructor puede usarse también en cláusulas WITH, como mostramos a continuación:

WITH vDatos (ID, Valor) AS (
  VALUES (7, 'Valor 7'),
         (8, 'Valor 8'),
         (9, 'Valor 9')
)
SELECT * FROM vDatos;

Con el siguiente resultado:

Uso en Sentencias MERGE

Otro ejemplo de usabilidad de este constructor es en sentencias MERGE. En el ejemplo, vamos a actualizar la tabla creada en el primer ejemplo, usando la siguiente sentencia: 

MERGE INTO tValues t
USING (VALUES (3, 'Valor 3 Modif.'), (4, 'Valor 4 Nuevo') ) s (ID, Valor)
ON (s.ID = t.ID)
WHEN MATCHED THEN 
    UPDATE SET t.Valor = s.Valor
WHEN NOT MATCHED THEN
    INSERT (t.ID, t.Valor)
    VALUES (s.ID, s.Valor);

Luego de ejecutar la sentencia MERGE, si consultamos el contenido de la tabla tValues veremos que la fila #3 ha sido modificada y la fila #4 ha sido insertada:

Conclusión

El uso del constructor VALUES simplifica la generación de múltiple filas en base a valores dados, para ser utilizados en diverso tipo de sentencias SQL.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c - GROUP BY y HAVING Usando Alias

La sintaxis de las cláusulas GROUP BY y HAVING hasta la versión 23c de Oracle Database requieren que se especifique la expresión completa que define una columna (tal cual se expresa en la cláusula SELECT) en las cláusulas GROUP BY o HAVING, como vemos a continuación:

SELECT INITCAP(owner) || '-' || INITCAP(segment_type) AS Tipo,
       COUNT(*) AS Cantidad, ROUND(SUM(BYTES / 1024 / 1024)) AS MBs
FROM dba_segments
GROUP BY INITCAP(owner) || '-' || INITCAP(segment_type)
HAVING COUNT(*) > 100 OR ROUND(SUM(BYTES / 1024 / 1024)) > 40

En este caso, si bien las expresiones no son complejas, debemos tener cuidado de repetirlas en forma exacta.

El resultado de la consulta anterior ejecutado en una base de datos Oracle 23c Developer Edition recién creada es el siguiente:

Uso de Alias en GROUP BY y HAVING en Oracle 23c

Oracle 23c permite el uso de alias tanto en las cláusulas GROUP BY como HAVING, como podemos ver a continuación:

SELECT INITCAP(owner) || '-' || INITCAP(segment_type) AS Tipo,
       COUNT(*) AS Cantidad, ROUND(SUM(BYTES / 1024 / 1024)) AS MBs
FROM dba_segments
GROUP BY Tipo
HAVING Cantidad> 100 OR MBs > 40;

Esta mejora nos evita tener que repetir la expresión, y lo que es más importante, en caso de tener que modificarla evita tener que hacer la modificación en dos lugares distintos, a la vez que hace más corta y legible la sentencia.

Elección de Alias

Al usar alias en GROUP BY, debemos tener mucho cuidado con la elección del mismo. Analicemos la siguiente sentencia SQL:

SELECT SUBSTR(segment_type,1,5) AS segment_type, COUNT(*) AS Cantidad
FROM dba_segments
GROUP BY segment_type
ORDER BY 1;

Y ahora veamos el resultado de ejecutar la misma:

Rápidamente nos damos cuenta que tenemos dos grupos de resultados con el valor INDEX y dos grupos con el valor TABLE. Esto se debe a que GROUP BY utiliza la columna segment_type de la tabla en vez del alias creado por nosotros.

Si reemplazamos el alias con un nombre que no que no exista en las tablas o vistas utilizadas en nuestra consulta, podemos solucionar el problema:

SELECT SUBSTR(segment_type,1,5) AS Segmento, COUNT(*) AS Cantidad
FROM dba_segments
GROUP BY Segmento
ORDER BY 1;

El resultado de ejecutar esta sentencia es el que realmente queríamos, como podemos ver a continuación:

Uso de Notación Posicional en GROUP BY en Oracle 23c

Así como podemos usar el alias de una columna, podemos usar la notación posicional de la misma en la cláusula GROUP BY. Previo a ello es necesario habilitar esta funcionalidad, ya sea nivel de sesión o a nivel de sistema, mediante el nuevo parámetro GROUP_BY_POSITION_ENABLED, como mostramos a continuación:

ALTER SESSION SET GROUP_BY_POSITION_ENABLED = TRUE;

Una vez habilitada esta opción, podemos usar la misma notación posicional (disponible desde hace muchísimo tiempo para la cláusula ORDER BY) también en la cláusula GROUP BY, como mostramos a continuación:

SELECT INITCAP(owner) || '-' || INITCAP(segment_type) AS Tipo,
       COUNT(*) AS Cantidad, ROUND(SUM(BYTES / 1024 / 1024)) AS MBs
FROM dba_segments
GROUP BY 1
HAVING Cantidad> 100 OR MBs > 40;

Nota: No podemos usar la notación posicional en la cláusula HAVING ya que no tendría forma de distinguir el valor posicional (en nuestro ejemplo 2 o 3) de los valores a comparar (en nuestro ejemplo 100 o 40).

Conclusión

El uso de alias o notación posicional nos evita tener que repetir expresiones completas en dos lugares distintos de nuestras consultas, haciendo más fácil tanto la escritura como el mantenimiento posterior del código SQL generado.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c - Mejoras a Sentencias y Expresiones CASE en PL/SQL

Oracle Database 23c introduce algunas mejoras a las sentencias y expresiones CASE que las hacen más fáciles de usar en PL/SQL

CASE con Comparaciones Adicionales a Igualdad (Sentencia y Expresión)

Hasta la versión 23c, tanto las sentencias como expresiones CASE permitían solo comparaciones por igualdad cuando se usaba en su forma simple, si se deseaba comparar de otra forma debía usarse en su forma "de búsqueda" como mostramos a continuación:

Ejemplo de Forma Simple (sólo Igualdad)

Cuando necesitamos usar solo igualdades, podemos usar la forma simple:

CASE TipoCliente
    WHEN 1 THEN 'Mayorista'
    WHEN 2 THEN 'Minorista'
    WHEN 3 THEN 'No Clasificado'
END CASE

Ejemplo de Forma de Búsqueda

Pero si necesitamos buscar valores no sólo por igualdad entonces tenemos que usar la forma de búsqueda, de la siguiente manera:

CASE 
    WHEN Saldo < 100000 THEN 'Deudor - Crítico'
    WHEN Saldo < 0 THEN 'Deudor - Normal'
    WHEN Saldo = 0 THEN 'Neutro'
    WHEN Saldo > 0 THEN 'Acreedor'
END CASE

La diferencia es que en cada cláusula WHEN debemos escribir una operación de comparación que devuelva un valor verdadero o falso, teniendo que repetir en este caso el nombre de la columna que deseamos comparar.

Case de Forma Simple en Oracle 23c

Ahora es posible usar una sentencia CASE de forma más simple, especificando la columna o expresión a utilizar para comparar una sola vez, y aclarando en cada cláusula WHEN la operación de comparación (mayor, menor, distinto, etc) que se desea realizar, en caso que no sea de tipo igualdad:

CASE Saldo 
    WHEN < 100000 THEN 'Deudor - Crítico'
    WHEN < 0 THEN 'Deudor - Normal'
    WHEN 0 THEN 'Neutro'
    WHEN > 0 THEN 'Acreedor'
END CASE

A continuación vemos un ejemplo muy simple de su uso como expresión (esta mejora también aplica a CASE como sentencia, no sólo como expresión):

DECLARE
    vTipo   VARCHAR2(20);
BEGIN 
    FOR cSegmentos IN (SELECT SEGMENT_NAME, BLOCKS FROM USER_SEGMENTS 
                       ORDER BY SEGMENT_NAME FETCH FIRST 10 ROWS ONLY)
    LOOP
        vTipo := CASE cSegmentos.BLOCKS 
                    WHEN > 1000 THEN 'Gigante'
                    WHEN > 100 THEN 'Grande'
                    WHEN > 0 THEN 'Normal'
                    WHEN 0 THEN 'Vacío'
                END;
        DBMS_OUTPUT.PUT_LINE (cSegmentos.SEGMENT_NAME || ' es ' || vTipo);
    END LOOP;
END;
/

Lo cual devuelve lo siguiente:

CASE con Múltiples Comparaciones (Sentencia y Expresión)

Adicionalmente, cuando existe más de una condición que debe devolver el mismo valor, podemos usar todas ellas en la misma sección de la cláusula WHEN en vez de tener que especificar una cláusula por cada condición, usando si deseamos la opción de comparación por cualquier tipo de operación.

A continuación, vemos un ejemplo pero usando una sentencia CASE en vez de una expresión (le mejora es válida para ambas)  y usando también la opción de comparación no sólo por igualdad sino también por rangos:

DECLARE
    vTipo   VARCHAR2(20);
BEGIN 
    FOR cSegmentos IN (SELECT SEGMENT_NAME, BLOCKS FROM USER_SEGMENTS 
                       ORDER BY SEGMENT_NAME FETCH FIRST 10 ROWS ONLY)
    LOOP
        CASE cSegmentos.BLOCKS 
            WHEN > 1000 THEN vTipo := 'Gigante';
            WHEN > 100 THEN vTipo := 'Grande';
            WHEN 8, 16, 32, 64 THEN vTipo := 'Normal';
            WHEN 0 THEN vTipo := 'Vacío';
        END CASE;
        DBMS_OUTPUT.PUT_LINE (cSegmentos.SEGMENT_NAME || ' es ' || vTipo);
    END LOOP;
END;
/

Conclusión

Estas mejoras a la sentencia y expresión CASE simplifican la escritura de código PL/SQL permitiendo escribir estas acciones en forma más concisa y clara.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c - Instalación más Sencilla de Esquemas de Ejemplo

Oracle provee desde hace décadas un conjunto de esquemas con datos para poder utilizarlos de ejemplo, tal como se detalla a continuación:

• HR: Human Resources.
• CO: Customer Orders.
• SH: Sales History.
• OE: Order Entry (Obsoleto - No se actualiza más).
• PM: Product Media (Obsoleto - No se actualiza más).

La instalación de los mismos solía ser una tarea "tediosa" ya que los scripts para instalarlos debían ser ejecutados por un usuario con permisos de DBA y no permitia elegir un esquema, siendo obligatorio instalarlo en el esquema provisto.

Instalación en Oracle 23c (en realidad Oracle 19c en adelante)

Oracle Database 23c incluye un nuevo conjunto de scripts para instalar los esquemas de ejemplo, que pueden ser instalados en cualquier base de datos Oracle 19c o posterior, y que no requieren de permisos de DBA ni la utilización de SQL*Loader.

Los mismos se encuentran disponibles en el repositorio de Oracle Samples en GitHub.

Descargando el archivo

Podemos descargar el archivo que contiene los esquemas usando el siguiente comando en Linux:

wget https://github.com/oracle-samples/db-sample-schemas/archive/refs/tags/v23.1.zip

Luego de descargado, debemos descompactar el mismo usando unzip:

unzip v23.1.zip

Este paso va a crear una carpeta "db-samples-schemas-23.1" que contiene los scripts necesarios para instalar los cinco esquemas mencionados anteriormente.

Instalando el esquema HR

En este artículo vamos a usar el esquema HR como ejemplo, pero lo vamos a instalar en otro usuario ya existente de la base de datos, en este caso llamado RRHH, a diferencia de los scripts anteriores que no permitian usar otro usuario y debían ser ejecutados por un usuario DBA.

Debemos asegurarnos que el usuario deseado (en este caso RRHH) tenga permisos para crear objetos (en Oracle 23c se puede hacer fácilmente, como contamos en el artículo "Oracle 23c - Nuevo Rol DB_DEVELOPER_ROLE") y que tenga cuota sobre algun tablespace para poder crear los objetos.

En el directorio "human_resources" ubicado dentro de "db-samples-schemas-23.1" vamos a encontrar los siguientes archivos:

• hr_create.sql
• hr_populate.sql
• hr_code.sql
• hr_install.sql
• hr_uninstall.sql
• README.md
• README.txt

Lo que debemos hacer a continuación es conectarnos a la base de datos con el usuario RRHH y ejecutar el siguiente script:

@hr_create

Este script crea los objetos de datos (tablas, vistas, índices) del esquema HR en el usuario en el que nos encontramos conectado.

A continuación, vamos a llenar las tablas creadas en el paso anterior mediante el siguiente script:

@hr_populate

El paso final es crear los triggers y procedimientos del esquema, en este caso con el siguiente script:

@hr_code

Y podemos usar el siguiente script para confirmar que todos los objetos del esquema HR han sido creados en el esquema destino:

col user format a20
col object_name format a30
col object_type format a20
SELECT user, object_name, object_type FROM user_objects;

Obteniendo el siguiente resultado:

USER                 OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ --------------------
RRHH                 DEPARTMENTS_SEQ                SEQUENCE
RRHH                 JOBS                           TABLE
RRHH                 JOB_ID_PK                      INDEX
RRHH                 EMPLOYEES                      TABLE
RRHH                 EMP_EMAIL_UK                   INDEX
RRHH                 EMP_EMP_ID_PK                  INDEX
RRHH                 EMPLOYEES_SEQ                  SEQUENCE
RRHH                 JOB_HISTORY                    TABLE
RRHH                 JHIST_EMP_ID_ST_DATE_PK        INDEX
RRHH                 EMP_DETAILS_VIEW               VIEW
RRHH                 EMP_DEPARTMENT_IX              INDEX
RRHH                 EMP_JOB_IX                     INDEX
RRHH                 EMP_MANAGER_IX                 INDEX
RRHH                 EMP_NAME_IX                    INDEX
RRHH                 DEPT_LOCATION_IX               INDEX
RRHH                 JHIST_JOB_IX                   INDEX
RRHH                 JHIST_EMPLOYEE_IX              INDEX
RRHH                 JHIST_DEPARTMENT_IX            INDEX
RRHH                 LOC_CITY_IX                    INDEX
RRHH                 LOC_STATE_PROVINCE_IX          INDEX
RRHH                 LOC_COUNTRY_IX                 INDEX
RRHH                 SECURE_DML                     PROCEDURE
RRHH                 SECURE_EMPLOYEES               TRIGGER
RRHH                 ADD_JOB_HISTORY                PROCEDURE
RRHH                 UPDATE_JOB_HISTORY             TRIGGER
RRHH                 REG_ID_PK                      INDEX
RRHH                 REGIONS                        TABLE
RRHH                 COUNTRIES                      TABLE
RRHH                 COUNTRY_C_ID_PK                INDEX
RRHH                 LOCATIONS                      TABLE
RRHH                 LOC_ID_PK                      INDEX
RRHH                 LOCATIONS_SEQ                  SEQUENCE
RRHH                 DEPARTMENTS                    TABLE
RRHH                 DEPT_ID_PK                     INDEX

34 rows selected

Podemos consultar alguna de las tablas:

Scripts Adicionales

El script "hr_install.sql" puede utilizarse, conectado con un usuario con permisos de DBA, para hacer la instalación completa del esquema HR, incluyendo la creación de dicho usuario.

El script "hr_uninstall.sql" puede utilizarse, conectado con un usuario con permisos de DBA, para hacer la desinstalación completa del esquema HR, incluyendo la eliminación de dicho usuario.

Nota: Los pasos de instalación, desinstalación o creación manual son similares para cualquiera de los esquemas de ejemplo provistos, sólo se deben usar los scripts similares disponibles en los otros directorios.

Conclusión

La instalación de los esquemas de ejemplo de Oracle 23c ha sido optimizada para que se puedan utilizar por cualquier usuario sin necesidad de permisos de DBA.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c - Mejoras a cláusula RETURNING

En esta versión de Oracle Database se introduce una pequeña pero interesante mejora a la clausula RETURNING que si bien es válida para cualquier sentencia DML (INSERT, UPDATE o DELETE) es realmente útil para UPDATE.

Como funcionaba RETURNING hasta Oracle 23c

La clausula RETURNING permitía obtener los valores involucrados en una sentencia DML, devolviendo el valor insertado en el caso de INSERT (ya que no hay un valor anterior), el valor existente o "anterior" en el caso de DELETE (ya que una vez borrado el registro no hay mas valores disponibles) y en el caso de UPDATE el nuevo valor asignado a la columna.

Ejemplo

Para ver el comportamiento, vamos a usar el código siguiente para crear una tabla de pruebas, con un registro en la misma:

CREATE TABLE PruebaReturning (
  ID NUMBER CONSTRAINT PK_PruebaReturning PRIMARY KEY,
  Valor VARCHAR2(50)
);
--
INSERT INTO PruebaReturning VALUES (1, 'Valor Inicial');
--
COMMIT;
--

Luego de crear y popular la tabla, ejecutaremos el siguiente código que usa RETURNING en un UPDATE para obtener el valor actualizado. Noten que el comportamiento por defecto de la clausula solo requiere identificar la columna para la cual se desea obtener el valor, y donde lo vamos a "almacenar":

DECLARE 
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING Valor INTO vValorFinal;
    --
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
END;
/
ROLLBACK;

Al ejecutar este código obtenemos el siguiente resultado:

El problema es que no podemos obtener el valor anterior que poseía la columna, lo cual puede ser algo interesante de tener en el caso de UPDATE.

Como funciona RETURNING desde Oracle 23c

Desde Oracle 23c la clausula RETURNING permite especificar la opción OLD o  NEW antes del nombre de la columna, lo cual permite "elegir" uno de ellos, o proporcionar ambos de ser requerido.

Sintaxis

La nueva sintaxis de la cláusula es la siguiente:

RETURNING [OLD | NEW] <Expresion> INTO <Variable>

Nota: Si bien es posible usar esta nueva opción en sentencias INSERT o DELETE, en el caso de estar insertando un nuevo registro el valor anterior (OLD) sera siempre NULL,  y en el caso de estar borrando el valor nuevo (NEW) será también siempre NULL, por lo que la misma no es útil aunque es permitida.

Ejemplo

A continuación, vamos a probar con un bloque PL/SQL similar al anterior, pero esta vez usaremos OLD y NEW obteniendo tanto el valor anterior como el nuevo:

DECLARE 
    vValorInicial VARCHAR2(50) := '-';
    vValorFinal   VARCHAR2(50) := '-';
BEGIN
    --
    UPDATE PruebaReturning
    SET Valor = REPLACE(Valor,'Inicial','Actualizado')
    WHERE ID = 1
    RETURNING OLD Valor, NEW Valor INTO vValorInicial, vValorFinal;
    --
    dbms_output.put_line('vValorInicial = ' || vValorInicial);
    dbms_output.put_line('vValorFinal = ' || vValorFinal);
    --
END;
/

Al consultar la salida de DBMS_OUTPUT podemos ver que pudimos capturar tanto el valor anterior como el nuevo valor al hacer la actualización de la fila:

 

Conclusión

Este cambio en Oracle Database 23c permite una mayor flexibilidad al momento de capturar las versiones anteriores o actuales de los datos que son alcanzados por una sentencia UPDATE.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.