mysql存储过程 循环 mysql存储过程循环次数

直接上内容

第一：存储过程中CASE 条件

 

1. CREATE PROCEDURE p_case(IN parameter1 INT) 
2. BEGIN
3. DECLARE variable1 INT; 
4. SET
5. CASE
6. WHEN 0 THEN INSERT INTO t VALUES(0); 
7. WHEN 1 THEN INSERT INTO t VALUES(1); 
8. ELSE INSERT INTO t VALUES(2); 
9. END CASE; 
10. END;

LOOPS 循环
【一】WHILE...END WHILE
【二】LOOP...END LOOP
【三】REPEAT...END REPEAT
【四】GOTO

一：WHILE...END WHILE

1. CREATE PROCEDURE
2. BEGIN
3. DECLARE v INT; 
4. SET
5.     WHILE v < 5 DO 
6. INSERT INTO t VALUES(v); 
7. SET
8. END
9. END;//

二：LOOP...END LOOP

 

1. CREATE PROCEDURE p_loop() 
2. BEGIN 
3.     DECLARE v INT; 
4.     SET v = 0; 
5.     loop_label:LOOP 
6.         INSERT INTO t VALUES(v); 
7.         SET v = v + 1; 
8.         IF v>=5 THEN 
9.             LEAVE loop_label; 
10. END
11. END
12. END;//

三：REPEAT...END REPEAT
 

1. CREATE PROCEDURE
2. BEGIN
3. DECLARE v INT; 
4. SET
5.     REPEAT 
6. INSERT INTO t VALUES(v); 
7. SET
8.         UNTIL v>=5 
9. END
10. END;//

存储过程：

有返回

1. CREATE PROCEDURE
2. OUT pl INT, 
3. OUT ph INT, 
4. OUT pa INT
5. ) 
6. BEGIN
7. SELECT MAX(option_id) INTO ph FROM
8. SELECT Min(option_id) INTO pl FROM
9. SELECT AVG(option_id) INTO pa FROM
10. END; 
11. CALL optionsrank(@pl,@ph,@pa) // 
12. SELECT

IN AND OUT 有传入和传出参数

 

1. CREATE PROCEDURE
2. IN in_option_id INT, 
3. OUT
4. ) 
5. BEGIN
6. SELECT option_value INTO out_option_value FROM wp_options WHERE
7. END; 
8. CALL in_out_test(100,@out) // 
9. SELECT @out

 

转载于:https://blog.51cto.com/phpzf/793775