问题描述
我在接收几个参数的过程中使用了一个简单的游标.然后我在一个选择查询上创建一个带有多个条件的 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';
这篇关于过程中的游标返回的值多于查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!