CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(50 BYTE) NOT NULL,
JOB VARCHAR2(50 BYTE) NOT NULL,
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(10,2),
COMM NUMBER(10,2),
DEPTNO NUMBER(2) NOT NULL,
REPORT_STATUS CHAR(20 BYTE)
);
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('06/27/2017 18:42:12', 'MM/DD/YYYY HH24:MI:SS'),
800, 0, 20, 'N ');
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('06/26/2017 18:42:53', 'MM/DD/YYYY HH24:MI:SS'),
1600, 300, 30, 'N ');
COMMIT;
---My Code
DECLARE
CURSOR emp_cur IS SELECT * FROM EMP WHERE empno = :p_eno;
emp_rec EMP%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP -- loop through the table and get each employee
FETCH emp_cur INTO emp_rec;
IF emp_cur%FOUND THEN
dbms_output.put_line('Employee #' || emp_rec.EMPNO ||
' is ' || emp_rec.ename);
ELSE
dbms_output.put_line('--- Finished processing employees ---');
EXIT;
END IF;
END LOOP;
CLOSE emp_cur;
END;
When I pass p_eno = 100 which is not in the table it displays "--- Finished processing employees ---" which is correct
But when I pass p_no = 7369 (table value) then it displays both the messages i.e the emp_cur%FOUND & in ELSE condition
" Employee #7369 is SMITH
--- Finished processing employees --- "
I want to display only the message in the emp_cur%FOUND when the cursor returns TRUE i.e "Employee #7369 is SMITH"
Can anyone help me.
Thanks.
Veronica