如何在MySQL中使用游标 [英] How to use Cursor in MySQL

问题描述

如何在MySQL中了解游标.....

How to know Cursor in MySQL .....

推荐答案

1.游标声明:要声明游标，必须使用DECLARE语句.

语法:

1. Cursor declaration: To declare a cursor you must use the DECLARE statement.

Syntax:

DECLARE cursor_name cursor for select_statement;

如果要使用多个游标，则游标名称必须具有唯一的名称，并且每个游标都具有指定的块.

2.游标open语句:要打开游标，必须使用OPEN语句.如果要从中获取行，则必须打开游标.

语法:

If you want to use the multiple cursor then the cursor name must have an unique name and each cursor have specified block.

2. Cursor open statement: To open the cursor you must use the OPEN statement. If you want to fetch rows from it then you must open the cursor.

Syntax:

OPEN cursor_name;  

3.游标获取语句:如果要从游标中检索下一行并将游标移至下一行，则需要获取游标.

语法:

3. Cursor fetch statement: If you want to retrieve next row from the cursor and move the cursor to next row then you need to fetch the cursor.

Syntax:

FETCH cursor_name INTO var_name;

如果存在一行，则上面的语句将获取下一行，并且光标指针将移至下一行.如果没有剩余数据，则会发生SQLSTATE值为02000的无数据条件".

4.游标close语句:此语句用于关闭打开的游标.

语法:

If a row exists, then the above statement fetches the next row and cursor pointer moves ahead to the next row. If no more data left "no data condition" with SQLSTATE value 02000 occurs.

4. Cursor close statement: This statement is used to close the opened cursor.

Syntax:

CLOSE cursor_name;  

光标示例:

学生桌:

Cursor Example:

student table:

ID	StudentName	address
1	Vinod	        Rohini,Delhi
2	Ravi	        Lucknow,Up
NULL	NULL	        NULL

声明，打开，获取并关闭游标:

分隔符//

Declare, open, fetch and close the Cursor:

DELIMITER //

CREATE FUNCTION student_list() RETURNS VARCHAR(255) 
   BEGIN    
   DECLARE record_not_found INTEGER DEFAULT 0; 
   DECLARE student_name VARCHAR(50) DEFAULT ""; 
   DECLARE stu_list VARCHAR(255) DEFAULT ""; 
   DECLARE my_cursor CURSOR FOR SELECT studentName FROM student;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;     
   OPEN my_cursor;    
   allStudents: LOOP 
   FETCH my_cursor INTO student_name; 
   IF record_not_found THEN 
   LEAVE allStudents; 
   END IF; 
   SET stu_list = CONCAT(stu_list,", ",student_name); 
   END LOOP allStudents;    
   CLOSE my_cursor;    
   RETURN SUBSTR(stu_list,3); 
   END 

//
定界符;

//
DELIMITER ;

SELECT student_list() AS Cities;
DROP FUNCTION student_list;

输出:

Output:

ID	StudentName	address
1	Vinod	        Rohini,Delhi

这篇关于如何在MySQL中使用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！