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
|
CREATE DEFINER=`cem`@`127.0.0.1` PROCEDURE `create_nodeB`(IN `nom_nodeB` VARCHAR(50), IN `type_nodeB` VARCHAR(50), IN `prestation_livraison` INT, IN `id_couple_NM` INT, IN `id_ORT` INT, IN `VLAN_ID_livraison_ORT` INT, IN `VLAN_ID_nodeB` INT, IN `nom_brasseur` VARCHAR(50), IN `port_working` INT, IN `port_protected` INT, IN `port_STM_working` INT, IN `port_STM_protected` INT, IN `aps_global` INT, IN `prestation_data` INT, IN `prestation_data_PVC` INT, IN `prestation_data_profil_ATM` INT, IN `prestation_voix` INT, IN `prestation_voix_PVC` INT, IN `prestation_voix_profil_ATM` INT)
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Création d\'un nodeB'
BEGIN
DECLARE id_ATM INT DEFAULT 0;
DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
/** Création de la prestation Data **/
INSERT INTO `prestations` (`numero_prestation`,`type`,`PVC`,`id_profil_ATM`)
VALUES (prestation_data,'Data',prestation_data_PVC,prestation_data_profil_ATM);
/** Création de la prestation Voix si elle existe **/
IF(prestation_voix IS NOT NULL) THEN
INSERT INTO `prestations` (`numero_prestation`,`type`,`PVC`,`id_profil_ATM`)
VALUES (prestation_voix,'Voix',prestation_voix_PVC,prestation_voix_profil_ATM);
END IF;
/** Création de l'objet ATM **/
INSERT INTO `atm` (`nom_brasseur`,`port_brasseur_working`,`port_brasseur_protected`,`port_STM_working`,`port_STM_protected`,`aps_global`)
VALUES (nom_brasseur,port_working,port_protected,port_STM_working,port_STM_protected,aps_global);
/** On récupére l'id_ATM crée**/
SET id_ATM = (SELECT LAST_INSERT_ID() FROM `atm`);
/** Création de l'objet NodeB **/
INSERT INTO `nodeb` (`nom_nodeB`,`type_nodeB`,`prestation_livraison`,`id_couple_NM`,`id_ORT`,`VLAN_ID_nodeB`,`VLAN_ID_livraison_ORT`,`id_ATM`)
VALUES(nom_nodeb,type_nodeB,prestation_livraison,id_couple_NM,id_ORT,VLAN_ID_nodeB,VLAN_ID_livraison_ORT,id_ATM);
COMMIT;
END |
Partager