sql - 在Sql Server中，如何将游标中的值放入临时表中？

Question

我正在尝试创建一个其中有光标的函数。我想从该游标获取数量值并将其放入临时表中。但我还没有成功获取该值并将其放入临时表中。

我把评论放在了我无法完成的地方......

这是我的代码

alter FUNCTION test(@input VARCHAR(250)) RETURNS Decimal(8, 2) AS BEGIN

DECLARE @rst Decimal(8, 2) SET @rst=0
DECLARE @Temp TABLE (Quantity Decimal(8,2), Price Decimal(8,2))
DECLARE @amount Decimal(8,2)
DECLARE @price Decimal(8,2)

DECLARE CrsOrfLine CURSOR FOR
SELECT AMOUNT FROM LG_001_01_ORFLINE 
 WHERE LINETYPE    = 0 
 AND ORDFICHEREF = (SELECT TOP 1 LOGICALREF FROM LG_001_01_ORFICHE WHERE GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22')
 ORDER BY LINENO_ ASC;

FETCH NEXT FROM CrsOrfLine INTO  @amount
WHILE (@@FETCH_STATUS = 0) 
BEGIN
     INSERT INTO @Temp (Quantity)

     /* HOW AM I SUPPOSED TO ADD IT INTO THE TEMP?????? */
     /* I COULDNT FIGURE THIS PART OUT                  */

FETCH NEXT FROM CrsOrfLine INTO @amount
END /*WHILE*/
CLOSE CrsOrfLine
DEALLOCATE CrsOrfLine

Answer 1

您可以执行以下操作。请注意，它仅插入数量，因此如果您打算包含价格，则需要对其进行修改。

DECLARE @Temp TABLE 
(
  Quantity Decimal(8,2), 
  Price Decimal(8,2)
)


INSERT INTO @temp (Quantity)
SELECT AMOUNT FROM LG_001_01_ORFLINE 
WHERE LINETYPE = 0 
AND ORDFICHEREF = (SELECT TOP 1 LOGICALREF FROM LG_001_01_ORFICHE WHERE GUID='EEB44E72-3717-4F5B-8F7E-6A36EB38EA22 ORDER BY LINENO_ ASC')