Tabla de Contenidos

Snippets Oracle SQL

Pruebas

Mostrar las tablas a las que tiene acceso nuestra cuenta: 

SELECT owner, TABLE_NAME
  FROM all_tables

Mostrar nuestras tablas: 

SELECT TABLE_NAME
  FROM user_tables

Fechas

Modificación

Fecha de hace 6 meses: 

SELECT sysdate, -- 01/07/25
       add_months(sysdate, - 6) -- 01/01/25
FROM dual

Operaciones

Diferencia entre fechas: 

SELECT (sysdate - to_date('2020-01-01 00:00', 'YYYY-MM-DD hh24:mi') AS dias_diferencia
FROM dual

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: 

SELECT EXTRACT(YEAR FROM sysdate)
FROM dual;
 
SELECT EXTRACT(MONTH FROM sysdate)
FROM dual;
 
SELECT EXTRACT(DAY FROM sysdate)
FROM dual;

Otra opción: 

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

Agrupar

Si queremos agrupar por año y mes: 

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

De otra forma, con un SQL estándar: 

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;

Filtrar

Descartar fines de semana (sábados y domingos) 

SELECT * 
FROM tabla
WHERE TO_CHAR(campo_fecha, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')

Texto

Extracción

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

SELECT regexp_substr('Metallica - Nothing Else Matters', '[^-[:space:]]*$')
FROM dual

Devolvería Nothing Else Matters

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.

Filtrando por texto que comienza por

SELECT column1, column2, column3
FROM dual
WHERE regexp_like(column1, '^(987|I)')

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

Otras

Versión de la base de datos

SELECT banner 
FROM v$version 
WHERE banner LIKE 'Oracle%'

Ejemplo de salida: 

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Limitar resultados

Desde la versión 12c de Oracle: 

SELECT *
FROM nombre_de_tabla
FETCH FIRST 10 ROWS ONLY;

En versiones anteriores: 

SELECT *
FROM nombre_de_tabla
WHERE ROWNUM <= 10;

Recursos