oracle中的游标学习

--1 使用普通变量 获取游标信息
set serverout on;

declare cursor cu_patientinfo is
  select  hpb.patient_name,to_char(hpb.birthday),hpb.sex,to_char(hpv.datetime_in) from his_patient_base hpb  inner  join his_patient_visit hpv on hpv.patient_id=hpb.patient_id where rownum<100 ;
  patient_name varchar2(20);
  birthday  varchar2(40) ;
  sex varchar2(8);
  datetime_in varchar2(40);
  begin
     open cu_patientinfo;
     fetch cu_patientinfo into patient_name,birthday,sex,datetime_in ;
     while cu_patientinfo%found loop
       dbms_output.put_line('我叫'||patient_name||':'||birthday||'出生,性别:'||sex||'生病时间为 :'||datetime_in);
       fetch cu_patientinfo into patient_name,birthday,sex,datetime_in;
       end loop;
       close cu_patientinfo;
       end;
  --2 使用行变量
  declare cursor cur_return is
  select * from his_patient_base where rownum<10;
  infoa his_patient_base%rowtype;
  begin
    open cur_return ;
    fetch cur_return into infoa ;
    while cur_return%found loop
     
      dbms_output.put_line(infoa.patient_name);
      fetch cur_return into infoa;
      end loop;
      close cur_return;
      end;
     
---3 有参数的游标
declare cursor cur_return(rowopertor number) is
select * from his_patient_base where rownum<rowopertor;
  employees his_patient_base%rowtype;
  i number :=0;
  begin
    open cur_return(4);
    fetch cur_return into employees ;
    while cur_return%found loop
     i := i+1;
      dbms_output.put_line(employees.patient_name||i);
      fetch cur_return into employees;
      end loop;
      close cur_return;
      end;
  ---------隐式游标的默认为内置游标
  --案例1
  begin

    if sql%isopen then
      dbms_output.put_line('sql游标一打开');
      else
        dbms_output.put_line('sql游标未打开');
        end if;
        dbms_output.put_line('游标获取记录数'||sql%rowcount);
        end;
 
    ---------有影响的隐式游标
declare patient_count varchar2(20) ;
begin
 
  select patient_id into patient_count from his_patient_base  where rownum<2;
 
  if sql%isopen
     then dbms_output.put_line('游标一打开');
    
  else
     dbms_output.put_line('游标未开');
end if;
dbms_output.put_line('返回受影响的行数'||sql%rowcount);
end ; 
---------------
---------------cursor for(隐式游标) 游标的使用
--不用声明游标变量和打开
begin
  for employee in (select * from his_patient_visit where rownum<5)loop
    dbms_output.put_line(employee.patient_id|| ':'||employee.datetime_in);
    end loop;
    end;
--------
显示游标麻烦但 灵活 ;隐式游标简练 使用于简单的;

动态游标:       强类型游标和弱类型游标;
强类型游标的定义: 在使用之前未指定sql查询定义;但类型以确定;
弱类型游标 : 当不确定返回类型是选择

语法: type name is ref cousor;