SQL 游标练习

游标练习
--学习内容:游标


 ---创建游标cursor name is (列表值或单个值)

 ----例如cursor jacky is select * from jacky_class where age > 50;

 
 --练习

declare
ck_age jacky_class.age%type;
cursor jacky_cl is select * from jacky_class where age > ck_age;
cur_record jacky_class%rowtype;
begin
   ck_age := 50;
   open jacky_cl;
   fetch jacky_cl into cur_record;
   
   dbms_output.put_line(cur_record.name || '\n' || '哈哈哈');
   dbms_output.put_line(cur_record.native || '\n' || '哈哈哈');
   
   close jacky_cl;
end;

--成功输出,不过只有一行值,但事实上大于50岁的人,有3个.


declare
ck_age jacky_class.age%type;
cursor jacky_cl is select * from jacky_class where age > ck_age;
cur_record jacky_class%rowtype;
begin
   ck_age := 50;
   open jacky_cl;
   fetch jacky_cl into cur_record;
   --取出jacky_cl游标的第一行的值,并赋给cur_record,把游标指向下一个行;

   while jacky_cl%found
   --当上一个fetch jacky为真,则循环

   --当某条件为真时,循环.

        loop
           
          dbms_output.put_line(cur_record.name || ' ' || cur_record.native);
          
     fetch jacky_cl into cur_record;
     --取出jacky_cl游标的第一行的值,并赋给cur_record,把游标指向下一个行;

   end loop;
   close jacky_cl;
end;

--成功

--不过个人感觉和其它的循环搭配比较好.

---用loop

      --循环体

      --exit when (条件)

      --end loop;来试试

declare
   cursor jacky_cur is select * from jacky_class where age > 36;
   cur_record jacky_class%rowtype;
begin
   open jacky_cur;
   loop
         fetch jacky_cur into cur_record;
         dbms_output.put_line('名字:' || cur_record.name);
      exit when not jacky_cur%found;
      --假(假)=>真 负负得正.

      --exit when的意思是当某条件为真时退出

    end loop;
end;

---也成功



--失败

declare
   ck_age jacky_class.age%type;
   cursor jacky_cur is select * from jacky_class where age > ck_age;
   cur_record jacky_class%rowtype;
begin
   ck_age := 30;
   open jacky_cur;
   for cur_record in jacky_cur
      loop
    dbms_output.put_line('名字:' || cur_record.name);
   end loop;
end;
--实验

--试了好多方法不成功.





--成功

declare
  cursor jacky_cur(ck_age jacky_class.age%type) is
    select * from jacky_class where age > ck_age;
begin
  for cur_record in jacky_cur(30) loop
  --为什么cur_record不用申明,这是什么东西?

    dbms_output.put_line(to_char(cur_record.name || ' ' || cur_record.age));
    --为什么要用to_char??游标结构是什么?访问机制又是什么?

    --to_char可有可无,for in的作用就是便利

    --dbms_output.put_line(to_char(cursorrecord.deptno || ' ' || cursorrecord.ename));

  end loop;
end;