下面开始写一些在存储过程中常用到游标例子(常用的关系型数据库)
-- MySql
BEGIN
-- 1. 声明变量
DECLARE v_done boolean ;
DECLARE v_column1 varchar(100);
DECLARE v_column2 varchar(100);
-- 2. 声明游标cur1
DECLARE cur1 CURSOR FOR
SELECT [column1(varchar(100)),column2(varchar(100)),......] FROM [table_name];
-- 3. 遍历结束后赋值v_done为true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = true;
-- 4. 打开游标
OPEN cur1;
LOOP1:LOOP
-- 5. 遍历结果集
FETCH cur1 INTO v_column1, v_column2;
IF v_done THEN
LEAVE LOOP1;
END IF;
-- 6. 这里处理业务逻辑
END LOOP;
-- 7. 关闭游标
CLOSE cur1;
END;
-- DB2
BEGIN
-- 1. 声明变量
DECLARE SQLCODE integer ; --系统SQL出错编码
DECLARE v_column1 varchar(100);
DECLARE v_column2 varchar(100);
-- 2. 声明游标cur1
DECLARE cur1 CURSOR FOR
SELECT [column1(varchar(100)),column2(varchar(100)),......] FROM [table_name];
-- 3. 打开游标
OPEN cur1;
-- 4. 遍历结果集
FETCH cur1 INTO v_column1, v_column2;
WHILE (SQLCODE = 0) DO
-- 5. 这里处理业务逻辑
FETCH cur1 INTO v_column1, v_column2;
END WHILE;
-- 6. 关闭游标
CLOSE cur1;
END;
-- SqlServer
-- 1.申明一个游标
cursor cur1 is
select * from a;
-- 2.打开游标
open cur1 ;
-- 3.循环游标
FETCH NEXT FROM cur1 INTO @v_assesscell_id ,@v_assesscell_name;
WHILE @@FETCH_STATUS =0
BEGIN
BEGIN
/* 这里是业务逻辑 */
END
FETCH NEXT FROM cur1 INTO @v_assesscell_id ,@v_assesscell_name;
END
-- 4.关闭游标
CLOSE cur1 ;
-- 5.释放资源
DEALLOCATE cur1 ;
-- Oracle
-- 1. 声明游标cur1
DECLARE CURSOR cur1 IS
SELECT [column1(varchar(100)),column2(varchar(100)),......] FROM [table_name];
-- 2. 声明变量
v_row cur%ROWTYPE; -- 行变量
BEGIN
-- 3. 打开游标
OPEN cur1;
LOOP
-- 4. 遍历结果集
FETCH cur1 INTO v_row;
-- 5. 遍历结束后退出循环
EXIT WHEN cur1%NOTFOUND;
-- 6. 这里处理业务逻辑
END LOOP;
-- 7. 关闭游标
CLOSE cur1;
END;