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

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

CALL myproc()

DROP PROCEDURE IF EXISTS myproc;

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

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

label: LOOP
?? ?statement_list

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