openGauss每日一练第17天 |学习openGauss定义游标

一、学习目标

学习openGauss数据库创建表、插入记录、查询记录和删除表基本使用

二、课程作业

1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标

create schema charley;
create table charley.students(id int,name char(15));
insert into charley.students values (1,'Lucy'),(2,'Frank'),(3,'Lily'),(4,'Jane'),(5,'rose');
start transaction;
cursor cursor1 for select * from charley.students order by id;
select * from pg_cursors;
FETCH FORWARD 3 FROM cursor1;
MOVE FORWARD 2 FROM cursor1;

2.在系统视图pg_cursors中查看游标

select * from pg_cursors;
end;

3.创建一个使用游标的存储过程

create or replace procedure procedu_cursor11
as
    student_id    int;
    student_name varchar(15);
    cursor cursor11 is --cursor without args
        select id,name from charley.students order by 1;
begin
    if not cursor11%isopen then
        open cursor11;
    end if;
    loop
        fetch cursor11 into student_id , student_name;
		RAISE INFO 'student_name: %' ,student_name;
        exit when cursor11%notfound;
    end loop;
    if cursor11%isopen then
        close cursor11;
    end if;
end;
/
call procedu_cursor11();
drop  procedu_cursor11

4.清理数据

drop table charley.students;
drop schema charley cascade;