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 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
| --------------
set autocommit = 0
--------------
--------------
start transaction
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
default character set `latin1`
default collate `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `adresses`
--------------
--------------
CREATE TABLE `adresses`
( `id_adresse` integer unsigned not null auto_increment primary key,
`adresse` varchar(255) not null
) engine=innoDB
default charset=latin1 collate=latin1_general_ci
row_format=compressed
AUTO_INCREMENT=101
--------------
--------------
DROP TABLE IF EXISTS `structures`
--------------
--------------
CREATE TABLE `structures`
( `id_structure` integer unsigned not null auto_increment primary key,
`siret` varchar(255) not null,
`id_adresse` integer unsigned not null,
`nom` varchar(255) not null,
CONSTRAINT `FK_01` FOREIGN KEY (`id_adresse`) REFERENCES `adresses` (`id_adresse`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innoDB
default charset=latin1 collate=latin1_general_ci
row_format=compressed
--------------
--------------
create procedure `trait` (
IN in_siret varchar(255),
IN in_adresse varchar(255),
IN in_nom varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _incr integer unsigned DEFAULT NULL;
insert into `adresses` (`adresse`) values (in_adresse);
select last_insert_id() into _incr;
insert into `structures` (`siret`,`id_adresse`,`nom`) values (in_siret, _incr, in_nom);
END
--------------
--------------
call trait('123456789', 'rue des petits travaux', 'dupond')
--------------
--------------
call trait('987654321', 'boulevard des allongés', 'durand')
--------------
--------------
call trait('113357799', 'route sans issue', 'smith')
--------------
--------------
select * from structures
--------------
+--------------+-----------+------------+--------+
| id_structure | siret | id_adresse | nom |
+--------------+-----------+------------+--------+
| 1 | 123456789 | 101 | dupond |
| 2 | 987654321 | 102 | durand |
| 3 | 113357799 | 103 | smith |
+--------------+-----------+------------+--------+
--------------
select * from adresses
--------------
+------------+------------------------+
| id_adresse | adresse |
+------------+------------------------+
| 101 | rue des petits travaux |
| 102 | boulevard des allongés |
| 103 | route sans issue |
+------------+------------------------+
--------------
commit
--------------
--------------
set autocommit = 1
--------------
Appuyez sur une touche pour continuer... |
Partager