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

来源：互联网发布：淘宝客服聊天记录技巧编辑：程序博客网时间：2024/05/22 16:39

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

SET serveroutput ONDROP 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 ONSQL> DROP TABLE emp2 PURGE;Table droppedSQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;Table createdSQL> 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:CLARKdeptno:10  ename:KINGdeptno:10  ename:MILLERPL/SQL procedure successfully completed

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

0 0