过程中的游标返回的值多于查询 [英] Cursor in procedure returning more values than query

问题描述

我在接收几个参数的过程中使用了一个简单的游标.然后我在一个选择查询上创建一个带有多个条件的 where 子句的游标，这些条件等于接收到的参数.这个游标应该只返回 1 行，而是返回多行.我发现了这一点，因为我正在使用 for 循环遍历此游标并根据此游标的值将某些内容插入到另一个表中.

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.

当我静态地在数据库上运行查询时(如在没有 pl/sql 的情况下)我得到了我所期望的，但是当我从一个只返回一行的游标中执行它并在 for 循环中运行它时，循环进行多次迭代.这怎么可能?

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?

谢谢！

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

我像这样运行游标:

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

这应该只返回一行，但我的光标返回 %rowcount 是 10.

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

推荐答案

您有名称冲突.如在文档中:

如果 SQL 语句引用的名称既属于列又属于局部变量或形参，则列名优先.

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.

前四项检查总是为真(除非您有空值)，因此您将获得具有 done = 'N' 的每一行.

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

如果这是在存储过程中，而不是在匿名块中，您可以使用过程/函数名称作为前缀，有些人更喜欢这样.例如，如果你的过程被称为 myproc，你可以这样做:

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

这篇关于过程中的游标返回的值多于查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！