SQL查询和存储过程未产生期望的结果 [英] SQL query and stored procedure not producing desired results

问题描述

我有此表并存储了proc函数：

I have this table and Stored proc function:

表：

CREATE TABLE _DMigNumbers(
    Number numeric(20,0) NOT NULL PRIMARY KEY
);
INSERT INTO _DMigNumbers VALUES(0)

存储的proc函数：

CREATE FUNCTION read_and_increment()
RETURNS NUMERIC(20,0)
BEGIN
    DECLARE @number_just_read NUMERIC(20,0);

      SELECT number INTO @number_just_read
        FROM _DMigNumbers;

      UPDATE _DMigNumbers
         SET number = number + 1;
   RETURN @number_just_read;
End

我也创建了这个Numbers表

and I create this Numbers table as well

CREATE TABLE _Numbers (
    Number int NOT NULL PRIMARY KEY
);
INSERT INTO _Numbers VALUES(1)
INSERT INTO _Numbers VALUES(2)
INSERT INTO _Numbers VALUES(3)
INSERT INTO _Numbers VALUES(4)

现在：

当我这样做时：

select 
    f.Number
    ,read_and_increment()
from _Numbers f

我得到：

  Number-----Value

   1          0   
   2          0   
   3          0   
   4          0   

我想要不同的值，例如（0,1,2,3）-我需要怎么做才能实现这一目标？

I want different value like (0,1,2,3) - what do I need to do to achieve this?

我了解由于使用单个Select，我得到了相同的值，但是不确定要做什么才能得到我现在想要的东西。 ...

I understand that I am getting the same values because of the single Select, but not sure what I need to do to get what I am after at the moment......

我无法使用IDENTITY或自动递增，请参见我以前的问题，如果有兴趣的话，可以了解更多详细信息...

I cannot use IDENTITY or autoincrement see my previous question for more details if interested...

谢谢

Voodoo

推荐答案

尝试将函数标记为 NOT确定性，看看是否有帮助。默认情况下，所有功能都是确定性的，这意味着数据库服务器可以在某些情况下缓存结果。以此方式进行标记将强制服务器每次重新评估查询/功能。

Try marking your function as NOT DETERMINISTIC and see if that helps. By default, all functions are deterministic, which means the database server can cache the result under certain circumstances. Marking it this way will force the server to re-evaluate the query/function each time.

CREATE FUNCTION read_and_increment()
RETURNS NUMERIC(20,0)
NOT DETERMINISTIC
BEGIN
    DECLARE @number_just_read NUMERIC(20,0);

      SELECT number INTO @number_just_read
        FROM _DMigNumbers;

      UPDATE _DMigNumbers
         SET number = number + 1;
   RETURN @number_just_read;
End

这篇关于SQL查询和存储过程未产生期望的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！