chapter12_PL/SQL之游标使用

                                                    

一、应用背景：
     在PL/SQL中，有时希望将SELECT语句的结果取出，然后逐条处理。但是PL/SQL中的复合变量只能存储一条记录结果。
  因此就迫切需要有一种机制可以解决该应用需求，于是乎，就出现了游标。

---

二、游标（cursor）：


       游标是一个SELECT语句执行结果集，其里面可以存储多条表记录，是一块内存区域。当游标被定义时，内存中并未分配存储空间，只是给了一个存储地址；游标定义中的SELECT也不会被执行。只有当游标被打开时，才会真正的执行SELECT，然后将执行结果放入指定的内存区域；在游标使用完成后，游标使用的内存并不会释放，只有当游标被关闭时，其所占用的内存才会被释放。

      简而言之，打开(open)游标时，分配内存，执行SELECT语句，存储执行结果到内存；关闭(close)游标时，释放其所占用的内存。提取(fetch)游标数据时，当前一行的全部值被取出存入指定的变量中，fetch使得游标指针指向游标的下一条记录。
          
三、创建游标:

  语法：

       cursor cursor_name is select ...;            //无参数游标

       cursor cursor_name(para1 data_type,...) is select ...;    //带参数的游标
       
       例如：cursor A is select * from emp;
              
               cursor A(v_dpetno number) is select * from emp where deptno=v_deptno;
              
  说明：
       cursor,is             ----是关键字
       cursor_name       ----表示游标名字
       select ...             ----表示SELECT语句


四、游标使用：
  
   打开游标：

      命令：open cursor_name;    --例如：open A;    
  
               //分配内存空间，执行游标中定义的SELECT语句，存储结果到内存中  

   关闭游标：

      命令：close cursor_name;   --例如：close A;  
 
            //关闭游标，释放内存。因此在游标使用完成时，一定要关闭游标。

   提取游标数据：
    
      命令： fetch cursor_name into var_name;    --例如：fetch A into v_A;  
 
            //提取游标当前值到变量v_A中，v_A是复合变量。

   游标变量：

          cursor_name%found : 当找到数据时，结果为真(true)；反之为假(false);

          cursor_name%notfound : 当无数据找到时，结果为真(true)；反之为假(false);
      

五、实例应用


   1、游标数据简单提取： 

    --set serverout on;      打开服务器输出。

    declare
       cursor c_emp is select * from emp;
       v_emp c_emp%rowtype;
    begin
       open c_emp;
       fetch c_emp into v_emp;                            --只取出第一行。
       dbms_output.put_line(v_emp.name);
       close c_emp;
    end;


    --执行结果：


     js_china


     PL/SQL procedure successfully completed.


  2、遍历游标（推荐/经典）: 
  
     提示：for循环中游标不需要open、close、featch，for语句会自动完成。

    --set serverout on;      打开服务器输出。

    declare
      cursor c is select * from emp; 
    begin
      for c_emp in c                                 --for循环中游标不需要open、close、featch，for语句会自动完成。
      loop 
       dbms_output.put_line(c_emp.name);
      end loop;
    end;
    
   
  3、遍历游标(while..loop): 


    --set serverout on;      打开服务器输出。 
    declare
      cursor c is select * from emp;
      v_emp c%rowtype;
    begin
      open c;                                 --必须打开游标
      fetch c into v_emp;
      while (c%found) loop
        dbms_output.put_line(v_emp.name);
        fetch c into v_emp;
      end loop;
      close c;
    end;


  4、遍历游标(loop ..exit when): 
 
    --set serverout on;          打开服务器输出。 
    declare
      cursor c is select * from emp;
      v_emp c%rowtype;
    begin
      open c;
      loop
        fetch c into v_emp;
        exit when (c%notfound);
        dbms_output.put_line(v_emp.name);
      end loop;
      close c;
    end;
  
   5、游标更新数据:
     
    --set serverout on;      打开服务器输出。 
    declare
      cursor c is select * from emp for update;      --必须加for update
    begin
      for c_emp in c 
      loop
        if (c_emp.sal < 2000) then 
          update emp set sal=sal + 5000 where current of c;
        elsif (c_emp.sal between 2000 and 10000 ) then
          update emp set sal=sal + 3000  where current of c;
        elsif (c_emp.sal between 10000 and 20000 ) then
          update emp set sal=sal + 1000  where current of c;
        else
          update emp set sal=sal + 100  where current of c; 
        end if ;
      end loop;
      commit;
    end;


   6、带参数的游标:
     
    --set serverout on;      打开服务器输出。 
    declare
      cursor c(v_deptno number) is select * from emp where emp.deptno = v_deptno;
    begin
      for c_emp in c(20)
      loop
         dbms_output.put_line(c_emp.name || ' ''s sal is '||c_emp.sal);
      end loop;
    end;


    执行结果：


    js_china 's sal is 16060
    js2 's sal is 14260
    hgx 's sal is 13260

    PL/SQL procedure successfully completed.