Oracle学习笔记4-游标的基本概念

         游标的定义

         是一种PL/SQL控制结构，它可以命名一个工作区来来存储该工作区的存储信息。游标可以增强SQL语句的功能，它可以对SQL语句的处理进行显示的控制。

        游标一般有两种个形式：显示游标和隐式游标。PL/SQL会隐式的为所有的DMLSQL语句创建一个游标，对于那些返回多条记录的查询语句，我们应该显示的声明一个游标来逐个处理这些数据：

          declare

                cursor  mycus  is 

                       select  s_name , s_age  from  student  where  stu_grade = ‘04212’;

       上面的例子就返回一个包含有多条记录的结果集，而游标就像是指向当前记录的指针。

        以下就显示游标和隐式游标分别介绍；

        1） 显式游标

               我们可以在PL/SQL块，子程序或任何包的声明部分显示的声明游标，可以用：open、fetch、close三个指令来控制游标。

              显示游标的处理一般包括四个步骤：声明游标、打开游标、提取游标(将结果集提取到PL/SQL变量中)。

        a)    声明游标

               在PL/SQL中不允许前向引用，因此在引用游标前必须在声明部分声明它，详见如下：

               cursor cursor_name [(parameter[,parameter])] [return return_type] is select_statement;

               解释：

　　                 cursor_name要声明游标的名称

                         return  return_type是可选项，是指游标的返回类型，一定要和返回结果集的类型一样

                        select_statement是将要进行处理的select语句（不能包含into语句因为，into语句是fetch语句的一部分），任何语句在这出现都是合法的，如UNION,JOIN。

           为了确保所有的游标声明中被引用的变量在被引用之前已经被声明，一个好的办法就是在声明的结尾部分再声明它。如下；

           

          有时也可以用游标自己带的参数赋值给where子句，但只能在游标内使用。超出游标以外的程序是访问不到的。就是在声明游标的时候设置一个参数cursor(s_grade  student.stu_grade%type),然后在打开游标之前，给改参数赋值并传给where子 句。

         b）打开游标

        我们利用打开游标来执行查询并得到一个结果，该结果集由查询返回的全部记录组成，如果定义的游标用了FOR UPDARE子句的话，打开游标的同时锁住这些记录。当我们打开一个结果集之后，该结果集是静态的，也就是说在游标打开后，所有对数据的错做的SQL语句对游标提取出来的结果集都没有影响，直到游标关闭再打开后。

         打开的一般形式为：OPEN CURSOR_NAME.

         当OPEN指令打开一个游标的时候，如果游标的SELECT语句有联编变量的话，将会对变量的取值进行检查，同时结果集被确定，游标的指针指向结果集的第一行。 如果游标中有参数的话，可以在OPEN语句中来传递参数给游标，除非用缺省值，如：

             

        c) 提取游标

        打开一个游标之后就应该从游标中提取数据，提取用FETCH语句，格式如下：

        FETCH CURSOR_NAME INTO VARIABLES_LIST

        其中CURSOR_NAME为游标的名称，VARIABLES_LIST为已经被声明的和游标中提取的结果集类型相同的PL/SQL变量列表，或者是和游标中提取的结果集类型相，同的已被声明的PL/SQL记录类型。

         在没有用循环控制语句的情况下，除非用了BULK COLLECT(批处理)子句。否则FETCH语句一次只能从结果集中提取一行，而且一次执行完后，游标的指针将自动指向结果集的下一行。而且游标的提取只能向前的，如果要重新提取已经提取过的数据只有重新打开游标。

         当游标的指针指向结果集的结尾后，FETCH语句检索出来的值是不确定的，而且Oracle不会报错。当我们在游标中使用BULK COLLECT子句可以使我们一次性绑定游标中的全部结果集，也就是说一次性将所有的记录提取到一个集合里。如下：

            declare

                type  nameTab is table of student.stu_name%type;

               type  ageTab is  table of student.stu_age%type;

              names nameTab;

               ages  ageTab;

              s_grade student.stu_grade;

              corsor mycos is

                       select stu_name , stu_age fro student where stu_grade = s_grade;

               begin

                       s_grade := ‘04212’;

             open mycos;

                        fetch mycos bulk collect into stu_name , stu_age;

             close mycos;

             for i in 1..names.count Loop

                        dbms_output.put_line(s_name||’今年’||s_age||’岁’)；

            end Loop;

           end;

         d）关闭游标

        当我们将结果集中的所有数据都检索出来之后，应关闭游标，关闭之后所有的和该游标有关的资源都会被释放，此时我们如果对任何关闭了游标的操作都会引发INVALID_CURSOR错误。关闭游标的形式如下： close  cursor_name。同样，引用已关闭的游标也会引发以上错误。

         e）显式游标的属性

        显示游标的属性返回关于执行多行记录的情况的消息。有四个属性：%ISOPEN、%FOUND、%NOTFOUND、%

        ROWCOUNT。

      （1）%FOUND

                当游标或游标变量被打开且在执行FETCH语句之前，它为NULL.如果FETCH有记录返回，则为true，没有为false。       

       （2）%NOTFOUND

               和上述%FOUND刚好相反，当游标或游标变量被打开但在执行FETCH语句之前时为null，如果FETCH的最后一行有记录返回为false，无则为true。

      （3）%ROWCOUNT

              属性是用来返回迄今为止从游标中返回的记录数目，在游标变量或游标没有打开而没有执行FETCH之前，它的属性为0.以后就为从游标中取出的记录数，FETCH每成功执行一次值就加一。

    （4）%ISOPEN

             当游标变量或游标被打开时，为true，否则为false。我们可以用该属性来判断一个游标是否被打开。

      2）隐式游标        

           前面我们说过Oracle为每一个不属于显式游标的SQL DML都创建了一个隐式游标，它没有名字，也称为SQL游标。和隐式不同的是不能对一个隐式游标显式的执行OPEN、CLOSE和FETCH语句。Oracle隐式地打开SQL游标，处理SQL游标，然后再关闭 该游标。但是，和显式游标相同的是也具有四大属性。

        但是，由于隐式游标没有名字，Oracle提供了这样的一种方法来访问游标属性：在属性的前面加关键字SQL。

        如下：

          declare

                imp_count integer;

         begin

              update student set salary = salary * 1.5 ;

              imp_count := SQL%ROWCOUNT;

             dbms_output.put_line(‘共影响||imp_count||’行’);

         end;

         由于Oracle自动创建并打开隐式游标，执行完后再自动关闭，所以我们在访问它的%ISOPEN属性时，总是假的。

        

         和显示游标一样，在SQL语句执行前隐式游标的属性：Found和Nofound属性为null，当操作执行完之后，影响一行或多行，则前者为true，后者为false。

         如下：

                  declare

                          imp_count;

                   begin

                           select * from student ;

                           if SQL%ROOWCOUNT then

                                  imp_count := SQL%ROWCOUNT

                                   dbms_output.put_line(‘共有’||imp_count);

                            then

                                    dbms_output.put_line(‘没有数据’);

                             end if;

                      end;

              一个值得注意的问题是：当SELECT . . INTO . .没有检索到数据时，会引发NO_DATA_FOUND错误，它会立即将控制权异常处理部分而不在往下执行。这是如果后面再跟一个NOTFOUND的话，则不起作用。

             如下所示：

                         

            另一个值得注意的问题是：由于隐式游标的属性值总是引用最近的SQL语句，所以在一个SQL块中，由于范围不同，属性的取值也有不同。