cursor 可以在存储过程,方法和触发器中创建。cursor用于一行一行的迭代的访问查询返回的结果。它不同与普通的sql 语句,一次将所有的结果返回。这样做有什么好处呢?方便你一行一行的操作你的结果啊!
1:cursor有三种类型的属性
Asensitive 不敏感性(不知道应该翻译成什么准确) Read only 只读 Non-scrollable 不可跳过,必须一行一行的处理
2:新建立一个cursor需要以下的语句
- Cursor declare
- Cursor open statement
- Cursor fetch statement
- Cursor close statement
3:下边来演示一个例子: 定义、打开、获取和关闭 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;
4:output
参考:http://www./sql/mysql-example/mySQL-cursor.shtml http://dev./doc/refman/5.0/en/cursors.html
|