mysql procedure for_MySQL Procedure(MySQL存储过程)[转]

1 --Say we only want student ID's and names. To update a procedure, we must

2 --first DROP it:

3

4 DROP PROCEDURE IF EXISTS ListStudents //

5

6 Query OK, 0 rows affected (0.00sec)7

8 --Again, whenever you drop a procedure, you should get a 'Query OK' message.

9 --From now on, we will always use "DROP PROCEDURE IF EXISTS procName" as

10 --a standard practice before declaring procedures:

11

12 DROP PROCEDURE IF EXISTS ListStudents //

13 CREATE PROCEDUREListStudents()14 SELECTStuID, LName, FName15 FROMStudent;16 //

17

18 CALL ListStudents() //

19

20 +-------+----------+---------+

21 | StuID | LName | FName |

22 +-------+----------+---------+

23 | 1001 | Smith | Linda |

24 | 1002 | Kim | Tracy |

25 .26 .27 .28 | 1034 | Epp | Eric |

29 | 1035 | Schmidt | Sarah |

30 +-------+----------+---------+

31 34 rows in set (0.00sec)32

33

34

35 --OK, let's use some parameters:

36

37 DROP PROCEDURE IF EXISTS sayHello //

38 CREATE PROCEDURE sayHello(IN name VARCHAR(20))39 SELECT CONCAT('Hello', name, '!') ASGreeting;40 //

41

42 --The 'IN' keyword tells MySQL that is should be expecting an input value for

43 --the parameter......hunh? Why would a parameter NOT have an input value? You will

44 --see in a little bit. First, let's see if sayHello works:

45

46 CALL sayHello('Omar') //

47

48 +-------------+

49 | Greeting |

50 +-------------+

51 | Hello Omar! |

52 +-------------+

53 1 row in set (0.00sec)54

55

56

57 --Another example:

58

59 DROP PROCEDURE IF EXISTS saySomething //

60 CREATE PROCEDURE saySomething(IN phrase VARCHAR(20), IN name VARCHAR(20))61 SELECT CONCAT(phrase, ' ', name, '!') ASOutput;62 //

63

64 CALL saySomething('Go','Blue Jays') //

65 CALL saySomething('Do','my homework') //

66

67 +---------------+

68 | Output |

69 +---------------+

70 | Go Blue Jays! |

71 +---------------+

72 1 row in set (0.00sec)73

74 +-----------------+

75 | Output |

76 +-----------------+

77 | Do my homework! |

78 +-----------------+

79 1 row in set (0.00sec)80

81

82

83 --and another one:

84

85 DROP PROCEDURE IF EXISTS FindStudent //

86 CREATE PROCEDURE FindStudent(IN id INT)87 SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'

88 FROMStudent89 WHERE StuID =id;90 //

91

92 CALL FindStudent(1001) //

93

94 +-------+--------------+

95 | StuID | Student Name |

96 +-------+--------------+

97 | 1001 | Linda Smith |

98 +-------+--------------+

99 1 row in set (0.00sec)100

101

102

103 --and yet another:

104

105 DROP PROCEDURE IF EXISTS calculate //

106 CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)107 SET sum = x +y;108 SET product = x *y;109 //

110

111 ERROR 1064 (42000): You have an error in your SQL syntax; checkthe manual ...112

113 --Well, that wasn't good. The reason is, we must use BEGIN/END if we have

114 --a compound statement:

115

116 DROP PROCEDURE IF EXISTS calculate //

117 CREATE PROCEDURE calculate(IN x INT, IN y INT, OUT sum INT, OUT product INT)118 BEGIN

119 SET sum = x +y;120 SET product = x *y;121 END;122 //

123

124 --Did you notice the 'OUT' keyword for sum and product? This tells MySQL that those

125 --two parameters are not 'input' parameters but are 'output' parameters instead.

126 --Now, when calling the procedure, we need to provide four parameters: two input

127 --values, and two MySQL *variables* where the results will be stored:

128

129 CALL calculate(4,5,@s,@p) //

130

131 Query OK, 0 rows affected (0.00sec)132

133 --Here, @s and @p are MySQL variables. Notice that they start with @, although

134 --procedure *parameters* do not start with @

135

136 SELECT @s //

137 SELECT @p //

138

139 +------+

140 | @s |

141 +------+

142 | 9 |

143 +------+

144 1 row in set (0.00sec)145

146 +------+

147 | @p |

148 +------+

149 | 20 |

150 +------+

151 1 row in set (0.00sec)152

153 --Note: you can also have INOUT parameters, which serve as both input and output

154 --parameters.

155

156

157

158 --OK, let's do some interesting stuff. First off, flow control:

159

160 DROP PROCEDURE IF EXISTS mySign //

161 CREATE PROCEDURE mySign(IN x INT)162 BEGIN

163 IF x > 0 THEN

164 SELECT x AS Number, '+' AS Sign;165 ELSEIF x < 0 THEN

166 SELECT x AS Number, '-' AS Sign;167 ELSE

168 SELECT x AS Number, 'Zero' AS Sign;169 END IF;170 END;171 //

172

173 CALL mySign(2) //

174 CALL mySign(-5) //

175 CALL mySign(0) //

176

177 +--------+------+

178 | Number | Sign |

179 +--------+------+

180 | 2 | + |

181 +--------+------+

182 1 row in set (0.00sec)183

184 +--------+------+

185 | Number | Sign |

186 +--------+------+

187 | -5 | - |

188 +--------+------+

189 1 row in set (0.00sec)190

191 +--------+------+

192 | Number | Sign |

193 +--------+------+

194 | 0 | Zero |

195 +--------+------+

196 1 row in set (0.00sec)197

198

199

200 --Before we get any further, let's introduce variables:

201

202 DROP PROCEDURE IF EXISTS mySign //

203 CREATE PROCEDURE mySign(IN x INT)204 BEGIN

205

206 DECLARE result VARCHAR(20);207

208 IF x > 0 THEN

209 SET result = '+';210 ELSEIF x < 0 THEN

211 SET result = '-';212 ELSE

213 SET result = 'Zero';214 END IF;215

216 SELECT x AS Number, result AS Sign;217

218 END;219 //

220

221 CALL mySign(2) //

222 CALL mySign(-5) //

223 CALL mySign(0) //

224

225 +--------+------+

226 | Number | Sign |

227 +--------+------+

228 | 2 | + |

229 +--------+------+

230 1 row in set (0.00sec)231

232 +--------+------+

233 | Number | Sign |

234 +--------+------+

235 | -5 | - |

236 +--------+------+

237 1 row in set (0.00sec)238

239 +--------+------+

240 | Number | Sign |

241 +--------+------+

242 | 0 | Zero |

243 +--------+------+

244 1 row in set (0.00sec)245

246

247

248 --Using CASE:

249

250 DROP PROCEDURE IF EXISTS digitName //

251 CREATE PROCEDURE digitName(IN x INT)252 BEGIN

253

254 DECLARE result VARCHAR(20);255

256 CASEx257 WHEN 0 THEN SET result = 'Zero';258 WHEN 1 THEN SET result = 'One';259 WHEN 2 THEN SET result = 'Two';260 WHEN 3 THEN SET result = 'Three';261 WHEN 4 THEN SET result = 'Four';262 WHEN 5 THEN SET result = 'Five';263 WHEN 6 THEN SET result = 'Six';264 WHEN 7 THEN SET result = 'Seven';265 WHEN 8 THEN SET result = 'Eight';266 WHEN 9 THEN SET result = 'Nine';267 ELSE SET result = 'Not a digit';268 END CASE;269

270 SELECT x AS Digit, result ASName;271

272 END;273 //

274

275 CALL digitName(0) //

276 CALL digitName(4) //

277 CALL digitName(100) //

278

279 +-------+------+

280 | Digit | Name |

281 +-------+------+

282 | 0 | Zero |

283 +-------+------+

284 1 row in set (0.00sec)285

286 +-------+------+

287 | Digit | Name |

288 +-------+------+

289 | 4 | Four |

290 +-------+------+

291 1 row in set (0.00sec)292

293 +-------+-------------+

294 | Digit | Name |

295 +-------+-------------+

296 | 100 | Not a digit |

297 +-------+-------------+

298 1 row in set (0.00sec)299

300

301

302 --As you'd expect, we have loops. For example, WHILE loops:

303

304 DROP PROCEDURE IF EXISTS fact //

305 CREATE PROCEDURE fact(IN x INT)306 BEGIN

307

308 DECLARE result INT;309 DECLARE i INT;310 SET result = 1;311 SET i = 1;312

313 WHILE i <=x DO314 SET result = result *i;315 SET i = i + 1;316 END WHILE;317

318 SELECT x AS Number, result asFactorial;319

320 END;321 //

322

323 CALL fact(1) //

324 CALL fact(2) //

325 CALL fact(4) //

326 CALL fact(0) //

327

328 +--------+-----------+

329 | Number | Factorial |

330 +--------+-----------+

331 | 1 | 1 |

332 +--------+-----------+

333 1 row in set (0.00sec)334

335 +--------+-----------+

336 | Number | Factorial |

337 +--------+-----------+

338 | 2 | 2 |

339 +--------+-----------+

340 1 row in set (0.00sec)341

342 +--------+-----------+

343 | Number | Factorial |

344 +--------+-----------+

345 | 4 | 24 |

346 +--------+-----------+

347 1 row in set (0.01sec)348

349 +--------+-----------+

350 | Number | Factorial |

351 +--------+-----------+

352 | 0 | 1 |

353 +--------+-----------+

354 1 row in set (0.00sec)355

356

357

358 --There is also REPEAT/UNTIL loops:

359

360 DROP PROCEDURE IF EXISTS fact //

361 CREATE PROCEDURE fact(IN x INT)362 BEGIN

363

364 DECLARE result INT DEFAULT 1; /*notice you can declare a variable*/

365 DECLARE i INT DEFAULT 1; /*and give it a value in one line*/

366

367 REPEAT368 SET result = result *i;369 SET i = i + 1;370 UNTIL i >x371 ENDREPEAT;372

373 SELECT x AS Number, result asFactorial;374

375 END;376 //

377

378 CALL fact(1) //

379 CALL fact(2) //

380 CALL fact(4) //

381 CALL fact(0) //

382

383 +--------+-----------+

384 | Number | Factorial |

385 +--------+-----------+

386 | 1 | 1 |

387 +--------+-----------+

388 1 row in set (0.00sec)389

390 +--------+-----------+

391 | Number | Factorial |

392 +--------+-----------+

393 | 2 | 2 |

394 +--------+-----------+

395 1 row in set (0.00sec)396

397 +--------+-----------+

398 | Number | Factorial |

399 +--------+-----------+

400 | 4 | 24 |

401 +--------+-----------+

402 1 row in set (0.00sec)403

404 +--------+-----------+

405 | Number | Factorial |

406 +--------+-----------+

407 | 0 | 1 |

408 +--------+-----------+

409 1 row in set (0.00sec)410

411

412

413 --OK, do you remember this?

414 /*

415 CREATE PROCEDURE FindStudent(IN id INT)416 SELECT StuID, CONCAT(FName, ' ', LName) AS 'Student Name'417 FROM Student418 WHERE StuID = id;419 //420 */

421

422 --What if we only want to extract the name without printing it out?

423 --Obviously, we need some OUT parameters. Still, how do you extract

424 --information into those OUT parameters?

425 --426 --Answer: something called a CURSOR:

427

428 DROP PROCEDURE IF EXISTS FindName //

429 CREATE PROCEDURE FindName(IN id INT, OUT fn VARCHAR(20), OUT ln VARCHAR(20))430 BEGIN

431 DECLARE cur CURSOR FOR

432 SELECTFName, LName433 FROMStudent434 WHERE StuID =id;435 OPENcur;436 FETCH cur INTOfn, ln;437 CLOSEcur;438 END;439 //

440

441 CALL FindName(1001,@f,@l) //

442

443 Query OK, 0 rows affected (0.00sec)444

445 --Remember that @f and @l are MySQL variables:

446

447 SELECT @f //

448 SELECT @l //

449

450 +-------+

451 | @f |

452 +-------+

453 | Linda |

454 +-------+

455 1 row in set (0.00 sec)