Esta entrada hará que las cosas se pongan «serias» y sobre todo peligrosas, ya que hasta este momento solo leíamos información con lo que el peligro era casi nulo, pero en este caso empezamos a cambiar información en la base de datos ya que las procedures son (en una traducción literal) procedimientos almacenados en la base de datos para poder ser llamados y que realicen cambios sobre la misma.
Aquí es donde empezamos a preparar a nuestros «bichitos» para que nos hagan el trabajo sucio…
NINGUNA DE LAS ACCIONES QUE VOY A EXPLICAR TIENE SOPORTE POR PARTE DE GEINFOR. MUCHO OJO Y BAJO VUESTRA RESPONSABILIDAD
Más que nunca hay que hacer caso a la advertencia y os recomiendo que tengáis un servidor de pruebas donde probemos todo el código antes de ponerlo en producción
Recomendaciones/normas de los Gremlins para las procedures
- Comienza todas las vistas por SP_
- La siguiente parte del nombre utilizarla para que todas las relacionadas estén juntas
- La última parte del nombre debe ser descriptiva y intenta no utilizar abreviaturas que solo tú entiendas
- Añade un comentario en la creación de la procedure. ¡Comenta tu código! no es un telegrama y no se cobra por palabras
Creando la primera procedure
La estructura de una procedure, es muy similar a la de las funciones, solo que estas pueden actualizar la información de las tablas y las funciones no pueden.
La estructura de una procedure es básicamente la siguiente
create or replace procedure "nombre de procedure" ("parametros") LANGUAGE SQL BEGIN ATOMIC --CODIGO A EJECUTAR END @
Como nota personal, ojo con especificar el BEGIN o BEGIN ATOMIC ya que de esta manera solo realizará los cambios si todo el contenido del código ha sigo OK.
Esto quiere decir que si utilizamos BEGIN ATOMIC y dentro de nuestra procedure tenemos 2 UPDATE y uno de ellos no realiza ningun cambio, no se ejecutarán ninguno de los dos. Tendremos que determinar en que casos nos interesa una cosa u otra según el tipo de procedure
Nuestra primera procedure la vamos a utilizar para actualizar la información de los proveedores a homologados o no homologados. El código sería el siguiente
CREATE or replace PROCEDURE SP_SIS_AJUSTESBBDD() LANGUAGE SQL BEGIN -- ajustar proveedores homologados, puntuales y baja por volumen de compras UPDATE MAESTRO_PROVEEDORES p SET p.HOMOLOGADO=0, p.COMENTARIO=' ', p.ESTADO=40, P.FECHACAMBIOESTADO=CURRENT_DATE WHERE (SELECT count(*) FROM FACT_DE_PROVEEDOR f WHERE f.FECHA BETWEEN current_date - 3 year AND current_date AND f.PROVEEDOR=p.CODIGO_PROVEEDOR) < 1 AND p.FECHA_ALTA<current_date - 12 month; UPDATE MAESTRO_PROVEEDORES p SET p.HOMOLOGADO=0, p.COMENTARIO='PROVEEDOR PUNTUAL' WHERE (SELECT count(*) FROM FACT_DE_PROVEEDOR f WHERE f.FECHA BETWEEN current_date - 3 year AND current_date AND f.PROVEEDOR=p.CODIGO_PROVEEDOR) BETWEEN 1 AND 9 ; UPDATE MAESTRO_PROVEEDORES p SET p.HOMOLOGADO=1, p.COMENTARIO='HOMOLOGADO' WHERE (SELECT count(*) FROM FACT_DE_PROVEEDOR f WHERE f.FECHA BETWEEN current_date - 1 year AND current_date AND f.PROVEEDOR=p.CODIGO_PROVEEDOR)>3 ; END @
El código nos recorre todos los proveedores y en función de la facturación actualiza la información del proveedor. Siempre vamos del caso más desfavorable al menos desfavorable:
- Si el proveedor se ha dado de alta hace más de 12 meses y no tiene compras en los últimos 3 años, lo marcamos como baja, como no homologado y actualizamos la fecha de cambio de estado
- Si el proveedor se le han realizado compras en los últimos 3 años pero son menos de 3 al año de promedio, lo marcamos como puntula
- Si el proveedor se le han realizado más de 3 compras/año en los últimos 3 años lo marcamos como homologado
Este sería el criterio de esta Procedure, pero se debe adaptar a los procedimientos de vuestra empresa.
Llamando a las PROCEDURES
Ahora tenemos nuestro código que va a hacer el trabajo sucio de reclasificar proveedore (y más adelante todo lo que se nos ocurra) pero ¿Cómo lo activamos?
Manualmente sería ir al SQLDBX y lanzar la instrucción
CALL SP_SIS_AJUSTESBBDD
En próximas entradas automatizaremos este trabajo de dos formas: bien desde el programador de tareas de windows y ficheros .bat o bien a través de la herramienta Sql Server Integration Services.
Espero que os sea útil y no dudéis en comentar o mandar un mensaje con cualquier consulta, aportación o inquietud que tengáis…
… y si algo sale mal… La Culpa de Sistemas 😉
Un comentario en “DB2: Los Procedures, ventajas y casos prácticos”