SQL – PL/SQL
Función Oracle: LISTAGG
La función LISTAGG se utiliza, para pivotar filas en una única columna concatenando los valores con un separador indicado. LA diferencia con la función PIVOT, es que pivota un valor a una columna sin combinarlas en el mismo campo (ver artículo PIVOT). Como alternativas en versiones anteriores podía utilizar CASE, pero no era dinámico, o la función COLLECT, pero es
Funciones Oracle: FIRST_VALUE y LAST_VALUE
Las funciones FIRST_VALUE() y LAST_VALUE() proporcionan el valor inicial y final en una lista ordenada respectivamente. FIRST_VALUE: Obtendría el primer valor LAST_VALUE: Obtendría el último valor SINTAXIS: función (<expresion_valor_1> [,<offset> [, <valor_por_defecto> ]]) OVER ([PARTITION BY <expresion_valor_3> [,<expresion_valor_4>, …]]) ORDER BY <expresion_valor_2> [ASC|DESC] [NULLS FIRST | NULLS LAST] [,…] [ventana]) siendo ventana: [ROWS|RANGE] inicio AND fin función – FIRST_VALUE o
Funciones ordenación:ROW_NUMBER, RANK y DENSE_RANK
ROW_NUMBER, RANK y DENSE_RANK son funciones de ordenación dentro de una partición. La función ROW_NUMBER es una función analítica, que asigna un valor único dentro una partición según un orden determiado. El valor parte de 1. Diferencias entre ROW_NUMBER, RANK y DENSE_RANK sería: ROW_NUMBER: asigna numeros contiguos y unicos desde 1 hasta el último según la ordenación y por cada
Funciones Oracle: LAG y LEAD
Con las funciones LAG() y LEAD() se obtienen el valor anterior o posterior en una lista ordenada. Con estas funciones se evita la necesidad de realizar un join con la misma tabla. LAG: Obtendría el valor del registro anterior LEAD: Obtendría el valor del registro posterior SINTAXIS: función (<expresion_valor_1> [,<offset> [, <valor_por_defecto> ]]) OVER ([PARTITION BY <expresion_valor_3> [,<expresion_valor_4>, …]]) ORDER
Funciones Oracle: pivot / unpivot
PIVOT, permite trasponer filas a columnas y UNPIVOT, permite el paso inverso, el paso de columnas a filas. Están disponibles a partir de la versión Oracle 11G. La mismo que se realiza mediante PIVOT y UNPIVOT, se podía realizar en versiones anteriores, mediante funciones de decodificación, pero son menos eficiente y si había muchos valores podría ser bastante engorroso. EJEMPLO
Evitar el error Oracle ORA-04091 (tabla mutante)
Este error ocurre usualmente al realizar una consulta de un campo desde un trigger y ese campo es el que hace, que salte el trigger. Vamos a ver un ejemplo, que produce este error: — Creamos una tabla CREATE TABLE usuarios (id Number, nombre VARCHAR2(10)); — Insertamos registros de prueba INSERT INTO usuarios values (1, ‘Pepe’); INSERT INTO usuarios
Mascaras de tiempo en Oracle
Las mascaras de tiempo se utilizan para indicar como se realizan las transformaciones de caracter a fecha u hora el formato. MÁSCARA DESCRIPCIÓN – / , . ; : Símbolos de puntuación se reproducen en la cadena resultante «texto» Se reproduce el texto entre comillas dobles YEAR, SYEAR Año alfabético. SYEAR devuelve las fechas AC como negativas YYYY, SYYYY Año
BULK COLLECT: ejemplos
Os recomiendo tambien leer: introducción a BULK COLLECT EJEMPLO recuperar registros con bulk collect: DECLARE TYPE t_salarios IS TABLE OF empleado.salario%TYPE; salarios t_salarios; BEGIN SELECT salario BULK COLLECT INTO salarios FROM empleados WHERE ROWNUM <= 1000; END; EJEMPLO recuperar registros utilizando un cursor: DECLARE TYPE DeptRecTab IS TABLE OF departamentos%ROWTYPE; dept_recs DeptRecTab; CURSOR c IS SELECT id_departamento, nob_departamento FROM departamentos
Busqueda de un texto en el código PL/SQL almacenado en base de datos
No os ha pasado alguna vez, que tenéis que buscar un literal en cierta parte del código y no os acordais donde era. Una forma rápida y sencilla es consultar las tablas user_source o all_source. Estas tablas contienen por cada línea de código el propietario del objeto, el nombre del objeto, el tipo, la línea y el contenido de la
Fechas: funciones y tipos en Oracle
TIPOS DE DATOS DE FECHAS DATE Rango desde 01/01/4712 AC hasta 31/12/9999 DC. El tamaño es de 7 bytes. El formato lo determina de manera implicita el parámetro NLS_TERRITORY y explicitamente el parámetro NLS_DATE_FORMAT No contempla las fracciones de segundo ni la zona horaria. TIMESTAMP [(fracciones_de_segundos)] Admite fracciones de 0 a 9 dígitos, aunque por defecto son 6. Dependiendo de
BULK COLLECT
En la base de datos Oracle hay dos motores, uno para ejecutar PL/SQL y otro para SQL. Cuando el PL/SQL quiere manipular datos, accede al motor de SQL. Esto conlleva muchos recursos. Bulk collect agrupa varias instrucciones y lo realiza en una sola ejecución, optimizando los cambios de contexto. Es importante si se trabaja con grandes volumenes de datos. Es
PL SQL dinámico
Consiste en la ejecución de sentencias SQL o bloques PL/SQL a partir de una cadena de texto montadas de manera dinámica. El uso de execute immediate es muy cómodo para la construcción de sql dinámicos el problema es el parseo de estas sentencias. Las bind variables, evita parseos inecesarios. Para evitar SQL Injection en PL/SQL dinámico es importante utilizar bind
Eliminación de registros duplicados en Oracle
Ejemplo de eliminación sencilla de registros duplicados en una tabla Oracle: Creamos una tabla: SQL> create table tabla (ID number, columna1 varchar2(20), columna2 varchar2(20)); # Hacemos algunos insert en la tabla: SQL> insert into tabla values (1, ‘primero’, ‘primero’); SQL> insert into tabla values (2, ‘segundo’, ‘primero’); SQL> insert into tabla values (3, ‘tercero’, ‘primero’); SQL> commit; # Duplicamos un
Introducción a Oracle PL/SQL
PL/SQL significa Procedural Language/Structured Query Language. Es un lenguaje de programación incluido dentro de la base de datos. Los procedimientos, funciones, disparadores y paquetes creados con el PL/SQL se almacenan en base de datos. Están incluidos dentro de las políticas de seguridad de Oracle y son altamente recomendables, para el tratamiento de datos. El PL/SQL surge ante la necesidad de
