Oracle学习笔记（3）

--游标cursor

游标的作用：不让程序中的select直接向终端屏幕输出，转向将select结果输出到PL/SQL程序中的内存变量

(a).隐式游标

    a).  SELECT  ...  INTO  ...  FROM  ... 【只能返回一行，不能多行】

    b).  FOR  cur  IN  ( SELECT  ...)  LOOP

          ...

         END  LOOP  ;

(b).显式游标

  定义游标：   CURSOR  cursorName[(parameter[,parameter]...)]  IS  SELECT  ...

  打开游标：   OPEN  cursorName;

  抓取游标数据到变量：  FETCH  cursorName  INTO (变量列表 | 记录型)；

  关闭游标：  CLOSE  cursorName;

 

  例：

    Name      Type          Nullable  Default  Comments

    ------- ------------ -------- ------- --------

   EMP_NO   NUMBER ( 10  )   Y                        

    NAME      VARCHAR2 ( 20  ) Y                        

   AGE      NUMBER ( 3  )    Y                        

   TEL      VARCHAR2 ( 30  ) Y                        

   DEPT_NO  NUMBER ( 5  )    Y                        

   SAL      NUMBER ( 15  , 2 ) Y                        

   BD       DATE          Y                       

 

 

    DECLARE

   v_id  NUMBER (  3 );

   v_name  VARCHAR2 ( 5  );

   v_age  NUMBER ( 3  );

   v_sal  NUMBER ( 12  );

    CURSOR  cur1  IS  SELECT  ROWNUM ,  NAME ,age,sal  FROM  t1;

    BEGIN

      OPEN  cur1;

           FOR  i  IN  1 ..10  LOOP

                FETCH  cur1  INTO  v_id,v_name,v_age,v_sal; 

               dbms_output.put_line(v_id|| ','  ||v_name|| ',' ||v_age||  ',' ||v_sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

    

    

    

    DECLARE

    TYPE  rec  IS  RECORD  (

   v_id  NUMBER (  3 ),

   v_name  VARCHAR2 ( 5  ),

   v_age  NUMBER ( 3  ),

   v_sal  NUMBER ( 12  )

   );

   recv rec;

    CURSOR  cur1  IS  SELECT  ROWNUM ,  NAME ,age,sal  FROM  t1;

    BEGIN

      OPEN  cur1;

           FOR  i  IN  1 ..10  LOOP

                FETCH  cur1  INTO  recv; 

               dbms_output.put_line(recv.v_id|| ','  ||recv.v_name|| ',' ||recv.v_age||  ',' ||recv.v_sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

    

    

    DECLARE

   recv t1%  ROWTYPE ;

    CURSOR  cur1  IS  SELECT  *  FROM  t1;

    BEGIN

      OPEN  cur1;

           FOR  i  IN  1 ..10  LOOP

                FETCH  cur1  INTO  recv; 

               dbms_output.put_line(recv.EMP_NO|| ','  ||recv.name|| ',' ||recv.age||  ',' ||recv.sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

游标属性   【使用方式 ：游标名游标属性    例：cursorName%  FOUND 】

　% FOUND  布尔型属性，当最近一次读记录时成功返回,则值为TRUE；

　% NOTFOUND  布尔型属性，与%FOUND相反；

　% ISOPEN  布尔型属性，当游标已打开时返回  TRUE ；

  % ROWCOUNT  数字型属性，返回已从游标中读取的记录数。

    

  DECLARE /*表中有多少条数据就输出多少条*/

   rowscount  NUMBER ( 2  );

   recv t1%  ROWTYPE ;

    CURSOR  cur1  IS  SELECT  *  FROM  t1;

    BEGIN

      SELECT  COUNT  (*)  INTO  rowscount  FROM  t1;

      OPEN  cur1;

           FOR  i  IN  1 ..rowscount  LOOP

                FETCH  cur1  INTO  recv; 

               dbms_output.put_line(recv.EMP_NO|| ','  ||recv.name|| ',' ||recv.age||  ',' ||recv.sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

  

    DECLARE /*表中有多少条数据就输出多少条*/

   rowscount  NUMBER ( 2  );

   recv t1%  ROWTYPE ;

    CURSOR  cur1  IS  SELECT  *  FROM  t1;

    BEGIN

      SELECT  COUNT  (*)  INTO  rowscount  FROM  t1;

      OPEN  cur1;

           FOR  i  IN  1 ..20  LOOP

                FETCH  cur1  INTO  recv;

                EXIT  WHEN  cur1% NOTFOUND ;

               dbms_output.put_line(recv.EMP_NO|| ','  ||recv.name|| ',' ||recv.age||  ',' ||recv.sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

带参数的游标使用

    DECLARE

   rowscount  NUMBER ( 2  );

   recv t1%  ROWTYPE ;

    CURSOR  cur1(v_emp_no  NUMBER /*地处的变量只能声明类型，不能指定长度*/  )  IS  SELECT  *  FROM  t1  WHERE  emp_no=v_emp_no  ORDER  BY  sal;

    BEGIN

      SELECT  COUNT  (*)  INTO  rowscount  FROM  t1;

      OPEN  cur1( 1  );

           FOR  i  IN  1 ..20  LOOP

               EXIT  WHEN  cur1% NOTFOUND ;

                FETCH  cur1  INTO  recv; 

               dbms_output.put_line(recv.EMP_NO|| ','  ||recv.name|| ',' ||recv.age||  ',' ||recv.sal);

           END  LOOP  ;

      CLOSE  cur1;

    END ;

  

     DECLARE

   rowscount  NUMBER ( 2  );

   recv t1%  ROWTYPE ;

    CURSOR  cur1(v_emp_no  NUMBER /*地处的变量只能声明类型，不能指定长度*/  )  IS  SELECT  *  FROM  t1  WHERE  emp_no=v_emp_no  ORDER  BY  sal;

    BEGIN

      FOR  i  IN  1 ..10  LOOP

      OPEN  cur1(i);

           LOOP

                FETCH  cur1  INTO  recv; 

                EXIT  WHEN  cur1% NOTFOUND ;

               dbms_output.put_line(cur1% rowcount  || ',' ||recv.EMP_NO||  ',' ||recv.name|| ','  ||recv.age|| ',' ||recv.sal);

               dbms_output.put_line( '---------------------------------------'  );

           END  LOOP  ;

      CLOSE  cur1;

      END  LOOP  ;

    END ;

  

游标的for循环

    DECLARE /*不带参数*/

    CURSOR  cura  IS  SELECT  *  FROM  t1;

    BEGIN

      FOR  recv  IN  cura  LOOP

       dbms_output.put_line(cura% rowcount  || ',' ||recv.EMP_NO||  ',' ||recv.name|| ','  ||recv.age|| ',' ||recv.sal);

      END  LOOP  ;

    END ;

  

     DECLARE /*带参数*/

    CURSOR  cura(v_emp_no  NUMBER )  IS  SELECT  *  FROM  t1  WHERE  emp_no=v_emp_no;

    BEGIN

      FOR  recv  IN  cura( 3 )  /*参数在这里使用*/  LOOP

       dbms_output.put_line(cura% rowcount  || ',' ||recv.EMP_NO||  ',' ||recv.name|| ','  ||recv.age|| ',' ||recv.sal);

      END  LOOP  ;

    END ;

  

      DECLARE /*带参数*/

    CURSOR  cura(v_emp_no  NUMBER )  IS  SELECT  *  FROM  t1  WHERE  emp_no=v_emp_no;

    BEGIN

      FOR  i  IN  1 ..10  LOOP

        FOR  recv  IN  cura(i) /*参数在这里使用*/  LOOP

         dbms_output.put_line(cura% rowcount  || ',' ||recv.EMP_NO||  ',' ||recv.name|| ','  ||recv.age|| ',' ||recv.sal);

        END  LOOP  ;

      END  LOOP  ;

    END ;

  

隐式游标属性

　　 SQL %  FOUND  布尔型属性,当最近一次读记录时成功返回，则值为true；

　　 SQL %  NOTFOUND  布尔型属性,与%found相反；

　　 SQL  %  ROWCOUNT  数字型属性, 返回已从游标中读取得记录数；

　　 SQL  %  ISOPEN  布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。

　　

    例：

     DECLARE

　　  v_deptno emp.deptno%  TYPE  := 2  ;

　　 BEGIN

　　   DELETE  FROM  emp  WHERE  deptno=v_deptno;

　　   IF  SQL  % FOUND  THEN

　　      DELETE  FROM  dept  WHERE  deptno=v_deptno;

　　   END  IF  ;

　　 END ;

 

     DECLARE

       TYPE  mycur  IS  REF  CURSOR ;

      cur1 mycur;

      rotype t1%  ROWTYPE ;

     BEGIN

         OPEN  cur1  FOR  SELECT  *  FROM  t1;

         LOOP

              FETCH  cur1  INTO  rotype;

              EXIT  WHEN  cur1% NOTFOUND ;

             dbms_output.put_line(rotype.name||  ' , ' ||rotype.age);

         END  LOOP  ;

         CLOSE  cur1;

     END ;

   

   

     CREATE  TABLE  t2  AS  SELECT  NAME  ,age  FROM  t1;

   

     DECLARE

    t1_r t1%  ROWTYPE ;

       TYPE  mycur  IS  REF  CURSOR  RETURN  t1_r% ROWTYPE /*添加return语句，进行请类型检查，限制返回的类型,return后面的返回类型必须是记录类型的*/  ;

      cur1 mycur;

      rotype t1%  ROWTYPE ;

     BEGIN

         OPEN  cur1  FOR  SELECT  *  FROM  t1;

         LOOP

              FETCH  cur1  INTO  rotype;

              EXIT  WHEN  cur1% NOTFOUND ;

             dbms_output.put_line(rotype.name||  ' , ' ||rotype.age);

         END  LOOP  ;

         CLOSE  cur1;

     END ;

     DECLARE

    t1_r t1%  ROWTYPE ;

       TYPE  mycur  IS  REF  CURSOR ;

      cur1 mycur;

      rotype t1%  ROWTYPE ;

      i  NUMBER ( 5  ):= 1 ;

     BEGIN

         OPEN  cur1  FOR  'SELECT * FROM t1 where emp_no=:a'  USING  i /*此处使用变量条件，使用条件变量时只能是弱类型游标*/  ;

         LOOP

              FETCH  cur1  INTO  rotype;

              EXIT  WHEN  cur1% NOTFOUND ;

             dbms_output.put_line(rotype.name||  ' , ' ||rotype.age);

         END  LOOP  ;

         CLOSE  cur1;

     END ;

     DECLARE

    t1_r t1%  ROWTYPE ;

       TYPE  mycur  IS  REF  CURSOR ;

      cur1 mycur;

      rotype t1%  ROWTYPE ;

      i  NUMBER ( 5  ):= 1 ;

      tableName  VARCHAR2 ( 20  ):= 't1' ;  /*使用游标变量可以灵活指定表名*/

     BEGIN

         OPEN  cur1  FOR  'SELECT * FROM ' ||tableName||  ' where emp_no=:a'  USING  i /*此处使用变量条件，使用条件变量时只能是弱类型游标*/  ;

         LOOP

              FETCH  cur1  INTO  rotype;

              EXIT  WHEN  cur1% NOTFOUND ;

             dbms_output.put_line(rotype.name||  ' , ' ||rotype.age);

         END  LOOP  ;

         CLOSE  cur1;

     END ;