游标使用,游标学习

时间:2020-01-10 10:26来源:江苏十一选五手机版数据库
游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。1.如何使用游标 1)定义游标语句 Declare 游标名 Cursor For2)创建游标语句 Open 游标名 3)提取

游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。 1.如何使用游标 1)定义游标语句 Declare 游标名 Cursor For 2)创建游标语句 Open 游标名 3)提取游标列值、移动记录指针 Fetch 列名列表 From 游标名 [Into 变量列表] 4)使用@@Fetch_Status利用While循环处理游标中的行 5)删除游标并释放语句 Close 游标名/Deallocate 游标名 6)游标应用实例 --定义游标 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName --创建游标 Open cur_Depart --移动或提取列值 Fetch From cur_Depart into @DeptID,@DeptName --利用循环处理游标中的列值 While @@Fetch_Status=0 Begin Print @DeptID,@DeptName Fetch From cur_Depart into @DeptID,@DeptName End --关闭/释放游标 Close cur_Depart Deallocate cur_Depart 2.语句的详细及注意 1)定义游标语句 Declare 游标名 [Insensitive] [Scroll] Cursor For Select 语句 [FOR {Read Only | Update [ OF 列名列表]}] Insensitive DBMS创建查询结果集数据的临时副本。游标是Read Only,也就是说不能修改其内容或底层表的内容; Scroll 指定游标支持通过使用任意Fetch 选项选取它的任意行作为当前行。如果此项省略,则游标将只支持向下移动单行; Select语句 定义游标结果集的标准 SELECT 语句。在游标声明的 Select语句内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO; Read Only 防止使用游标的用户通过更新数据或删除行改变游标的内容; Update 创建可更新游标且列出值能被更新的游标列。如果子句中列入了任意列,则只有被列入的列才能被更新。如果Declare Cursor语句中只指定的UPDATE,则游标将允许更新它的任何或所有列。 Declare cur_Depart Cursor For Select * From Department For Update OF cDeptID,cDeptName 2)提取游标列值、移动记录指针语句 Fetch [Next | Prior | First | Last | {Absolute 行号} | {Relative 行号}] From 游标名 [Into 变量列表……] 每次执行Fetch语句时,DBMS移到游标中的下一行并把游标中的列值获取到Into中列出的变量中。因此Fetch语句的Into子句中列出的变量必须与游标定义中Select 语句中的列表的类型与个数相对应; 仅当定义游标时使用Scroll参数时,才能使用Fetch语句的行定位参数;如果Fetch语句中不包括参数Next | Prior | First | Last,DBMS将执行默认的Fetch Next; Next 向下、向后移动一行; Prior 向上、向前移动一行; First 移动至结果集的第一行; Last 移动至结果集的最后一行; Absolute n 移动到结果集中的第n行。如果n是正值,DBMS从结果集的首部向后或向下移动至第n行;如果n是负数,则DBMS从结果集的底部向前或向上移动n行; Fetch Absolute 2 From cur_Depart Into @DeptID,@DeptName Relative n 从指针的当前位置移动n行。如果n是正值,DBMS将行指针向后或向下移动至第n行;如果n是负数,则DBMS将行指针向前或向上移动n行; Fetch Relative 2 From cur_Depart Into @DeptID,@DeptName 3)基于游标的定位DELETE/UPDATE语句 如果游标是可更新的,就可以用游标从游标数据的源表中DELETE/UPDATE行,即DELETE/UPDATE基于游标指针的当前位置的操作; 举例: --删除当前行的记录 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName Open cur_Depart Fetch From cur_Depart into @DeptID,@DeptName Delete From Department Where CURRENT OF cur_Depart --更新当前行的内容 Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department into @DeptID,@DeptName Open cur_Depart Fetch From cur_Depart into @DeptID,@DeptName Update Department Set cDeptID='2007' + @DeptID Where CURRENT OF cur_Depart 3.游标使用技巧及注意 1)利用Order By改变游标中行的顺序。此处应该注意的是,只有在查询的中Select 子句中出现的列才能作为Order by子句列,这一点与普通的Select语句不同; 2)当语句中使用了Order By子句后,将不能用游标来执行定位DELETE/UPDATE语句;如何解决这个问题,首先在原表上创建索引,在创建游标时指定使用此索引来实现;例如: Declare cur_Depart Cursor For Select cDeptID,cDeptName From Department With INDEX(idx_ID) For Update Of cDeptID,cDeptName 通过在From子句中增加With Index来实现利用索引对表的排序; 3)在游标中可以包含计算好的值作为列; 4)利用@@Cursor_Rows确定游标中的行数; ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @NIAN VARCHAR(8000),@NUMS INT,@NIANS VARCHAR(8000) SET @NUMS=1 DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year DESC OPEN GETYEAR FETCH NEXT FROM GETYEAR INTO @NIAN WHILE @@FETCH_STATUS=0 BEGIN IF @YEARNUM=@NUMS SET @NIANS=ISNULL(@NIANS+',','')+RTRIM(@NIAN) SELECT @NUMS=@NUMS+1 FETCH NEXT FROM GETYEAR INTO @NIAN --FETCH ABSOLUTE 3 FROM GETYEAR INTO @NIAN END CLOSE GETYEAR DEALLOCATE GETYEAR --PRINT @NIANS RETURN @NIANS END ALTER FUNCTION SEL_KEYAR(@YEARNUM INT,@f_k_lessonid VARCHAR(15)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @NIAN VARCHAR(8000),@NIANS VARCHAR(8000) DECLARE GETYEAR CURSOR FOR SELECT f_year FROM t_kejianol WHERE f_k_lessonid=@f_k_lessonid GROUP BY f_year ORDER BY f_year ASC OPEN GETYEAR FETCH ABSOLUTE @YEARNUM FROM GETYEAR INTO @NIAN CLOSE GETYEAR DEALLOCATE GETYEAR RETURN @NIANS END

与windows或DOS的“光标”不同,MS-SQL的游标是一种临时的数据库对象,既对可用来旋转储存在系统永久表中的数据行的副本,也可以指向储存在系统永久表中的数据行的指针。
    游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。

1,什么是游标?
 ①从表中检索出结果集,从中每次指向一条记录进行交互的机制。
   
 ②关系数据库中的操作是在完整的行集合上执行的。
  由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。
     应用程序,尤其是互动和在线应用程序,把完整的结果集作为一个单元处理并不总是有效的。
     这些应用程序需要一种机制来一次处理一行或连续的几行。而游标是对提供这一机制的结果集的扩展。

1.如何使用游标

     游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问 API 的一部分而得以实现的软件,
     用来管理从数据源返回的数据的属性(结果集)。这些属性包括并发管理、在结果集中的位置、返回的行数,
     以及是否能够在结果集中向前和/或向后移动(可滚动性)。

   1)定义游标语句 Declare <游标名> Cursor  For
   2)创建游标语句 Open <游标名>
   3)提取游标列值、移动记录指针 Fetch  <列名列表>  From  <游标名>  [Into <变量列表>]
   4)使用@@Fetch_Status利用While循环处理游标中的行
   5)删除游标并释放语句 Close  <游标名>/Deallocate  <游标名>
   6)游标应用实例

     游标跟踪结果集中的位置,并允许对结果集逐行执行多个操作,在这个过程中可能返回至原始表,也可能不返回至原始表。
     换句话说,游标从概念上讲基于数据库的表返回结果集。
     由于它指示结果集中的当前位置 ,就像计算机屏幕上的光标指示当前位置一样,“游标”由此得名。

   --定义游标
   Declare cur_Depart Cursor
     For  Select  cDeptID,cDeptName  From  Department  into  @DeptID,@DeptName
   --创建游标
   Open cur_Depart
   --移动或提取列值
   Fetch  From  cur_Depart  into  @DeptID,@DeptName
   --利用循环处理游标中的列值
   While  @@Fetch_Status=0
     Begin
       Print @DeptID,@DeptName
       Fetch  From  cur_Depart into  @DeptID,@DeptName
     End
   --关闭/释放游标
   Close  cur_Depart
   Deallocate  cur_Depart

2,游标有什么作用?
 ①指定结果集中特定行的位置。
 ②基于当前的结果集位置检索一行或连续的几行。
 ③在结果集的当前位置修改行中的数据。
 ④对其他用户所做的数据更改定义不同的敏感性级别。
 ⑤可以以编程的方式访问数据库。
    
3,为什么避免使用游标?
 ①在创建游标时,最需要考虑的事情是,“是否有办法避免使用游标?”
  因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;
  如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

2.语句的详细及注意

4,Oracle游标的类型?
 ①静态游标:结果集已经确实(静态定义)的游标。分为隐式和显示游标。
  ⑴隐式游标:所有DML语句为隐式游标,通过隐式游标属性可以获取SQL语句信息。
  ⑵显示游标:用户显示声明的游标,即指定结果集。当查询返回结果超过一行时,就需要一个显式游标。
 ②REF游标:动态关联结果集的临时对象。
 
5,Oracle游标的状态有哪些,怎么使用游标属性?
 ①游标的状态是通过属性来表示。
  %Found :Fetch语句(获取记录)执行情况 True or False。
  %NotFound : 最后一条记录是否提取出 True or False。
  %ISOpen : 游标是否打开True or False。
  %RowCount :游标当前提取的行数 。
 ②使用游标的属性。
  例子:/* conn scott/tiger */
  Begin
   Update emp Set  SAL = SAL + 0.1  Where JOB = 'CLERK';
   If  SQL%Found  Then
    DBMS_OUTPUT.PUT_LINE('已经更新!');
   Else
    DBMS_OUTPUT.PUT_LINE('更新失败!');
   End  If;
  End;
 
6,如何使用显示游标,?如何遍历循环游标?
  ①使用显示游标
   ⑴声明游标:划分存储区域,注意此时并没有执行Select 语句。
    CURSOR 游标名( 参数 列表)   [返回值类型]   IS   Select 语句;
   ⑵打开游标:执行Select 语句,获得结果集存储到游标中,此时游标指向结果集头, 而不是第一条记录。
    Open 游标名( 参数 列表);
   ⑶获取记录:移动游标取一条记录
    Fetch  游标名 InTo  临时记录或属性类型变量;
   ⑷关闭游标:将游标放入缓冲池中,没有完全释放资源。可重新打开。
    Close  游标名;
 ②遍历循环游标
  ⑴For 循环游标
   循环游标隐式打开游标,自动滚动获取一条记录,并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
     For  变量名  In  游标名 
     Loop
      数据处理语句;
     End Loop;
   ⑵Loop循环游标
     。。。
    Loop
     Fatch  游标名 InTo  临时记录或属性类型变量;
     Exit  When   游标名%NotFound;
    End   Loop;
     。。。
  例子1:
  /* conn scott/tiger */
   Declare
     Cursor myCur is select empno,ename,sal from emp;
     vna varchar2(10);
     vno number(4);
     vsal number(7,2);
  Begin
     open myCur;
     fetch myCur into vno,vna,vsal;
     dbms_output.put_line(vno||'    '||vna||'    '||vsal);
     close myCur;
  End;
  /
 
 例子2:使用loop遍历游标。
 /* conn scott/tiger */
  Declare
     Cursor myCur is select ename,job,sal,empno from emp;
     varE myCur%rowType;
  Begin
     if myCur%isopen = false then
        open myCur;
       dbms_output.put_line('Opening...');
     end if;
     loop
        fetch myCur into varE;
        exit when myCur%notfound;
        dbms_output.put_line(myCur%rowCount||'    '||vare.empno||'    '||vare.ename||'    '||vare.sal);
     end loop;
     if myCur%isopen then
        Close myCur;
        dbms_output.put_line('Closing...');
     end if;
  End;
  /
 
  例子3:使用For循环遍历游标,
  /* conn scott/tiger */
  Declare
     Cursor myCur is select * from emp;
  Begin
     for varA in myCur
      loop
         dbms_output.put_line(myCur%rowCount||'    '||varA.empno||'    '||varA.ename||'  '||varA.sal);
      end loop;
  End;
  /

   1)定义游标语句

7,怎样更新和删除显示游标中的记录?
 ①UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
  要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,
  所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,
  不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
   在多表查询中,使用OF子句来锁定特定的表,如果忽略了OF子句,那么所有表中选择的数据行都将被锁定。
   如果这些数据行已经被其他会话锁定,那么正常情况下ORACLE将等待,直到数据行解锁。
 ②使用更新或删除:
  ⑴声明更新或删除显示游标:
   Cursor 游标名 IS  SELECT 语句   For Update [ Of  更新列列名];
   Cursor 游标名 IS  SELECT 语句   For Delete [ Of  更新列列名];
  ⑵使用显示游标当前记录来更新或删除:
   Update  表名   SET   更新语句  Where   Current  Of   游标名;
   Delete  From  表名   Where   Current  Of   游标名;
 
   例子1:更新显示游标记录
   /*conn scott/tiger*/
   Declare
     Cursor myCur is select job from emp for update;
       vjob empa.job%type;
       rsal empa.sal%type;
    Begin
       open myCur;
       loop
          fetch myCur into vjob;
          exit when myCur%notFound;
          case  (vjob)
             when 'ANALYST' then  rsal := 0.1;
            when  'CLERK' then  rsal := 0.2;
             when  'MANAGER' then  rsal := 0.3;
             else
               rsal := 0.5;
          end case;
        update emp set sal = sal + rsal where current of myCur;
       end loop;
    End;
    /
    例子2:删除显示游标记录
    /*conn scott/tiger
    Crate table  empa  Select * from scott.emp;
    */
    Declare
      Cursor MyCursor  Select   JOB  From  empa  For  Update;
      vSal   emp.Sal%TYPE;
    Begin
      Loop
       Fetch  MyCursor  InTo  vSal;
       Exit  When  MyCursor%NotFound;
       If   vSal < 800 Then
        Delete  From empa  Where  Cursor  Of   MyCursor;
       End  If;  
      End    Loop;
    End;/
8,什么是带参数的显示游标?
 ①与过程和函数相似,可以将参数传递给游标并在查询中使用。
  参数只定义数据类型,没有大小(所有Oracle中的形参只定义数据类型,不指定大小)。
  与过程不同的是,游标只能接受传递的值,而不能返回值。
   可以给参数设定一个缺省值,当没有参数值传递给游标时,就使用缺省值。
  游标中定义的参数只是一个占位符,在别处引用该参数不一定可靠。
 ②使用带参数的显示游标
  ⑴声明带参数的显示游标:
   CURSOR 游标名  [(parameter[,parameter],...)]    IS   Select语句;;
  
   参数形式:1,参数名   数据类型  
       2,参数名   数据类型  DEFAULT  默认值
      
   例子:
    /*conn scott/tiger
    Crate table  empa  Select * from scott.emp;
    */
    Declare
      Cursor MyCursor(pSal  Number  Default   800)  Select   JOB  From  empa Where  SAL >  pSal ;
      varA  MyCursor%ROWTYPE;
    Begin
      Loop
       Fetch  MyCursor  InTo  varA;
       Exit  When  MyCursor%NotFound;
       DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||'    '||varA.empno||'    '||varA.ename||'  '||varA.sal); 
      End    Loop;
    End;/

   Declare <游标名>  [Insensitive] [Scroll]  Cursor
     For  <Select 语句>  [FOR {Read Only | Update [ OF <列名列表>]}]

 

  •     Insensitive  DBMS创建查询结果集数据的临时副本(而不是使用直接引用数据库表中的真实数据行中的列)。游标是Read Only,也就是说不能修改其内容或底层表的内容;
  •     Scroll  指定游标支持通过使用任意Fetch 选项(First  Last  Prior  Next Relative  Absolute)选取它的任意行作为当前行。如果此项省略,则游标将只支持向下移动单行(即只支持游标的Fetch  Next);
  •     Select语句  定义游标结果集的标准 SELECT 语句。在游标声明的 <Select语句>内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO;
  •     Read Only  防止使用游标的用户通过更新数据或删除行改变游标的内容;
  •     Update  创建可更新游标且列出值能被更新的游标列。如果子句中列入了任意列,则只有被列入的列才能被更新。如果Declare  Cursor语句中只指定的UPDATE(没有列名列表),则游标将允许更新它的任何或所有列。
        Declare  cur_Depart  Cursor
            For  Select  *  From  Department  For  Update  OF  cDeptID,cDeptName

    2)提取游标列值、移动记录指针语句

     Fetch  [Next | Prior | First | Last | {Absolute <行号>} | {Relative <行号>}]
          From  <游标名>  [Into <变量列表……>]

  •     每次执行Fetch语句时,DBMS移到游标中的下一行并把游标中的列值获取到Into中列出的变量中。因此Fetch语句的Into子句中列出的变量必须与游标定义中Select 语句中的列表的类型与个数相对应;
  •     仅当定义游标时使用Scroll参数时,才能使用Fetch语句的行定位参数(First  Last  Prior  Next Relative  Absolute);如果Fetch语句中不包括参数Next | Prior | First | Last,DBMS将执行默认的Fetch Next;
  •     Next  向下、向后移动一行(记录);
  •     Prior  向上、向前移动一行(记录);
  •     First  移动至结果集的第一行(记录);
  •     Last  移动至结果集的最后一行(记录);
  •     Absolute  n  移动到结果集中的第n行。如果n是正值,DBMS从结果集的首部向后或向下移动至第n行;如果n是负数,则DBMS从结果集的底部向前或向上移动n行;
              Fetch  Absolute  2  From  cur_Depart  Into  @DeptID,@DeptName
  •     Relative  n   从指针的当前位置移动n行。如果n是正值,DBMS将行指针向后或向下移动至第n行;如果n是负数,则DBMS将行指针向前或向上移动n行;
              Fetch  Relative  2  From  cur_Depart  Into  @DeptID,@DeptName

    3)基于游标的定位DELETE/UPDATE语句

    如果游标是可更新的(也就是说,在定义游标语句中不包括Read Only 参数),就可以用游标从游标数据的源表中DELETE/UPDATE行,即DELETE/UPDATE基于游标指针的当前位置的操作;
    举例:

 

    --删除当前行的记录
     Declare cur_Depart Cursor
       For  Select  cDeptID,cDeptName  From  Department  into  @DeptID,@DeptName
     Open cur_Depart
     Fetch  From  cur_Depart  into  @DeptID,@DeptName
     Delete  From  Department  Where  CURRENT  OF  cur_Depart

    --更新当前行的内容
     Declare cur_Depart Cursor
       For  Select  cDeptID,cDeptName  From  Department  into  @DeptID,@DeptName
     Open cur_Depart
     Fetch  From  cur_Depart  into  @DeptID,@DeptName
     Update  Department  Set  cDeptID=’2007’ + @DeptID  Where  CURRENT  OF  cur_Depart

3.游标使用技巧及注意

   1)利用Order By改变游标中行的顺序。此处应该注意的是,只有在查询的中Select 子句中出现的列才能作为Order by子句列,这一点与普通的Select语句不同;
  2)当语句中使用了Order By子句后,将不能用游标来执行定位DELETE/UPDATE语句;如何解决这个问题,首先在原表上创建索引,在创建游标时指定使用此索引来实现;例如:

   Declare cur_Depart Cursor
     For  Select  cDeptID,cDeptName  From  Department  With  INDEX(idx_ID)
     For  Update  Of  cDeptID,cDeptName

    通过在From子句中增加With Index来实现利用索引对表的排序;

        
  3)在游标中可以包含计算好的值作为列;
  4)利用@@Cursor_Rows确定游标中的行数;

编辑:江苏十一选五手机版数据库 本文来源:游标使用,游标学习

关键词: