oracle 游标基础

一、显示游标
  1、声明游标
    (1)声明一个游标用来查询表students中的所有学生姓名
    
    declare cursor cu_student_name is
            select student_name from students;
    声明一个游标的时候一般还应当声明一个相应的变量，专门用来获取游标中的数据
    
    (2)声明游标和游标对应的局部变量
        declare cursor cu_student_name is
            select student_name from students;
            student_name varchar2(32);
        游标中有多列，通常为每列声明一个变量
    (3)在游标内部声明变量
        declare cursor cu_student_id_name is
            select student_id,student_name from students;
            student_id varchar2;
            student_name varchar2(32);
    (4)使用列类型声明变量
        declare cursor cu_student_id_name is
            select student_id,student_name from students;
            student_id students.student_id%type;
            student_name students.student_name%type;
    (5)声明行类型变量
        declare cursor cu_student is
            select * from students;
            student students%rowtype;
    (6)声明带参数的游标
        declare cursor cu_student(minAge in number,maxAge in number) is
            select * from students where student_age >=minAge and student_age <=maxAge;
            student students%rowtype;
            
  2、使用游标
    (1)使用变量获取游标信息
       declare cursor cu_student_id_name is
           select student_id,student_name from students;
           
           student_id students.student_id%type;
           student_name students.student_name%type;
           
           begin
             open cu_student_id_name;
             fetch cu_student_id_name into student_id,student_name;
             
             while cu_student_id_name %found loop
               dbms_output.put_line(student_id || ':' || student_name);
               fetch cu_student_id_name into student_id,student_name;
             end loop;
             
             close cu_student_id_name;
           end;
       说明: open cu_student_id_name 用于打开游标
             fetch cu_student_id_name into student_id,student_name：从游标中获取数据
             while cu_student_id_name %found loop 用于循环处理
             cu_student_id_name%found返回一个布尔值，即游标的当前位置能否获得记录，
             每次对游标使用fetch动作时都会更新%found 属性；
             dbms_output.put_line(student_id || ':' || student_name);用于输出当前变量
             student_id和student_name的信息；
             fetch cu_student_id_name into student_id,student_name;将游标位置向下移动，
             并获取移动后的记录
     (2)利用行类型获取游标信息
        declare cursor cu_student is
           select * from students;
           student students%rowtype;
           begin
             open cu_student;
             fetch cu_student into student;
             
             while cu_student%found loop
               dbms_output.put_line(student.student_id || '-' || student.student_name);
               fetch cu_student into student;
             end loop;
             close cu_student;
           end;
     (3)使用带有参数的参数
        declare cursor cu_student(minAge in number,maxAge in number) is
          select * from students where student_age >= minAge and student_age <=maxAge;
          student students%rowtype;
          
          begin
            open cu_student(19,20);
            fetch cu_student into student;
            
            while cu_student%found loop
              dbms_output.put_line(student.student_name || ':' || student.student_age || '岁');
              fetch cu_student into student;
            end loop;
         end;
二、隐式游标
	隐式 游标有两种：使用oracle预定义的名为sql 的隐式游标 和使用 cursor for loop 进行循环的隐式游标。
   1、 sql 的隐式游标。 oracle为pl/sql的会话都定义了一个名为 sql的游标变量 。可以在plsql中直接调用该变量
      (1)在plsql中直接调用游标变量sql
          begin
            if sql%rowcount>0 then
              dbms_output.put_line('sql 游标变量的 rowcount 属性大于 0');
            end if;
          end;
          
          说明 : if sql%rowcount >0 then 用于获得游标变量sql的rowcount属性，即游标的行数。
          根据游标变量是否能够抓取记录进行条件判断，并输出响应的提示
      (2)oravle 自动更新游标变量
          begin
            update students set student_age = student_age - 1;
            dbms_output.put_line('共更新了' || sql%rowcount || '条记录');
          end;
  2、sql隐式游标
    -- 尝试控制sql游标变量(错误示例)
    declare
      student students%rowtype;
      begin
        update students set student_age = student_age;
        fetch sql into student;
        while sql%found loop
              dbms_output.put_line(student.student_id || ':' || student.student_name
              || ' ' || student.student.student_age
              );
        fetch sql into student;
        end loop;
       end;
    游标变量sql并不能使用fetch命令显示操作。
    sql隐式变量只能用于更新删除等操作之后的属性信息获取
  3、cursor for 游标
    利用该游标可以像使用普通循环语句一样来处理select语句所获得的每一天记录
    （1）演示 cursor for 游标的使用
          begin
            for student in (select * from students) loop
              dbms_output.put_line(
                student.student_id || ':' || student.student_name || '--'
                || student.student_age || '岁'
              );
            end loop;
          end;
          代码说明
          for student in (select * from students) loop 用于创建循环，
          student 为循环中的临时变量 像for(i=0;i<count;i++)中的i一样；
          select * from students 用于获得要处理的结果集；
           dbms_output.put_line(); 用于输出学生的基本信息,
          student.student_id 用户获得student变量的student_id的属性
        
     （2）需求：
        父表c_louyu，子表c_room 关联字段c_louyu中的lh和c_room的louhao
        把子表中的louhao换成对应的父表对应的id
        begin 
            for p_louyu in (select * from c_louyu) loop
              dbms_output.put_line(
               'update c_room set louhao = ''' || p_louyu.id || ''' where louhao = ''' || p_louyu.lh || ''';'
              );
            end loop;
        end;

        create table C_LOUYU
        (
          id  CHAR(32),
          lh  VARCHAR2(20),
          glz VARCHAR2(32),
          mc  VARCHAR2(50),
          bz  VARCHAR2(50)
        );
        create table C_ROOM
        (
          id        CHAR(32),
          louhao    VARCHAR2(32),
          fanghao   VARCHAR2(32),
          bumen     VARCHAR2(32),
          shuliang  VARCHAR2(32),
          louceng   VARCHAR2(32),
          dianhua   VARCHAR2(32),
          bukeyong  VARCHAR2(32),
          chaoxiang VARCHAR2(32)
        );
      
   游标属性
   常用 found、not found、rowcount、isopen
   利用4中属性坚实游标状态
      declare
        cursor students is select * from students;
        student students%rowtype;
        begin
          if students%isopen then
             dbms_output.put_line('声明游标之后，游标已经打开');
          else
             dbms_output.put_line('声明游标之后，游标尚未打开');
          end if;
          
          open students;
          if students%isopen then
             dbms_output.put_line('执行open之后，游标已经打开');
          else
             dbms_output.put_line('执行open之后，游标尚未打开');
          end if;
          
          fetch students into student;
             dbms_output.put_line('第一次执行fetch之后，游标rowcount 属性值为' || students%rowcount);
          if students%found then
             dbms_output.put_line('第一次执行fetch之后，游标 found 属性值为 :true');
          else
             dbms_output.put_line('第一次执行fetch之后，游标 notfound 属性值为 :true');
          end if;
          dbms_output.put_line('-------------------------------------');
          
          loop
            if students%found then
              dbms_output.put_line('循环执行。。游标的rowcount 属性值为：' || students%rowcount);
              fetch students into student;
            else
              dbms_output.put_line('游标的 found 属性为 ' || students%found);
              dbms_output.put_line('完毕。。游标的rowcount 属性值为：' || students%rowcount);
              exit;
            end if;
          end loop;
          dbms_output.put_line('-------------------------------------');
          close students;
          if students%isopen then
            dbms_output.put_line('执行close 命令之后，游标任然打开');
          else
            dbms_output.put_line('执行close 命令之后，游标关闭');
          end if;
        end;

三、动态游标
	1、强类型动态游标
		 强类型动态游标：当游标声明时，虽然未设定其查询语句，但是已经指定了游标的返回类型
		 游标返回类型可以是oracle内置类型，也可以是自定义类型。
		 
		 声明一个强类型游标首先自定义一个 ref cursor 的游标类型 ， 然后利用该自定义类型，声明一个游标
		 eg:先需要一个打印学生信息的存储过程，用户可以向存储过程传递一个年龄参数，
		 如果传入的年龄小于等于0 则打印所有学生的信息，如果传入的参数大于0 ，则打印年龄与参数相同的学生信息
		 
		 eg; 演示如何利用强类型动态游标打印学生信息
		   create or replace procedure printStudents(in_age in number) as
			 begin
			   declare
				  type student_type is record(
					   id varchar2(32),
					   name varchar2(32),
					   age number
				  );
				  type students_type is ref cursor
					   return student_type;
				  
				  v_students students_type;
				  v_student student_type;
				
				  begin
					if in_age <=0 then
					  open v_students for
						   select * from students;
					else
					  open v_students for
						   select * from students where student_age = in_age;
					end if;
					
					fetch v_students into v_student;
					
					  while v_students%found loop
						dbms_output.put_line(v_student.id || ':' 
											|| v_student.name || ':'
											|| v_student.age || ':'
						);
						
						fetch v_students into v_student;
					  end loop;
					close v_students;
				   end;
			 end printStudents;
			 
		   说明：
		   declare type student_type is record(id varchar2(32),name varchar2(32),age number);
		   用于声明自定义类型student_type ，该类型的基类是一个record 类型，他包含3个属性，id,name.age。
		   
		   type students_type is ref cursor return student_type;用于声明自定义类型。
		   ref cursor 表明该自定义游标是一个动态游标，
		   return student_type ：该动态游标是强类型动态游标，返回结果集的类型是 student_type,
		   注：student_type是结果集类型，students_type是游标类型。
		   因为students_type 引用到了student_type ,所以首先声明student_type 然后声明students_type
		   二者顺序不可颠倒；
		   v_students students_type 用于声明变量 v_students， 该变量是一个动态游标；
		   v_student student_type 用于声明变量 v_student 该变量是一个 record 类型；
		   if in_age <=0 用于判断存储过程的传入参数是否为 0 ；
		   open v_students for select * from students where student_age = in_age; 
		   表示 当传入的年龄参数大于0 时 按照实际年龄进行查询；
		   fetch v_students into v_student; 用于获取游标中的记录 ，并存储在变量 v_student 中；
		   while v_students%found loop 用于创建循环，
		   该循环执行的条件 可以从游标 v_students 中获取记录，
		   dbms_output.put_line(···); 利用变量v_student输出学生信息。
		   
		   测试
			 begin printStudents(19);
			 end;
			 
	2、弱类型动态游标
	 弱类型动态游标：在声明游标时不使用 return 关键字指定游标的返回类型，那么在以后的程序中，
	 可以对其使用不同的返回类型。例如，对于学生表，用户可能只希望获得学生姓名或学生的年龄，
	 则可以创建一个存储过程，向存储过程传递一个参数，该参数标识用户希望获得的数据。
	 此时，可以利用弱类型游标来实现此功能。
	 
	 (1) 使用弱类型游标
		 create or replace procedure printStudentsByFlag(in_flag in varchar2) as
				begin
				  declare
				   type name_type is record (
						id varchar2(32),
						name varchar2(32)
				   );
				   type age_type is record (
						id varchar2(32),
						age number
				   );
				   type students_type is ref cursor;
				   
				   v_name name_type;
				   v_age age_type;
				   v_students students_type;
				   
				 begin
				   if upper (in_flag) = 'NAME' then
					 open v_students for
						  select student_id,student_name from students;
						  
					 fetch v_students into v_name;
					 
					 while v_students%found loop
					   dbms_output.put_line(v_name.id || '号 学生的姓名是：' || v_name.name);
					   fetch v_students into v_name;
					 end loop;
					elsif upper(in_flag) ='AGE' then
					  open v_students for
						   select student_id,student_age from students;
					  fetch v_students into v_age;
					  
					  while v_students%found loop
						dbms_output.put_line(v_age.id || '号 学生的年龄是：' || v_age.age);
						fetch v_students into v_age;
					  end loop;
					end if;
					
					if v_students%isopen then
					  close v_students;
					end if;
				  end;
				end printStudentsByFlag;
			测试
			begin
			  printStudentsByFlag('age');
			end;
	 (2) cursor for 游标的使用
		 
		 begin
		   for student in (select * from students where student_age >= 18 order by student_age desc)
			   loop
			   dbms_output.put_line(
					   student.student_name || ':' || student.student_age || '岁'
			   );
			   end loop;
		 end;