Probleme avec retour de procedure

**Marc_3** · 23/03/2016, 11h36

Bonjour,

Je fais une procedure stockée avec mysql et je ne peux utiliser les if else à l'intérieur.
J'utilise la procedure depuis un programme extérieur.

L'idée est pour moi de récuperer le id d'un Tuple si celui-ci existe sinon -1.

Est-ce que quelqu'un peut m'aider pour la syntaxe??
NB Sans le if else la procedure marche tres bien

Voici la procedure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
DROP PROCEDURE IF EXISTS is_leg_id_in_database;
DELIMITER $$                                                      
CREATE PROCEDURE is_leg_id_in_database (IN given_airline_cd VARCHAR(3),
									   IN given_flight_nr INT,
									   IN given_arp_dep varchar(3),
									   IN given_arp_arr varchar(3), 
									   IN given_time_dep int, 
									   IN given_time_arr INT,
									   IN given_op_airline_cd VARCHAR(3),
									   IN given_leg_sequence_nr INT,
									  OUT id_leg INT)  
 
BEGIN
   DECLARE tmp_var INT;
    SELECT TW_LEG_ID as 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 given_leg_sequence_nr = given_leg_sequence_nr; 
 
//************** Ici ce qui ne marche pas
	IF (tmp_var > 0)
	then SET id_leg = tmp_var;	   
	else SET id_leg = -1;	
	END IF;
//******************
 
 
END $$
 
DELIMITER ;

Merci d'avance

marc_3

**escartefigue** · 23/03/2016, 11h54

bonjour,

Vous pouvez simplifier ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT case
when exists
    (select 1
     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 given_leg_sequence_nr = given_leg_sequence_nr)
     then TW_LEG_ID
     else -1
     end as tmp_var

**Marc_3** · 23/03/2016, 12h44

Merci pour la réponse,
oui en fait c'est bien plus simple ainsi

**Artemus24** · 23/03/2016, 15h16

Salut Marc_3.

Voici ta procédure stockée sous deux écritures différentes, l'une avec un "if" et l'autre avec la fonction "ifnull".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

Si tu veux une amélioration, tu le demandes.

@+

Probleme avec retour de procedure

SQL Procédural MySQL

Discussions similaires

Partager

Partager