Sentencias POCO Comunes en SQL

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.

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:

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:

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:

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:

____________________________________________________________________________________
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).

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:

_____________________________________________________________________________________

Fuentes: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm