游标的概念
游标(cursor)可以看作是私有SQL区的句柄或名称,在SQL语句执行过程中保存解析后的语句和其它信息.
隐式游标与显式游标
游标分为隐式游标与显式游标
- 隐式游标: 当运行DML与 SELECT INTO 语句时,PLSQL会打开一个隐式游标.隐式游标自动打开,自动关闭无法人工干预
- 显式游标: 用户在代码中定义的游标,并由用户在程序中控制打开,检索,关闭.
游标的属性
游标属性 | 隐式游标 | 显式游标 |
---|---|---|
ISOPEN | 永远为FALSE | 游标处于打开状态为TRUE,反之为FALSE |
FOUND | 涉及到数据为TRUE 反之为FALSE | 可以提取到数据为TRUE,反之为FALSE |
NOT FOUND | 与上面含义相反 | 与上面含义相反 |
ROWCOUNT | 返回涉及到数据的总行数 | 累计从结果集中提取到多少行 |
隐式游标属性
由于没有显示的声明游标,所以隐式游标也没有名字,引用隐式游标属性只能使用关键字SQL.
如果语句块中存在多个DML 或 SELECT INTO 则隐式游标属性为最近执行的SQL的属性
例程:ROWCOUNT
BEGIN
DELETE FROM SCOTT.EMP;
DBMS_OUTPUT.put_line(SQL%ROWCOUNT); --当前游标属性为删除EMP表的属性
DELETE FROM SCOTT.DEPT;
DBMS_OUTPUT.put_line(SQL%ROWCOUNT); --当前游标属性为删除DEPT表的属性
ROLLBACK ;
END;
执行结果
14
4
PL/SQL procedure successfully completed
例程:ISOPEN
BEGIN
DELETE FROM SCOTT.EMP; --隐式游标自动打开关闭,所以ISOPEN 状态永远为FALSE
IF SQL%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR OPEN');
ELSE
DBMS_OUTPUT.put_line('CURSOR CLOSE!');
END IF ;
ROLLBACK ;
END;
执行结果
CURSOR CLOSE!
PL/SQL procedure successfully completed
例程:FOUND
BEGIN
DELETE FROM SCOTT.EMP;
IF SQL%FOUND THEN
DBMS_OUTPUT.put_line('ROWS FOUND!');
ELSE
DBMS_OUTPUT.put_line('ROWS NOT FOUND!');
END IF ;
ROLLBACK ;
END;
执行结果
ROWS FOUND!
PL/SQL procedure successfully completed
例程:NOTFOUND
BEGIN
DELETE FROM SCOTT.EMP WHERE 1=2 ;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.put_line('ROWS NOT FOUND! ');
ELSE
DBMS_OUTPUT.put_line('ROWS FOUND!');
END IF ;
ROLLBACK ;
END;
执行结果
ROWS NOT FOUND!
PL/SQL procedure successfully completed
显示游标属性
例程:ISOPEN
BEGIN
DELETE FROM SCOTT.EMP; --隐式游标自动打开关闭,所以ISOPEN 状态永远为FALSE
IF SQL%ISOPEN THEN
DBMS_OUTPUT.put_line('CURSOR OPEN');
ELSE
DBMS_OUTPUT.put_line('CURSOR CLOSE!');
END IF ;
ROLLBACK ;
END;
执行结果
CURSOR CLOSE!
PL/SQL procedure successfully completed
DECLARE
CURSOR C1 IS SELECT * FROM EMP ;
BEGIN
IF C1%ISOPEN
THEN
DBMS_OUTPUT.put_line('BEFORE OPEN');
END IF ;
OPEN C1 ;
IF C1%ISOPEN
THEN
DBMS_OUTPUT.put_line('AFTER OPEN');
END IF ;
CLOSE C1 ;
IF C1%ISOPEN
THEN
DBMS_OUTPUT.put_line('AFTER CLOSE');
END IF ;
END;
执行结果
AFTER OPEN
PL/SQL procedure successfully completed
例程:FOUND
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP WHERE ROWNUM <= 2;
V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
OPEN C1;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('BEFORE FETCH FOUND !');
END IF;
FETCH C1
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('1 FETCH FOUND !');
END IF;
FETCH C1
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('2 FETCH FOUND !');
END IF;
FETCH C1 --由于SQL只返回两行,此时FETCH 无法获取最新的数据
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('3 FETCH FOUND !');
END IF;
CLOSE C1 ;
END;
执行结果
1 FETCH FOUND !
2 FETCH FOUND !
PL/SQL procedure successfully completed
例程:NOTFOUND
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP WHERE ROWNUM <= 2;
V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
OPEN C1;
IF C1%NOTFOUND THEN
DBMS_OUTPUT.put_line('BEFORE FETCH NOT FOUND !');
END IF;
FETCH C1
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('1 FETCH NOT FOUND !');
END IF;
FETCH C1
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('2 FETCH NOT FOUND !');
END IF;
FETCH C1
INTO V_ENAME;
IF C1%FOUND THEN
DBMS_OUTPUT.put_line('3 FETCH NOT FOUND !');
END IF;
CLOSE C1 ;
END;
执行结果
1 FETCH NOT FOUND !
2 FETCH NOT FOUND !
PL/SQL procedure successfully completed
例程:ROWCOUNT
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP WHERE ROWNUM <= 2;
V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
OPEN C1;
DBMS_OUTPUT.put_line(C1%ROWCOUNT); --此处返回0 因为还没提取到数据
FETCH C1
INTO V_ENAME;
DBMS_OUTPUT.put_line(C1%ROWCOUNT); --此处返回1 已经提取了1行数据
FETCH C1
INTO V_ENAME;
DBMS_OUTPUT.put_line(C1%ROWCOUNT); --此处返回2 已经提取了2行数据
FETCH C1
INTO V_ENAME;
DBMS_OUTPUT.put_line(C1%ROWCOUNT); --此处返回2 因为并没有新行被提取
CLOSE C1;
END;
执行结果
0
1
2
2
PL/SQL procedure successfully completed
游标循环
游标对应的SQL语句可能会返回很多的行,而游标的只能一次提取一行,此时我们需要使用循环遍历这些行.
简单游标循环
例程:简单游标循环
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP ORDER BY ENAME ASC;
V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1
INTO V_ENAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.put_line(V_ENAME);
END LOOP;
CLOSE C1;
END;
执行结果
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
PL/SQL procedure successfully completed
WHILE 游标循环
例程: WHILE 游标循环
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP ORDER BY ENAME ASC;
V_ENAME SCOTT.EMP.ENAME%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO V_ENAME ; --由于WHILE循环是先判断后循环,所以需要先FETCH 一次
WHILE C1%FOUND
LOOP
DBMS_OUTPUT.put_line(V_ENAME);
FETCH C1
INTO V_ENAME;
END LOOP;
CLOSE C1;
END;
执行结果
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
PL/SQL procedure successfully completed
游标FOR循环
游标FOR循环比较简单,并且自动打开游标,自动关闭游标,
游标FOR循环有两种实现形式
例程:游标FOR循环1
DECLARE
CURSOR C1 IS
SELECT ENAME FROM SCOTT.EMP ORDER BY ENAME ASC;
BEGIN
FOR R IN C1 --R为记录类型
LOOP
DBMS_OUTPUT.put_line(R.ENAME);
END LOOP ;
END;
执行结果
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
PL/SQL procedure successfully completed
例程:游标FOR循环2
BEGIN
FOR R IN ( SELECT ENAME FROM SCOTT.EMP ORDER BY ENAME ASC) --R为记录类型
LOOP
DBMS_OUTPUT.put_line(R.ENAME);
END LOOP ;
END;
执行结果
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
PL/SQL procedure successfully completed