Oracle游标管理

--游标	
--在PL/SQL中 内存中记录集合的指针，指向一行，提供逐行访问记录
--在初始状态下指向的是首记录

--游标的属性：
	%found
   	%notfound
	%isopen
	%rowcount 


--Oracle 游标分为  显式游标和隐式游标

--隐式游标
--PL/SQL 中用户在使用数据操作语言（DML）时，预先定义一个名SQL的隐式游标
--用户不能直接控制此游标，但可以通过检查隐式游标的属性获取信息
--执行DML语句后，Oracle自动关闭
begin
      update scott.emp set sal=sal+1 where deptno=50;
      if SQL%found then
           dbms_output.put_line('表已更新...'||sql%rowcount||'行记录');
      else
           dbms_output.put_line('没有找到记录....');
      end if;
     if not sql%isopen then
           dbms_output.put_line('sql游标已经关闭');
      end if;
      commit;
end;



--显式游标     用户自定义的游标
  
--一般使用步骤：  
     --声明游标： cursor 游标名 is 查询语句 ;
     --打开游标： open 游标名;
     --提取游标:  fetch  游标名 into 变量;
     --关闭游标:  close 游标名
declare
 	cursor v_cs is select * from scott.emp; 	--声明游标
 	v_temp v_cs%rowtype;           
begin
  	open v_cs;                             			--打开游标
  	loop
    	fetch v_cs into v_temp;              		--提取游标
    	dbms_output.put_line(v_temp.ename ||'    '||v_temp.hiredate);
    	exit when v_cs%notfound;            		 --如果游标没有记录退出循环
  	end loop;
  	close v_cs;                              		--关闭游标
end;

--游标属性说明
declare
    v_dept number;
    cursor v_cur is select * from scott.emp where deptno=v_dept;
    c_temp v_cur%rowtype;
begin
    v_dept:=30;
    open v_cur;
    dbms_output.put_line('fetch前游标总记录数:'||v_cur%rowcount);
    if(v_cur%isopen) then
         dbms_output.put_line('游标已经打开');
    end if;
    loop
       fetch v_cur into c_temp;
       dbms_output.put('游标总记录数:'||v_cur%rowcount||' ');
       if(v_cur%found) then   
           dbms_output.put_line(c_temp.ename||'  '||c_temp.sal);
       else
           dbms_output.put_line('游标已经没有记录');
           exit;
       end if;
   end loop;
   close v_cur;
end;


/*带参数的游标
   声明:  cursor 游标名(形参 数据类型...) is select.... where ...=形参;
   使用: open  游标名(实参)；
*/
declare
 		v_input scott.emp.deptno%type;
 		v_name scott.emp.ename%type;
 		v_sal scott.emp.sal%type;
 		cursor c_cur(v_param number) is select ename,sal from scott.emp where deptno=v_param;
begin
 		v_input:='&部门编号';
 		open c_cur(v_input);  --传入实参
 		loop
   		fetch c_cur into v_name,v_sal;
   		exit when c_cur%notfound;
   		dbms_output.put_line(v_name||'   '||v_sal);
 		end loop;
 		close c_cur;
end;


/*
用于delete/update的游标  
声明时指定  for update [of 列] [nowait]   加行级锁  
[of 列] 如果涉及到多张表，默认会在所有表上加行共享锁，
为了只在特定表上加行共享锁，需要在for update子句后带有of子句指定字段名  
 更新时   update ......where current of 游标名
*/
declare 
      cursor c_cur is select * from scott.emp  for update ;
      v_temp emp%rowtype;
begin
     open c_cur;
     loop
       fetch c_cur into v_temp;
       exit when c_cur%notfound;
       if(v_temp.sal<1000) then
               update scott.emp set sal=sal+1 where current of c_cur;
       end if;
     end loop;
     close c_cur;
     commit;
end;


/*
   for 循环游标
          自动打开，关闭，自动获取记录
   声明游标：  cursor cursor_name is select......
   使用:      for  v_name  in cursor_name(或select语句)
              loop.....end loop;
*/
--例1
declare
  		cursor c_cur is select * from scott.emp;
begin 
  		for v_temp in c_cur  loop  --v_temp局部变量，无需声明
  				dbms_output.put_line(v_temp.ename||'  '||v_temp.sal);
  		end loop;
end;  
--例2
declare
       v_input varchar2(1);
begin
      v_input:='&请选择';
      if v_input='a' then
         for v_rec in (select * from emp)
         loop
             dbms_output.put_line(v_rec.empno||':'||v_rec.ename||' '||
v_rec.hiredate||'  '||v_rec.sal);
         end loop;
      elsif v_input='b' then
         for v_rec in (select * from dept)
         loop
            dbms_output.put_line(v_rec.deptno||':'||v_rec.dname);             
         end loop;
      else
         dbms_output.put_line('please input...');
         return;
      end if; 
end;



/********* for 循环游标(带参数) *************
      声明游标：  cursor cursor_name(形参列表) is select......
      使用:   for  v_name  in cursor_name(实参)
              loop.....end loop;
****/

declare
   v_input emp.deptno%type;
   cursor c(dept number) is select * from emp where deptno=dept ; 
begin 
    v_input:='&部门编号';
    for v_rec in c(v_input)  loop
       dbms_output.put_line(v_rec.ename||':'||v_rec.sal);
    end loop;
end;



/**************  ref cursor游标*******************
用于处理运行时动态执行的 SQL查询 即在open游标时动态指定sql语句
声明ref cursor类型 :    
TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];
    声明ref游标类型的游标变量:  变量名  游标名;
    打开方式：     open cursor_name for 查询语句
***/
声明强类型的 REF 游标:
TYPE t_refcur IS REF CURSOR RETURN emp%ROWTYPE; 
V_cur t_refcur;

声明弱类型的 REF 游标 (用于动态SQL)
TYPE t_refcur IS REF CURSOR; 
V_cur t_refcur;

declare
     type v_refcur is ref cursor;		-- -声明 ref cursor 类型
     refcur v_refcur;    			       --声明 ref cursor 类型的游标变量
     v_id number;
     v_name varchar2(20);
     v_selection varchar2(1):=upper(substr('&请输入员工(E)或部门编号(D)',1,1));
     begin
          if v_selection='E' then
             open refcur for select empno,ename from emp;	--打开游标
             dbms_output.put_line('====员工信息====');
          elsif v_selection='D' then
             open refcur for select deptno,dname from dept; --打开游标
             dbms_output.put_line('====部门信息====');
          else
              dbms_output.put_line('请输入员工信息（E）或部门信息（D）');
              return;   -- 返回
          end if;
          fetch refcur into v_id,v_name;-- 提取游标
          while refcur%found  loop
                  dbms_output.put_line('#'||v_id||':'||v_name);
                  fetch refcur into v_id,v_name;
          end loop;
          close refcur;  -- 关闭游标
end;



/*******动态SQL（使用ref cursor 返回多行记录）***********
    使用占位符
     Open cursor_name for v_sql [using 占位符对应的变量]
*****/
declare
   type v_refcur is ref cursor;
   v_cur v_refcur;
   v_emp emp%rowtype;
   v_salary number;
   v_sql varchar2(100);
   v_dno number;
begin
    v_salary :='&请输入薪水标准';
    v_dno:='&请输入部门编号';
   v_sql:='select * from emp where sal>:1 and deptno=:2 order by sal desc';
   open v_cur for v_sql  using  v_salary,v_dno;
   dbms_output.put_line('薪水大于'||v_salary ||'的员工:');
   loop
     fetch v_cur into v_emp;
     exit when v_cur%notfound;
     dbms_output.put_line('姓名: '||v_emp.ename||' 薪水: '||v_emp.sal);
   end loop;
   close v_cur;
end;