Oracle_PL_ SQL_ 教程：游标

3wws666m2d

--######################################--
--游标
--王林 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;