PLSQL Procudure (Oracle) Comparing a variable in where where

Question

It drives me crazy. I want to make a simple comparison of a column and a variable, but that just doesn't work. The next line always takes into account all the tuples, while I only need those that are due to the where clause.

SELECT count(*) INTO cnt from class where class.fid = fid; 

It looks so simple, but I'm working on this watch. Full sql process -

The big confusing thing is that if I replaced fid with some hardcode ID (e.g. 105), it gives the correct answer), but when I use fid it just doesn't work anymore and returns the number of all classes. For some reason, always class.fid = fid. When I use>, <or <>, 0 is returned!

 create or replace PROCEDURE pro_report2 AS CURSOR c_dept IS select deptid, dname from department; TYPE cur_typ IS REF CURSOR; c1 cur_typ; query_str1 VARCHAR2(200); fid faculty.fid%type := 102; fname faculty.fname%type; cnt NUMBER; BEGIN FOR dept_row in c_dept LOOP DBMS_OUTPUT.PUT_LINE('Dept.Name: ' || dept_row.dname); DBMS_OUTPUT.PUT_LINE('Faculty Name' || chr(9)|| chr(9) || '0 Class' || chr(9) || chr(9) || '1 Class' || chr(9) || chr(9) || '2 Classes' || chr(9) || '>2 Classes'); DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------'); --find all faculty in this department query_str1 := 'select fid, fname from faculty where faculty.deptid = ' || to_char(dept_row.deptid); open c1 for query_str1; LOOP FETCH c1 into fid, fname; exit when c1%notfound; DBMS_OUTPUT.PUT_LINE(fname); SELECT count(*) INTO cnt from class where class.fid = fid; DBMS_OUTPUT.PUT_LINE(to_char(cnt) || ' ' || to_char(fid)); END LOOP; -- Spaces between departments DBMS_OUTPUT.PUT_LINE(chr(10)); DBMS_OUTPUT.PUT_LINE(chr(10)); END LOOP; END; 

thanks

Answer 1

I believe that you need to rename or prefix the local variable fid , since it, unfortunately, matches the column name in the table you are querying. The SQL engine simply compares fid = fid for each row, which will always be true (except for zeros, but this is another story). It’s also harder to read your code when you have variables with a name similar to a column.

In PL / SQL, there is a rule for prefixing local variables with l_ (for local) so that it clearly defines the target. However, for any name other than the column name will be enough. Try:

 l_fid faculty.fid%type := 102; 

And then...

 SELECT count(*) INTO cnt from class where class.fid = l_fid; 

(plus other suitable replacements.)