minzu_光标.txt

orale培训教材包括了所有的sql说明和实例

----------------------------------minzu--------
drop table man;

drop table minzu;

--增加人口总数:rekou 
create table minzu
(mincheng  varchar2(2) primary key,
renkou number(10,0));

insert into minzu values('汉',100000);
insert into minzu values('满',100);




--注意：此时不使用外键!
create table man 
(name varchar2(10),
minzu varchar2(2));

insert into man values('张飞','汉');
insert into man values('关羽','汉');
insert into man values('康熙','满');
---------------------------------------------
--用参数光标给出man表中对应的汉族人的名称
declare
        cursor c_man(p_minzu  varchar2) is select * from man where minzu=p_minzu;
        a varchar2(2);
        r_man man%rowtype;

begin 
        a :='汉';

        open c_man(a);

        loop 
                fetch c_man into r_man ;
                exit when c_man%notfound;
                dbms_output.put_line(r_man.name);
        end loop;
        close c_man;
end;

------------------------------------------------------------------
--用参数光标,结合光标给出man表中对应的各族人的名称

declare 
  cursor c_minzu is select * from minzu;
  cursor c_man(p_minzu varchar2) is select * from man where minzu=p_minzu;
  r_minzu minzu%rowtype;
  r_man man%rowtype;

begin
  
  open c_minzu;

  loop 
    fetch c_minzu into r_minzu;

    if c_minzu%notfound then 
      exit;
    end if;
    
    --以c_minzu光标弹出结果为参数，打开c_man光标。
    open c_man(r_minzu.mincheng);

    loop 

      fetch c_man into r_man;
      if c_man%notfound then
        exit;
      end if;

      dbms_output.put_line(r_man.minzu || '  ' || r_man.name);
    end loop;

    --注意：打开的光标必须在循环退出之后，关闭。
    close c_man;

  end loop;
  close c_minzu;
end;




----------------------------------------------------
declare
        cursor c_man(p_minzu out varchar2) is select * from man where minzu=p_minzu;
        a varchar2(2);
        r_man man%rowtype;

begin 
        a :='汉';

        open c_man(a);

        loop 
                fetch c_man into r_man ;
                exit when c_man%notfound;
                dbms_output.put_line(r_man.name);
        end loop;
        close c_man;
end;