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;