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

查看:306
本文介绍了游标在过程中返回比查询更多的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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



当我运行查询数据库静态(如没有pl / sql)我得到了我的期望,但是当我从一个游标,它应该只返回一行,并运行它在一个for循环,它执行多个迭代。这是怎么回事?



谢谢!



编辑:

  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

其中x.mjt = mjt
和x.salesman = salesman
和x.kind = kind
and x.kolo1 = kolo1
and x.done ='N';

这应该只返回一行,而是我的游标返回%rowcount为10。

解决方案

您有名称冲突。您已调用您的局部变量与列名称相同,并且列名称优先,如


如果SQL语句引用的名称属于

注意

当变量或参数名称被解释为列名时,可以无意中删除,更改或插入数据。


检查总是为真(除非你有空值),所以你会得到每一行 done ='N'



将您的局部变量名更改为其他值;通常使用前缀来区分局部变量,参数和列,例如:

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

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

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


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.

解决方案

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

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆