Oracle-Cursor

Question

Oracle-Cursor

I am studying PL / SQL and testing the use of CURSOR, but I am not able to test an example:

SET SERVEROUTPUT ON
DECLARE
  V_NAME VARCHAR2(50);
  CURSOR CUR_CURSOR IS
    SELECT FIRST_NAME FROM EMPLOYEES;
  TEMP CUR_CURSOR%ROWTYPE;
BEGIN
  IF NOT CUR_CURSOR%ISOPEN THEN
    OPEN CUR_CURSOR;
    LOOP
      FETCH CUR_CURSOR INTO TEMP;
      EXIT
    WHEN CUR_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(V_NAME);
    END LOOP;
    CLOSE CUR_CURSOR;
END;

Error:

Error Reporting - Ora-06550: row 17, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following:
   if
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

0

sql oracle11g oracle

Author: Victor Stafusa, 2018-03-29

Source

1 answers

score 1 · Answer 1

SET SERVEROUTPUT ON

DECLARE
  V_NAME VARCHAR2(50);
  CURSOR CUR_CURSOR IS
    SELECT FIRST_NAME FROM EMPLOYEES;
  TEMP CUR_CURSOR%ROWTYPE;
BEGIN
  IF NOT CUR_CURSOR%ISOPEN THEN
    OPEN CUR_CURSOR;
    LOOP
      FETCH CUR_CURSOR INTO TEMP;
      EXIT
    WHEN CUR_CURSOR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(V_NAME);
    END LOOP;
    CLOSE CUR_CURSOR;
  END IF;
END;

Another (simpler) way

SET SERVEROUTPUT ON

BEGIN
  FOR REMP IN (SELECT FIRST_NAME FROM EMPLOYEES)
  LOOP
    DBMS_OUTPUT.PUT_LINE(REMP.FIRST_NAME);
  END LOOP;
END;