DELIMITER // CREATE PROCEDURE PROCEDURE_NAME([[IN | OUT | INOUT] ARG_NAME TYPE [, [INT | OUT | INOUT] ARG_NAME TYPE...]]) BEGIN PROCEDURE BODY END // DELIMITER ;
mysql> DROP PROCEDURE IF EXISTS pro_if; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER // mysql> CREATE PROCEDURE pro_if(IN type INT) -> BEGIN -> DECLARE c VARCHAR(500); -> IF type = 0 THEN SET c = 'PARAM IS 0'; -> ELSEIF type = 1 THEN SET c = 'PARAM IS 1'; -> ELSE SET c = 'PARAM IS OTHERS'; -> END IF; -> END; -> // Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL pro_if(0); +------------+ | c | +------------+ | PARAM IS 0 | +------------+ 1 row inset (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL pro_if(1); +------------+ | c | +------------+ | PARAM IS 1 | +------------+ 1 row inset (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL pro_if(2); +-----------------+ | c | +-----------------+ | PARAM IS OTHERS | +-----------------+ 1 row inset (0.01 sec)
mysql> DELIMITER // mysql> CREATE PROCEDURE proc_case(type int) -> BEGIN -> DECLARE c VARCHAR(500); -> casetype -> WHEN 0 THEN SET c = 'PARAM IS 0'; -> WHEN 1 THEN SET c = 'PARAM IS 1'; -> WHEN 2 THEN SET c = 'PARAM IS 2'; -> ELSE SET c = 'PARAM IS OTHERS'; -> SELECT c; -> END CASE; -> END; -> // Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL proc_case(0); +------------+ | c | +------------+ | PARAM IS 0 | +------------+ 1 row inset (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL proc_case(1); +------------+ | c | +------------+ | PARAM IS 1 | +------------+ 1 row inset (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL proc_case(2); +------------+ | c | +------------+ | PARAM IS 2 | +------------+ 1 row inset (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL proc_case(3); +-----------------+ | c | +-----------------+ | PARAM IS OTHERS | +-----------------+ 1 row inset (0.00 sec)
mysql> DELIMITER // mysql> CREATE PROCEDURE proc_while(IN n INT) -> BEGIN -> DECLARE i INT; -> DECLARE s INT; -> SET i=0; -> SET s=0; -> WHILE i<=n DO -> SET s = s + i; -> SET i = i + 1; -> END WHILE; -> SELECT s; -> END; -> // Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS proc_loop; Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER // mysql> CREATE PROCEDURE proc_loop(IN n INT) -> BEGIN -> DECLARE i INT; -> DECLARE s INT; -> SET i=0; -> SET s=0; -> LOOP_LABEL:LOOP -> SET s = s + i; -> SET i = i + 1; -> IF i > 10 THEN LEAVE LOOP_LABEL; -> END IF; -> END LOOP; -> SELECT s; -> END; -> // Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS proc_repeat; Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER // mysql> CREATE PROCEDURE proc_repeat(IN n INT) -> BEGIN -> DECLARE i INT; -> DECLARE s INT; -> SET i=0; -> SET s=0; -> REPEAT -> SET s = s + i; -> SET i = i + 1; -> UNTIL i>10 -> END REPEAT; -> SELECT s; -> END; -> // Query OK, 0 rows affected (0.01 sec)