====== Snippets Oracle SQL ======

===== Pruebas =====

Mostrar las tablas a las que tiene acceso nuestra cuenta:

<code sql>
SELECT owner, table_name
  FROM all_tables
</code>

Mostrar nuestras tablas:

<code sql>
SELECT table_name
  FROM user_tables
</code>
===== Fechas =====

==== Modificación ====

Fecha de hace 6 meses:

<code sql>
SELECT sysdate, -- 01/07/25
       add_months(sysdate, - 6) -- 01/01/25
FROM dual
</code>
==== Operaciones ====

Diferencia entre fechas:

<code sql>
select (sysdate - to_date('2020-01-01 00:00', 'YYYY-MM-DD hh24:mi') as dias_diferencia
from dual
</code>

Mostraría los días entre sysdate y las 2020-01-01 00:00. Si queremos pasarlo a horas, habría que multiplicarlo por 24. Para pasar a minutos, multiplicarlo por 24 y por 60, etc.

==== Extracción ====

Para extraer año, mes, día de una fecha:

<code sql>
SELECT EXTRACT(YEAR FROM sysdate)
FROM dual;

SELECT EXTRACT(MONTH FROM sysdate)
FROM dual;

SELECT EXTRACT(DAY FROM sysdate)
FROM dual;
</code>

Otra opción:

<code sql>
SELECT to_char(sysdate, 'yyyy')
FROM dual;

SELECT to_char(sysdate, 'mm')
FROM dual;

SELECT to_char(sysdate, 'dd')
FROM dual;
</code>

  * [[https://livesql.oracle.com/apex/livesql/file/content_GCEY1DN2CN5HZCUQFHVUYQD3G.html|TO_CHAR Day Format Masks]]
==== Agrupar ====

Si queremos agrupar por año y mes:

<code sql>
select to_char(DATE_CREATED, 'YYYY-MM'), sum(Num_of_Pictures)
from pictures_table
group by to_char(DATE_CREATED, 'YYYY-MM')
order by 1
</code>

De otra forma, con un SQL estándar:

<code sql>
select extract(year from date_created) as yr, extract(month from date_created) as mon,
       sum(Num_of_Pictures)
from pictures_table
group by extract(year from date_created), extract(month from date_created)
order by yr, mon;
</code>

==== Filtrar ====

Descartar fines de semana (sábados y domingos)

<code sql>
SELECT * 
FROM tabla
WHERE TO_CHAR(campo_fecha, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')
</code>

==== Exportación Excel en español ====

Si vamos a exportar datos de una consulta a fichero CSV y queremos abrirlo con Excel con configuración española, para que Excel interprete bien las fechas hay que usar alguno de estos formatos:

  * Estándar ISO: ''%%'yyyy-mm-dd hh24:mi:ss'%%''
  * Formato Excel en español: ''%%'dd/mm/yyyy hh24:mi:ss'%%''

Ejemplo:

<code sql>
SELECT
    id,
    TO_CHAR(date_creation, 'yyyy-mm-dd hh24:mi:ss') AS fecha_creacion,
    TO_CHAR(date_solution, 'yyyy-mm-dd hh24:mi:ss') AS fecha_solucion
FROM tabla    
</code>

O:

<code sql>
SELECT
    id,
    TO_CHAR(date_creation, 'dd/mm/yyyy hh24:mi:ss') AS fecha_creacion,
    TO_CHAR(date_solution, 'dd/mm/yyyy hh24:mi:ss') AS fecha_solucion
FROM tabla    
</code>
===== Texto =====

==== Extracción ====

Mediante la función REGEXP_SUBSTR se puede extraer un subcadena de un texto:

<code sql>
select regexp_substr('Metallica - Nothing Else Matters', '[^-[:space:]]*$')
from dual
</code>

Devolvería ''Nothing Else Matters''

<WRAP center round important 60%>
Como el regex de Oracle está basado en POSIX, no se puede utilizar ''\s'' para coincidencias de espacios en blanco sino que se debe usar ''[:space:]'' o ''[:blank:]'' para espacios y tabulaciones.
</WRAP>

==== Filtrando por texto que comienza por ====

<code sql>
SELECT column1, column2, column3
FROM dual
WHERE regexp_like(column1, '^(987|I)')
</code>

Filtraríamos por la column1 buscando que comenzase por ''987'' o por ''I''.

===== Otras =====

==== Versión de la base de datos ====

<code sql>
SELECT banner 
FROM v$version 
WHERE banner LIKE 'Oracle%'
</code>

Ejemplo de salida:

<code>
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
</code>

==== Limitar resultados ====

Desde la versión 12c de Oracle:

<code sql>
SELECT *
FROM nombre_de_tabla
FETCH FIRST 10 ROWS ONLY;
</code>

En versiones anteriores:

<code sql>
SELECT *
FROM nombre_de_tabla
WHERE ROWNUM <= 10;
</code>

==== Formato de fecha ====

Para ver el formato de fecha actual (`NLS_DATE_FORMAT`) en Oracle Database, tienes varias opciones según lo que quieras comprobar.

Ver el formato en la sesión actual:

<code sql>
SELECT value 
FROM nls_session_parameters 
WHERE parameter = 'NLS_DATE_FORMAT';
</code>

Ver el formato a nivel de base de datos (valor por defecto que puede ser sobrescrito por la sesión):

<code sql>
SELECT value 
FROM nls_database_parameters 
WHERE parameter = 'NLS_DATE_FORMAT';
</code>

Ejemplo de salida:

<code>
DD-MON-RR
</code>

  * ''DD'': día con dos dígitos
  * ''MON'': mes abreviado en texto.
  * ''RR'': año con 2 dígitos (''RR'' es parecido a ''YY'', pero con lógica para siglos)
===== Recursos =====

  * [[https://stackoverflow.com/questions/8919481/how-to-select-only-1-row-from-oracle-sql|Selecciónar solo 1 registro]]
  * [[https://learnsql.com/blog/the-most-useful-date-and-time-functions-in-oracle-database|The Most Useful Date and Time Functions]]