Lunes, 08 Junio 2020 02:33

Sentencias POCO Comunes en SQL

Rate this item
(0 votes)

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:

PIVOT


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:

WITHOUT PIVOT


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:

Datos Insertados

 

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:

UNPIVOT


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 namespacellamado '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 INSERTUPDATE 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:

SYS CONTEXT


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

Read 242 times Last modified on Jueves, 06 Agosto 2020 16:35

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Buscador

Búsqueda

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.