Tag Archives: oracle

Consultas Jerárquicas en Oracle | Connect By Prior

Las consultas jerárquicas amplían la típica relación entre padres e hijos en una construcción de varias generaciones. En la típica relación entre padres e hijos, una tabla contendrá filas primarias, y otra tabla contendrá las filas secundarias. En otras palabras, sólo dos generaciones están representados.

Los datos jerárquicos tiene una estructura diferente para permitir la posibilidad de múltiples generaciones. En lugar de dos tablas separadas con una tabla primaria y una tabla secundaria, todos los datos se introducen en una sola tabla o estructura similar (como una vista), y las múltiples generaciones se definen sobre la base de los datos dentro de cada fila. El resultado es que podríamos tener padre-hijo-nieto-bisnieto, y así sucesivamente.

El ejemplo clásico de una jerarquía es un organigrama, en el que un CEO es el nivel superior, o el nodo raíz, y todo el mundo en la organización que reporta directamente al CEO es un registro hijo en el segundo nivel. Además, cada empleado que informa al segundo nivel es un nieto del nodo raíz y existe en el tercer nivel en relación con el nodo raíz, y así sucesivamente.

Consultas Jerárquicas

 

La primera muestra de nivel el director general, que sirve como el nodo raíz de esta jerarquía. El segundo nivel consiste en nodos que dependen del nodo raíz. Cada uno de ellos representa una rama en el árbol, en el que el director financiero termina su rama en particular, por lo que es un nodo hoja. Pero los otros dos nodos continúan Nivel 3, y así sucesivamente.

Continue reading

Clausulas de limitación para ’SQL Row’ | Novedades Oracle 12c

Clausulas de limitación

En “Oracle Database 12c los mecanismos para limitar cantidad y modo de extracción de registros han tenido una mejora significativa, proveyéndonos de nuevas clausulas.

Podemos especificar la cantidad de registros a ser retornados utilizando las palabras claves: “FETCH”,”FIRST”,”NEXT”. También podemos especificar la cantidad de registros a ser retornados con la clausula “PERCENT”.

Con la clausula “OFFSET” podemos especificar la cantidad de registros deseados a partir del primer registro de un set de resultados complemento a la data que ya se ha obtenido.

Las consultas que generalmente utilizan este tipo de clausulas son conocidas como: “Top-N Queries”. Veamos un ejemplo práctico de esta característica:

Continue reading

Auto_increment en Oracle | Novedades Oracle 12c

Auto_increment en Oracle

Al momento de pasarte a trabajar con la Base de Datos de Oracle una de las primeras cosas que siempre preguntan los programadores es como hacen el auto_increment en Oracle o el Identity (sql server).

Anteriormente dicho procedimiento pasaba por crear una sequencia y posteriormente utilizarla en cualquier sentencia insert que realizaras sobre la tabla o creando un trigger (before insert for each row) para realizar dicho procedimiento, es decir, hacer lo siguiente:

Sin embargo a partir de ahora existen dos métodos para realizar el auto_increment en Oracle, el primero es asignar por default el valor de una secuencia o el segundo es crear una columna del tipo identidad.

Para mas información pueden consultar la documentación de Oracle

Clausula With | Novedades Oracle 12c

clausula with

Clausula WITH

A pesar de que ya van para 3 años de la salida de Oracle 12c, comenzaré a escribir un conjunto de artículos donde les traeré las novedades de la nueva y robusta base de datos de Oracle.

El día de hoy les hablaré de la clausula WITH, pero ojo no me refiero al ya conocido uso para simplificar sentencia sql complejas que utilizan subquerys, sino al uso de la misma al momento de crear funciones y procedimientos.

La clausula WITH permite la declaración de procedimientos y funciones que serán utilizadas en un mismo bloque de código PL/SQL, como ya se imaginaran se sigue la filosofía del with en consultas dml permitiendo un bloque dinámico de PL/SQL, optimizando los tiempos de ejecución con respecto al modelo de Program Units y sin necesidad de ser almacenados como objetos en la Base de datos.

Después de la teoría, nada mejor que un ejemplo de la clausula WITH

Actualizar tabla con inner join | Oracle

inner-join

Al momento de querer ejecutar un update en una base de datos de ORACLE al resultado de la union de dos tablas mediante la sentencia join lo mas probable es que ejecutemos un query como el siguiente:

UPDATE tabla1 t1
INNER JOIN tabla2 t2 ON t1.valor = t2.DESC and t1.fecha=t2.fecha
SET t1.valor = t2.CODE
WHERE t1.filtro='correcto';

Sin embargo nos arroja un error como el siguiente:

SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 – “SQL command not properly ended”

Por lo tanto se pueden aplicar las siguientes alternativas:

Como utilizar funciones JAVA desde PL/SQL

java-plsql

Existe una manera de poder utilizar métodos desarrollados en java desde funciones en PL/SQL con la finalidad de poder aprovechar toda la potencia y flexibilidad de un lenguaje de programación orientado a objetos.

Para esto debemos cumplir con los siguientes puntos:

Crear una clase con un método estático público y que utilice variables de entrada/salida con tipos de datos simples

public class Factorial {
   public static int calcFactorial ( int n ) {
      if ( n == 1 ) return 1;
      else return n* calcFactorial ( n –1 );
   }
}

Cargar la clase java desde su código fuente (.java) o utilizando el archivo compilado (.class)

loadjava

Teniendo en cuenta que oe/oe vendría siendo usuario/password y que el comando debe ser ejecutado desde la carpeta bin de la instalación de oracle (donde se encuentran los binarios)

Como último paso se debe crear una función en la base de datos que sea la encargada de ser el puente entre la base de datos y la clase de java

CREATE OR REPLACE FUNCTION plstojavafac_fun (N NUMBER) 
RETURN NUMBER AS
LANGUAGE JAVA
NAME ‘Factorial.calcFactorial (int) return int’ ;

Finalmente utilizar la función creada desde PL/SQL

SELECT plstojavafac_fun (5)
FROM dual;

PLSTOJAVAFAC_FUN (5)
——————————
120

Para mas información pueden ahondar directamente en la documentación de oracle: http://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/02_load3.htm

¿Cómo eliminar acentos y caracteres especiales de una consulta de SQL|Oracle?

eliminar acentos oracle

eliminar acentos oracle

El día de hoy se creó la necesidad de devolver el resultado de un campo pero realizando una conversión para omitir los acentos y/o caracteres especiales, teniendo en cuenta que conozco la función replace para realizar este tipo de requerimientos, cree una función como la siguiente:

create function obtiene_nombre(primer_nombre varchar2,
segundo_nombre varchar2, primer_apellido varchar2, segundo_apellido
varchar2)
return varchar2 as
resultado varchar2(21);
begin

resultado:= SUBSTR(NVL(primer_apellido,segundo_apellido) || ' ' || NVL(primer_nombre,segundo_nombre),1,21);

resultado := REPLACE(resultado,'Ñ','N');
resultado := REPLACE(resultado,'á','a');
resultado := REPLACE(resultado,'Á','A');
resultado := REPLACE(resultado,'é','e');
resultado := REPLACE(resultado,'É','E');
resultado := REPLACE(resultado,'í','i');
resultado := REPLACE(resultado,'Í','I');
resultado := REPLACE(resultado,'ó','o');
resultado := REPLACE(resultado,'Ó','O');
resultado := REPLACE(resultado,'ú','u');
resultado := REPLACE(resultado,'Ú','U');
return RPAD(resultado,21);

end;
/

Posteriormente me di a la tarea de probarlo, realizando un query que devolvía un total de 8735 registros y que tenía una duración promedio de 360 milisegundos.

select
count(distinct obtiene_nombre(nombre,nombre2,apellido,apellido2) )
from empleados ;

Sin embargo leyendo en internet, conseguí la función TRANSLATE que se puede decir, agrupa varios replace en uno solo, para realizar algo parecido al función que realice, la forma de utilizarlo sería la siguiente:

select TRANSLATE('niñíto',
'ñáéíóúàèìòùãõâêîôôäëïöüçÑÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ',
'naeiouaeiouaoaeiooaeioucNAEIOUAEIOUAOAEIOOAEIOUC') remplazo from dual;

Por lo tanto el query para medir el rendimiento de como eliminar acentos oracle quedaría de la siguiente manera

select
count(distinct RPAD( TRANSLATE(SUBSTR(NVL(apellido,apellido2) || ' ' || NVL(nombre,nombre2),1,21),'ñáéíóúàèìòùãõâêîôôäëïöüçÑÁÉÍÓÚÀÈÌÒÙÃÕÊÎÔÛÄËÏÖÜÇ', 'naeiouaeiouaoaeiooaeioucNAEIOUAEIOUAOAEIOOAEIOUC'),21) )
from empleados ;

Ofreciéndome los siguientes resultados:

  • El tiempo promedio de respuesta había bajado a 111 milisegundos
  • No se tuvo que desarrollar una función nueva
  • El código se redujo considerablemente

Nota: si están interesados en usar la función TRANSLATE en SQL Server (t-sql), les comento que la misma no existe, pero existen versiones creadas por gente de la comunidad para facilidad de los programadores. Pueden encontrar varias en el siguiente link: Translate en t-sql