Oracle 游标

带参数显式游标

/*
===========================================================
|        带参数显式游标
============================================================
*/
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;