pl/sql---游标

游标

DECLARE
  CURSOR  A IS SELECT EMPNO,ENAME FROM EMP;
  B A%ROWTYPE;
BEGIN
  OPEN A;
  LOOP
    FETCH A INTO B;
    EXIT WHEN  A%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(B.EMPNO||' '||B.ENAME);
    
  END LOOP;
  CLOSE A;
END;   

-----LOOP
/

DECLARE
  CURSOR  A IS SELECT EMPNO,ENAME FROM EMP;
  B A%ROWTYPE;
BEGIN
  OPEN A;
  FETCH A INTO B;
  WHILE A%FOUND LOOP
    
   
    DBMS_OUTPUT.PUT_LINE(B.EMPNO||' '||B.ENAME);
    FETCH A INTO B;
  END LOOP;
  CLOSE A;
END;  

-----WHILE 加一次读取

/
DECLARE
  CURSOR  A IS SELECT EMPNO,ENAME FROM EMP;
  B A%ROWTYPE;

BEGIN

  FOR C IN A LOOP
    DBMS_OUTPUT.PUT_LINE(C.EMPNO||' '||C.ENAME);
  END LOOP;

END;  

-----FOR 省略开启读取和关闭

DECLARE
  CURSOR  A IS SELECT EMPNO,ENAME FROM EMP;
  B A%ROWTYPE;
BEGIN
  OPEN A;
  IF A%ISOPEN THEN           ------%ISOPEN
    DBMS_OUTPUT.PUT_LINE('游标已开启');
  ELSE
    DBMS_OUTPUT.PUT_LINE('游标未开启');
  END IF;
  CLOSE A;
   IF A%ISOPEN THEN 
    DBMS_OUTPUT.PUT_LINE('游标已开启');
  ELSE
    DBMS_OUTPUT.PUT_LINE('游标未开启');
  END IF;
  
END;   
%ISOPEN  查看游标是否开启 

-----隐式游标

DECLARE

BEGIN
  DELETE FROM EMP1 WHERE DEPTNO=10;
  
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
  COMMIT;
END;

BEGIN
  DML
  INSERT INTO 日志表 VALUES (SQL%ROWCOUNT);
  COMMIT;
END;

/

BEGIN
  FOR A IN (SELECT * FROM EMP1) LOOP
    DBMS_OUTPUT.PUT_LINE(A.EMPNO||A.ENAME||A.SAL||A.JOB);
  END LOOP;
END;

---动态游标
DECLARE
  TYPE AAA IS REF CURSOR;
  CUR_A AAA;
  E   EMP%ROWTYPE;
  D   DEPT%ROWTYPE;
BEGIN
  OPEN CUR_A FOR SELECT * FROM EMP;
  LOOP
    FETCH CUR_A INTO E;
    EXIT WHEN CUR_A%NOTFOUND;
    DBMS_OUTPUT.put_line(E.ENAME);
  END LOOP;
  
  CLOSE CUR_A;
  
END;

------------ERROR