Herramientas Útiles en SQL

Objetivos:

Ampliar el repertorio de herramientas disponibles en Oracle.
Describir las cláusulas PIVOT y UNPIVOT.
Describir la función SYS_CONTEXT.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

Introducción

Si interactúas con frecuencia con una Base de Datos Oracle, notarás que se trata de un entorno realmente amplio. En ocasiones encontramos sentencias que incluyen funciones y cláusulas poco familiares, lo que dificulta el análisis y la comprensión del bloque en cuestión.

En muchos casos, estas funciones ofrecen grandes ventajas y resultan muy eficientes cuando se combinan con aquellas que ya dominamos, facilitando así una mejor administración de la base de datos.

Sin mas que decir he aquí un diminuta lista de cláusulas y funciones útiles de Oracle.

PIVOT

La cláusula PIVOT, introducida en Oracle 11g, permite crear consultas de referencia cruzada a partir de una tabla relacional mediante sentencias SQL. Asimismo, facilita la transformación de datos entre una tabla relacional y una tabla de referencia cruzada.

En otras palabras, las consultas PIVOT convierten filas en columnas para generar resultados en formato de tabla cruzada, una técnica útil para la elaboración de informes.

Sintaxis:

SELECT *
FROM
 (
   SELECT column1, column2
   FROM tables
   WHERE conditions
 )
PIVOT 
 (
   aggregate_function(column2)
   FOR column2
   IN ( expr1, expr2, ... expr_n) | subquery
 )
ORDER BY expression [ ASC | DESC ];

Ejemplo:

SELECT *
FROM
 (
    SELECT
          e.employee_id, 
          d.department_name,
          c.country_name
    FROM
          hr.employees e,
          hr.departments d,
          hr.locations l,
          hr.countries c
    WHERE
          e.department_id = d.department_id
    AND   d.location_id = l.location_id
    AND   l.country_id = c.country_id
 )
PIVOT
 (
   COUNT(employee_id)
   FOR country_name
   IN 
      (
        'United States of America',
        'United Kingdom',
        'Canada'
      )
 )
 WHERE department_name IN
                          (
                            'Purchasing',
                            'Shipping',
                            'Sales',
                            'Marketing'
                          );

En este ejemplo usamos la Sentencia PIVOT para determinar la cantidad de empleados por país que trabajan en los departamentos Purchasing, Shipping, Sales, Marketing. Notar que limitamos los resultados solo para que contenga el conteo de los empleados en países como United States of America, United Kingdom y Canada.

SELECT *
FROM
 (
    SELECT
          e.employee_id, 
          d.department_name,
          c.country_name
    FROM
          hr.employees e,
          hr.departments d,
          hr.locations l,
          hr.countries c
    WHERE
          e.department_id = d.department_id
    AND   d.location_id = l.location_id
    AND   l.country_id = c.country_id
 )
 WHERE department_name IN
                          (
                            'Purchasing',
                            'Shipping',
                            'Sales',
                            'Marketing'
                          );

Para percibir la utilidad de PIVOT, ejecutamos la misma consulta, pero sin esta cláusula.

UNPIVOT

La cláusula UNPIVOT, también introducida en Oracle 11g, tiene como función rotar datos de columnas a filas. Su nombre sugiere una acción opuesta a la cláusula PIVOT; sin embargo, UNPIVOT no revierte los cambios realizados por esta.

En síntesis, UNPIVOT transforma columnas pivotadas en filas, generando una fila de datos por cada columna despivotada.

Sintaxis:

SELECT *
FROM
 (
   SELECT column1, column2, ... columnN
   FROM tables
   WHERE conditions
 )
UNPIVOT [INCLUDE|EXCLUDE NULLS]
 (
   unpivot_clause
   FOR unpivot_for_clause
   IN ( column1, column2, ... columnN) 
 )
ORDER BY expression [ ASC | DESC ];

Ejemplo:

CREATE TABLE hr.ventas
(codigo_emp      NUMBER(5)   NOT NULL,
  mes             NUMBER(2)   NOT NULL,
  semana          NUMBER(1)   NOT NULL,
  lunes           NUMBER(7),
  martes          NUMBER(7),
  miercoles       NUMBER(7),
  jueves          NUMBER(7),
  viernes         NUMBER(7));

Para percibir el uso de la cláusula UNPIVOT, creamos una tabla para registrar las ventas que realicen los empleados por cada día laborable de la semana. Tabla usada más adelante.

INSERT INTO hr.ventas
VALUES(150,1,1,5000,350,10000,6000,50560);

INSERT INTO hr.ventas
VALUES(150,1,2,5800,8850,16000,60,560);

INSERT INTO hr.ventas
VALUES(111,1,2,8964,350,6621,471,962);

INSERT INTO hr.ventas
VALUES(111,1,4,4756,4514,894,6000,7852);

COMMIT;

SELECT
      codigo_emp,
      lunes,
      martes,
      miercoles,
      jueves,
      viernes
FROM hr.ventas;

Insertamos algunas ventas realizadas por los empleados 150 y 111 durante algunas semanas del mes de enero.

SELECT  *
FROM
(
    (
        SELECT
              codigo_emp,
              lunes,
              martes,
              miercoles,
              jueves,
              viernes
        FROM hr.ventas
    )
    UNPIVOT
    (
        ventas
        FOR dia  IN
                        (
                          lunes,
                          martes,
                          miercoles,
                          jueves,
                          viernes
                        )
    )
)
ORDER   BY codigo_emp, dia, ventas;

En este ejemplo, con el uso de UNPIVOT convertimos los datos almacenados en columnas separadas en registros.

SYS_CONTEXT

La función SYS_CONTEXT permite obtener información relevante sobre el entorno de ejecución de la base de datos Oracle, como datos de sesión, usuario y configuración del sistema.

En términos generales, SYS_CONTEXT devuelve valores asociados a un namespace del sistema, proporcionando acceso dinámico a atributos del contexto actual dentro de una sesión.

Sintaxis:

SYS_CONTEXT('namespace', 'parameter' [, length ])

En la Sintaxis:

namespace: Es un namespace de Oracle previamente creado. Para obtener los atributos que describen la sesión actual de Oracle puede usar el namespace: ‘USERENV’.
parameter: Es un Atributo válido previamente establecido mediante el procedimiento DBMS_SESSION.set_context.
length: Opcional. Es la longitud del valor de retorno en bytes. Si se omite este parámetro o si se proporciona una entrada no válida, la longitud seria 256 bytes por defecto.

Nota: Los parámetros válidos para el namespace llamado ‘USERENV’ son los siguientes: (Tenga en cuenta que no todos los parámetros son válidos en todas las versiones de Oracle).

*Parámetro*	*Valor de Retorno*
ACTION	Identifica la posición en el módulo (nombre de la aplicación).
AUDITED_CURSORID	Devuelve el ID del cursor de *SQL* que activó la auditoría. Este parámetro no es válido en un entorno de auditoría de grano fino. Si lo especifica en dicho entorno, la Base de Datos *Oracle* siempre devuelve *NULL*.
AUTHENTICATED_IDENTITY	Devuelve la identidad utilizada en la autenticación.
AUTHENTICATION_DATA	Retorna los datos que se utilizan para autenticar al usuario de inicio de sesión.
AUTHENTICATION_METHOD	Devuelve el método de autenticación. De manera adicional puede usar AUTHENTICATION_TYPE para distinguir entre usuarios externos y enterprise.
BG_JOB_ID	Si la sesión se estableció mediante un proceso de background de Oracle, este parámetro devolverá el JOB ID. De lo contrario, devolverá *NULL*.
CLIENT_IDENTIFIER	Devuelve el identificador de cliente. Este atributo es utilizado para identificar usuarios de aplicaciones que se autentican como el mismo Usuario de la Base de Datos.
CLIENT_INFO	Información sobre la sesión del usuario.
CURRENT_BIND	Variables BIND para auditorías de grano fino.
CURRENT_SCHEMA	Nombre del esquema predeterminado que utiliza el usuario actual. Es posible cambiar dicho valor en la sesión con la sentencia: *ALTER SESSION SET CURRENT_SCHEMA*.
CURRENT_SCHEMAID	Devuelve el identificador del esquema predeterminado utilizado por el usuario actual.
CURRENT_SQL	Devuelve el *SQL* que activó el evento de auditoría.
CURRENT_SQL_LENGTH	Devuelve la longitud de la instrucción *SQL* actual que desencadenó el evento de auditoría.
DB_DOMAIN	Dominio de la Base de Datos especificado en el parámetro de inicialización DB_DOMAIN.
DB_NAME	Nombre de la Base de Datos especificado en el parámetro de inicialización DB_ NAME.
DB_UNIQUE_NAME	Nombre de la Base de Datos especificado en el parámetro de inicialización DB_ UNIQUE_NAME.
ENTRYID	Identificador de entrada de la auditoría.
ENTERPRISE_IDENTITY	Retorna la identidad enterprise-wide del usuario.
FG_JOB_ID	Si la sesión fue establecida mediante un proceso foreground del cliente, este parámetro devolverá el JOB ID. De lo contrario, devolverá *NULL*.
GLOBAL_CONTEXT_MEMORY	Es el número utilizado en el Área Global del Sistema (System Global Area) por el contexto de acceso global.
GLOBAL_UID	Retorna el ID de usuario global de Oracle Internet Directory para los inicios de sesión de seguridad empresarial. Devuelve *NULL* para todos los demás inicios de sesión.
HOST	Nombre de la máquina host desde la cual se ha conectado el cliente.
IDENTIFICATION_TYPE	Devuelve la forma en que se creó el esquema del usuario en la Base de Datos.
INSTANCE	Es el número de identificación de la instancia actual.
INSTANCE_NAME	Es el nombre de la instancia actual.
IP_ADDRESS	Dirección IP de la máquina desde la cual el cliente está conectado.
ISDBA	Devuelve *TRUE* si el usuario tiene privilegios DBA. De lo contrario, devolverá FALSE.
LANG	La abreviatura *ISO* para el nombre idioma.
LANGUAGE	Retorna el idioma, territorio y el conjunto de caracteres de la sesión. En el formato:language_territory.characterset
MODULE	Devuelve el nombre de la aplicación en uso, dicho valor es establecido a través del paquete DBMS_APPLICATION_INFO o OCI.
NETWORK_PROTOCOL	Retorna el Protocolo de Red utilizado.
NLS_CALENDAR	El calendario actual de la sesión.
NLS_CURRENCY	Es la moneda de la sesión actual.
NLS_DATE_FORMAT	Retorna e l formato de fecha para la sesión actual.
NLS_DATE_LANGUAGE	El lenguaje utilizado para expresar las fechas.
NLS_SORT	Retorna *BINARY* o la base de clasificación lingüística.
NLS_TERRITORY	Retorna el territorio de la sesión actual.
OS_USER	Retorna el nombre de usuario del sistema operativo del proceso cliente que inició la sesión de Base de Datos.
POLICY_INVOKER	Invocador de la política de seguridad a nivel de fila.
PROXY_ENTERPRISE_IDENTITY	Devuelve el DN de Oracle Internet Directory cuando el usuario proxy es un usuario enterprise.
PROXY_GLOBAL_UID	Devuelve el ID de usuario global de Oracle Internet Directory para usuarios de proxy enterprise. Devuelve *NULL* para todos los demás usuarios proxy.
PROXY_USER	Nombre de usuario de la Base de Datos que abrió la sesión actual en nombre de SESSION_USER.
PROXY_USERID	Identificador de usuario de la Base de Datos que abrió la sesión actual en nombre de SESSION_USER.
SERVER_HOST	Retorna el nombre host de la máquina en la que se ejecuta la instancia.
SERVICE_NAME	Retorna el nombre del servicio al cual está conectada la sesión.
SESSION_USER	Para usuarios enterprise, devuelve el esquema. Para otros usuarios, devuelve el nombre de usuario de la Base de Datos.
SESSION_USERID	Identificador de usuario de la Base de Datos por el cual fue autenticado.
SESSIONID	El identificador de sesión de auditoría. Nota: no puede utilizar este atributo en sentencias SQL distribuidas.
SID	Es el número de sesión (No es lo mismo que SESSIONID).
STATEMENTID	El identificador de la sentencia de auditoría. STATEMENTID representa el número de sentencias SQL auditadas en una sesión determinada.
TERMINAL	El identificador del sistema operativo para el cliente de la sesión actual. En sentencias SQL distribuidas, este atributo devuelve el identificador de su sesión local. En un entorno distribuido, esto sólo se admite para las sentencias *SELECT* remotas, no para las operaciones remotas *INSERT, UPDATE* o *DELETE*. (La longitud de retorno de este parámetro puede variar según el sistema operativo).

Ejemplo:

SELECT
       SYS_CONTEXT('USERENV', 'HOST')                       AS HOST,
       SYS_CONTEXT('USERENV', 'OS_USER')                    AS OS_USER,
       SYS_CONTEXT('USERENV', 'INSTANCE')                   AS INSTANCE,
       SYS_CONTEXT('USERENV', 'DB_NAME')                    AS DB_NAME,
       SYS_CONTEXT('USERENV', 'SESSION_USER')               AS SESSION_USER,
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD')      AS AUTHENTICATION_METHOD,
       SYS_CONTEXT('USERENV', 'ISDBA')                      AS ISDBA
FROM dual;