Oracle - Cursor

Asked

Viewed 225 times

0

I’m studying PL/SQL and testing the use of CURSOR, but I’m 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:

Report of errors - 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:
sql oracle oracle11g

  • see if all structure commands start and came in the order that were declared, see begin, if, open, loop etc.

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 way (simpler) 

SET SERVEROUTPUT ON

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

Browser other questions tagged sql oracle oracle11g

You are not signed in. Login or sign up in order to post.