(8.0.22)
https://dev.mysql.com/doc/refman/8.0/ja/cursors.html
暗黙カーソル、パラメータつきカーソルは不可
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
select * from tab1;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare done int default false;
declare col1, col2 int;
declare cur1 cursor for select * from tab1 order by col1;
declare continue handler for not found set done = true;
open cur1;
read_loop: loop
fetch cur1 into col1,col2;
if done then
leave read_loop;
end if;
select col1,col2;
end loop;
close cur1;
end//
delimiter ;
call proc1();
(19c)
暗黙カーソル、パラメータつきカーソルも可能
drop table tab1 purge;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
commit;
select * from tab1;
set serveroutput on
declare
cursor cur1 is select * from tab1 order by col1;
cursor cur2(vparam1 in number) is select * from tab1 where col1 = vparam1;
rtab1 cur1%rowtype;
begin
for c1 in cur1 loop
dbms_output.put_line(c1.col1||','||c1.col2);
end loop;
for c2 in cur2(1) loop
dbms_output.put_line(c2.col1||','||c2.col2);
end loop;
open cur1;
loop
fetch cur1 into rtab1;
exit when cur1%notfound;
dbms_output.put_line(rtab1.col1||','||rtab1.col2);
end loop;
close cur1;
end;
/
(13)
暗黙カーソル、パラメータつきカーソルも可能
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
select * from tab1;
do
language plpgsql
$$
declare
cur1 cursor for select * from tab1 order by col1;
cur2 cursor(vparam1 numeric) for select * from tab1 where col1 = vparam1;
rtab1 record;
begin
for c1 in cur1 loop
raise notice 'col1=%,col2=%', c1.col1,c1.col1;
end loop;
for c2 in cur2(1) loop
raise notice 'col1=%,col2=%', c2.col1,c2.col1;
end loop;
open cur1;
loop
fetch cur1 into rtab1;
if not found then
exit;
end if;
raise notice 'col1=%,col2=%', rtab1.col1,rtab1.col1;
end loop;
close cur1;
end;
$$
;
暗黙カーソル、パラメータつきカーソルは不可
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,100);
insert into tab1 values(2,200);
insert into tab1 values(3,300);
select * from tab1;
declare @col1 int;
declare @col2 int;
declare cur1 cursor for select * from tab1 order by col1;
open cur1;
fetch next from cur1 into @col1,@col2;
while @@fetch_status = 0
begin
print cast(@col1 as varchar) + ',' + cast(@col2 as varchar);
fetch next from cur1 into @col1,@col2;
end
close cur1;
deallocate cur1;
GO
