The an example provided by the "Astro121", the stored procedure contains a simple SELECT, you can instead create procedures that contain complex syntax containing multiple statements, in practice, the actual script, with the ability to control the flow through the different constructs (IF, CASE, LOOP, WHILE, REPEAT, LEAVE, ITER). You can also use cursors to handle the resultset. Here's an example of a procedure of some complexity:
Code:
DELIMITER // CREATE PROCEDURE procedure1 (INT param1, param2 CHAR (3), OUT param3 INT)
BEGIN DECLARE done INT default 0; DECLARE a INT;
DECLARE b CHAR (50); CUR1
DECLARE CURSOR FOR SELECT id, name FROM customer WHERE cat = param2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 'SET done = 1;
OPEN CUR1, pre-set param3 = 0; CUR1 FETCH INTO a, b;
loop: WHILE NOT finished DO IF param3 <param1 THEN December param3 = param3 + 1; FETCH CUR1 INTO a, b; ELSE LEAVE cycle;
END IF; END WHILE loop; END // DELIMITER;
Bookmarks