SQL Server: ejecutar sentencias con número variable de parámetros

Hace algún tiempo escribí una breve introducción de cómo ejecutar SQL dinámico, y lo sencillo que era. Ahora bien, para evitar tener que escribir funciones y sentencias cuando usamos infinidad de tablas, campos y parámetros distintos, hoy me vino la idea de crear una única función que nos haga el trabajo, da igual el número y tipo de parámetros a ejecutarse, y que todas las llamadas a esta función sean parametrizadas y customizadas de forma que centralizamos la ejecución, evitamos duplicar código, etc.

Os dejo una función, la más simple que se me ocurre possible, y quedo a la espera de vuestros comentarios y sugerencias:

Algunos ejemplos de llamadas al procedimiento podrían ser:

D2B Express C: función CONCAT, ordenación de registros y valores NULL

Tres aspectos prácticos básicos cuando tenemos que trabajar con BDs son poder unir cadenas, concatenando sus valores, así como ser capaces de ordenar por distintas columnas (ascendente y descendentemente) y cómo manejar valores vacíos (NULL).

Veamos en el siguiente ejemplo formas idénticas de unir cadenas, bien con la función CONCAT o con el separador "||":

Ahora vamos a recuperar aquellos empleados con el campo SEPDATE a un valor vacío (NULL):

Y por último recordemos cómo podemos ordenar por un campo (o varios) y ascendentemente (ASC o no especificado ya que es el tipo de ordenación por defecto) o descendentemente (DESC):

En la última imagen hemos ordenado primero los empleados primero por fecha de nacimiento y además a su vez por apellido pero descendentemente. En el segundo caso hemos hecho lo contrario: ordenar primero por fecha de nacimiento descendentemente (es decir los más jóvenes primero) y a la vez por apellido ascendentemente.  Un recurso a veces olvidado, es que también es posible especificar la/s columna/s por las que queremos ordenar usando el valor numérico de esa columna entre los valores devueltos, es decir, en el ejemplo anterior hubiera sido equivalente usar "ORDER BY 5, 2 desc" y "ORDER BY 5 desc, 2" (ya que la 5ª columna del recordset es el Birthdate, y la 2ª columna es LastName).

Y por último en este post vamos a recordar el uso de las funciones de agregado: SUM, COUNT, AVG, MAX, MIN, etc, es decir aquellas que tras agrupar registros nos interesa obtener el valor máximo o mínimo, la media, contar dichos registros, sumarlos, etc. Empecemos con nuestra tabla empleados, agregando una nueva columna para indicar el salario y actualicemos los registros existentes:

Y ahora vamos a ejecutar como ejemplo algunas de las funciones de agregado para conocer su funcionamiento: sumar los salarios de todos los empleados contratados según su año de alta, y después obtener el salario más bajo de cada año de contratación.

D2B Express C: uso avanzado de SELECT

Creo que ya tenemos un conocimiento suficiente para poder explotar bien cualquier base de datos en DB2, sabiendo que en la principal sentencia para recuperar los datos (SELECT), debemos incluir sólo las columnas que se necesiten, para una mayor rendimiento del motor, y que además en las condiciones WHERE podemos buscar valores entre rangos (BETWEEN), que estén incluidos en una lista (IN), o no (NOT IN), usar los caracteres de comparación (<, >, >=, <=, =), etc.

Además, hoy veremos cómo funciona la potente cláusula LIKE, básicamente para poder hacer búsquedas con "comodines" y que los valores que nos interesa recuperar siguen un "patrón". Si quisiéramos indicar un único carácter en ese patrón debemos usar el símbolo "_".Veamos un ejemplo para recuperar los empleados en cuyo nombre aparezca una letra "n":

Otro punto que seguro nos será útil será, respecto a comparaciones de campos fecha (DATE), es si queremos listar registros entre años, para lo cual usaremos WHERE YEAR(nombre campo de tabla) BETWEEN [desde año] AND [hasta año]

Además, siempre que nos interese, podemos crear ALIAS para las columnas que recuperemos (la palabra AS, como veremos, es opcional). Un ejemplo, traduciendo algunos de los nombres de campos de nuestra BD pruebas, al español:

D2B Express C: sentencias ALTER, UPDATE, DELETE y DROP

Hoy vamos a repasar algunas de las principales acciones para el mantenimiento de una BD en DB2, usando sentencias para borrar, actualizar, cambiar o eliminar definitivamente registros, tablas, etc. Para ello continuamos con nuestra base de datos creada en los posts anteriores.

Lo primero que vamos a hacer es añadir 2 nuevos campos DATE a la tabla Empleado. Para ello usaremos el comando ALTER TABLE [nombre tabla] ADD [nombre columna] [tipo datos]

Además, como puede verse en la imagen, hemos usado el comando REORG TABLE [nombre tabla], cuya finalidad es la de reorganizar la tabla indicada, reconstruyendo las filas para eliminar los datos fragmentados y compactando la información. Es muy recomendable hacerlo cuando alteramos la estructura de una tabla.

Por otro lado, para actualizar registros la sentencia es UPDATE, siendo la sintaxis la siguiente: UPDATE [nombre tabla] SET [nombre columna] = [nuevo valor], y opcionalmente podemos incluir al final un WHERE [condición] para así solo actualizar los registros que cumplan una determinada regla.  Además podemos actualizar varios campos simultáneamente. Veamos algunos ejemplos:

El borrado de registros tampoco tiene mucho secreto. Usamos para ello la sentencia DELETE FROM [nombre tabla] [condición].  ¡Ojo, que si no ponemos condición alguna borraremos TODOS los registros de la tabla!. Vamos a borrar uno de nuestros empleados en nuestra BD pruebas:

Y por último vamos a ver el uso de la sentencia DROP. Con ella podemos:

   -  Eliminar definitivamente una BD entera:
      DROP DATABASE [nombre BD]

   -  Eliminar definitivamente una tabla:
      DROP TABLE [nombre tabla]

  -   Eliminar definitivamente una columna de una tabla:
      ALTER TABLE [nombre tabla] DROP COLUMN [nombre columna]

D2B Express C: secuencias, importaciones y actualización de registros

En esta tercera entrega vamos a ver cómo establecer restricciones (CONSTRAINTs) en nuestra base de datos,  qué son y para qué sirven las secuencias, cómo importar información desde ficheros texto y cómo borrar y actualizar registros de las tablas.

Empecemos con CONSTRAINT. Algunos ejemplos son las claves primarias y externas, valores por defecto en columnas, chequeo de integridad, etc. Vamos a ver algunos usos en acción creando por ejemplo una nueva tabla, Empleados, y a la que vamos a establecer algunas reglas de diseño: el "número de empleado" (nuestra Primary key en este caso) será de tipo "Integer" PERO con formato 5 dígitos y con ceros a la izquierda. Además el campo "sexo" sólo podrá contener los valores "M" (masculino) ó "F" (femenino).

Respecto a las secuencias (SEQUENCE), éstas nos sirven para generar valores numéricos ÚNICOS independientes de las tablas. Un ejemplo sería este código para generar los números impares, donde indicamos también que se guarden hasta los 5 próximos valores en memoria CACHE (para mejor rendimiento) y que NO se generen números cuando se alcance el máximo o mínimo de la secuencia (NO CYCLE):

Ahora usemos dicha secuencia para insertar un registro en nuestra tabla Empleado, aprendiendo ya de paso cómo rellenar con '0's a la izquierda el número de empleado haciendo uso de la función LPAD, y de longitud '5'. Sí, tal y como puedes imaginar, existe la función RPAD que hace exactamente lo mismo pero rellenando la cadena a la derecha con el carácter pasado como parámetro. El "truco" está en usar la sintaxis "NEXT VALUE FOR [nombresecuencia]"

 Ahora insertemos un par de registros más en la tabla Empleado:

¡Detalle importante!: si por algún motivo salimos de la sesión o la cerramos tecleando TERMINATE, cuando volvamos y si intentáramos insertar un nuevo registro hay que tener en cuenta que la secuencia NO CONTINUARÁ EXACTAMENTE POR DONDE SE QUEDÓ EN LA TABLA, sino por donde se quedó en memoria caché. Es decir, como definimos la secuencia con 5 valores en caché, cuando insertemos un nuevo registro tomará el CONTADOR SIGUIENTE DE LA SECUENCIA al último previamente guardado en la caché, en este caso "11".

De todos modos siempre podremos alterar el índice de la secuencia si fuera necesario, usando ALTER SEQUENCE [nombresecuencia] RESTART WITH [siguientevalor]. Vamos a suponer que queremos insertar un registro que sea el empleado número exactamente "21":

Ahora vamos a crear un par de nuevas tablas, para almacenar, en una los empleos que han tenido los trabajadores y en la otra tabla el historial de dichos empleos, asociados a algún departamento. Empecemos por la primera (tabla "Job"), y de paso vamos a aprender cómo importar datos de un fichero directamente a nuestras tablas DB2. La sentencia para ello es simple: IMPORT FROM [fichero texto] OF DEL INSERT INTO [nombre tabla]

Creamos un simple fichero CSV con los registros que queremos insertar, y lo llamaremos "jobfile" por ejemplo. Cada línea tendrá los valores separados por coma. En mi caso he creado también una subcarpeta FICHEROS dentro de C:\DB2 y es ahí donde almacenaré este tipo de ficheros. El contenido de dicho fichero CSV podría ser similar a esto:

Vamos ahora con la última tabla, la del historial de empleos. Aquí vamos a introducir los nuevos conceptos de clave primaria asociada a varios campos a la vez y cómo se referencian (REFERENCES) las claves externas a otras tablas y sus campos correspondientes.

Y ahora probemos a forzar algunos de los CONSTRAINT de la tabla y veremos que sólo cuando introducimos datos "válidos" el registro podrá ser insertado. En el primer intento, no existe el JobCode "AAAA", aunque sí por ejemplo el empleado "00001" y el resto de campos son correctos, a pesar de ello da error por FOREIGN KEY. Finalmente insertamos por ejemplo a "John Smith" como VP, con un salario de 50000 a fecha 31-12-2000 aunque no está asociado a ningún departamento, y ya parece que la cosa va bien, como podemos ver más abajo: