游标、过程、函数、包

 一）游标：
            1）每次声明、打开、推进、关闭游标
declare
cursor c is select .........;--声明
begin
open c;--打开
loop
exit when..........;
fetch c into .........;--推进
......
.....
end loop;
close c;--关闭
end;
             2）使用游标变量更加简洁
declare
cursor c is select..........;声明
begin
for rec_c in c loop      --打开、推进、关闭 全包括
exit when......
end loop;
end;

             3)动态游标；

             declare
type c_ref is ref cursor;    ---------------------声明动态游标
v_c_ref c_ref;                  -----------------------定义一个动态游标
type rec is record(          ------------------------声明记录
id   sun_department.id%type,                      
department  sun_department.department%type);
v_rec rec;                              --------------------定义一个记录；
begin
open v_c_ref for                          --------------------打开动态游标并付集合值
select id,department from sun_department where id<100;
fetch v_c_ref into v_rec;                                 -------------将游标起到记录中
while v_c_ref%found loop                               ----------------------只要游标中有值就一直输出；
dbms_output.put_line(v_rec.id||'   '||v_rec.department);
fetch  v_c_ref into v_rec;                                 ---------------------游标的取出值放到记录中；
end loop;                                                 
close v_c_ref;
end;

 

二）过程

  create or replace  procedure     test_p(id in number,name  out  varchar2)----参数in  out    in out

    is

      i number:=0;                                  -----------------------------------------------无declare   为局部变量；

      cursor  c is  select  sun.name from sun where sun.id=id;

      begin                                               

      for v_c in c                                     ----------------------------------------------------------游标变量；

      loop

         name:=v_c.name;

          -- dbms_output.put_line('name'||name);  

      end loop;

       end test_p;                                         

三）函数

       create  or replace function test_f(id in number,name out varchar2)  

         return   number  is                                       ----------------------------------------------return  类型

           i   number:=0; 

            val   number;

            cursor    c   is  select s.id    from sun.department  s   where id<10 for update of id;

           begin

              open c;

               loop

               fetch c into val;

                if (c%rowcount<>0)then

                update   sun_department set   id:=100  where   current of  c;

               else

                    update   sun_department set   id:=1 where current of   c;

                end  if;

                  i:=c%rowcount

                end loop;

end;

 

 

          end;

 

四）包

     
create or replace package pack_test        --package specification start--
is
   type get_test_cursor is ref cursor;      --定义动态游标
   function getcur return get_test_cursor;  --函数声明【函数返回类型为动态游标】
end pack_test;                            
/                                          --package specification  end--

create or replace package body pack_test   --package body start --
is
  function getcur return get_test_cursor
  is
    mycur get_test_cursor;                 --用动态游标定义变量
  begin
    open mycur                             --打开并与具体数据关联
    for select * from  sun_department;
    return mycur;                          --返回游标
  end getcur;
end pack_test;
/                                          --package body end --

declare                                  -- package test start --
    testcur pack_test.get_test_cursor;
    temp  sun_department%rowtype;                    --记录变量定义
  begin
    testcur := pack_test.getcur();       --方法调用
    loop
      fetch testcur  into temp;
         dbms_output.put_line(temp.id || ' : ' ||  temp.department);
      exit when testcur%notfound;
    end loop;
    close testcur;
end;
/                                        -- package test end --