MySql官方手冊學習筆記3—MySql中的存儲過程簡介
相關鏈接：
MySql官方手冊學習筆記1—MySql簡單上手
http://www.BkJia.com/database/201210/159522.html；
MySql官方手冊學習筆記2—MySql的模糊查詢和正則表達式
http://www.BkJia.com/database/201210/160095.html
SELECT INTO
www.2cto.com
CREATE PROCEDURE sp1 (x VARCHAR(5)) //創建儲存過程
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob'; //聲明局部變量
DECLARE newname VARCHAR(5);
DECLARE xid INT;
SELECT xname,id INTO newname,xid //將查到的屬性值存入變量中
FROM table1 WHERE xname = xname;
SELECT newname;
END;
變量名不能與列名一樣，當這個程序被調用的時候，無論table.xname列的值是什麼，變量newname將返回值‘bob’。
HANDLER
mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter // //將定界符“;”改為“//”，因為程序中需要用到“;”。
www.2cto.com
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE insertErr CONDITION FOR SQLSTATE '23000'; //聲明CONDITION
-> DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1;//聲明HANDLER
-> SET @x = 1; //為局部變量賦值
-> INSERT INTO test.t VALUES (1);
-> SET @x = 2;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 3;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
注意到，@x是3，這表明MySQL被執行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1; 這一行不在，第二個INSERT因PRIMARY KEY強制而失敗之後,MySQL可能已經采取 默認(EXIT)路徑，並且SELECT @x可能已經返回2。
www.2cto.com
CURSOR
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1; //使用前先打開游標
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
CASE
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
www.2cto.com
存儲程序的CASE語句實現一個復雜的條件構造。如果search_condition 求值為真，相應的SQL被執行。如果沒有搜索條件匹配，在ELSE子句裡的語句被執行。
LEAVE
LEAVE label
這個語句被用來退出任何被標注的流程控制構造。它和BEGIN ... END或循環一起被使用。
ITERATE
ITERATE label
www.2cto.com
ITERATE只可以出現在LOOP, REPEAT, 和WHILE語句內。ITERATE意思為：“再次循環。”
例如：
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END
REPEAT
mysql> delimiter //
www.2cto.com
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
www.2cto.com
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
WHILE
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
EN
