学习游标

更新数据

declare
  v_title teachers.title%type;
  cursor teachers_cur is
    select title from teachers for update;
begin
  open teachers_cur;
  loop
    fetch teachers_cur
      into v_title;
    exit when teachers_cur%notfound;
    case
      when v_title = '教授' then
        update teachers
           set wage = 1.1 * wage
         where current of teachers_cur;
      when v_title = '高工' or v_title = '副教授' then
        update teachers
           set wage = 1.05 * wage
         where current of teachers_cur;
      else
        update teachers
           set wage = wage + 100
         where current of teachers_cur;
    end case;
  end loop;
  close teachers_cur;
end;

输出

declare
  v_specialty students.specialty%type;
  v_sname     students.name%type;
  v_dob       students.dob%type;
  cursor students_cur is
    select name, dob from students where specialty = v_specialty;
begin
  v_specialty := '&specoalty';
  open students_cur;
  dbms_output.put_line('学生姓名 出生日期');
  loop
    fetch students_cur
      into v_sname, v_dob;
    exit when students_cur%notfound;
    dbms_output.put_line(v_sname || '  ' || v_dob);
  end loop;
  close students_cur;
end;

游标for循环

方式一：

declare
  v_specialty students.specialty%type;
  cursor students_cur is
    select name, dob from students where specialty = v_specialty;
begin
  v_specialty := '&specialty';
  dbms_output.put_line('序号 学生姓名 出生日期');
  for students_record in students_cur loop
    dbms_output.put_line(students_cur%rowcount || '  '||
                         students_record.name || '  '|| students_record.dob);
  end loop;
end;

方式二

declare
  v_specialty students.specialty%type;
  cursor students_cur is
    select name, dob from students where specialty = v_specialty;
begin
  v_specialty := '&specialty';
  dbms_output.put_line('序号 学生姓名 出生日期');
  for students_record in (select name, dob
                            from students
                           where specialty = v_specialty) loop
    dbms_output.put_line(students_cur%rowcount || '  ' ||
                         students_record.name || '  ' ||
                         students_record.dob);
  end loop;
end;

参数游标

declare
  v_dob   students.dob%type;
  v_sname students.name%type;
  cursor students_cur(v_specialty students.specialty%type) is
    select name, dob from students where specialty = v_specialty;
begin
  open students_cur('自动化');
  while students_cur%found loop
    fetch students_cur
      into v_sname, v_dob;
    dbms_output.put_line(v_sname || ' ' || v_dob);
  end loop;
end;