游标在定义还是打开时读取数据

有网友在问这个问题，这很容易证明，测试语句如下

SET serveroutput ON
DROP TABLE emp2 PURGE;
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
DECLARE
  V_DEPTNO NUMBER;
  CURSOR CUR_EMP2 IS
    SELECT * FROM EMP2 WHERE DEPTNO = V_DEPTNO;
BEGIN
  DELETE FROM emp2;
  V_DEPTNO := 10;
  INSERT INTO EMP2
    SELECT * FROM EMP WHERE DEPTNO = 10;
  FOR CUR IN CUR_EMP2 LOOP
    DBMS_OUTPUT.PUT_LINE('deptno:' || TO_CHAR(CUR.DEPTNO) || '  ename:' ||
                         CUR.ENAME);
  END LOOP;
END;
/

我们来看测试结果

SQL> SET serveroutput ON
SQL> DROP TABLE emp2 PURGE;
Table dropped
SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
Table created
SQL> DECLARE
  2    V_DEPTNO NUMBER;
  3    CURSOR CUR_EMP2 IS
  4      SELECT * FROM EMP2 WHERE DEPTNO = V_DEPTNO;
  5  BEGIN
  6    DELETE FROM emp2;
  7    V_DEPTNO := 10;
  8    INSERT INTO EMP2
  9      SELECT * FROM EMP WHERE DEPTNO = 10;
 10    FOR CUR IN CUR_EMP2 LOOP
 11      DBMS_OUTPUT.PUT_LINE('deptno:' || TO_CHAR(CUR.DEPTNO) || '  ename:' ||
 12                           CUR.ENAME);
 13    END LOOP;
 14  END;
 15  /
deptno:10  ename:CLARK
deptno:10  ename:KING
deptno:10  ename:MILLER
PL/SQL procedure successfully completed

在定义时emp2中数据为空，在打开前我们添加了部门10的数据。可以看出查询到了部门10的信息。