sql - Cursor in procedure returning more values than query

Question

I am using a simple cursor in a procedure that receives a couple of parameters. I then make a cursor on a select query with a where clause with multiple conditions, which are equal to the received parameters. This cursor should only return 1 row, instead it returns multiple rows. I found this out because I'm using a for loop to go through this cursor and insert something into another table based on the values of this cursor.

When I run the query on the database statically(as in without pl/sql) I get what I expect, but when I do it from a cursor which should return only one row, and run it in a for loop, the loop does multiple iterations. How is this possible?

Thank you!

EDIT:

ID kind kolo kolo1 mjt salesman money   date       done
1  001  001  002   00013 00056  100,00  21-feb-12  N

I run a cursor like this:

Cursor linija IS 
SELECT *
FROM table_x X

where x.mjt = mjt
and   x.salesman = salesman
and x.kind = kind
and x.kolo1 = kolo1
and x.done = 'N';

This should return only one row, but instead my cursor returns %rowcount is 10.

Answer 1

You have a name conflict. You have called your local variables the same as your column names, and the column names are taking precedence, as noted in the documentation:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

Caution:
When a variable or parameter name is interpreted as a column name, data can be deleted, changed, or inserted unintentionally.

The first four checks are always going to be true (unless you have null values), so you'll get every row that has done = 'N'.

Change your local variable names to something else; it's fairly common to use a prefix to distinguish between local variables, parameters, and columns, something like:

Cursor linija IS 
SELECT *
FROM table_x X
where x.mjt = l_mjt
and   x.salesman = l_salesman
and x.kind = l_kind
and x.kolo1 = l_kolo1
and x.done = 'N';

If this is in a stored procedure, rather than an anonymous block, you could use the procedure/function name as a prefix, which some people prefer. If your procedure was called myproc, for example, you could do:

Cursor linija IS 
SELECT *
FROM table_x X
where x.mjt = myproc.mjt
and   x.salesman = myproc.salesman
and x.kind = myproc.kind
and x.kolo1 = myproc.kolo1
and x.done = 'N';