Foros del web, para los que viven de bits

digdig · 18-mar-2007, 18:12

Hola, después de investigar un poco he descubierto como utilizar PROCEDURE de MySqL para generar un codigo de reserva que tenga 6 caracteres con valores entre A-Z y 0-9 y que tome valores no consecutivos, es decir, aleatorios.
Ejemplos de localizadores de reserva:
R5FCX1
T4WDPD
PNTZ4Z

En primer lugar me creo una tabla con dos columnas (posicion, caracter). Tenemos tantos registros como caracteres A-Z y 0-9, vamos 36 caracteres.
+----------+----------+
| caracter | posicion |
+----------+----------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| A | 10 |
| B | 11 |
| C | 12 |
| D | 13 |
| E | 14 |
| F | 15 |
| G | 16 |
| H | 17 |
| I | 18 |
| J | 19 |
| K | 20 |
| L | 21 |
| M | 22 |
| N | 23 |
| O | 24 |
| P | 25 |
| Q | 26 |
| R | 27 |
| S | 28 |
| T | 29 |
| U | 30 |
| V | 31 |
| W | 32 |
| X | 33 |
| Y | 34 |
| Z | 35 |
+----------+----------+
36 rows in set (0.00 sec)

El código para generar el procedimiento es el siguiente:

Código PHP:


			
DELIMITER $$



DROP PROCEDURE IF EXISTS `test`.`generaLocalizador`$$

CREATE PROCEDURE `test`.`generaLocalizador` (

OUT localizador VARCHAR(6)

)

BEGIN



DECLARE  A1 VARCHAR(1);

DECLARE  A2 VARCHAR(1);

DECLARE  A3 VARCHAR(1);

DECLARE  A4 VARCHAR(1);

DECLARE  A5 VARCHAR(1);

DECLARE  A6 VARCHAR(1);



DECLARE  C1 VARCHAR(1);

DECLARE  C2 VARCHAR(1);

DECLARE  C3 VARCHAR(1);

DECLARE  C4 VARCHAR(1);

DECLARE  C5 VARCHAR(1);

DECLARE  C6 VARCHAR(1);



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A1;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A1

INTO C1;



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A2;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A2

INTO C2;



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A3;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A3

INTO C3;



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A4;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A4

INTO C4;



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A5;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A5

INTO C5;



SELECT MOD(FLOOR(RAND()*1000), 35)

INTO A6;

SELECT caracter  as CaracterAleatorio

FROM caracteres

WHERE posicion= A6

INTO C6;



SELECT CONCAT(C1, C2, C3, C4, C5, C6) 

INTO localizador;



END$$



DELIMITER ;

ANALISIS
MOD(FLOOR(RAND()*1000), 35)
Genera valor aleatorio, multiplica por 1000 y saca el módulo 35. El modulo 35 da valores entre 0 y 35 (Es el resto de dividir un numero entre 35).
Ese valor lo usamos para acceder a la fila de la tabla caracteres y recuperar el valor.

Para ejecutar el procedimiento usamos estas sentencias:

Código PHP:


			
CALL generaLocalizador(@localizador);



select @localizador;

Y nos mostraría la siguiente salida:

+----------------+
| @localizador |
+----------------+
| Q3706Q |
+----------------+

ó

+----------------+
| @localizador |
+----------------+
| ZR2ADS |
+----------------+

etc

Me falta crear el DISPARADOR que llame a mi procedimiento despues de insetar un nuevo registro en la tabla 'reserva' y meta el localizador devuelto por el procedimiento en la clave principal del nuevo registro.
Además tengo que ver como evito que se intente dar un valor de localizador ya existente o como lo detecto para probar con otro valor.

Es la primera vez que trabajo con procedimientos asi que las sugerencias para mejorar mi código o como codificar el TRIGGER son bienvenidas.

Jorge F.M.

abulon · 18-mar-2007, 19:07

En tu trigger pones solo el nombre del procedure , y debes guardar la variable ,pero debes declararla dentro del trigger

CREATE TRIGGER OK
AFTER INSERT ON RESERVA
FOR EACH ROW

BEGIN

DECLARE variable tipo_de_dato;

'aki llmas tu procedure y lo ke te devuelva ke lo guarde en la variable

END;

Y en cuanto a ke se repite deberia de ser unique el campo o primary key..

Saludos

digdig · 18-mar-2007, 19:57

Gracia Abulon por responder, sólo me falta que al repetirse un localizador pruebe automaticamente con otro localizador aleatorio hasta que no coincida. No deberia dejarse de introducir una reserva sólo porque el localizador estaba repetido.

Como el localizador es de 6 caracteres hay millones de valores posibles por lo que ya sería mucha casualiadad que coincida más de 3 veces con otro localizador. Creo que la sentencia insert tiene una opción para decidir que hacer si la clave ya existe, algo asi como:

Código PHP:


			
INSERT INTO [...]

SI SE REPITE clave= claverepetida+1        //Prueba con el valor siguiente.

¿Alguien sabe la sintaxis exacta?

Otra dudilla, ¿procedure soporta arrays como variable? lo digo porque prefiero almacenar los caracteres posibles en un array en vez de, tal como está ahora, en una tabla.
¿Como me crearía una variable array y como accedería a los valores?

Pseudocodigo::

Código PHP:


			
caracteres varchar [36] = {'A','B','C','D', ... , '8', '9', '0' }; 

C1 = caracteres [A1];

[...]

SELECT CONCAT(C1, C2, C3, C4, C5, C6) INTO localizador

Gracias y un saludo a todos, me está gustando esto del foro

Jorge F.M. ( Calico )

abulon · 18-mar-2007, 20:01

Los procedures no manejan array, pero manejan excepcions o handling excepcions, si arroja el error tal de duplicate key entoncs generar clave aleatoria de nuevo...un saludo

digdig · 18-mar-2007, 21:30

Bueno para que no quede la cosa sin terminar os cuelgo el codigo, ya probado, para generar el TRIGGER:

Código PHP:


			
DELIMITER $$



CREATE TRIGGER nuevareserva

BEFORE INSERT ON reserva

FOR EACH ROW



BEGIN



DECLARE locAux VARCHAR(6);



CALL generaLocalizador(@loc);

SELECT @loc INTO locAux;

SET NEW.localizador= locAux;



END$$



DELIMITER ;

Si necesitamos borrar el trigger usariamos la sentencia:

Código PHP:


			
DROP trigger nuevareserva;

Finalmente para que veais que no miento cuando digo que funciona os pongo las salidas al insertar dos nuevas reservas (comprador y crucero son los identificadores de los respectivos registros en otras tablas):

Código PHP:


			
mysql> insert into reserva (comprador, crucero) values (3432, 123);

Query OK, 1 row affected, 1 warning (0.01 sec)



mysql> select * from reserva;

+-------------+-----------+---------+

| localizador | comprador | crucero |

+-------------+-----------+---------+

| HNW32L      |      3432 |     123 | 

+-------------+-----------+---------+

1 row in set (0.00 sec)



mysql> insert into reserva (comprador, crucero) values (3563, 165);

Query OK, 1 row affected, 1 warning (0.00 sec)



mysql> select * from reserva;

+-------------+-----------+---------+

| localizador | comprador | crucero |

+-------------+-----------+---------+

| HNW32L      |      3432 |     123 | 

| SP4A8S      |      3563 |     165 | 

+-------------+-----------+---------+

2 rows in set (0.00 sec)

RESUMEN:
Necesitabamos que mysql generara automáticamente localizadores aleatorios formados por caracteres y dígitos. La tendencia de los programadores es delegar esta función en la aplicación cliente, sin embargo, el correcto uso de procedure y trigger permite al usuario de la base datos abstraerse de esta tarea con las ventajas que eso supone.
La solución que hemos optado es crear un procedimiento que concatena caracteres recuperados aleatoriamente de una tabla auxiliar rellena de los 36 caracteres del localizador. Con el disparador before insert lo que hacemos es llamar al procedimiento para obtener un localizador válido y usarlo para almacenarlo en el campo localizador de cada fila insertada.

Un saludo a todos

Jorge F.M. (Calico UAX)

18-mar-2007, 18:12	#1 (permalink)
digdig Fecha de Ingreso: marzo-2007 Mensajes: 13	Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER Hola, después de investigar un poco he descubierto como utilizar PROCEDURE de MySqL para generar un codigo de reserva que tenga 6 caracteres con valores entre A-Z y 0-9 y que tome valores no consecutivos, es decir, aleatorios. Ejemplos de localizadores de reserva: R5FCX1 T4WDPD PNTZ4Z En primer lugar me creo una tabla con dos columnas (posicion, caracter). Tenemos tantos registros como caracteres A-Z y 0-9, vamos 36 caracteres. +----------+----------+ \| caracter \| posicion \| +----------+----------+ \| 0 \| 0 \| \| 1 \| 1 \| \| 2 \| 2 \| \| 3 \| 3 \| \| 4 \| 4 \| \| 5 \| 5 \| \| 6 \| 6 \| \| 7 \| 7 \| \| 8 \| 8 \| \| 9 \| 9 \| \| A \| 10 \| \| B \| 11 \| \| C \| 12 \| \| D \| 13 \| \| E \| 14 \| \| F \| 15 \| \| G \| 16 \| \| H \| 17 \| \| I \| 18 \| \| J \| 19 \| \| K \| 20 \| \| L \| 21 \| \| M \| 22 \| \| N \| 23 \| \| O \| 24 \| \| P \| 25 \| \| Q \| 26 \| \| R \| 27 \| \| S \| 28 \| \| T \| 29 \| \| U \| 30 \| \| V \| 31 \| \| W \| 32 \| \| X \| 33 \| \| Y \| 34 \| \| Z \| 35 \| +----------+----------+ 36 rows in set (0.00 sec) El código para generar el procedimiento es el siguiente: Código PHP: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`generaLocalizador`$$ CREATE PROCEDURE `test`.`generaLocalizador` ( OUT localizador VARCHAR(6) ) BEGIN DECLARE A1 VARCHAR(1); DECLARE A2 VARCHAR(1); DECLARE A3 VARCHAR(1); DECLARE A4 VARCHAR(1); DECLARE A5 VARCHAR(1); DECLARE A6 VARCHAR(1); DECLARE C1 VARCHAR(1); DECLARE C2 VARCHAR(1); DECLARE C3 VARCHAR(1); DECLARE C4 VARCHAR(1); DECLARE C5 VARCHAR(1); DECLARE C6 VARCHAR(1); SELECT MOD(FLOOR(RAND()1000), 35) INTO A1; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A1 INTO C1; SELECT MOD(FLOOR(RAND()1000), 35) INTO A2; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A2 INTO C2; SELECT MOD(FLOOR(RAND()1000), 35) INTO A3; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A3 INTO C3; SELECT MOD(FLOOR(RAND()1000), 35) INTO A4; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A4 INTO C4; SELECT MOD(FLOOR(RAND()1000), 35) INTO A5; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A5 INTO C5; SELECT MOD(FLOOR(RAND()1000), 35) INTO A6; SELECT caracter as CaracterAleatorio FROM caracteres WHERE posicion= A6 INTO C6; SELECT CONCAT(C1, C2, C3, C4, C5, C6) INTO localizador; END$$ DELIMITER ; ANALISIS MOD(FLOOR(RAND()1000), 35) Genera valor aleatorio, multiplica por 1000 y saca el módulo 35. El modulo 35 da valores entre 0 y 35 (Es el resto de dividir un numero entre 35). Ese valor lo usamos para acceder a la fila de la tabla caracteres y recuperar el valor. Para ejecutar el procedimiento usamos estas sentencias: Código PHP: `CALL generaLocalizador(@localizador); select @localizador;` Y nos mostraría la siguiente salida: +----------------+ \| @localizador \| +----------------+ \| Q3706Q \| +----------------+ ó +----------------+ \| @localizador \| +----------------+ \| ZR2ADS \| +----------------+ etc Me falta crear el DISPARADOR que llame a mi procedimiento despues de insetar un nuevo registro en la tabla 'reserva' y meta el localizador devuelto por el procedimiento en la clave principal del nuevo registro. Además tengo que ver como evito que se intente dar un valor de localizador ya existente o como lo detecto para probar con otro valor. Es la primera vez que trabajo con procedimientos asi que las sugerencias para mejorar mi código o como codificar el TRIGGER son bienvenidas. Jorge F.M. Última edición por digdig; 18-mar-2007 a las 21:35. Razón: camibo de titulo, Procedure y Trigger*

18-mar-2007, 19:07	#2 (permalink)
abulon Fecha de Ingreso: diciembre-2006 Mensajes: 113	Re: Generar Localizador Reserva con PROCEDURE En tu trigger pones solo el nombre del procedure , y debes guardar la variable ,pero debes declararla dentro del trigger CREATE TRIGGER OK AFTER INSERT ON RESERVA FOR EACH ROW BEGIN DECLARE variable tipo_de_dato; 'aki llmas tu procedure y lo ke te devuelva ke lo guarde en la variable END; Y en cuanto a ke se repite deberia de ser unique el campo o primary key.. Saludos

18-mar-2007, 19:57	#3 (permalink)
digdig Fecha de Ingreso: marzo-2007 Mensajes: 13	Re: Generar Localizador Reserva con PROCEDURE Gracia Abulon por responder, sólo me falta que al repetirse un localizador pruebe automaticamente con otro localizador aleatorio hasta que no coincida. No deberia dejarse de introducir una reserva sólo porque el localizador estaba repetido. Como el localizador es de 6 caracteres hay millones de valores posibles por lo que ya sería mucha casualiadad que coincida más de 3 veces con otro localizador. Creo que la sentencia insert tiene una opción para decidir que hacer si la clave ya existe, algo asi como: Código PHP: `INSERT INTO [...] SI SE REPITE clave= claverepetida+1 //Prueba con el valor siguiente.` ¿Alguien sabe la sintaxis exacta? Otra dudilla, ¿procedure soporta arrays como variable? lo digo porque prefiero almacenar los caracteres posibles en un array en vez de, tal como está ahora, en una tabla. ¿Como me crearía una variable array y como accedería a los valores? Pseudocodigo:: Código PHP: `caracteres varchar [36] = {'A','B','C','D', ... , '8', '9', '0' }; C1 = caracteres [A1]; [...] SELECT CONCAT(C1, C2, C3, C4, C5, C6) INTO localizador` Gracias y un saludo a todos, me está gustando esto del foro Jorge F.M. ( Calico )

18-mar-2007, 20:01	#4 (permalink)
abulon Fecha de Ingreso: diciembre-2006 Mensajes: 113	Re: Generar Localizador Reserva con PROCEDURE Los procedures no manejan array, pero manejan excepcions o handling excepcions, si arroja el error tal de duplicate key entoncs generar clave aleatoria de nuevo...un saludo

18-mar-2007, 21:30	#5 (permalink)
digdig Fecha de Ingreso: marzo-2007 Mensajes: 13	Re: Generar Localizador Reserva con PROCEDURE Bueno para que no quede la cosa sin terminar os cuelgo el codigo, ya probado, para generar el TRIGGER: Código PHP: `DELIMITER $$ CREATE TRIGGER nuevareserva BEFORE INSERT ON reserva FOR EACH ROW BEGIN DECLARE locAux VARCHAR(6); CALL generaLocalizador(@loc); SELECT @loc INTO locAux; SET NEW.localizador= locAux; END$$ DELIMITER ;` Si necesitamos borrar el trigger usariamos la sentencia: Código PHP: `DROP trigger nuevareserva;` Finalmente para que veais que no miento cuando digo que funciona os pongo las salidas al insertar dos nuevas reservas (comprador y crucero son los identificadores de los respectivos registros en otras tablas): Código PHP: mysql> insert into reserva (comprador, crucero) values (3432, 123); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from reserva; +-------------+-----------+---------+ \| localizador \| comprador \| crucero \| +-------------+-----------+---------+ \| HNW32L \| 3432 \| 123 \| +-------------+-----------+---------+ 1 row in set (0.00 sec) mysql> insert into reserva (comprador, crucero) values (3563, 165); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from reserva; +-------------+-----------+---------+ \| localizador \| comprador \| crucero \| +-------------+-----------+---------+ \| HNW32L \| 3432 \| 123 \| \| SP4A8S \| 3563 \| 165 \| +-------------+-----------+---------+ 2 rows in set (0.00 sec) RESUMEN: Necesitabamos que mysql generara automáticamente localizadores aleatorios formados por caracteres y dígitos. La tendencia de los programadores es delegar esta función en la aplicación cliente, sin embargo, el correcto uso de procedure y trigger permite al usuario de la base datos abstraerse de esta tarea con las ventajas que eso supone. La solución que hemos optado es crear un procedimiento que concatena caracteres recuperados aleatoriamente de una tabla auxiliar rellena de los 36 caracteres del localizador. Con el disparador before insert lo que hacemos es llamar al procedimiento para obtener un localizador válido y usarlo para almacenarlo en el campo localizador de cada fila insertada. Un saludo a todos Jorge F.M. (Calico UAX)

Herramientas
Mostrar Versión Imprimible Enviar por Correo
Desplegado
Mode Lineal Cambiar a Modo Hibrido Cambiar a Modo Hilado