Objetivos:• Ampliar el repertorio de herramientas disponibles en Oracle.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
Cláusulas y Funciones de poco Uso.
Cláusulas y Funciones de poco Uso.
Si comúnmente interactúas con una Base de Datos Oracle, te darás cuenta que la misma es un mundo definitivamente inmenso. De cuando en vez vemos sentencias con funciones y cláusulas nada familiares, lo cual nos dificulta el análisis/entendimiento del bloque en question. En la mayoría de los casos encontramos funciones que proveen gran ayuda y resultan eficientes si las combinamos con las 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 la versión Oracle 11g, le permite escribir una consulta de referencia cruzada partiendo de una tabla relacional con el uso de sentencias de SQL. La misma también le permite mover datos de una tabla de referencia cruzada a una tabla relacional.[1][2]
Dicho de otra manera, las consultas PIVOT transforman filas en columnas para así generar resultados en formato de tabla cruzada, técnica util para la generació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 así 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.*/
---OUTPUT:
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 así 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.*/
---OUTPUT:
Para percibir la utilidad de PIVOT, ejecutamos la misma Consulta, pero sin esta cláusula:
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'
);
---OUTPUT:
UNPIVOT
La cláusula UNPIVOT, también introducida en la versión Oracle 11g, tiene la función de rotar datos de columnas a filas. Su nombre implica una acción opuesta a la que ejecuta la cláusula PIVOT, sin embargo, UNPIVOT no revierte los cambios realizados por PIVOT. En síntesis UNPIVOT convierte columnas pivotadas en filas (una fila de datos para cada columna a se des-pivotada).[1][2]
Sintaxis:
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 que registrara las ventas que realicen los empleados por cada día laborable de la semana. Dicha tabla será 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;
/*Aquí insertamos algunas ventas realizadas por los empleados 150 y 111 durante algunas semanas del mes de enero.*/
---
SELECT
codigo_emp,
lunes,
martes,
miercoles,
jueves,
viernes
FROM hr.ventas;
/*Consultamos los datos insertados.*/
---OUTPUT:
SELECT
codigo_emp,
ventas
FROM
(
(
SELECT
codigo_emp,
lunes,
martes,
miercoles,
jueves,
viernes
FROM hr.ventas
)
UNPIVOT
(
ventas
FOR valor IN
(
lunes,
martes,
miercoles,
jueves,
viernes
)
)
);
/*He Aquí la consulta anterior expresada con la ayuda de la cláusula UNPIVOT.*/---OUTPUT:
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'.
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 que registrara las ventas que realicen los empleados por cada día laborable de la semana. Dicha tabla será 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;
/*Aquí insertamos algunas ventas realizadas por los empleados 150 y 111 durante algunas semanas del mes de enero.*/
---
SELECT
codigo_emp,
lunes,
martes,
miercoles,
jueves,
viernes
FROM hr.ventas;
/*Consultamos los datos insertados.*/
---OUTPUT:
codigo_emp,
ventas
FROM
(
(
SELECT
codigo_emp,
lunes,
martes,
miercoles,
jueves,
viernes
FROM hr.ventas
)
UNPIVOT
(
ventas
FOR valor IN
(
lunes,
martes,
miercoles,
jueves,
viernes
)
)
);
/*He Aquí la consulta anterior expresada con la ayuda de la cláusula UNPIVOT.*/---OUTPUT:
____________________________________________________________________________________
SYS_CONTEXT
SYS_CONTEXT
La función SYS_CONTEXT es bastante util para obtener informaciones relevantes acerca del entorno de Base de Datos Oracle.
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).
• 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ámetros:
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).
|
Ejemplos:
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;
/*En este ejemplo consultamos algunos datos referentes a mi sección de Oracle mediante el uso de la función: SYS_CONTEXT .*/
---OUTPUT:
/*En este ejemplo consultamos algunos datos referentes a mi sección de Oracle mediante el uso de la función: SYS_CONTEXT .*/
---OUTPUT:
