[转]MySQL存储过程示例

转载自: http://www.itpub.net/thread-1212446-1-1.html

mysql> CREATE TABLE TEST1(ID INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TEST1 VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE TEST2(ID INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TEST2 VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE TEST3(ID INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TEST3 VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT table_name  FROM information_schema.tables WHERE table_name LIKE  '%TEST%';
+------------+
| table_name |
+------------+
| TEST1      | 
| TEST2      | 
| TEST3      | 
| testha     | 
+------------+
4 rows in set (0.00 sec)



mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_test()
    -> BEGIN
    -> DECLARE done INTEGER DEFAULT 0;
    -> DECLARE v_tname VARCHAR(20);
    -> DECLARE v_sql VARCHAR(400);
    -> DECLARE v_Id INTEGER;
    -> DECLARE v_Total INTEGER;
    -> DECLARE result INTEGER;
    -> DECLARE v_cursor CURSOR FOR SELECT table_name  FROM information_schema.tables WHERE table_name LIKE  'TEST%';
    -> DECLARE l_cur    CURSOR FOR SELECT id FROM tmp;
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    -> SET v_total=0;
    -> DROP TABLE IF EXISTS tmp;
    -> CREATE TEMPORARY TABLE tmp 
    -> (id INT(11));
    -> OPEN v_cursor;
    -> FETCH v_cursor INTO v_tname;
    -> WHILE done<>1 DO
    -> BEGIN
    -> SET @SQL=CONCAT('insert into tmp select id from ',v_tname, ' where id>1');
    -> PREPARE st FROM @SQL;
    -> EXECUTE st;
    -> DEALLOCATE PREPARE st;
    -> OPEN l_cur;
    -> FETCH l_cur INTO v_id;
    -> WHILE done<>1 DO
    -> SET v_total=v_total+v_id;
    -> FETCH l_cur INTO v_id;
    -> END WHILE;
    -> TRUNCATE TABLE tmp;
    -> END;
    -> FETCH v_cursor INTO v_tname;
    -> END WHILE;
    -> CLOSE v_cursor;
    -> SELECT v_total;
    -> END;
    -> 
    -> 
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test();
    -> //
+---------+
| v_total |
+---------+
|      14 | 
+---------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)