一.游标概念
游标是sql的一个内存工作区,由系统或用户以变量的形式定义。其作用就是临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在硬盘的表中调到计算机内存中进行处理,最终将处理结果显示出来或写回到数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
游标分为两种类型:显式游标和隐式游标。通常用到的select..into语句,一次只能从数据库中提取一行数据,对于这种形式的查询或者dml操作,系统都会使用一个隐式游标,但是如果要提取多行数据,就需要手工定义一个显示游标,并通过与游标相关的语句进行处理。显式游标对应一个返回结果为多行多列的select语句。
游标一旦打开,数据就从数据库中传送到游标变量中,然而应用程序从游标变量中分解出需要的数据,并进行处理。
二.隐式游标
dml操作和单行select语句会使用隐式游标,分别是:
a. insert
b. delete
c. update
d. select .. into ..
当系统使用一个隐式游标的时候,可以用过隐式游标的属性来访问操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字sql来访问,但是需要注意的是,通过sql游标名总是只能访问到前一个dml操作或单行select操作的游标属性。所以通常在执行完操作之后,立即使用sql游标名来访问属性。游标属性有以下四种:
a. sql%isopen(布尔型)
b. sql%isfound(布尔型)
c. sal%notfound(布尔型)
d. sal%rowcount(数值型)
当执行一条DML语句后,DML语句的结果保存在四个游标属性中,用于控制程序流程或者程序的状态。当运行dml语句时,plsql打开一个内建游标并处理结果,游标是维护查询结果的内存中的一个区域,游标在运行dml语句时打开,完成后关闭。
需要注意的点:
a. 对于隐式游标,sql%isopen总是关闭的,因为隐式游标在dml语句执行时打开,结束就立刻关闭。
b. 隐式游标在select..into中使用有三种可能
B1:结果集只有一行,且select是成功的
B2:没有查询到任何结果集,NO_DATA_FOUND异常
B3:结果集中含有两行或两行以上数据,TOO_MANY_ROWS异常
使用实例:
BEGIN
UPDATE EMP1 A SET A.ENAME = 'test'WHERE A.EMPNO = '7369';
IF SQL%ISOPENTHEN
DBMS_OUTPUT.PUT_LINE('opening');
ELSE
DBMS_OUTPUT.PUT_LINE('closing');
END IF;
IF SQL%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('游标指向了有效行');
ELSE
DBMS_OUTPUT.PUT_LINE('sorry');
END IF;
DBMS_OUTPUT.PUT_LINE('更改行数:'|| SQL%ROWCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
WHEN OTHERSTHEN
DBMS_OUTPUT.PUT_LINE('err');
END;
三.显示游标
显示游标的使用分为四个部分
a. 声明
Cursor [游标名](参数..) is select..;
参数为可选部分,所定义的参数可以出现在select之后的where字句中,如果定义了参数,则在打开游标时,必须传递相应的参数值。
b. 打开游标
Open [游标名](参数值…)
打开游标时,select语句的查询结果就被传递到了游标工作区
c. 提取数据
Fetch [游标名] into [变量名]
游标打开后有一个指针指向数据区,fetch语句一次返回指针所指的一行数据,要返回多行需要重复执行,可以通过循环语句实现。控制循环可以通过判断游标的属性实现。
d. 关闭游标
Close [游标名]
显示游标打开后必须显示的关闭。游标一旦关闭,所占用的资源也就释放,游标变为无效。
--FETCH游标的使用实例(loop)
DECLARE
CURSOR C_CUR IS
SELECT * FROM EMP;
C_CUR_ROW C_CUR%ROWTYPE;
BEGIN
OPEN C_CUR;
LOOP
FETCH C_CUR
INTO C_CUR_ROW;
EXIT WHEN C_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||
C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);
END LOOP;
CLOSE C_CUR;
END
--使用游标和while循环取游标
DECLARE
CURSOR C_CUR IS
SELECT * FROM EMP;
C_CUR_ROW C_CUR%ROWTYPE;
BEGIN
OPEN C_CUR;
FETCH C_CUR
INTO C_CUR_ROW;
WHILE C_CUR%FOUNDLOOP
DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||
C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);
FETCH C_CUR
INTO C_CUR_ROW;
END LOOP;
END;
--使用for循环简化游标的使用,此时不再需要open,fetch,close的游标操作,也不需要notfoun属性来检测是否到最后一条记录,这一切由oracle隐式的完成
DECLARE
CURSOR C_CUR IS
SELECT * FROM EMP;
C_CUR_ROW C_CUR%ROWTYPE;
BEGIN
FOR C_CUR_ROW IN C_CUR LOOP
DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||
C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);
END LOOP;
END;
;
--参数游标的使用
DECLARE
CURSOR C_CUR(P_JOBVARCHAR2) IS
SELECT * FROM EMP WHERE JOB= P_JOB;
C_CUR_ROW C_CUR%ROWTYPE;
BEGIN
FOR C_CUR_ROW IN C_CUR('SALESMAN')LOOP
DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||
C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);
END LOOP;
END;
--使用游标更新数据
DECLARE
CURSOR C_UPDATE IS
SELECT * FROM EMP1 FORUPDATE OF SAL;
C_UPDATE_INFO C_UPDATE%ROWTYPE;
SALEINFO EMP.SAL%TYPE;
BEGIN
FOR C_UPDATE_INFO IN C_UPDATE LOOP
IF C_UPDATE_INFO.SAL < 1500 THEN
SALEINFO := C_UPDATE_INFO.SAL* 1.2;
ELSIF C_UPDATE_INFO.SAL < 2000 THEN
SALEINFO := C_UPDATE_INFO.SAL* 1.5;
END IF;
UPDATE EMP1 SET SAL = SALEINFO WHERE EMPNO = C_UPDATE_INFO.EMPNO;
END LOOP;
END;
--删选两个资格最老的人员信息定义计数器控制
DECLARE
CURSOR C_CUR IS
SELECT * FROM EMP ORDERBY HIREDATE;
C_CUR_ROW C_CUR%ROWTYPE;
TOP_N NUMBER := 2;
BEGIN
OPEN C_CUR;
FETCH C_CUR
INTO C_CUR_ROW;
WHILE TOP_N > 0 LOOP
DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||
C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);
TOP_N := TOP_N - 1;
FETCH C_CUR
INTO C_CUR_ROW;
END LOOP;
CLOSE C_CUR;
END;