Sybase选择变量逻辑 [英] Sybase select variable logic

问题描述

好的，我有一个与以前遇到的问题有关的问题.我知道如何解决它，但是我们在重现该错误时遇到了问题.

Ok, I have a question relating to an issue I've previously had. I know how to fix it, but we are having problems trying to reproduce the error.

我们有一系列基于其他记录创建记录的过程.记录通过link_id链接到主记录.在获取此link_id的过程中，查询为

We have a series of procedures that create records based on other records. The records are linked to the primary record by way of a link_id. In a procedure that grabs this link_id, the query is

select @p_link_id = id --of the parent
from  table
where thingy_id = (blah)

现在，表中有该活动的多个行.有些可以取消.我拥有的代码不会在select语句中包含取消的行，因此，如果以前有取消的行，则这些id将显示在select中.如果我不包括已取消的行，总是会有一个打开"记录被选中. (附加where status != 'C')

Now, there are multiple rows in the table for the activity. Some can be cancelled. The code I have doesn't disinclude cancelled rows in the select statement, so if there are previously cancelled rows, those ids will appear in the select. There is always going to be one 'open' record that is selected if I disinclude cancelled rows. (append where status != 'C')

这解决了此问题.但是，我需要能够在我们的开发环境中重现该问题.

This solves this issue. However, I need to be able to reproduce the issue in our development environment.

我已经经历了输入大量数据，打开，取消等过程，以尝试获取此select语句以返回无效的id.但是，每当我运行select时，id都是按顺序排列的(生成序列)，但是在发生此错误的情况下，select语句将似乎是第一个值返回到了变量中.

I've gone through a process where I've entered a whole heap of data, opening, cancelling, etc to try and get this select statement to return an invalid id. However, whenever I run the select, the ids are in order (sequence generated), but in the case where this error occured, the select statement returned what seems to be the first value into the variable.

例如.

ID   Status
1    Cancelled
2    Cancelled
3    Cancelled
4    Open

鉴于上述情况，如果我对想要的ID进行选择，我想得到"4".在错误中，结果为1.但是，即使我输入了10条已取消的记录，我仍然会在选择中得到最后一条.

Given the above, if I do a select for the ID I want, I want to get '4'. In the error, the result is 1. However, even if I enter in 10 cancelled records, I still get the last one in the select.

在oracle中，我知道如果选择一个变量并返回多个记录，则会出现错误(我认为). Sybase显然可以将多个值分配给一个变量而不会出错.

In oracle, I know that if you select into a variable and more than one record is returned, you get an error (I think). Sybase apparently can assign multiple values into a variable without erroring.

我在想，这与从表中选择数据的方式有关，没有排序顺序的id不会以升序返回，或者有一个dboption，其中将对变量的选择保存起来查询的第一个或最后一个值.

I'm thinking that there's either something to do with how the data is selected from the table, where the id's without a sort order don't return in ascending order, or there's a dboption where a select into a variable will save the first or last value queried.

看来我们可以通过回滚存储过程的更改来重现此错误.但是，procs不会在此link_id列附近的任何地方.对数据库体系结构的更改是否有可能破坏索引或其他内容?

it looks like we can reproduce this error by rolling back stored procedure changes. However, the procs don't go anywhere near this link_id column. Is it possible that changes to the database architecture could break an index or something?

推荐答案

根据 last 值. href ="http://www.oracle.com/technology/tech/migration/kb/articles/238775.1.html" rel ="nofollow noreferrer">此.

If more than one row is returned, the value that is stored will be the last value in the list, according to this.

如果您未指定通过ORDER BY进行检索的顺序，则数据库引擎将方便地返回返回的顺序.它可能会因数据库实例而异.由于数据在数据库块结构中的放置位置，它可能按照创建的顺序出现，甚至显示为随机".

If you haven't specified an order for retrieval via ORDER BY, then the order returned will be at the convenience of the database engine. It may very well vary by the database instance. It may be in the order created, or even appear "random" because of where the data is placed within the database block structure.

故事的寓意

1. 始终使单例SELECT返回单行
2. 当无法完成第一项操作时，请使用ORDER BY来确保您关心的对象排在最后

1. Always make singleton SELECTs return a single row
2. When #1 can't be done, use an ORDER BY to make sure the one you care about comes last

这篇关于Sybase选择变量逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！