使用游标打印报表3种方法

1、游标常量

declare
  v_sname varchar(10);
  v_sno   varch：ar(10);

  cursor c_students IS
    select sno, sname from students order by 1;
begin
  open c_students;
  loop
    fetch c_students
      into v_sno, v_sname;
    exit when c_students%notfound;  --放在一切动作之前
    dbms_output.put_line(v_sno || '  ' || v_sname);
  end loop;
  close c_students;
end;

/

2、游标变量

 set serveroutput on
 set pagesize 0
 variable c_students refcursor
 begin
 open :c_students for select sno,sname from students order by 1;
 end;
 /
 print c_students（打印完成后自动关闭游标）

3、for隐式游标（自动打开和关闭）
begin
  for cur in (select sno,sname from students order by 1) loop
    dbms_output.put_line(cur.sno || '  ' || cur.sname);
  end loop;
end;

4、游标打开瞬间决定了游标的内容

declare
  v_cnt   number := 100004;
  v_sname varchar(10);
  v_sno   varchar(10);

  cursor c_students IS
    select sno, sname from students where sno < v_cnt;
begin
  v_cnt := 100005;
  open c_students;
  loop
    v_cnt := 100006;
    fetch c_students
      into v_sno, v_sname;
    exit when c_students%notfound;
    dbms_output.put_line(v_sno || '  ' || v_sname);
  end loop;
  v_cnt := 100007;
  close c_students;
end;

注：student ddl

create table STUDENTS
(
  SNO    NUMBER(6) not null,
  SNAME  CHAR(8) not null,
  AGE    NUMBER(3),
  SEX    CHAR(4),
  BPLACE CHAR(20)
);

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100008, '马明    ', 26, '男  ', '河北                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100009, '曲玲玲  ', 28, '女  ', '湖北                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100010, '李双    ', 24, '女  ', '湖南                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100011, '蔡明    ', 38, '男  ', '湖北                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100012, '张美丽  ', 25, '女  ', '');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100001, '李冰    ', 23, '女  ', '山东                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100003, '程东    ', 24, '男  ', '河南                ');

insert into students (SNO

, SNAME, AGE, SEX, BPLACE)
values (100004, '刘静    ', 20, '女  ', '乌鲁木齐            ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100006, '张萍萍  ', 18, '女  ', '广西                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100007, '李楠    ', 25, '女  ', '四川                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100005, '张波    ', 30, '男  ', '陕西                ');

insert into students (SNO, SNAME, AGE, SEX, BPLACE)
values (100002, '马季    ', 21, '男  ', '江西                ');