1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP PROCEDURE IF EXISTS `build`
--------------
--------------
CREATE PROCEDURE `build`
(
in _base varchar(255),
in _table varchar(255),
in _col varchar(255),
in _where varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _fin INTEGER DEFAULT 1;
DECLARE _z varchar(255);
DECLARE _tab CURSOR FOR SELECT concat(' where (', group_concat(column_name order by ordinal_position separator ','),') = ')
from information_schema.key_column_usage
where table_schema = _base
and table_name = _table
and constraint_name = 'primary'
order by table_schema, table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
OPEN _tab;
FETCH _tab INTO _z;
CLOSE _tab;
set @req = concat("select ",_col," from ",_base,'.',_table,_z,_where);
select @req;
PREPARE stmt1 FROM @req;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END
--------------
--------------
call `build` ('base','test','*','3')
--------------
+----------------------------------------+
| @req |
+----------------------------------------+
| select * from base.test where (id) = 3 |
+----------------------------------------+
+----+-------+
| id | val |
+----+-------+
| 3 | trois |
+----+-------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager