I want to use the function SQL%ROWCOUNT as a way to tell me if a record is in a table or not. The code that I have is the following:
DECLARE
v_emp employee%ROWTYPE;
CURSOR c_emp IS
SELECT * FROM employee WHERE name='chuck';
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_emp;
IF SQL%ROWCOUNT=1 THEN
DBMS_OUTPUT.PUT_LINE('found');
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
END IF;
END;
But it does not print anything at all, even though that record with that name exists in the database
Normally, you'd do something like
DECLARE
l_count PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM employee
WHERE name = 'chuck'
AND rownum = 1;
IF( l_count = 1 )
THEN
dbms_output.put_line( 'found' );
ELSE
dbms_output.put_line( 'not found' );
END IF;
END;
If you really want to use an explicit cursor, you would need to check the <<cursor_name>>%rowcount
, not sql%rowcount
to determine how many rows had been fetched. If you're going to use an explicit cursor, you also need to take care to close the cursor. Since you didn't post your table definition or the data you're using, I'll use the EMP
table in the SCOTT
schema as an example
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 v_emp emp%ROWTYPE;
3 CURSOR c_emp IS
4 SELECT * FROM emp WHERE ename='SMITH';
5 BEGIN
6 OPEN c_emp;
7 FETCH c_emp INTO v_emp;
8 IF c_emp%ROWCOUNT=1 THEN
9 DBMS_OUTPUT.PUT_LINE('found');
10 ELSE
11 DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
12 END IF;
13 CLOSE c_emp;
14* END;
SQL> /
found
PL/SQL procedure successfully completed.
And note that no matter what approach you use, if you want the output from DBMS_OUTPUT
to be displayed, you need to enable output in whatever tool you are using. If you are using SQL*Plus, that would mean running
SQL> set serveroutput on;
before executing the anonymous PL/SQL block.