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 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195
| --------------
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 `tw_leg`
--------------
--------------
CREATE TABLE `tw_leg`
( `tw_leg_id` integer unsigned NOT NULL AUTO_INCREMENT primary key,
`airline_cd` char(03) NOT NULL,
`flight_number` integer NOT NULL,
`dep_arp_cd` char(03) NOT NULL,
`arr_arp_cd` char(03) NOT NULL,
`s_time_dep` time NOT NULL,
`s_time_arr` time NOT NULL,
`op_airline_cd` char(03) NOT NULL,
`leg_sequence_nr` integer NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP PROCEDURE IF EXISTS `is_leg_id_in_database_1`
--------------
--------------
CREATE PROCEDURE `is_leg_id_in_database_1` (
IN given_airline_cd char(3),
IN given_flight_nr integer,
IN given_arp_dep char(3),
IN given_arp_arr char(3),
IN given_time_dep time,
IN given_time_arr time,
IN given_op_airline_cd CHAR(3),
IN given_leg_sequence_nr integer,
OUT id_leg integer
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE tmp_var integer default null;
SELECT TW_LEG_ID into tmp_var
FROM TW_LEG
WHERE AIRLINE_CD = given_airline_cd
AND FLIGHT_NUMBER = given_flight_nr
AND DEP_ARP_CD = given_arp_dep
AND ARR_ARP_CD = given_arp_arr
AND S_TIME_DEP = given_time_dep
AND S_TIME_ARR = given_time_arr
AND OP_AIRLINE_CD = given_op_airline_cd
AND leg_sequence_nr = given_leg_sequence_nr;
IF (tmp_var > 0) then
SET id_leg = tmp_var;
ELSE SET id_leg = -1;
END IF;
END
--------------
--------------
DROP PROCEDURE IF EXISTS `is_leg_id_in_database_2`
--------------
--------------
CREATE PROCEDURE `is_leg_id_in_database_2` (
IN given_airline_cd char(3),
IN given_flight_nr integer,
IN given_arp_dep char(3),
IN given_arp_arr char(3),
IN given_time_dep time,
IN given_time_arr time,
IN given_op_airline_cd CHAR(3),
IN given_leg_sequence_nr integer,
OUT id_leg integer
)
DETERMINISTIC
NO SQL
BEGIN
SELECT IFNULL ((
SELECT TW_LEG_id
FROM TW_LEG
WHERE AIRLINE_CD = given_airline_cd
and FLIGHT_NUMBER = given_flight_nr
and DEP_ARP_CD = given_arp_dep
and ARR_ARP_CD = given_arp_arr
and S_TIME_DEP = given_time_dep
and S_TIME_ARR = given_time_arr
and OP_AIRLINE_CD = given_op_airline_cd
and leg_sequence_nr = given_leg_sequence_nr
), -1) into id_leg;
END
--------------
--------------
INSERT INTO `tw_leg` (`tw_leg_id`,`airline_cd`,`flight_number`,`dep_arp_cd`,`arr_arp_cd`,`s_time_dep`,`s_time_arr`,`op_airline_cd`,`leg_sequence_nr`) VALUES
(17,'xxx', 235, 'yyy', 'zzz', '15:00:00', '17:25:00', 'ttt', 5)
--------------
--------------
select * from tw_leg
--------------
+-----------+------------+---------------+------------+------------+------------+------------+---------------+-----------------+
| tw_leg_id | airline_cd | flight_number | dep_arp_cd | arr_arp_cd | s_time_dep | s_time_arr | op_airline_cd | leg_sequence_nr |
+-----------+------------+---------------+------------+------------+------------+------------+---------------+-----------------+
| 17 | xxx | 235 | yyy | zzz | 15:00:00 | 17:25:00 | ttt | 5 |
+-----------+------------+---------------+------------+------------+------------+------------+---------------+-----------------+
--------------
set @toto = null
--------------
--------------
call `is_leg_id_in_database_1` ('xxx', 235, 'yyy', 'zzz', '15:00:00', '17:25:00', 'ttt', 5, @toto)
--------------
--------------
select @toto
--------------
+-------+
| @toto |
+-------+
| 17 |
+-------+
--------------
call `is_leg_id_in_database_1` ('xxx', 235, 'yyy', 'zzz', '15:00:00', '17:25:00', 'ttt', 1, @toto)
--------------
--------------
select @toto
--------------
+-------+
| @toto |
+-------+
| -1 |
+-------+
--------------
set @toto = null
--------------
--------------
call `is_leg_id_in_database_2` ('xxx', 235, 'yyy', 'zzz', '15:00:00', '17:25:00', 'ttt', 5, @toto)
--------------
--------------
select @toto
--------------
+-------+
| @toto |
+-------+
| 17 |
+-------+
--------------
call `is_leg_id_in_database_2` ('xxx', 235, 'yyy', 'zzz', '15:00:00', '17:25:00', 'ttt', 1, @toto)
--------------
--------------
select @toto
--------------
+-------+
| @toto |
+-------+
| -1 |
+-------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager