带参数显式游标
/*
===========================================================
| 带参数显式游标
============================================================
*/
DECLARE
CURSOR emp_cursor(no NUMBER) IS
SELECT ename,sal
FROM employee
WHERE deptno=no;
emp_record emp_cursor%ROWTYPE;
v_sal employee.sal %TYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor(10);
END IF;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
使用显式游标更新行
/*
===========================================================
| 使用显式游标更新行
============================================================
*/
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal,e.deptno,dname
FROM employee e,dept d
WHERE e.deptno=d.deptno
FOR UPDATE OF e.job;
emp_record emp_cursor%ROWTYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF emp_record.deptno=30 THEN
UPDATE employee SET sal=sal+100 WHERE CURRENT OF emp_cursor;
END IF;
dbms_output.put_line(emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
ROLLBACK;
使用游标类型变量
/*
===========================================================
| 使用游标类型变量
============================================================
*/
DECLARE
CV SYS_REFCURSOR; -- cursor variable
v_lastname employee.ename%TYPE; -- variable for last_name
query_2 VARCHAR2(200) := 'SELECT * FROM dept';
v_employee employee%ROWTYPE; -- record variable row of table
v_dept dept%ROWTYPE; -- record variable row of table
BEGIN
OPEN CV FOR
SELECT ename
FROM employee
WHERE job='MANAGER'
ORDER BY ename;
LOOP
FETCH CV
INTO v_lastname;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_lastname);
END LOOP;
DBMS_OUTPUT.PUT_LINE('-------------------------------------');
OPEN CV FOR query_2;
LOOP
FETCH CV
INTO v_dept;
EXIT WHEN CV%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept.dname);
END LOOP;
CLOSE CV;
END;
循环游标
/*
===========================================================
| 循环游标
============================================================
*/
DECLARE
CURSOR emp_cursor(no NUMBER) IS
SELECT ename,sal
FROM employee
WHERE deptno=no;
BEGIN
FOR emp_record IN emp_cursor(10) LOOP
dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
END LOOP;
END;
隐式游标
/*
===========================================================
| 隐式游标
============================================================
*/
--%NOTFOUND属性举例
DECLARE
v_name employee.ename%TYPE;
BEGIN
SELECT ename INTO v_name
FROM employee
WHERE empno=45;
IF SQL%NOTFOUND THEN
dbms_output.put_line('NOTFOUND-不存在该员工');
ELSE
dbms_output.put_line('存在该员工');
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found-不存在该员工');
WHEN OTHERS THEN
dbms_output.put_line('其他错误');
END;
游标类型变量
/*
===========================================================
| 游标类型变量
============================================================
*/
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal
FROM employee
WHERE deptno=10;
--v_ename employee.ename%TYPE;
--v_sal employee.sal%TYPE;
v_emp emp_cursor%ROWTYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_emp.ename||':'||v_emp.sal);
END LOOP;
CLOSE emp_cursor;
END;