PL/SQL(oracle)4_游标(cursor)

nf923gjzvb

　　1、游标的概念

cursor：实际上是一块内存工作区，用来存放DML或者select操作的结果。

2、游标的分类：

   隐式游标：声明、打开及管理都是Oracle自动维护的，通常用来处理DML操作或者返回单行的select。

   隐式游标的几个属性：

   %Rowcount:会返回最近执行的一条SQL语句所影响的记录行数。

   %Found:

   %Notfound:

   %isOpen:

   %前缀为SQL

  

   练习：使用替代变量删除指定部门的员工，并显示删除的员工数量。


Begin
Delete From emp Where deptno = &dept;
dbms_output.put_line('删除的员工数为:'||sql%rowcount);
End;
　　
  

   显式游标：声明、打开及管理都是需要程序员手动操作的。通常用来处理查询结果是多行或零行的情况。

   1、声明：在declare部分使用:

   Cursor 游标名 Is 查询语句;

   注意：查询语句不能带into子句。
　　

cursor emp_cursor is select empno,ename from emp ;
　　
　　2、打开：

   语法：open 游标名； 写在begin块中。

   打开游标实际上就是相当于执行游标所对应的查询语句，并且把查询结果集放入到内存中。

   3、提取数据：

   语法：fetch 游标名 Into 变量名列表|记录型变量 ，写在begin块，该语句通常是放在一个循环中。

   第一次提取数据之前游标的指针指向第一行，然后每执行一次提取，指针自动向下移动一行。

   注意：变量名列表一定要和游标声明时所对应的查询语句中的列的个数及数据类型保持一致。

   如果变量为记录型变量，那么该变量的内部分量一定要和游标声明时所对应的查询语句中的列的个数及数据类型保持一致。

   游标指针只能向下移动。

   4、关闭游标：

   语法：close 游标名；相当于释放内存。

   显示游标的几个属性：

   %Rowcount:返回的是已经从游标中提取到的记录行数。

   %Found:

   %Notfound:

   %isOpen:

   %前缀为游标名

   练习：使用游标，遍历出所有部门平均工资在1000以上的部门ID，部门名称，平均工资及人数。

   Declare
Cursor avg_cursor Is Select e.deptno,dname,Avg(sal) ,Count(empno)
From emp e ,dept d
Where e.deptno = d.deptno
Group By e.deptno,d.dname
Having Avg(sal)>1000;
v_deptno dept.deptno%Type;
v_dname dept.dname%Type;
v_avgsal emp.sal%Type;
v_count Number;
Begin
Open avg_cursor;
Loop
Fetch Avg_cursor Into v_deptno,v_dname,v_avgsal,v_count ;
Exit When avg_cursor%Notfound;
dbms_output.put_line('部门编号为'||v_deptno'||的部门名称为'||v_dname||',其平均工资为:'||v_avgsal||',人数为'||v_count);
End Loop;
Close avg_cursor;
End;
 　　

  5、几种方便使用游标的方式。

     a.游标和记录

        1.使用游标名%Rowtype 声明一个记录型变量。

        2.直接使用fetch 游标名 Into 记录型变量名。 

     b.游标式的For循环

       语法：

       For 记录型变量名 In 游标名 Loop

           循环体;

       End Loop;

      

       说明：记录型变量名不用事先声明。

             此种写法，oracle会自动打开游标，从游标中提取数据，关闭游标。

      c.游标式的for循环

        可以直接省略游标的声明，把游标所对应的SElect语句直接放到for循环的in子句中，注意要在两端加上括号。


    练习：使用游标式的For循环，遍历出所有员工姓名，所在部门名称，即使这个员工没有部门。


   Begin
For avg_record In ( Select ename,dname From emp e ,dept d Where e.deptno = d.deptno(+) ) Loop
dbms_output.put_line('部门编号为'||avg_record.ename||'的部门名称为'||avg_record.dname);
End Loop;         
End;
　　

   6、带参数的游标

      a、游标声明时和以前有变化，需要在游标名后面指定形式参数列表。

      b、在打开游标时，需要给形式参数赋予响应的实参列表。

      c、提取数据以及关闭游标和先前没有变化。

    7、for Update [Of Column Reference] [Nowait]:该子句用来给游标所对应的查询加锁的。

    8、where Current Of 游标名：用来标示从游标中取出的当前行。

   

    练习：判断每个部门的工资总和，如果大于10000，则给该部门的人员工资增加10%，否则增加20%。


    Declare
Cursor sumsal_cursor Is Select Sum(sal) sumsal,deptno From emp Group By deptno For Update;
Begin
For r_1 In sumsal_cursor Loop
If r_1.sumsal >10000 Then
Update emp Set sal = sal*1.1 Where deptno = r_1.deptno;
Else
Update emp Set sal = sal*1.2 Where deptno = r_1.deptno;
End If;
End Loop;
End ;