sqloracle-databasestored-procedurescursor

Cursor in procedure returning more values than query


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.


Solution

  • 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';
    

    You could also give your cursor its own formal arguments, and reference those in the same way, with prefixed argument names:

    Cursor linija (
      p_mjt table_x.mjt%type, p_salesman table_x.salesman%type,
      p_kind table_x.kind%type, p_kolo1 table_x.kolo1%type
    ) IS 
    SELECT *
    FROM table_x X
    where x.mjt = p_mjt
    and   x.salesman = p_salesman
    and x.kind = p_kind
    and x.kolo1 = p_kolo1
    and x.done = 'N';
    

    or prefixing with the cursor name:

    Cursor linija (
      mjt table_x.mjt%type, salesman table_x.salesman%type,
      kind table_x.kind%type, kolo1 table_x.kolo1%type
    ) IS 
    SELECT *
    FROM table_x X
    where x.mjt = linija.mjt
    and   x.salesman = linija.salesman
    and x.kind = linija.kind
    and x.kolo1 = linija.kolo1
    and x.done = 'N';
    

    and either way then include arguments when you open the cursor, e.g. if you changed the variable names at procedure level anyway:

    for x in linija (l_mjt, l_salesman, l_kind, l_kolo1) loop
    

    or if you didn't:

    for x in linija (myproc.mjt, myproc.salesman, myproc.kind, myproc.kolo1) loop