En la
primera parte definimos y asumo que creamos la base de datos y las primeras tablas para el sistema de registro/login de la pagina que estamos haciendo.
Creamos 4 tablas: paises, usuarios, estados_de_usuario, tipos_de_usuario para los cuales esta vez crearemos las funciones y/o procedimientos almacenados necesarios para realizar las tareas básicas de un administrador o maestro, es decir, INSERT, SELECT, DELETE y UPDATE.
Para esto tomaremos como base la tabla paises y crearemos 4 procedimientos almacenados llamados:
- ipaises
- upaises
- spaises
- sallpaises
- dpaises
Así es que empecemos, si alguien no sabe que es un procedimiento almacenado le recomiendo leer esta
definición de wikipedia.
Para la creación (CREATE) y modificación (ALTER) de los procedimientos almacenados utilizaremos
heidiSQL ya que su interfaz nos facilitara esta tarea (y se los explique en un
post anterior, así que háganme caso jjajaja), así que abrimos heidiSQL y seleccionamos la base de datos que creamos (db_shopping), hacemos clic derecho sobre el nombre de la base de datos y elegimos “create new->Stored Routine”
Nos quedará una pantalla como la siguiente:
En este punto definiremos el procedimiento almacenado, sus parámetros y el código SQL que ejecutará, para esto habrá que llenar los campos que tenemos disponibles:
name: ipaises
definer: dejar en blanco y el definer será el usuario que creo el procedimiento, para asignarle uno diferente deberás tener el privilegio SUPER en tu usuario (normalmente los web hosting compartidos no te dan este permiso el único que conozco yo es hostgator.com).
Type: Procedure (doesn’t return a result)
nota: en algunos casos yo prefiero utilizar una función (function) cuando lo único que devolveré es si se pudo o no ejecutar el código, pero realmente las funciones fueron pensadas en MySQL para que se ejecuten dentro del mismo MySQL, así que para respetar eso, usemos procedimientos almacenados con los cuales podemos a través de los parámetros devolver resultados tambien.
Data access: elegimos “Modifies SQL Data” ya que este procedimiento almacenado servirá para ingresar datos a la tabla.
Definicion: Varias características proporcionan información sobre la naturaleza de los datos usados por la rutina.
CONTAINS SQL indica que la rutina no contiene comandos que leen o escriben datos.
NO SQL indica que la rutina no contiene comandos SQL .
READS SQL DATA indica que la rutina contiene comandos que leen datos, pero no comandos que escriben datos.
MODIFIES SQL DATA indica que la rutina contiene comandos que pueden escribir datos.
CONTAINS SQL es el valor por defecto si no se dan explícitamente ninguna de estas características.
SQL Security: elegimos“Invoker” porque nos interesa que el usuario que ejecute el procedimiento almacenado tenga los permisos necesarios.
Definicion: La característica
SQL SECURITY puede usarse para especificar si la rutina debe ser ejecutada usando los permisos del usuario que crea la rutina o el usuario que la invoca. El valor por defecto es
DEFINER. Esta característica es nueva en SQL:2003. El creador o el invocador deben tener permisos para acceder a la base de datos con la que la rutina está asociada. Desde MySQL 5.0.3, es necesario tener el permiso
EXECUTE para ser capaz de ejecutar la rutina. El usuario que debe tener este permiso es el definidor o el invocador, en función de cómo la característica
SQL SECURITY .
Deterministic: lo marcamos.
Definicion: Un procedimiento o función se considera “determinista” si siempre produce el mismo resultado para los mismos parámetros de entrada, y “no determinista” en cualquier otro caso. Si no se da ni
DETERMINISTIC ni
NOT DETERMINISTIC por defecto es
NOT DETERMINISTIC.
Ahora nos vamos a la pestaña “Parameters” y agregamos los parametros necesarios presionando el botón “Add”
Para nuestro caso los parámetros de entrada serán los siguientes: (antepone la letra m a los campos de la tabla países, para mantener un estándar)
Nombre Parámetro | Tipo de Dato | Tamaño/Valor | Contexto |
| mpais_nombre | Varchar | 50 | IN |
| mpais_codigo | char | 2 | IN |
| mpais_simbolo_moneda | char | 10 | IN |
| mpais_moneda_nombre | char | 20 | IN |
| mpais_moneda_nombre_plural | char | 20 | IN |
| mpais_moneda_iso | char | 3 | IN |
| mpais_estado | ENUM | 'Testing','Produccion' | IN |
| mpais_activo | ENUM | 'Activo','Inactivo' | IN |
| mpais_url_testing | varchar | 100 | IN |
| mpais_url_produccion | varchar | 100 | IN |
| mpais_timezone | char | 50 | IN |
| mpais_email_contacto | varchar | 50 | IN |
| last_insert | int | | OUT |
A continuación el código SQL que colocaremos entre las etiquetas BEGIN y END:
INSERT INTO paises(
pais_nombre,
pais_codigo,
pais_simbolo_moneda,
pais_moneda_nombre,
pais_moneda_nombre_plural,
pais_moneda_iso,
pais_estado,
pais_activo,
pais_url_testing,
pais_url_produccion,
pais_timezone,
pais_email_contacto
)
VALUES(
mpais_nombre,
mpais_codigo,
mpais_simbolo_moneda,
mpais_moneda_nombre,
mpais_moneda_nombre_plural,
mpais_moneda_iso,
mpais_estado,
mpais_activo,
mpais_url_testing,
mpais_url_produccion,
mpais_timezone,
mpais_email_contacto
);
SELECT LAST_INSERT_ID() INTO last_insert;
END IF;
Este código lo podríamos leer así: Insertar datos a la tabla paises, y si no hay error guarde en la variable last_insert el insert_id de la tabla.
¿Como lo probamos???
Pues utilizando siempre heidiSQL presiona el botón que esta en la esquina inferior derecha de tu monitor que dice “Run routine(s)” con esto nos solicitara los valores de cada uno de los parámetros de nuestro procedimiento almacenado, pero deben tener en cuenta que los parámetros de salida nos tocara modificarlos a pie luego del código que nos genere heidiSQL, ahora presionen y llenen los parámetros según los vaya solicitando y deberían quedar con una pantalla con error como la siguiente:
Esto se debe a la variable OUT que tenemos al final, la cual no se reconoce bien por heidiSQL, así que solo presionen el botón OK pero les quedará el Query en la pantalla el cual podremos modificar para que lo podamos ejecutar efectivamente, el código que debió quedarles es el siguiente:
CALL `ipaises`('El Salvador', 'sv', '$', 'Dolar', 'Dolares', 'USD', 'Testing', 'Activo', 'http://127.0.0.1/shopping/', '', 'America/El_Salvador', 'hstanley@shopping.com', '')
El ultimo par de comillas simples corresponden a nuestra variable OUT (last_insert) y lo reemplazaremos por el nombre de una variable en mySQL por ejempo @last_insert y para observar que nos devolvera, agregaremos ademas lo siguiente: SELECT @last_insert; quedando todo junto de la siguiente manera:
CALL `ipaises`('El Salvador', 'sv', '$', 'Dolar', 'Dolares', 'USD', 'Testing', 'Activo', 'http://127.0.0.1/shopping/', '', 'America/El_Salvador', 'hstanley@shopping.com', @last_insert);
SELECT @last_insert;
Al ejecutar este código, nos insertara si no existe el código de país y nos devolverá el valor del pais_id en la variable @last_insert y deberá mostrar algo como lo siguiente:
Si ejecutamos SELECT * FROM países veremos que se agrego la fila con los datos especificados.
Si volvemos a ejecutar el procedimiento con los mismo datos, nos devolverá un error que no esta siendo controlado por nosotros, para lograr esto debemos agregar algunas líneas al inicio de nuestro script justo después de la palabra BEGIN:
DECLARE insert_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET insert_error=1;
END;
Y al final del INSERT agregamos:
IF insert_error THEN
SELECT -1 INTO last_insert;
ELSE
SELECT LAST_INSERT_ID() INTO last_insert;
END IF;
Con este código estamos manejando los errores en nuestro script, y asi controlar toda la situación (ERROR HANDLER), el codigo completo seria:
BEGIN
DECLARE insert_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET insert_error=1;
END;
INSERT INTO paises(
pais_nombre,
pais_codigo,
pais_simbolo_moneda,
pais_moneda_nombre,
pais_moneda_nombre_plural,
pais_moneda_iso,
pais_estado,
pais_activo,
pais_url_testing,
pais_url_produccion,
pais_timezone,
pais_email_contacto
)
VALUES(
mpais_nombre,
mpais_codigo,
mpais_simbolo_moneda,
mpais_moneda_nombre,
mpais_moneda_nombre_plural,
mpais_moneda_iso,
mpais_estado,
mpais_activo,
mpais_url_testing,
mpais_url_produccion,
mpais_timezone,
mpais_email_contacto
);
IF insert_error THEN
SELECT -1 INTO last_insert;
ELSE
SELECT LAST_INSERT_ID() INTO last_insert;
END IF;
END
Guardamos, y volvemos a ejecutar el query que ya teníamos, y esta vez @last_insert nos devolverá –1
Sencillo verdad? bueno todo es conceptual y ordenarnos un poco y como siempre digo, mantener un estándar.
Luego de INSERT ahora haremos SELECT, es decir spaises y sallpaises.
Para spaises (seleccionar un pais especifico) para crearlo realizaremos el mismo proceso anterior definiéndolo así:
name: spaises
definer: dejar en blanco y el definer será el usuario que creo el procedimiento.
Type: Procedure (doesn’t return a result)
Data access: elegimos “Reads SQL data” ya que este procedimiento almacenado servirá para leer datos.
SQL Security: elegimos“Invoker” .
Deterministic: lo marcamos.
Ahora nos vamos a la pestaña “Parameters” y agregamos los parámetros necesarios presionando el botón “Add” de la misma manera en que lo hicimos con el INSERT con la única diferencia, que esta vez los parámetros serán de salida, así:
Nombre |
Tipo de Dato |
Contexto |
| mpais_id | INT | IN |
| mpais_nombre | varchar(50) | OUT |
| mpais_codigo | char(2) | OUT |
| mpais_simbolo_moneda | char(10) | OUT |
| mpais_moneda_nombre | char(20) | OUT |
| mpais_moneda_nombre_plural | char(20) | OUT |
| mpais_moneda_iso | char(3) | OUT |
| mpais_estado | ENUM('Testing','Produccion') | OUT |
| mpais_activo | ENUM('Activo','Inactivo') | OUT |
| mpais_url_testing | varchar(100) | OUT |
| mpais_url_produccion | varchar(100) | OUT |
| mpais_timezone | char(50) | OUT |
| mpais_email_contacto | varchar(50) | OUT |
A continuación el código SQL que colocaremos entre las etiquetas BEGIN y END:
SELECT
pais_nombre,
pais_codigo,
pais_simbolo_moneda,
pais_moneda_nombre,
pais_moneda_nombre_plural,
pais_moneda_iso,
pais_estado,
pais_activo,
pais_url_testing,
pais_url_produccion,
pais_timezone,
pais_email_contacto
FROM paises
WHERE pais_id=mpais_id
INTO
mpais_nombre,
mpais_codigo,
mpais_simbolo_moneda,
mpais_moneda_nombre,
mpais_moneda_nombre_plural,
mpais_moneda_iso,
mpais_estado,
mpais_activo,
mpais_url_testing,
mpais_url_produccion,
mpais_timezone,
mpais_email_contacto;
Para probarlo, debemos hacerlo de la misma forma que el INSERT pero debemos sustituir todos los parámetros de salida por variables, quedando así:
CALL spaises('1', @a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l);
SELECT @a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l;
Ejercicio: Ahora les dejo hacer como ejercicio el procedimiento llamado scodigo_paises, con el cual a través del campo pais_codigo podamos extraer toda la data de un país en especifico, el cual con el siguiente código nos debería devolver los datos del país con código ‘sv’:
CALL scodigo_paises('sv', @a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l);
SELECT @a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l;
Bueno, hasta aquí este día, en la próxima entrega haremos sall_paises, delete y update.
Saludos
