Oracle_PL_ SQL_ 教程：游标

--######################################--
--游标
--王林 2012.07.06
--######################################--

/*
游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
对游标的操作包含四个部分：定义、打开、读取、关闭。
*/

--######################################--
--准备练习用表
CREATE TABLE wl_emp
AS
SELECT * FROM scott.emp;
--
SELECT * FROM wl_emp;

--######################################--
--一般游标
CREATE OR REPLACE PROCEDURE promotion_review_1
IS
    nemployeeid NUMBER;
    dstartdate  DATE;
    denddate    DATE;
    sjobid      VARCHAR2 (20);
       
    CURSOR cselectjob
    IS
        SELECT a.empno 
             , a.hiredate 
             , a.hiredate 
             , a.job
         FROM wl_emp a;
BEGIN
    OPEN cselectjob;
    LOOP
        FETCH cselectjob INTO nemployeeid
                            , dstartdate
                            , denddate
                            , sjobid ;
        EXIT WHEN cselectjob%NOTFOUND;
        DBMS_OUTPUT.put_line( 'Employee '
                              || nemployeeid
                              || ' has job '
                              || sjobid
                              || ' for '
                              || (denddate - dstartdate)
                              || ' days.'
                             );
    END LOOP;
    CLOSE cselectjob;
END;

--######################################--
--FOR游标
CREATE OR REPLACE PROCEDURE promotion_review_2
IS
    CURSOR cselectjob
    IS
        SELECT a.empno
             , a.hiredate dstartdate
             , a.hiredate denddate
             , a.job
          FROM wl_emp a;
BEGIN
    FOR jh_rec IN cselectjob LOOP
        DBMS_OUTPUT.put_line( 'Employee '
                              || jh_rec.empno
                              || ' had job '
                              || jh_rec.job
                              || ' for '
                              || (jh_rec.dstartdate - jh_rec.denddate)
                              || ' days.'
                            );
    END LOOP;
END;

--######################################--
--隐式游标
/*
隐式游标的%ISOPEN属性始终为FALSE，因为没有打开显示游标。为了访问隐式游标的属性可以使用SQL%attribute引用最近执行的隐式游标。
任何执行块或异常块中的DML语句都是隐式游标。它包括INSERT、UPDATE和DELETE语句。
在组合使用SELECT语句与INTO或BULK COLLECT INTO语句，或是在游标FOR循环语句中潜入SELECT语句时，也可以创建隐式游标。
PL/SQL块中的每条SQL语句实际上都是隐式游标。通过DML主句后使用%ROWCOUNT特性，可知道任何语句所改变的行数。
也可以对SELECT语句查询返回的行数。
*/
CREATE OR REPLACE PROCEDURE promotion_review_3
IS
    nempno NUMBER;
    CURSOR cselectjob
    IS
        SELECT a.empno
             , a.hiredate dstartdate
             , (a.hiredate + 1) denddate
             , a.job
          FROM wl_emp a;
BEGIN
    SELECT COUNT(*) 
      INTO nempno
      FROM wl_emp;
    
    DBMS_OUTPUT.put_line( 'There are ' 
                          || nempno
                          || ' employee history records.'
                         );
    FOR jh_rec IN cselectjob LOOP
        DBMS_OUTPUT.put_line( 'Employee '
                              || jh_rec.empno
                              || ' had job '
                              || jh_rec.job
                              || ' for '
                              || (jh_rec.denddate - jh_rec.dstartdate)
                              || ' days.'
                            );
    END LOOP;
END;

--######################################--
--REF游标
--动态显式游标
CREATE OR REPLACE PROCEDURE demo
IS 
    --局部变量的名称不能和列名相同，否则列名值会被替换而不是变量值。
    n_low_no  NUMBER ：= 7369;
    n_high_no NUMBER := 7654;
    emp_rec   VARCHAR2(20);    
    CURSOR c_selt_name
    IS
        SELECT a.ename
          FROM scott.emp a
         WHERE a.empno BETWEEN n_low_no AND n_high_no;
BEGIN
    OPEN c_selt_name;
    LOOP 
        FETCH c_selt_name INTO emp_rec;
        EXIT WHEN c_selt_name%NOTFOUND;
        DBMS_OUTPUT.put_line(emp_rec);
    END LOOP;
END demo;

--使用输入参数
CREATE OR REPLACE PROCEDURE demo
IS  
    n_low_no  NUMBER;
    n_high_no NUMBER;
    emp_rec   VARCHAR2(20);    
    CURSOR c_selt_name
    IS
        SELECT a.ename
          FROM scott.emp a
         WHERE a.empno BETWEEN n_low_no AND n_high_no;
BEGIN
    --将程序改变为使用输入参数。
    n_low_no 　:＝　TO_NUMBER(NVL(&1,7364))；
    n_high_no　：＝　TO_NUMBER(NVL(&2,7655))；
    OPEN c_selt_name;
    LOOP 
        FETCH c_selt_name INTO emp_rec;
        EXIT WHEN c_selt_name%NOTFOUND;
        DBMS_OUTPUT.put_line(emp_rec);
    END LOOP;
END demo;

--使用形式参数
CREATE OR REPLACE PROCEDURE demo
IS 
    n_low_no  NUMBER;
    n_high_no NUMBER;
    emp_rec   VARCHAR2(20); 
    --游标中的这些变量没有物理大小，因为他们在运行时获得。   
    CURSOR c_selt_name(low_id  NUMBER 
                     , high_id NUMBER)
    IS
        --SELECT语句中的变量不再是局部变量，他们是游标的局部变量，由游标定义中的形式参数定义。
        SELECT a.ename
          FROM scott.emp a
         WHERE a.empno BETWEEN low_id AND high_id;
BEGIN
    --在程序运行的时候，输入&1和&2分别赋值给局部变量n_low_no和n_high_no。
    n_low_no 　:＝　TO_NUMBER(NVL(&1,7364))；
    n_high_no　：＝　TO_NUMBER(NVL(&2,7655))；
    --局部变量成为打开游标时传递的实参，然后将实参赋给游标作用域的变量low_id和high_id。
    OPEN c_selt_name (n_low_no , n_high_no);
    LOOP 
        FETCH c_selt_name INTO emp_rec;
        EXIT WHEN c_selt_name%NOTFOUND;
        DBMS_OUTPUT.put_line(emp_rec);
    END LOOP;
END demo;