4.plsql游标使用

1.概念性问题
概念：是指向结果集的一个指针
说明：Oracle会为查询语句分配一个空间，存储结果集
      这块内存空间由游标来命名，通过游标来访问这块内存中存储的信息
  类型：隐式游标、显示游标
/*有多条数据的时候用游标处理结果集
当查询的结果只有一条结果的时候可以不使用游标来处理结果，当然用游标处理也是可以的，
但是就没有必要使用游标了。*/
======================================
2.游标属性
%isopen: 测试游标是否打开，返回布尔值
%found:  fetch 后，是否提取到数据，返回布尔值
%notfound: fetch 后，是否没有提取到记录，返回布尔值
%rowcount: 返回游标中已提取的记录行数，返回整数
注意：已提取的记录行数不一定是总的记录行数，但一般我们提取到的行数就是总的记录行数
==========================================
3.隐式游标
隐式游标由系统自动创建并管理
PL/SQL会为所有的SQL操作声明一个隐式游标
包括只返回一条操作记录的查询操作和所有的ＤＭＬ语句。
说明：所有隐式游标的名字都是sql
--insert update dalete select一条结果
declare
	v_no emp.deptno%type := &no;
	v_count number(2);
begin
	--execute immediate 'update emp set sal=sal+100 where deptno='||v_no;
  update emp set sal=sal+100 where deptno=v_no;
  dbms_output.put_line(sql%rowcount);
end;
==============================================================
4.显示游标
显示游标，暂存查询取出多行数据，然后一行一行地进行处理（循环处理）
使用步骤：
声明游标：CURSOR 游标名 IS SELECT语句;
打开游标：OPEN 游标名;相当于执行查询语句
--声明的游标必须打开才能使用
使用游标：一般在循环语句中使用fetch语句提取游标中的记录来进行操作
          fetch相当于指针，执行之后之指针指向下一条记录
关闭游标：close 游标名称;
--使用完游标后，一定要记得关闭，不然会一直占用内存空间
--游标包含的就是查询结果集

方案一：用普通变量接收游标中的数据
declare 
  cursor cur_emp is select ename,sal from emp where deptno=10;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin 
  open cur_emp;
  loop
    fetch cur_emp into v_ename,v_sal;--最后取不到数据变量里面数据不变
    --dbms_output.put_line(v_ename||'  '||v_sal);
    exit when cur_emp%notfound;
    dbms_output.put_line(v_ename||'  '||v_sal);
   end loop;
   close cur_emp;
end;

方案二：用记录变量类型接收数据
declare 
  cursor cur_emp is select ename,sal from emp where deptno=10;
  type myrec is record(--定义记录变量类型
  v_ename emp.ename%type,
  v_sal emp.sal%type
  );
  emp_rec myrec;--实列变量
begin 
  open cur_emp;
  loop
    fetch cur_emp into emp_rec;--取不到数据变量里面数据不变
    exit when cur_emp%notfound;
    dbms_output.put_line(emp_rec.v_ename||'  '||emp_rec.v_sal);
   end loop;
   close cur_emp;
end;

方案三：使用复合变量类型接收数据
declare 
  cursor cur_emp is select ename,sal from emp where deptno=10;
  --v_emp emp%rowtype;
  v_emp cur_emp%rowtype;
begin 
  open cur_emp;
  loop
    --fetch cur_emp into v_emp.ename,v_emp.sal;
    fetch cur_emp into v_emp;--最后取不到数据变量里面数据不变
    exit when cur_emp%notfound;
    dbms_output.put_line(v_emp.ename||'  '||v_emp.sal);
    --dbms_output.put_line(cur_emp%rowcount);
   end loop;
   close cur_emp;
end;

方案4：使用for循环接收游标中的数据--自动声明变量类型i
--ORACLE语言提供了游标 FOR 循环语句，
--自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能；
declare 
  cursor cur_emp is select ename,sal from emp where deptno=10;
begin 
  for i in cur_emp loop
  --ORACLE还允许在游标 FOR 循环语句中使用子查询来实现游标的功能
  --for i in (select ename,sal from emp where deptno=10) loop
    dbms_output.put_line(i.ename||'  '||i.sal);
    --exit when cur_emp%notfound;
  end loop;
end;
===================================================
5.参数游标
使用带有参数的游标可能简化游标的管理
语法：cursor 游标名(参数名 参数类型,...) is select_statement
--举例：如果需要对某一字段进行多值查询，通过键盘录入
说明： 不使用参数游标也是可以实现的，比如刚才上面的代码。

使用游标参数方式：
declare 
  --cursor cur_emp(v_no emp.deptno%type) 
  --is select ename,sal from emp where deptno=v_no;
  cursor cur_emp(v_no emp.deptno%type default 10) --[:=10]
  is select ename,sal from emp where deptno=v_no;--提供默认值
  v_emp cur_emp%rowtype;
begin 
  --open cur_emp(10);--直接赋值
  --open cur_emp(&部门编号);--键盘录入赋值
  open cur_emp;--有默认值传不传都行
  loop
    fetch cur_emp into v_emp;
    exit when cur_emp%notfound;
    dbms_output.put_line(v_emp.ename||'  '||v_emp.sal);
   end loop;
   close cur_emp;
end;
说明：cur_emp(v_no emp.deptno%type)中，我们设置了游标的参数v_no emp.deptno%type，
      这个游标参数v_no的类型是通过%type进行赋值的，如果不使用%type进行赋值的
      话，可以使用number或者varchar2进行赋值，但是number、varchar2都不能有参
      数，也就是说不能写成nnumber(2)、varchar2(5)这样带参数的形式。

==========================================
6.游标 for 循环
特点：
用FOR循环可大大简化游标的循环操作
在FOR循环中不需要事先定义循环控制变量
在游标的FOR循环之前，系统能自动打开游标；在FOR循环结束后，系统能够自动关闭游标，不需要人为操作。
在游标的FOR循环过程中，系统能够自动执行FETCH语句不需要人为执行FETCH语句。

格式：
Declare
  声明游标
Begin
  For record_name in 游标
  loop
  循环体
  End loop;
End;
循环体中只需要写输出语句就可以了，可以通过游标for循环的特点可知。
record_name是记录类型变量，不需要声明，Oracle会隐式声明，同样，当循环结束，它也会被隐式关闭。

declare
	cursor cur_emp(v_no emp.deptno%type default 10)  is select ename,sal from emp where deptno=v_no;
begin  
	for emp_rec in cur_emp(20)
	loop
	dbms_output.put_line(emp_rec.ename || '   ' || emp_rec.sal);
	end loop;
end;
===========================================
___________________________以上均为静态游标————————————————————————————————

7、游标变量
动态游标或引用游标
游标变量是指在打开时才指定其所应的SQL语句。
分类：强型游标和弱型游标
引用游标（REF游标）是一种动态游标，它比普通的静态游标更加灵活，因为它不依赖指定的查询语句。换言之，引用游标在运行时可以与不同的查询语句相关联，也可以使用游标变量。
引用游标有两种类型，即强型游标和弱型游标。强型游标返回指定格式的结果集，而弱型游标则没有返回类型。
除非有特殊的需要，建议在声明引用游标时指定返回类型，因为这样减小出现错误的概率。
尽量使用强型游标

--_________________a、强型游标___________________--
声明一个强型游标的变量
1、 声明一个记录类型
2、声明游标返回上面定义的记录类型
3、 声明一个强型游标变量

处理游标变量的关键步骤
四步：声明、打开、提取和关闭

步骤格式：
声明一个记录类型，该记录类型是select语句的结果集
type emp_record record(ename emp.ename%type,empjob varchar2(20),sal emp.sal%type);
申明一个 ref cursor 游标变量
type ref_cursor_emp is ref cursor return emp_record;
声明一个强类型游标变量
c1 ref_cursor_emp;

强型游标，动态游标的新式;
declare
  --声明记录类型
  type dept_rec is record(dname dept.dname%type,loc dept.loc%type);
  --声明一个强型游标类型，返回值就是前面的记录类型
  type dept_cur is ref cursor return dept_rec;
  --声明强型游标变量
  c1 dept_cur;
  --用于接收游标中的一行数据
  mydept c1%rowtype;
begin 
  open c1 for select dname,loc from dept;--后面可以有where子句
  loop
    fetch c1 into mydept;
    exit when c1%notfound;
    dbms_output.put_line(mydept.dname || '         ' || mydept.loc);
  end loop;
  close c1;
end;
--_________________b.弱类型游标___________________--
b、弱型游标
  弱型游标的游标变量没有声明返回类型，当定义游标所对应的SQL语句时可
  以返回任意结构的数据，更自由和方便。       
declare
  type dept_cur is ref cursor;
  --声明强型游标变量
  c1 dept_cur;
  --用于接收游标中的一行数据
  type myrec is record(
  dname dept.dname%type,
  loc dept.loc%type);
  rec_dept myrec;
  
begin 
  --v_id:=1
  --v_sql='select dname,loc from dept where deptno=:1';
  open c1 for select dname,loc from dept;--后面可以有where子句
  --open c1 for v_sql using v_id;--绑定变量常用优化手段
  loop
    fetch c1 into rec_dept;
    exit when c1%notfound;
    dbms_output.put_line(rec_dept.dname || '  ' ||rec_dept.loc);
  end loop;
  close c1;
end; 

--_____________________c. 系统类型 sys_refcursor______________
常用，省去了手动定义动态游标的步骤，以下效果等同：
declare
   -- type cur_stu_type is ref cursor; -- 手动定义动态游标
   -- cur_stu_info cur_stu_type;
   
   -- 声明动态游标， 这一个步骤等于上面两个步骤
   cur_stu_info sys_refcursor; 
begin
  
end;

8.三种游标循环效率对比
结论：批量处理>隐式游标>单条处理
说明：若有兴趣，可以在数据量多的表里面，分别尝试下列三种写法，并打印时间，用作比较
1. 批量处理
   declare
   type 类型名 is table of 记录类型;
   集合变量 类型名;
   begin
   open 游标;
   loop
      fetch 游标 bulk collect  
       into 集合变量（也就是 table 类型） limit 数值; -- 一般 500 左右  
       exit when 条件; -- cursor.count = 0
       逻辑处理;
    end loop;
    close 游标;
    end;

2. 隐式游标
   for x in (sql 语句) loop
      逻辑处理;
   end loop;

3. 单条处理
   open  游标;
   loop
      fetch 游标 
       into 变量; 
       exit when 条件;
       逻辑处理;
   end loop;
   close 游标;

=================================================
扩展：

--15-7:更新游标行  
DECLARE  
  CURSOR emp_cursor IS  
    SELECT ename,sal,deptno FROM emp FOR UPDATE;  
  dno INT:=&no;  
BEGIN  
  FOR emp_record IN emp_cursor LOOP  
    IF emp_record.deptno=dno THEN  
       dbms_output.put_line('姓名:'||emp_record.ename  
        ||',原工资:'||emp_record.sal);  
       UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor; 
    END IF;  
  END LOOP;  
END; 

--15-8:删除游标行  
DECLARE  
  CURSOR emp_cursor IS  
    SELECT ename FROM emp FOR UPDATE;  
  name VARCHAR2(10):=lower('&name');  
BEGIN  
  FOR emp_record IN emp_cursor LOOP  
    IF lower(emp_record.ename)=name THEN  
       DELETE FROM emp WHERE CURRENT OF emp_cursor;  
    ELSE  
       dbms_output.put_line('姓名:'||emp_record.ename);  
    END IF;  
  END LOOP;  
END;  

--15-9:使用of子句在特定表上加行共享锁．  
DECLARE  
  CURSOR emp_cursor IS  
    SELECT a.dname,b.ename FROM dept a JOIN emp b  
    ON a.deptno=b.deptno  
    FOR UPDATE OF b.deptno;  
  name VARCHAR2(10):=LOWER('&name');  
BEGIN  
  FOR emp_record IN emp_cursor LOOP  
    IF LOWER(emp_record.dname)=name THEN  
      dbms_output.put_line('姓名:'||emp_record.ename);  
      DELETE FROM emp WHERE CURRENT OF emp_cursor;  
    END IF;  
  END LOOP;  
END;   
--15-9:使用of子句在特定表上加行共享锁．  
DECLARE  
  CURSOR emp_cursor IS  
    SELECT a.dname,b.ename FROM dept a JOIN emp b  
    ON a.deptno=b.deptno  
    FOR UPDATE OF b.deptno;  
  name VARCHAR2(10):=LOWER('&name');  
BEGIN  
  FOR emp_record IN emp_cursor LOOP  
    IF LOWER(emp_record.dname)=name THEN  
      dbms_output.put_line('姓名:'||emp_record.ename);  
      DELETE FROM emp WHERE CURRENT OF emp_cursor;  
    END IF;  
  END LOOP;  
END;  


--15-13:使用limit子句限制提取行数  
DECLARE  
  CURSOR emp_cursor IS SELECT * FROM emp;  
  TYPE emp_array_type IS VARRAY(5) OF emp%ROWTYPE;  
  emp_array emp_array_type;  
BEGIN  
  OPEN emp_cursor;  
  LOOP  
    FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT &rows;  
    FOR i IN 1..emp_array.COUNT LOOP  
      dbms_output.put_line('姓名:'||emp_array(i).ename  
        ||',工资:'||emp_array(i).sal);  
    END LOOP;  
    EXIT WHEN emp_cursor%NOTFOUND;  
  END LOOP;  
  CLOSE emp_cursor;  
END;  


--15-14:使用cursor表达式  
DECLARE  
  CURSOR dept_cursor(no NUMBER) IS  
     SELECT a.dname,CURSOR(SELECT * FROM emp  
     WHERE deptno=a.deptno)  
     FROM dept a WHERE a.deptno=no;  
  TYPE ref_cursor_type IS REF CURSOR;  
  emp_cursor ref_cursor_type;  
  emp_record emp%ROWTYPE;  
  v_dname dept.dname%TYPE;  
BEGIN  
  OPEN dept_cursor(&dno);  
  LOOP  
     FETCH dept_cursor INTO v_dname,emp_cursor;  
     EXIT WHEN dept_cursor%NOTFOUND;  
     dbms_output.put_line('部门名:'||v_dname);  
     LOOP  
       FETCH emp_cursor INTO emp_record;  
       EXIT WHEN emp_cursor%NOTFOUND;  
       dbms_output.put_line('----雇员名:'||emp_record.ename  
        ||',岗位:'||emp_record.job);  
     END LOOP;  
  END LOOP;  
  CLOSE dept_cursor;  
END;