What is the use of mysql cursor?

In mysql, cursors function as pointers and are mainly used to traverse the record result set returned by querying the database in order to perform corresponding operations. A cursor is actually a mechanism that extracts one record at a time from a result set that contains multiple data records. The relational database management system is essentially set-oriented. In MySQL, there is no expression form to describe a single record in the table, unless the WHERE clause is used to limit only one record to be selected; so sometimes a cursor must be used to select a single record. data processing.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL Cursor (Cursor)

A cursor is actually a method that can extract one record at a time from a result set that includes multiple data records. Mechanisms.

The cursor acts as a pointer.

Although the cursor can traverse all rows in the result, it only points to one row at a time.

The function of the cursor is to traverse the records returned by the query database in order to perform corresponding operations.

The relational database management system is essentially set-oriented. In MySQL, there is no expression form to describe a single record in the table, unless the WHERE clause is used to limit only one record to be selected. So sometimes we must use cursors to process data on a single record.

Generally, the cursor is used to locate a certain row of the result set for data modification.

Usage of cursor

1. Declare a cursor: declare cursor name CURSOR for table;(table here It can be any collection you query)

2. Open the defined cursor: open cursor name;

3. Get the next row of data:FETCH cursor Name into testrangeid,versionid;

4. Statements that need to be executed (add, delete, modify, check): This depends on the specific situation

5. Release the cursor:CLOSE Cursor name ;

Note: Each sentence of mysql stored procedure must end with;, and the temporary fields used need to be declared before defining the cursor.

Example

-  BEGIN  
  --定义变量  declare testrangeid BIGINT;  
declare versionid BIGINT;   
declare done int;  
--创建游标，并存储数据  declare cur_test CURSOR for   
   select id as testrangeid,version_id as versionid from tp_testrange;  
--游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  open cur_test;  
--执行循环    posLoop:LOOP  
--判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
--取游标中的值  
    FETCH  cur_test into testrangeid,versionid;  
--执行更新操作  
    update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  
  END LOOP posLoop;  
--释放游标  CLOSE cur_test;  
  
END  -

Example 2:

We are now going to use a stored procedure to make a function to count the total inventory of iPhones What is the value and output the total to the console.

--在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。  delimiter //  drop procedure if exists StatisticStore;  
CREATE PROCEDURE StatisticStore()  
BEGIN  
    --创建接收游标数据的变量  
    declare c int;  
    declare n varchar(20);  
    --创建总数变量  
    declare total int default 0;  
    --创建结束标志变量  
    declare done int default false;  
    --创建游标  
    declare cur cursor for select name,count from store where name = 'iphone';  
    --指定游标循环结束时的返回值  
    declare continue HANDLER for not found set done = true;  
    --设置初始值  
    set total = 0;  
    --打开游标  
    open cur;  
    --开始循环游标里的数据      read_loop:loop  
    --根据游标当前指向的一条数据  
    fetch cur into n,c;  
    --判断游标的循环是否结束  
    if done then  
        leave read_loop;    --跳出游标循环  
    end if;  
    --获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  
    set total = total + c;  
    --结束游标循环  
    end loop;  
    --关闭游标  
    close cur;  
  
    --输出结果  
    select total;  
END;  
--调用存储过程  call StatisticStore();

fetch is to obtain the data row currently pointed by the cursor and point the pointer to the next row. Continuing execution when the cursor is already pointing to the last row will cause cursor overflow.
When using a loop cursor, it will not monitor whether it has reached the last piece of data. Writing the following code will cause an infinite loop;

read_loop:loop  
fetch cur into n,c;  
set total = total+c;  
end loop;

In MySql, when the cursor overflows MySQL's predefined NOT FOUND error will be triggered, so the following code is used above to specify a continue event when a not found error is raised, and to modify the value of the done variable when this event occurs.

declare continue HANDLER for not found set done = true;

So the following code is added to the loop:

--判断游标的循环是否结束  if done then  
    leave read_loop;    --跳出游标循环  end if;

If the value of done is true, end the loop. Continue to execute the following code

Usage methods

There are three ways to use cursors:

• The first is the above implementation, using loop loop;

• The second way is as follows, using a while loop:

drop procedure if exists StatisticStore1;  
CREATE PROCEDURE StatisticStore1()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    fetch cur into n,c;  
    while(not done) do  
        set total = total + c;  
        fetch cur into n,c;  
    end while;  
      
    close cur;  
    select total;  
END;  
  
call StatisticStore1();

The third way is to use repeat execution:

drop procedure if exists StatisticStore2;  
CREATE PROCEDURE StatisticStore2()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    repeat  
    fetch cur into n,c;  
    if not done then  
        set total = total + c;  
    end if;  
    until done end repeat;  
    close cur;  
    select total;  
END;  
  
call StatisticStore2();

Cursor nesting

In mysql, each begin end block is an independent scope area. Since the same error event in MySql can only be defined once, if it is defined multiple times, When compiling, it will prompt Duplicate handler declared in the same block.

drop procedure if exists StatisticStore3;  
CREATE PROCEDURE StatisticStore3()  
BEGIN  
    declare _n varchar(20);  
    declare done int default false;  
    declare cur cursor for select name from store group by name;  
    declare continue HANDLER for not found set done = true;  
    open cur;  
    read_loop:loop  
    fetch cur into _n;  
    if done then  
        leave read_loop;  
    end if;  
    begin  
        declare c int;  
        declare n varchar(20);  
        declare total int default 0;  
        declare done int default false;  
        declare cur cursor for select name,count from store where name = 'iphone';  
        declare continue HANDLER for not found set done = true;  
        set total = 0;  
        open cur;  
        iphone_loop:loop  
        fetch cur into n,c;  
        if done then  
            leave iphone_loop;  
        end if;  
        set total = total + c;  
        end loop;  
        close cur;  
        select _n,n,total;  
    end;  
    begin  
            declare c int;  
            declare n varchar(20);  
            declare total int default 0;  
            declare done int default false;  
            declare cur cursor for select name,count from store where name = 'android';  
            declare continue HANDLER for not found set done = true;  
            set total = 0;  
            open cur;  
            android_loop:loop  
            fetch cur into n,c;  
            if done then  
                leave android_loop;  
            end if;  
            set total = total + c;  
            end loop;  
            close cur;  
        select _n,n,total;  
    end;  
    begin  
      
    end;  
    end loop;  
    close cur;  
END;  
  
call StatisticStore3();

The above is to implement a nested loop. Of course, this example is far-fetched. Just take a look.

Dynamic SQL

Mysql supports dynamic SQL functions

set @sqlStr='select * from table where condition1 = ?';  
prepare s1 for @sqlStr;  
--如果有多个参数用逗号分隔  execute s1 using @condition1;  
--手工释放，或者是 connection 关闭时， server 自动回收  deallocate prepare s1;

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the use of mysql cursor?. For more information, please follow other related articles on the PHP Chinese website!