Cursor in procedure returning more values than que

问题:

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.

回答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';

回答2:

In addition to what Alex has said (and I can't second his advice to distinguish variable names from column names enough!), why are you using a cursor for loop to do the insert?

You could just do your insert in one SQL statement, eg:

insert into your_table (col1, col2, ...)
select col1, col2, ...
from   your_table
where  ...

That will perform much better than going through the whole dataset and inserting each row one at a time. When it comes to databases, think set-based as much as you can, not procedurally!