oracle数据库PL/SQL学习的例子

oracle记录的学习.
声明一个记录
begin
 declare
 type myrecord is record(
 id number,
 user_type varchar(20)
 );

 realrecord myrecord;
 begin
   select id,user_type into realrecord from base_user where user_name='zhangjiaxun';

   DBMS_OUTPUT.PUT_LINE(realrecord.id||','||realrecord.user_type);
 end;
 

end;

声明一个表的记录类型.

begin
 declare
 realrecord  base_user%rowtype;

 begin
   select * into realrecord from base_user where user_name='zhangjiaxun';

   DBMS_OUTPUT.PUT_LINE(realrecord.id||','||realrecord.user_type);
 end;
 

end;

以一个表的字段声明一个记录类型.
begin
 declare
 type myrecord is record
 (
   id base_user.id%type,
   user_type base_user.user_type%type
 );

 realrecord myrecord;
 begin
   select id,user_type into realrecord from base_user where user_name='zhangjiaxun';

   DBMS_OUTPUT.PUT_LINE(to_char(realrecord.id)||','||realrecord.user_type);
 end;
 

end;

游标的学习

begin
  declare
  srecord S%ROWTYPE;
  currentrow number;
  cursor scursor is select s1,s2 from S ; 
  begin
  open scursor;
  fetch scursor into srecord;

  while scursor%FOUND loop
     currentrow := scursor%rowcount;
     if(currentrow mod 2=0) then
        begin
        insert into a(a1,a2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then       
        rollback;
        end;       
        DBMS_OUTPUT.PUT_LINE('偶数');
     else
        begin
        insert into b(b1,b2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('奇数');
     end if;
     fetch scursor into srecord;
  end loop;
  close scursor;
  exception
  when others then
  begin
    if scursor%isopen then
     close scursor;
  end if;
     raise;
  end;
  end;
 
end;

for循环游标.

begin
  declare
  srecord S%ROWTYPE;
  currentrow number;
  cursor scursor is select s1,s2 from S ; 
  begin

    for srecord in scursor loop
     currentrow := scursor%rowcount;
     if(currentrow mod 2=0) then
        begin
        insert into a(a1,a2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then       
        rollback;
        end;       
        DBMS_OUTPUT.PUT_LINE('偶数');
     else
        begin
        insert into b(b1,b2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('奇数');
     end if;
     end loop;
  exception
  when others then
  begin
    if scursor%isopen then
     close scursor;
  end if;
     raise;
  end;
  end;
 
end;