MySql存储过程和游标的使用

- 日理万妓 2024-03-24 11:34 63阅读 0赞

这里存储过程和游标的定义和作用就不介绍了，网上挺多的，只通过简单的介绍，然后用个案例让大家快速了解。实例中会具体说明变量的定义，赋值，游标的使用，控制语句，循环语句的介绍。

1.创建存储过程。

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

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值  
OUT:该值可在存储过程内部被改变，并可返回  
INOUT:调用时指定，并且可被改变和返回

2.查看存储过程名称

SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名称';

3.调用存储过程

CALL myproc()

4.删除存储过程

DROP PROCEDURE IF EXISTS myproc;

因为mysql中游标只能在存储过程和方法中使用，所以就直接通过案例介绍游标。

案例：该案例采用无参存储过程，有参的也挺简单，根据上面的介绍，对应实现就行，该存储过程主要就创建一个存储过程，用它做查询 修改等操作。

#检查该存储过程是否存在 存在就删除了再创建
    DROP PROCEDURE IF EXISTS processnames ;
    #创建存储过程
    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;
    #定义游标
    DECLARE nameCursor CURSOR FOR SELECT ParentId FROM datadictionary GROUP BY ParentId;
    #该sql语句作用是  在你遍历游标的时候  游标循环结束 就会执行这句话，并给done赋值为1 然后循环就会停止 但该语句不适用WHILE 循环语句。
    DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
    #查询一个表分组的总数量  并赋值给count变量
    SELECT count(*) into count FROM (SELECT ParentId FROM datadictionary GROUP BY ParentId) t1;
    #打开游标
    OPEN nameCursor;
    #循环体
        WHILE i<count DO
        #拿到游标指向的当前行的数据  并赋值给var_name变量
        FETCH nameCursor INTO var_name;
        #通过var_name变量进行相应的数据查询 把查询出来的数据通过into赋值给变量var_uuid
        SELECT uuid into var_uuid FROM datadictionary WHERE ID=var_name;
        #进行修改操作
        UPDATE datadictionary SET ParentId=var_uuid WHERE ParentId=var_name;
        #可以通过set 给变量赋值  这里用来记录循环体执行了多少次  看和游标遍历的条数是否一致
      SET i=i+1;
        #当i>count的时候循环体结束
      END WHILE;
        #关闭游标
        CLOSE nameCursor;
        #打印i 
        SELECT i;
        #查看修改后的数据
        SELECT * FROM datadictionary;
        #存储过程结束标志
    END;
    #调用存储过程
    CALL processnames()

案例2 添加操作，并且获取游标中的多列值

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关键字 在存储过程内部就是定义变量的。  
2.DECLARE nameCursor CURSOR FOR SELECT ParentId FROM datadictionary GROUP BY ParentId; 该语句用来定义游标，其中FOR后面跟随的是你的查询语句，把查询出来的结果赋值给了nameCursor这个游标  
2.1 遍历游标前一定要先打开游标 OPEN nameCursor;  
2.2 遍历结束 一定要关闭游标，CLOSE nameCursor;如果不关闭当存储过程执行完遇到最后一个END的时候也会自动关闭。  
3.DECLARE continue handler FOR SQLSTATE '02000' SET done = 1; 有很多人不知道这段是什么意思，我开始也不知道，首先本案例中这段话可以直接注释，没有作用，一般在没有条件体的循环体中使用，如：REPEAT statement\_list UNTIL search\_condition END REPEAT;循环体和LOOP 循环体使用的比较多。该句话的意思如注释所说，当你没有循环条件作为循环结束语句的时候，就可以用到上面DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;该句话的作用就是：当你游标在上述循环体中遍历结束的时候，就会执行该语句，让done =1；打个比方，假如你用的是REPEAT statement\_list UNTIL search\_condition END REPEAT;语句作为循环体，我们可写成 UNTIL done因为REPEAT循环体执行顺序是先执行一次sql语句流然后再进行UNTIL done进行判断，当游标遍历结束，done=1然后循环体结束。SQLSTATE '02000'只是一个标识，具体可以官网查询。  
然后再简单说下

label: LOOP
        statement_list
    
        IF done=1 THEN
            LEAVE label; 
        END IF; 
    END LOOP label;

其中statement\_list还是你需要执行的sql流，Loop和REPEAT有点像，都是先执行，再判断。我案例中是用的WHILE 该循环体是先判断条件，再执行，比较符合我当前的运用场景。  
4. FETCH nameCursor INTO var\_name;把当前游标指向的行赋值给变量，这里游标执行过程是顺序执行的，就相当于遍历数组，从第一个一直遍历到最后一个。  
5. 如果需要把查询出来的结果，赋值给一个变量，可以使用into如这样:SELECT uuid into var\_uuid FROM datadictionary WHERE ID=var\_name;  
6. 如果非查询赋值，可以用set如: SET i=i+1;  
7. 获取一行游标中的多列值，只需定义和列关联的变量，然后用into进行赋值 如例2：FETCH nameCuursor INTO userid,depatementid;