Transact SQL 002 Funciones

En el post anterior se habló la extracción de información mediante la unión de tablas; usando join como conector para relacionar columnas comunes.

Extraer la información de las bases de datos a través de transact SQL no se limita a tomar la información de forma filtrada. Manipular la data al momento de la extracción es posible; un punto que le da gran utilidad.

Cuando se diseña un sistema, la información se extrae del servidor de bases de datos; optimizado generalmente para realizar gran cantidad de operaciones en el menor tiempo posible. Son equipos con mayor cantidad de poder de cómputo, por tanto al momento de realizar un sistema el aprovechar este recurso es necesario.

Funciones

El uso de funciones para manipular la información de las columnas al momento de las consultas es un tema extenso por la gran cantidad que existen. Esta es una muestra de la tabla de funciones se puede encontrar en el Manual Online de MySQL:

Name	Description
ABS()	Return the absolute value
ACOS()	Return the arc cosine
ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
AES_DECRYPT()	Decrypt using AES
AES_ENCRYPT()	Encrypt using AES
AND, &&	Logical AND
ANY_VALUE()	Suppress ONLY_FULL_GROUP_BY value rejection
ASCII()	Return numeric value of left-most character

Tabla completa

Funciones numéricas, texto y fecha son de uso común en todo lenguaje, manipular cadenas de caracteres, fechas entre otras, son un herramienta que permiten mejorar la calidad y forma de la información extraída en una consulta mediante Transact SQL.

Estructuras de la práctica

En el vídeo de práctica veremos sólo algunas pocas y trataremos de enfocarnos en como su correcto uso puede ayudar a traer la información más preparada, de forma de disminuir la cantidad de código que se ejecuta en el cliente o la capa superior del sistema.

Para este vídeo trabajaremos con las siguientes estructuras:

DROP TABLE IF EXISTS vrecibodetalle;
CREATE TABLE vrecibodetalle (
identificador int(10) unsigned NOT NULL auto_increment,
cedula varchar(13) NOT NULL default '',
monto decimal(13,4) NOT NULL default '0.0000',
comun char(8) NOT NULL default '',
tipoinmueble varchar(13) NOT NULL default '',
inmueble varchar(7) NOT NULL default '',
tipogasto char(4) NOT NULL default '',
descripcion varchar(60) NOT NULL default '',
refbase bigint(20) unsigned NOT NULL default '0',
montocalculado decimal(14,4) NOT NULL default '0.0000',
PRIMARY KEY (identificador),
KEY Index_2 (tipogasto)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La tabla vrecibodetalle, cuya función en el sistema es albergar de forma transitoria la data de los recibos de un mes, cuando se ejecuta un pre cierre del mes.

CREATE TABLE tblrecibobase (
identificador int(10) unsigned NOT NULL auto_increment,
cedula varchar(13) NOT NULL default '',
apto varchar(7) NOT NULL default '',
alicuotaactual decimal(12,4) NOT NULL default '0.0000',
montomes decimal(12,2) NOT NULL default '0.00',
mes int(10) unsigned NOT NULL default '0',
etapa varchar(15) NOT NULL default '',
torre varchar(5) NOT NULL default '',
ano int(10) unsigned NOT NULL default '0',
refbase int(10) unsigned NOT NULL default '0',
cancelado tinyint(1) NOT NULL default '0',
montosindeuda decimal(12,2) NOT NULL default '0.00',
nota varchar(40) NOT NULL default '',
formapago varchar(20) NOT NULL default '',
nodocumento varchar(25) NOT NULL default '',
fecha date NOT NULL default '0000-00-00',
PRIMARY KEY (identificador)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La tabla tblrecibobase, ya la utilizamos en los ejercicios del post anterior y aquí se sigue usando sin modificaciones, en esta ocasión nos servirá para probar funciones numéricas.

DROP TABLE IF EXISTS tblaptos;
CREATE TABLE tblaptos (
identificador int(10) unsigned NOT NULL auto_increment,
inmueble varchar(7) NOT NULL default '',
torre varchar(5) NOT NULL default '0',
etapa varchar(15) NOT NULL default '',
alicuota decimal(6,4) NOT NULL default '0.0000',
tipoapto varchar(15) NOT NULL default '',
PRIMARY KEY (identificador),
KEY FK_tblaptos_2 (torre)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La estructura tblaptos es un tabla sencilla que usaremos para ver algunos ejemplos básicos de manipulación de textos.

CREATE TABLE tbldetallecomprobante (
identificador bigint(20) UNSIGNED NOT NULL,
linea int(10) UNSIGNED NOT NULL DEFAULT '0',
cuenta varchar(10) NOT NULL DEFAULT '',
descripcion varchar(70) NOT NULL DEFAULT '',
comentario varchar(60) NOT NULL DEFAULT '',
debe decimal(12,2) NOT NULL DEFAULT '0.00',
haber decimal(12,2) NOT NULL DEFAULT '0.00',
refbase bigint(20) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE tbldetallecomprobante
ADD PRIMARY KEY (identificador);
ALTER TABLE tbldetallecomprobante ADD FULLTEXT KEY descripcion (descripcion);
ALTER TABLE tbldetallecomprobante ADD FULLTEXT KEY comentario (comentario);

ALTER TABLE tbldetallecomprobante
MODIFY identificador bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=255;

Las estructuras aquí colocadas pertenecen a un conjunto mayor, por tal motivo y para fines prácticos de los ejercicios, en este documento sólo están definidas las estructuras de forma individual sin presentar sus relaciones.

Para poder aprovechar más los recursos del manejador de base de datos existen los store procedure, estos se abordarán en el próxima práctica.

En el ejercicio anterior tbldetallecomprobante se usó con su estructura original, para este se realizó una modificación en dos de sus campos descripción y comentario, estos campos almacenan descripciones de facturas y justificaciones, sin embargo no se usan en búsquedas. En el ejercicio se requiere buscar información dentro de estos, por eso se realiza una modificación, creando índices de texto mediante ALTER TABLE tbldetallecomprobante ‘ADD FULLTEXT KEYdescripcion(descripcion`). La alteración es necesaria para poder realizar búsquedas dentro de campos varchar.

Estos índices son útiles si tenemos un sistema donde se almacenan documentos y deseamos realizar búsquedas dentro de estos. Sin embargo, no se recomienda abusar de su uso, estos ocupan gran espacio y su implementación de forma indiscriminada se puede volver un lastre para la memoria disponible.

Los indices son campos que se indexan para poder realizar búsquedas mas rápidas basados en estos, podemos entenderlos como pequeños listados generados en memoria con un campo de referencia, a la tabla principal; así cuando buscamos algo que esta indexado usamos la tabla pequeña, en lugar de la estructura principal de mayor tamaño, reduciendo el tiempo de búsqueda. Por ejemplo cuando definimos nuestra tabla y creamos una llave primaria con un campo incremental que no se repite, generamos sobre este un indice.

Cuando se definen las funciones de un sistema, una parte importante es estudiar cuales serán los campos que usaremos de forma mas frecuente para realizar una búsqueda, documentos de identidad, números de recibo y facturas, son campos que se usan con frecuencia. La descripción de un producto en una tienda virtual puede ser usado para buscar productos con similitudes, pudiendo ser útil un indice.

Cada indice creado genera un espacio de memoria que se reescribe junto con la tabla, crear demasiados indices que no se utilizan conlleva a ocupar una gran cantidad de memoria y tener que mantener una cantidad de pequeñas tablas que no se utilizan, perdiendo su utilidad.

Material previo de Transact SQL

Post	Contenido	Ir
Instalacion Servidor LAMP en Xubuntu 18.04	Vídeo explicativo de la instalación de servidor LAMP de laboratorio base	Post 01
Transact SQL, Unión de tablas (Join)	Práctica del uso de las formas de Join para consultas de tablas enlazadas	Post 02

Referencia

-Manual de Referencia en línea MySQL 8.0 link

Imágenes

La imagen de la miniatura fue compuesta con Gimp 2.10 utilizando la siguiente Fuente, de PixBay