显式游标和游标变量具有相同的属性。
%ISOPEN：如果打开了游标则返回TRUE，否则返回FALSE。该属性用于：
l
在打开游标前用于检测显式游标是否打开。
l
在关闭游标前，检测是否已经打开。
CREATE OR REPLACE PROCEDURE P_6_11 AS
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
END;
%FOUND：用于判断是否有取回的行。返回值：
l
NULL 在打开显式游标之后，FETCH数据之前。
l
TRUE 最近一个FETCH获得了一条数据
l
FALSE 其他
CREATE OR REPLACE PROCEDURE P_6_12 AS
CURSOR c1 IS SELECT last_name, salary FROM employees
WHERE ROWNUM < 11 ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 103 -
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed
EXIT;
END IF;
END LOOP;
END;
--Result:
--Name = Austin, salary = 4800
--Name = De Haan, salary = 17000
--Name = Ernst, salary = 6000
--Name = Faviet, salary = 9000
--Name = Greenberg, salary = 12008
--Name = Hunold, salary = 9000
--Name = King, salary = 24000
--Name = Kochhar, salary = 17000
--Name = Lorentz, salary = 4200
--Name = Pataballa, salary = 4800
%NOTFOUND：用于退出循环
l
NULL在打开显式游标之后，FETCH数据之前。
l
FLASE最近一个FETCH获得了一条数据
l
TRUE其他
CREATE OR REPLACE PROCEDURE P_6_13 AS
CURSOR c1 IS SELECT last_name, salary FROM employees
WHERE ROWNUM < 11 ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = '|| my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
--Result:
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 104 -
--Name = Austin, salary = 4800
--Name = De Haan, salary = 17000
--Name = Ernst, salary = 6000
--Name = Faviet, salary = 9000
--Name = Greenberg, salary = 12008
--Name = Hunold, salary = 9000
--Name = King, salary = 24000
--Name = Kochhar, salary = 17000
--Name = Lorentz, salary = 4200
--Name = Pataballa, salary = 4800
%ROWCOUNT：返回值
l
ZERO：在打开显式游标之后，FETCH数据之前
l
其他：FETCH获得了行数
CREATE OR REPLACE PROCEDURE P_6_14 AS
CURSOR c1 IS SELECT last_name FROM employees
WHERE ROWNUM < 11 ORDER BY last_name;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE( name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
END IF;
END LOOP;
CLOSE c1;
END;