Create Sequence en Oracle

Create Sequence en Oracle

create sequence seq_config_mov_auto_condicion increment by 1 start with 1;

CTP6 de SQL Server 2008

Ya está disponible para bajar la CTP 6 (Community Technology Preview) de Microsoft SQL Server 2008. Ahora mismo la estoy bajando para echarle un vistazo el fin de semana y contarles qué tal.

http://www.microsoft.com:80/downloads/details.aspx?familyid=749bd760-f404-4d45-9ac0-d7f1b3ed1053&displaylang=en&tm

A la espera de SQL Server 2008

Me temo que esta semana no voy a presentar nada nuevo en mi blog. El lanzamiento del CTP6 de Microsoft SQL Server 2008 en España está ofrecido para el 26 de Febrero y quiero ponerme a jugar un rato con esa nueva versión antes de publicar.

Según voceros de Microsoft España, el Release To Market de SQL 2008 será para el verano.

Novedades de SQL Server 2008

Durante la semana pasada, asistí a un curso sobre las novedades de SQL Server 2008 en la sede de Microsoft España, en Pozuelo de Alarcón.

Uno va a estos cursos con muy pocas expectativas. Yo siempre he pensado que la letra entra con sangre, y eso no significa que haya que darle golpes al muchacho para que aprenda, sino que la única manera de aprender algo es hacerlo por uno mismo, ejercitarlo y repetirlo hasta que se domina. El número de repeticiones necesarias depende mucho de la habilidad que se tenga para la materia que es objeto de estudio, y por buena fortuna, para las bases de datos mi número de repeticiones es pequeño :)

Bueno... a lo que iba. Si tuviera que mencionar las cinco cosas que me parecieron más interesantes de SQL Server, yo diría:

5. Cache de Lookups en Analysis Services. Desde los antiguos DTS que existían en la versión 2000 hemos podido usar lookups. Esta característica de DTS nos permitía obtener el valor de un campo dependiente a partir del campo del cual depende, usando un origen de datos distinto al de la tabla principal. El ejemplo típico es un campo clave foránea que se usa para buscar un atributo en otra tabla, como cuando se usa el código de departamento para obtener el nombre. En MS SQL Server 2008 podemos colocar en cache la los datos de la tabla de referencia y reutilizarlos a lo largo de la vida del paquete. Además, los datos del lookup se leen de una sola vez. Ambas cosas mejoran sustantivamente el desempeño.

4. La administración por políticas de múltiples servidores... es algo parecido a aplicar políticas en un dominio y todos los equipos miembros tienen que adaptarse a ellas, sólo que aplicado a servidores SQL y sus objetos. Esto, claro, es para los administradores de bases de datos. Yo no soy un administrador, soy un desarrollador de sistemas de información, y por lo tanto me cuesta valorar en su justa medida las mejoras administrativas.

3. La gestión de atributos ralos (sparse). Un atributo, columna o campo "ralo" es uno que contiene un alto porcentaje de nulos. Los diseñadores de bases de datos "a la antigua" tendemos a pensar que la presencia de una columna rala en una tabla es consecuencia de una falla de diseño y una mala normalización, y solucionamos el problema creando otra tabla con una relación 1-a-1 parcial con la tabla principal, y colocamos el campo ralo en la nueva tabla. SQL Server 2008 ofrece otro modelo de gestión y almacenamiento para este tipo de atributos, o más en general, una manera diferente de tratar las especializaciones de una misma entidad, mediante el uso de columnas ralas. También permite crear índices sobre columnas ralas.

2. La posibilidad de declarar y popular una tabla "al vuelo". Muchas veces los programadores de bases de datos tenemos la necesidad de declarar una tabla y poblarla con datos en la misma instrucción. Hoy en día resolvemos el problema creando una tabla temporal y la llenamos mediante varias sentencias INSERT. SQL Server 2008 nos permite, en una sola sentencia, declarar una tabla y poblarla con datos usando la cláusula VALUES.

1. Nuevos tipos de datos para el manejo de fecha y hora. En las versiones previas de MS SQL Server con mucha frecuencia padecíamos problemas de incompatibilidad entre el tipo de datos Datetime y los tipos de datos de fecha usados por otros manejadores como ORACLE. Estos problemas eran frecuentes al migrar datos de un servidor foráneo a MS SQL. La nueva versión implementa tres tipos de datos nuevos para el manejo de fechas: Date, Time y Datetime2. Date y Time permiten manejar, o bien Fechas sin hora, o bien Horas sin fecha, con la consiguiente mejora en el uso de memoria. Datetime2 sustituye a Datetime pero sin las restricciones de rango de fechas del calendario gregoriano. También se incorpora un tipo de datos DatetimeOffset para manejar horas que están asociadas a una zona horaria, particularmente útil cuando se tiene sistemas distribuidos en varios países.

Más adelante entraré en detalles sobre cada una de estas características y otras más, cómo usarlas y en qué situaciones se le podría sacar el mejor provecho.

Número de Registros Afectados

Cuando se hace una operación de actualización o eliminación en ORACLE, se actualiza la metavariable SQL%ROWCOUNT.

Para mostrar cuántos registros fueron afectados por una operación:

dbms_output.put_line('Fueron afectados ' || SQL%ROWCOUNT || ' registros.');

Funciones Analíticas actuando sobre Funciones de Agrupamiento

En Oracle se pueden componer funciones analíticas con funciones de grupo. Las funciones analíticas son las que hemos mencionado antes:

• SUM () OVER(PARTITION BY ... ORDER BY ....)


• COUNT() OVER(PARTITION BY ... ORDER BY ....)


• RANK() OVER(PARTITION BY ... ORDER BY ....)


• DENSE_RANK OVER(PARTITION BY ... ORDER BY ....)


• AVG() OVER(PARTITION BY ... ORDER BY ....)

Las funciones de Agrupamiento son las de toda la vida que actúan sobre un conjunto de valores y devuelven un valor, como por ejemplo:

• MIN()


• MAX()


• SUM()


• COUNT()


• AVG()

Imaginemos la siguiente situación: tenemos una tabla de alumnos, una tabla de cursos, y una tabla de los alumnos que asisten a cada curso, que llamaremos curso__alumno.

Queremos un listado de los cursos ordenados por nombre, que muestre el número de alumnos en cada curso, pero además, un acumulado del número de alumnos. Una primera aproximación puede ser:

SELECT Nombre, Alumnos, SUM(Alumnos) OVER(ORDER BY Nombre)

FROM

(

SELECT C.Nombre, COUNT(*) Alumnos

FROM CURSO C

JOIN CURSO__ALUMNO CA

ON C.Curso_Id = CA.Curso_Id

GROUP BY C.Curso_Id, C.Nombre

)

Otra forma de hacer lo mismo, pero más compacta, es "componer" las funciones (sí, como en la universidad se hacía "composición de funciones", metiendo una función dentro de otra):

SELECT

C.Nombre,

COUNT(*) Alumnos,

SUM(COUNT(*)) OVER(ORDER BY C.Nombre)

FROM CURSO C

JOIN CURSO__ALUMNO CA

ON C.Curso_Id = CA.Curso_Id

GROUP BY C.Curso_Id, C.Nombre

Flashback Queries

Una característica muy interesante disponible en Oracle son los llamados "flashback querys". Este "feature" disponible en Oracle a partir de la versión 9i permite que uno consulte los registros de una tabla tal como estaban en el pasado, bien sea para un timestamp o para una "versión" del registro.

Referencia: http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_fl.htm

Para que los flashback querys funcionen, es necesario configurar algunas cosas en la base de datos:

1) La base de datos necesita un undo tablespace. El undo tablespace es equivalente al transaction log de MS SQL Server, un espacio de almacenamiento que guarda cada cambio hecho en cada registro de la base de datos.

2) Configurar la base de datos para manejar automáticamente el undo log y mantener la información del log tanto tiempo como sea posible (o lo que es lo mismo, hasta que sea inevitable sobreescribirla).

3) Otorgar ciertos privilegios a los usuarios que van a realizar los flashback queries, entre ellos:

a) Execute sobre el package DBMS_FLASHBACK:

GRANT EXECUTE ON DBMS_FLASHBACK TO ARDILLA;

b) Select y Flashback sobre las tablas que se van a consultar:

GRANT EXECUTE, FLASHBACK ON PROFESOR TO ARDILLA;

También es posible otorgar permiso de hacer flashback sobre cualquier tabla a un usuario:

GRANT FLASHBACK ANY TABLE TO ARDILLA;

c) Permisos sobre las transacciones:

GRANT SELECT ANY TRANSACTION TO ARDILLA;

Una vez cumplidos estos requisitos, podemos lanzar nuestro query:

SELECT *
FROM PROFESOR
AS OF TIMESTAMP TO_TIMESTAMP('25/09/2007 13:51:00', 'DD/MM/YYYY HH24:MI:SS')


Más adelante comentaré sobre la otra forma de usar los flashback queries, que se vasa en el número de versión del registro.

Otra Función Analítica: SUM

En el capítulo anterior les hablé del uso de las funciones RANK y DENSE_RANK para obtener la posición que ocupa un registro en un grupo. Hoy quiero hablarles sobre otra función que también operan sobre grupos siguiendo un criterio determinado: SUM.

La función SUM, cuando se la usa como función analítica y no como función de agrupamiento sirve para hacer lo que en inglés se llaman "running totals", y que podríamos llamar "acumulados", o "saldos" en castellano.

Al igual que las funciones de rango, la idea es darle a la función un criterio de ordenamiento y opcionalmente, otro de particionamiento:

SUM(Campo) OVER([PARTITION BY CampoPartiocion1[, CampoParticion2,...]] ORDER BY CampoOrdenamiento1[, CampoOrdenamiento2,...])

Por ejemplo, supongamos que tenemos una tabla en la cual se guardan los movimientos de una cuenta, y queremos consultar los movimientos y el saldo de la cuenta después de cada movimiento.

Transacción	Mes	Importe	Saldo
1	1	100.00	100.00
2	1	-10.00	90.00
3	2	-20.00	70.00

La forma de lograrlo es simple:

SELECT

Transaccion,
Importe,
SUM(Importe) OVER (ORDER BY Transaccion) Saldo

FROM Movimientos

¿Qué pasa si queremos separar las transacciones de acuerdo a un criterio, por ejemplo: mes a mes, de tal forma que el saldo comience en cero cada mes? Usando el criterio PARTITION BY podemos lograrlo:


SELECT

Transaccion,
Importe,
SUM(Importe) OVER (PARTITION BY Mes ORDER BY Transaccion) Saldo

FROM Movimientos

Transacción	Mes	Importe	Saldo
1	1	100.00	100.00
2	1	-10.00	90.00
3	2	-20.00	-20.00

Funciones de Rango

Hoy quiero hablar de las funciones de rango que existen en Oracle a partir de la versión 8i, y en Microsoft SQL Server a partir de la versión 9 (SQL Server 2005). Estas funciones se usan para asignar a cada registro un rango, una jerarquía, de acuerdo a un criterio determinado.

Las funciones que voy a mencionar hoy son RANK() y DENSE_RANK().

1. Referencias.

No quiero centrarme en la definición formal de las funciones, sino más bien en cómo sacarle provecho. Las definiciones de las funciones pueden encontrarse en:

Para Microsoft SQL Server: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Para Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions123.htm

2. Contexto de Ejemplo

Supongamos que tenemos una base de datos de un colegio, que cuenta con las tablas ALUMNO, MATERIA, CURSO, SECCION, CORTE, EVALUACION. Los alumnos pertenecen a un curso, por ejemplo: Primero, Segundo, Tercero, Cuarto... (Imaginemos que son cursos de la ESO en España, o de Primaria en Venezuela)

Cada curso tiene varias Secciones, o grupos de alumnos. Digamos que las Secciones son A, B, C... Cada alumno pertenece a una sección de un curso. Además, los profesores dictan Materias. Para cada curso hay varias materias, y una materia pertenece a un solo curso. Un Curso se compone de varios Cortes, y en cada corte se evalúan todas y cada una de las materias del curso. Los resultados de la evaluación de cada alumno en cada corte se guardan en la tabla Evaluación.

Alumno

Alumno_Id

Nombre

Seccion_Actual_Id

Materia

Materia_Id

Nombre

Curso_Id

Curso

Curso_Id

Nombre

Seccion

Seccion_Id

Curso_Id

Nombre

Corte

Corte_Id

Fecha

Evaluacion

Alumno_Id

Materia_Id

Corte_Id

Califiacion

3. Cómo obtener los primeros N registros.

Supongamos que queremos obtener los tres alumnos que han obtenido las mejores notas en un corte:

select

Alumno.Nombre,

Materia.Nombre,

O.Calificacion,

O.Puesto
from
(

select

Alumno_Id,

Materia_Id,

Corte_Id

Calificacion,

rank() over(order by calificacion desc) Puesto

from Evaluacion

where Corte_Id = 1
) O
join Alumno
on Alumno.Alumno_Id = O.Alumno_Id
join Materia
on Materia.Materia_Id = O.Materia_Id
where O.Puesto <=3

4. Obtener los primeros N registros de cada grupo.

Ahora supongamos que nos interesan los tres alumnos que han obtenido las notas más altas en cada materia, por ejemplo: los tres mejores de Matemáticas, los tres mejores de Deporte (jajaja)...

select

Alumno.Nombre,

Materia.Nombre,

O.Calificacion,

O.Puesto
from
(

select

Alumno_Id,

Materia_Id,

Corte_Id

Calificacion,

rank() over(partition by Materia_Id order by calificacion desc) Puesto

from Evaluacion

where Corte_Id = 1
) O
join Alumno
on Alumno.Alumno_Id = O.Alumno_Id
join Materia
on Materia.Materia_Id = O.Materia_Id

La función RANK() no es excluyente, eso significa que si dos alumnos tienen la misma calificación, RANK() devuelve el mismo valor para ambos. Además, RANK() deja espacios vacíos, es decir, si hay un empate en el primer lugar, no habrá nadie ocupando el segundo lugar, por ejemplo:

Juanito	Matemáticas	99	1
Pepito	Química	99	1
Sandrita	Matemáticas	98	3

Si no queremos espacios vacíos, podemos usar la función DENSE_RANK(), y si no queremos "empates", tendremos que darle a la función más argumentos que permitan desempatar:

rank() over (partition by Materia_Id order by Calificacion desc, Materia_Id asc, Alumno_Id desc)

Sentencias con Parámetros abiertos en PL/SQL Developer

En PL/SQL Developer se pueden hacer sentencias con parámetros abiertos. Al momento de ejecutar la sentencia, PL/SQL developer pide que se ingresen valores de cada uno de los parámetros.

Para definir un parámetro abierto, se usa el signo "&" seguido del nombre del parámetro, por ejemplo:

SELECT *
FROM Persona
WHERE Nombre = &Nombre;

Usar parámetros abiertos evita tener que definir variables y darle valores en tiempo de diseño:

DECLARE

vNombre varchar(20) := 'Alejandro';
BEGIN

SELECT *

FROM Persona

WHERE Nombre = vNombre;
END;

Los parámetros abiertos también pueden usarse en vez de "variables atadas" (bound variables), aunque el uso de variables atadas es más frecuente dentro de programas hechos en asp, php, referenciados mediante JDBC u OleDB. Por ejemplo, se puede usar uun parámetro abierto en lugar de:

SELECT *
FROM Persona
WHERE Nombre = :Nombre;

Una vez hace mucho tiempo alguien preguntó en el newsgroup de SQLServer en español si le recomendaban aprender ADO o SQL. La pregunta era un poco de neófita, pero dio pie a una respuesta que fue muy halagada en su momento:

"Imagínate SQL Server como un tanque de agua, y el agua son tus datos. Entonces, ADO.Net sería como una bomba que te permite sacar agua del tanque, usando diferentes tipos de tubería, como ODBC, OLEDB, etc."

ORA-03113

Este error puede ocurrir en Oracle 9i cuando se usa la sintaxis de JOIN explicitos, por ejemplo:

SELECT A.CAMPO1, B.CAMPO2
FROM A JOIN B ON A.Clave = B.ClaveA

La solución es cambiar a la sintaxis de join nativa de oracle:

SELECT A.CAMPO1, B.CAMPO2
FROM A, B
WHERE A.Clave = B.ClaveA