CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
20.2.11.1.声明光标
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。
SELECT语句不能有INTO子句。
20.2.11.2. 光标OPEN语句
OPEN cursor_name
这个语句打开先前声明的光标。
20.2.11.3. 光标FETCH语句
FETCH cursor_name INTO var_name [, var_name] ...
这个语句用指定的打开光标读取下一行(如果有下一行的话),并且前进光标指针。
20.2.11.4. 光标CLOSE语句
CLOSE cursor_name
这个语句关闭先前打开的光标。
create procedure mypro( INOUT name )
as begin
declare tmp boolen default true;
declare mycursor cursor for
select name from T ;
declare continue handler for not found
……
[/Quote]
create procedure mypro( INOUT name )
as begin
declare tmp boolen default true;
declare mycursor cursor for
select name from T ;
declare continue handler for not found
set found=false ;
open mycursor;
fetch mycursor into name;
while found
do
fetch mycursor into name;
end while
close mycursor
end