DB2: Las funciones, ventajas y caso práctico

Siguiendo con la introducción de herramientas, ahora le toca el turno a las funciones de la base de datos. Estas sirven principalmente para que nos devuelvan un dato cuando las “llamemos”. Nos facilita la vida de forma que reutilizamos código y al igual que las vistas, si hay que hacer algun ajuste, con modificarlo en un sitio lo tendremos aplicado a todo nuestro código.

En esta entrada os voy a enseñar 2 casos concretos de vistas aplicadas a Geinfor que creo que facilitan mucho la vida (por lo menos para mí es así)

NINGUNA DE LAS ACCIONES QUE VOY A EXPLICAR TIENE SOPORTE POR PARTE DE GEINFOR. MUCHO OJO Y BAJO VUESTRA RESPONSABILIDAD

Tras la frase de alerta a navegantes de rigor, vamos al lío.

Recomendaciones/normas de los Gremlins para las funciones

  • Comienza todas las vistas por fx_
  • 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 funcion. ¡Comenta tu código! no es un telegrama y no se cobra por palabras

Tipo función para subconsultas

Cuando estamos cruzando información de varias tablas para crear vistas externas de análisis e informes, sucede siempre que en las tablas de datos tenemos los códigos numéricos pero no los textos que nos gusta leer a los humanos (raritos que somos)

¿Qué pasa en estos casos? Que estamos siempre llamando a un montón de tablas para obtener las descripciones y repitiendo una y otra vez las subselect o la tabla en el where con su join y demás…

Se pueden crear funciones para solo hacer una llamada rápida para que nos devuelva la descripción. Si cogemos de ejemplo esa vista de facturación del post anterior (aquí), vamos a crear una vista para todas las dimensiones de tipo, estado, clase, familia y grupo de articulo.

La estructura de la función es la siguiente

CREATE OR REPLACE FUNTION "NOMBRE_DE_FUNCION" (Parametro1 tipoParametro, Parametron tipoparametro) Returns TipoValorDevolucion

Este “churro” que visto así es muy raro vemos un ejemplo

CREATE OR REPLACE FUNCTION FX_ART_DIMENSIONES(ParamArt VARCHAR(60), ParamDimension VARCHAR(60)) RETURNS VARCHAR(200)
BEGIN 
    /*DEVUELVE EL TEXTO ASOCIADO A LA DIMENSIÓN DEL ARTICULO
    Hay que pasar como parametros el código del artículo y el nombre del campo
    
    Adicionalmente tenemos clasificaciones especiales en funcion de valores de "X" campos
    como por ejemplo CLASIFICACIONTEST*/
	DECLARE ValorInt	 	VARCHAR(60);
	DECLARE ValorResultado 	VARCHAR(200);
		
	-- Devuelve el txt del campo codigocontable
	IF ParamDimension = 'CODIGOCONTABLE'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.CODIGO_CONTABLE FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT PLAN_CONTABLE.DENOMINACION FROM PLAN_CONTABLE WHERE PLAN_CONTABLE.CODIGO_CONTABLE=ValorInt AND PLAN_CONTABLE.PLANCONTABLE=0);
	END IF;
	-- Devuelve el txt del campo Clase
	IF ParamDimension = 'CLASE'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.CLASE FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT TABLA_CLASE_ARTICULO.DENOMINACION FROM TABLA_CLASE_ARTICULO WHERE TABLA_CLASE_ARTICULO.CODIGO=ValorInt);
	END IF;
	
	-- Devuelve el txt del campo Grupo
	IF ParamDimension = 'GRUPO'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.GRUPO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT TABLA_CARACTERISTICA.DENOMINACION FROM TABLA_CARACTERISTICA WHERE TABLA_CARACTERISTICA.CODIGO=ValorInt);
	END IF;
	
	-- Devuelve el txt del campo tipo
	IF ParamDimension = 'TIPO'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.TIPO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT TIPOS_ARTICULO.DESCRIPCION FROM TIPOS_ARTICULO WHERE TIPOS_ARTICULO.CODIGO=ValorInt);
	END IF;
	
	-- Devuelve el txt del campo familia
	IF ParamDimension = 'FAMILIAPRODUCTO'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.FAMILIAPRODUCTO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT TFAMILIA_PRODUCTOS.DESCRIPCION FROM TFAMILIA_PRODUCTOS WHERE TFAMILIA_PRODUCTOS.CODIGO=ValorInt);
	END IF;
	
	-- Devuelve el txt del campo Estado
	IF ParamDimension = 'ESTADO'
	 THEN
	   SET VALORINT = (SELECT MAESTRO_DE_ARTICULOS.ESTADO FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	   SET ValorResultado = (SELECT T_ESTADOARTICULO.DESCRIPCION FROM T_ESTADOARTICULO WHERE T_ESTADOARTICULO.CODIGO=ValorInt);
	END IF;
	-- Clasificación de agrupación propio
	IF ParamDimension = 'CLASIFICACIONTEST'
	 THEN
	   SET ValorResultado = (SELECT
							 CASE MAESTRO_DE_ARTICULOS.CODIGO_CONTABLE
							 WHEN '701020000' THEN 'FABRICACIÓN'
							 WHEN '701030000' THEN 'MIXTO'
							 WHEN '700010000' THEN 'COMERCIAL'
							 when '702000000' THEN 'SEMIELABORADO'
							 WHEN '438000000' THEN 'A CUENTA'
							 ELSE 'OTROS' END
	                         FROM MAESTRO_DE_ARTICULOS WHERE MAESTRO_DE_ARTICULOS.CODIGO_ARTICULO=ParamArt);
	END IF;
	
	
	IF ValorResultado IS NULL THEN SET ValorResultado = 'NO DEFINIDO'; END IF;
	
	RETURN (ValorResultado); 
END @

Con esta función le estamos diciendo que al llamarla, le pasaremos como parametro el código del articulo y con comilla simple el nombre del campo y nos devolverá la denominación de ese campo.

Un ejemplo sencillo de utilización sería este:

SELECT
CODIGO_ARTICULO,
DENOMINACION,
clase,
FX_ART_DIMENSIONES(CODIGO_ARTICULO,'CLASE') AS CLASE_TXT
FROM MAESTRO_DE_ARTICULOS

Si esto lo aplicamos a nuestra vista de facturación, nos devolverá los textos que son mucho más legibles

Tipo de función para cálculos

La otra aplicación más utilizada es realizar cálculos y esta podría ser la cantidad Asignada, Pedida y Disponible de un articulo. Vamos a ver el ejemplo de las PEDIDAS

El código sería el siguiente

CREATE OR REPLACE FUNCTION FX_ALM_PEDIDAS(CodArticulo VARCHAR(60), CodAlmacen INTEGER)
RETURNS DOUBLE

/*La función nos devolverá la cantidad asignada de un articulo para un almacen concreto o para todos los almacenes si pasamos como parametro almacén -1*/


BEGIN 
DECLARE Pedidas DOUBLE;
DECLARE PedidasPED DOUBLE;
DECLARE PedidasORD DOUBLE;
DECLARE AlmacenMin INTEGER;
DECLARE AlmacenMax INTEGER;

/*Seleccionar si la operativa es para un almacen o para todos los almacenes según parametro*/
IF CodAlmacen=-1 THEN SET AlmacenMin=0;
SET almacenmax=9999;
ELSE SET AlmacenMin=CodAlmacen;
SET almacenmax=CodAlmacen;
END IF;

SET PedidasPED = (Select Sum( (lin.cantidad_pedida - lin.cantidad_devuelta - lin.cantidad_entregada) * art.coeficiente ) as Pedidas
From Lineas_pedido_provee lin, Cab_pedidos_proveed cab, Maestro_de_articulos art, TABLA_ALMACENES TALM 
Where cab.SERIE=lin.SERIE AND
cab.PEDIDO=lin.NUMERO_DE_PEDIDO AND
lin.CODIGO_ARTICULO=art.CODIGO_ARTICULO AND
cab.ALMACEN=talm.CODIGO AND

lin.SITUACION NOT IN (20,30) AND -- indica que la línea esta cerrada o cancelada

-- para asegurarnos que si hay un error de proceso en los pedidos no nos desvirtue la información
lin.CANTIDAD_PEDIDA-lin.CANTIDAD_ENTREGADA-lin.CANTIDAD_DEVUELTA>0 AND 
cab.ALMACEN BETWEEN Almacenmin AND Almacenmax and --todos los pedidos del rango del almacen
TALM.NOCALCENDISPONIBLE = 0);

IF PedidasPED IS NULL THEN SET PedidasPED = 0; END IF;

SET PedidasORD = (Select Sum(O.cantidad_pedida+O.cant_en_desviacion-O.cantidad_realizada) as Pedidas
From Ordenes_de_trabajo O, TABLA_ALMACENES TALM 
Where O.codigo_articulo = CodArticulo and O.estado <> 40
and (O.cantidad_pedida+O.cant_en_desviacion-O.cantidad_realizada) > 0
and O.Almacen BETWEEN Almacenmin AND Almacenmax and O.Almacen = TALM.Codigo And TALM.NOCALCENDISPONIBLE = 0);--
IF PedidasORD IS NULL THEN SET PedidasORD = 0; END IF;--

SET Pedidas = PedidasPED + PedidasORD;--

RETURN (Pedidas); --
END @

De esta forma solo con una línea de llamada a la función tendremos la cantidad pedida de un articulo rápidamente, y si por algún motivo (cambio de criterio de la empresa o cambio de criterio en GEINFOR ERP) solo con ajustar la función tendríamos todo el código actualizado

SELECT
CODIGO_ARTICULO,
DENOMINACION,
clase,
FX_ART_DIMENSIONES(CODIGO_ARTICULO,'CLASE') AS CLASE_TXT,
FX_ALM_PEDIDAS(CODIGO_ARTICULO,0) AS Pedidas_Alm_0,
FX_ALM_PEDIDAS(CODIGO_ARTICULO,-1) AS Pedidas_Todos_alm
FROM MAESTRO_DE_ARTICULOS

Cierre de código de funcion

Por si os ha llamado la atención, después del END hay un espacio y una @. Esto es intencionado. Podríamos poner un ; y funcionaría igual, pero más adelante veremos como preparar varias funciones y poder desplegarlas y actualizarlas de forma masiva y para ese apartado es importante que el acabar con @

Conclusión

Si juntamos esta entrada con la anterior de crear vistas podemos hacer unas vistas muy potentes, fáciles de mantener y reutilizar. 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 😉

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.