前言

這里存儲(chǔ)過(guò)程和游標(biāo)的定義和作用就不介紹了，網(wǎng)上挺多的，只通過(guò)簡(jiǎn)單的介紹，然后用個(gè)案例讓大家快速了解。實(shí)例中會(huì)具體說(shuō)明變量的定義，賦值，游標(biāo)的使用，控制語(yǔ)句，循環(huán)語(yǔ)句的介紹。

1.創(chuàng)建存儲(chǔ)過(guò)程。

CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END

存儲(chǔ)過(guò)程根據(jù)需要可能會(huì)有輸入、輸出、輸入輸出參數(shù)，如果有多個(gè)參數(shù)用","分割開(kāi)。MySQL存儲(chǔ)過(guò)程的參數(shù)用在存儲(chǔ)過(guò)程的定義，共有三種參數(shù)類型,IN,OUT,INOUT:

• IN參數(shù)的值必須在調(diào)用存儲(chǔ)過(guò)程時(shí)指定，在存儲(chǔ)過(guò)程中修改該參數(shù)的值不能被返回，為默認(rèn)值
• OUT:該值可在存儲(chǔ)過(guò)程內(nèi)部被改變，并可返回
• INOUT:調(diào)用時(shí)指定，并且可被改變和返回

2.查看存儲(chǔ)過(guò)程名稱

SELECT routine_name FROM information_schema.routines WHERE routine_schema='數(shù)據(jù)庫(kù)名稱';

3.調(diào)用存儲(chǔ)過(guò)程

CALL myproc()

4.刪除存儲(chǔ)過(guò)程

DROP PROCEDURE IF EXISTS myproc;

因?yàn)閙ysql中游標(biāo)只能在存儲(chǔ)過(guò)程和方法中使用，所以就直接通過(guò)案例介紹游標(biāo)。

案例：該案例采用無(wú)參存儲(chǔ)過(guò)程，有參的也挺簡(jiǎn)單，根據(jù)上面的介紹，對(duì)應(yīng)實(shí)現(xiàn)就行，該存儲(chǔ)過(guò)程主要就創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用它做查詢 修改等操作。

#檢查該存儲(chǔ)過(guò)程是否存在 存在就刪除了再創(chuàng)建
DROP PROCEDURE IF EXISTS processnames ;
#創(chuàng)建存儲(chǔ)過(guò)程
CREATE PROCEDURE processnames()
#BEGIN END  存儲(chǔ)過(guò)程中的sql邏輯寫在BEGIN 和END 中
BEGIN
#定義變量
DECLARE var_name VARCHAR(300);
DECLARE var_uuid VARCHAR(300);
DECLARE count int DEFAULT 0 ;
DECLARE i int DEFAULT 0 ;
DECLARE done INT;
#定義游標(biāo)
DECLARE nameCursor CURSOR FOR SELECT ParentId FROM datadictionary GROUP BY ParentId;
#該sql語(yǔ)句作用是  在你遍歷游標(biāo)的時(shí)候  游標(biāo)循環(huán)結(jié)束 就會(huì)執(zhí)行這句話，并給done賦值為1 然后循環(huán)就會(huì)停止 但該語(yǔ)句不適用WHILE 循環(huán)語(yǔ)句。
DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
#查詢一個(gè)表分組的總數(shù)量  并賦值給count變量
SELECT count(*) into count FROM (SELECT ParentId FROM datadictionary GROUP BY ParentId) t1;
#打開(kāi)游標(biāo)
OPEN nameCursor;
#循環(huán)體
    WHILE i<count DO
    #拿到游標(biāo)指向的當(dāng)前行的數(shù)據(jù)  并賦值給var_name變量
    FETCH nameCursor INTO var_name;
    #通過(guò)var_name變量進(jìn)行相應(yīng)的數(shù)據(jù)查詢 把查詢出來(lái)的數(shù)據(jù)通過(guò)into賦值給變量var_uuid
    SELECT uuid into var_uuid FROM datadictionary WHERE ID=var_name;
    #進(jìn)行修改操作
    UPDATE datadictionary SET ParentId=var_uuid WHERE ParentId=var_name;
    #可以通過(guò)set 給變量賦值  這里用來(lái)記錄循環(huán)體執(zhí)行了多少次  看和游標(biāo)遍歷的條數(shù)是否一致
  SET i=i+1;
    #當(dāng)i>count的時(shí)候循環(huán)體結(jié)束
  END WHILE;
    #關(guān)閉游標(biāo)
    CLOSE nameCursor;
    #打印i 
    SELECT i;
    #查看修改后的數(shù)據(jù)
    SELECT * FROM datadictionary;
    #存儲(chǔ)過(guò)程結(jié)束標(biāo)志
END;
#調(diào)用存儲(chǔ)過(guò)程
CALL processnames()

案例2 添加操作，并且獲取游標(biāo)中的多列值

DROP PROCEDURE if EXISTS proce;
CREATE PROCEDURE proce()
BEGIN
 DECLARE  userid VARCHAR(50);
 DECLARE  depatementid VARCHAR(50);
 DECLARE  done INT; 
 DECLARE nameCuursor CURSOR FOR SELECT id,FrameworkDepartmentId FROM frameworkusers ;
 DECLARE  continue handler FOR SQLSTATE '02000' SET done = 1;
 OPEN nameCuursor;
label: LOOP
  FETCH nameCuursor INTO userid,depatementid;
    IF done = 1 THEN
        LEAVE label; 
    END IF; 
    INSERT into frameworkdepartmentuser(ID,FrameworkUserId,FrameworkDepartmentId) VALUES(UUID(),userid,depatementid);
END LOOP label;
CLOSE nameCuursor;
END;
CALL proce();
SELECT * FROM frameworkdepartmentuser

注意：

1.DECLARE關(guān)鍵字 在存儲(chǔ)過(guò)程內(nèi)部就是定義變量的。

2.DECLARE nameCursor CURSOR FOR SELECT ParentId FROM datadictionary GROUP BY ParentId;該語(yǔ)句用來(lái)定義游標(biāo)，其中FOR后面跟隨的是你的查詢語(yǔ)句，把查詢出來(lái)的結(jié)果賦值給了nameCursor這個(gè)游標(biāo)

2.1 遍歷游標(biāo)前一定要先打開(kāi)游標(biāo) OPEN nameCursor;

2.2 遍歷結(jié)束 一定要關(guān)閉游標(biāo)，CLOSE nameCursor;如果不關(guān)閉當(dāng)存儲(chǔ)過(guò)程執(zhí)行完遇到最后一個(gè)END的時(shí)候也會(huì)自動(dòng)關(guān)閉。

3.DECLARE continue handler FOR SQLSTATE '02000' SET done = 1; 有很多人不知道這段是什么意思，我開(kāi)始也不知道，首先本案例中這段話可以直接注釋，沒(méi)有作用，一般在沒(méi)有條件體的循環(huán)體中使用，如：REPEAT statement_list UNTIL search_condition END REPEAT;循環(huán)體和LOOP 循環(huán)體使用的比較多。該句話的意思如注釋所說(shuō)，當(dāng)你沒(méi)有循環(huán)條件作為循環(huán)結(jié)束語(yǔ)句的時(shí)候，就可以用到上面DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;該句話的作用就是：當(dāng)你游標(biāo)在上述循環(huán)體中遍歷結(jié)束的時(shí)候，就會(huì)執(zhí)行該語(yǔ)句，讓done =1；打個(gè)比方，假如你用的是REPEAT statement_list UNTIL search_condition END REPEAT;語(yǔ)句作為循環(huán)體，我們可寫成UNTIL done因?yàn)镽EPEAT循環(huán)體執(zhí)行順序是先執(zhí)行一次sql語(yǔ)句流然后再進(jìn)行UNTIL done進(jìn)行判斷，當(dāng)游標(biāo)遍歷結(jié)束，done=1然后循環(huán)體結(jié)束。SQLSTATE '02000'只是一個(gè)標(biāo)識(shí)，具體可以官網(wǎng)查詢。

然后再簡(jiǎn)單說(shuō)下

label: LOOP
?? ?statement_list

?? ?IF done=1 THEN
?? ??? ?LEAVE label;?
?? ?END IF;?
END LOOP label;

其中statement_list還是你需要執(zhí)行的sql流，Loop和REPEAT有點(diǎn)像，都是先執(zhí)行，再判斷。我案例中是用的WHILE 該循環(huán)體是先判斷條件，再執(zhí)行，比較符合我當(dāng)前的運(yùn)用場(chǎng)景。

4. FETCH nameCursor INTO var_name;把當(dāng)前游標(biāo)指向的行賦值給變量，這里游標(biāo)執(zhí)行過(guò)程是順序執(zhí)行的，就相當(dāng)于遍歷數(shù)組，從第一個(gè)一直遍歷到最后一個(gè)。

5. 如果需要把查詢出來(lái)的結(jié)果，賦值給一個(gè)變量，可以使用into如這樣:SELECT uuid into var_uuid FROM datadictionary WHERE ID=var_name;

6. 如果非查詢賦值，可以用set如:SET i=i+1;

7. 獲取一行游標(biāo)中的多列值，只需定義和列關(guān)聯(lián)的變量，然后用into進(jìn)行賦值 如例2：FETCH nameCuursor INTO userid,depatementid;

總結(jié)

到此這篇關(guān)于MySql存儲(chǔ)過(guò)程和游標(biāo)使用的文章就介紹到這了,更多相關(guān)MySql存儲(chǔ)過(guò)程游標(biāo)使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論