显式游标和游标变量具有相同的属性。 %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;