赋值问题

Question

直接看SQL：BEGIN
  DECLARE
    V_AAE262 NUMBER;
    V_AAE264 NUMBER;
    V_AAE266 NUMBER;
    V_AAE335 NUMBER;
    V_AAE336 NUMBER;
    V_AAE337 NUMBER;
    V_AAE338 NUMBER;
    V_AAE364 NUMBER;
    V_AAE379 NUMBER;
    V_AAE387 NUMBER;
    V_AAE388 NUMBER;
    V_AAE389 NUMBER;
    V_AAE390 NUMBER;
  BEGIN
    SELECT nvl(decode(aae341,'31',sum(aae023)),0),
           nvl(decode(aae341,'11',sum(aae023)),0),
           nvl(decode(aae341,'41',sum(aae023),'42',sum(aae023),'43',sum(aae023),'44',sum(aae023)),0)+V_AAE266,
           nvl(decode(aae341,'41',sum(aae023)),0),
           nvl(decode(aae341,'42',sum(aae023)),0),
           nvl(decode(aae341,'43',sum(aae023)),0),
           nvl(decode(aae341,'44',sum(aae023)),0),
           nvl(decode(aae341,'32',sum(aae023)),0),
           nvl(decode(aae341,'51',sum(aae023),'52',sum(aae023),'53',sum(aae023),'54',sum(aae023)),0)+V_AAE379,
           nvl(decode(aae341,'51',sum(aae023)),0),
           nvl(decode(aae341,'52',sum(aae023)),0),
           nvl(decode(aae341,'54',sum(aae023)),0),
           nvl(decode(aae341,'53',sum(aae023)),0)
    INTO V_AAE262,V_AAE264,V_AAE266,V_AAE335,V_AAE336,V_AAE337,V_AAE338,V_AAE364,
    V_AAE379,V_AAE387,V_AAE388,V_AAE389,V_AAE390 FROM AC43 WHERE AC43.AAZ083 = 769622 GROUP BY aae341;
    DBMS_OUTPUT.PUT_LINE(V_AAE338);
  END;
END;
目的:先分组再SUM,根据AAE341的值，再赋值不同的变量.现在的问题:"实际返回的行数超出请求的行数"。

Answer 1

你select语句查询出来的结果有多行
变量只能接收一个值

Answer 2

你一次select了多行，赋值给一个变量，所以你会得到“实际返回的行数超出请求的行数”错误。

Answer 3

能否有办法满足:根据不同的AAE341值写入AAE023到指定的变量呢?

Answer 4

AC43的结果是:
AAE341        AAE023
11                100
44                15
42                15需求是根据AAE341的是,把AAE023写入到指定的变量中.例如当AAE341等于11时,那么把AAE023的值写入到V_AAE264中
注： 数据库版本是9I.

Answer 5

更正 ：BEGIN
  DECLARE
    V_AAE262 NUMBER default 0.00;
    V_AAE264 NUMBER default 0.00;
    V_AAE266 NUMBER default 0.00;
    V_AAE335 NUMBER default 0.00;
    V_AAE336 NUMBER default 0.00;
    V_AAE337 NUMBER default 0.00;
    V_AAE338 NUMBER default 0.00;
    V_AAE364 NUMBER default 0.00;
    V_AAE379 NUMBER default 0.00;
    V_AAE387 NUMBER default 0.00;
    V_AAE388 NUMBER default 0.00;
    V_AAE389 NUMBER default 0.00;
    V_AAE390 NUMBER default 0.00;
  BEGIN
    SELECT nvl(decode(aae341,'31',sum(aae023)),0),
           nvl(decode(aae341,'11',sum(aae023)),0),
           nvl(decode(aae341,'41',sum(aae023),'42',sum(aae023),'43',sum(aae023),'44',sum(aae023)),0)+V_AAE266,
           nvl(decode(aae341,'41',sum(aae023)),0),
           nvl(decode(aae341,'42',sum(aae023)),0),
           nvl(decode(aae341,'43',sum(aae023)),0),
           nvl(decode(aae341,'44',sum(aae023)),0),
           nvl(decode(aae341,'32',sum(aae023)),0),
           nvl(decode(aae341,'51',sum(aae023),'52',sum(aae023),'53',sum(aae023),'54',sum(aae023)),0)+V_AAE379,
           nvl(decode(aae341,'51',sum(aae023)),0),
           nvl(decode(aae341,'52',sum(aae023)),0),
           nvl(decode(aae341,'54',sum(aae023)),0),
           nvl(decode(aae341,'53',sum(aae023)),0)
    INTO V_AAE262,V_AAE264,V_AAE266,V_AAE335,V_AAE336,V_AAE337,V_AAE338,V_AAE364,
    V_AAE379,V_AAE387,V_AAE388,V_AAE389,V_AAE390 FROM AC43 WHERE AC43.AAZ083 = 769622 GROUP BY aae341;
    DBMS_OUTPUT.PUT_LINE(V_AAE338);
  END;
END;
我原来的写法是(但效率太慢，10条数据都消耗17秒):FOR i IN 0..TXTINDEX LOOP
        V_AAE262 := 0.00;
        V_AAE264 := 0.00;
       
        V_AAE335 := 0.00;
        V_AAE336 := 0.00;
        V_AAE337 := 0.00;
        V_AAE338 := 0.00;
        V_AAE364 := 0.00;
        V_AAE387 := 0.00;
        V_AAE388 := 0.00;
        V_AAE389 := 0.00;
        V_AAE390 := 0.00;
        V_AAE266 := 0.00;
        V_AAE379 := 0.00;
        
        FOR R_AC43 IN C_AC43(aaz083Array(i)) LOOP
            IF R_AC43.AAE341 = '11' THEN
              V_AAE264 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '31' THEN
              V_AAE262 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '32' THEN
              V_AAE364 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '41' THEN
              V_AAE335 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '42' THEN
              V_AAE336 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '43' THEN
              V_AAE337 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '44' THEN
              V_AAE338 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '51' THEN
              V_AAE387 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '52' THEN
              V_AAE388 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '53' THEN
              V_AAE390 := NVL(R_AC43.AAE023, 0);
            ELSIF R_AC43.AAE341 = '54' THEN
              V_AAE389 := NVL(R_AC43.AAE023, 0);
            END IF;
        END LOOP;
        V_AAE262 := nvl(V_AAE262,0);
        V_AAE264 := nvl(V_AAE264,0);
       
        V_AAE335 := nvl(V_AAE335,0);
        V_AAE336 := nvl(V_AAE336,0);
        V_AAE337 := nvl(V_AAE337,0);
        V_AAE338 := nvl(V_AAE338,0);
        V_AAE364 := nvl(V_AAE364,0);
        V_AAE387 := nvl(V_AAE387,0);
        V_AAE388 := nvl(V_AAE388,0);
        V_AAE389 := nvl(V_AAE389,0);
        V_AAE390 := nvl(V_AAE390,0);
        V_AAE266 := nvl(V_AAE266,0);
        V_AAE379 := nvl(V_AAE379,0);
        V_AAE266 := V_AAE266 + V_AAE335 + V_AAE336 + V_AAE337 + V_AAE338;
        V_AAE266 := nvl(V_AAE266,0);
        V_AAE379 := V_AAE379 + V_AAE387 + V_AAE388 + V_AAE390 + V_AAE389;
        V_AAE379 := nvl(V_AAE379,0);
        UPDATE AC51
           SET AC51.AAE262 = NVL(AC51.AAE262, 0) + V_AAE262,
               AC51.AAE264 = NVL(AC51.AAE264, 0) + V_AAE264,
               AC51.AAE266 = NVL(AC51.AAE266, 0) + V_AAE266,
               AC51.AAE335 = NVL(AC51.AAE335, 0) + V_AAE335,
               AC51.AAE336 = NVL(AC51.AAE336, 0) + V_AAE336,
               AC51.AAE337 = NVL(AC51.AAE337, 0) + V_AAE337,
               AC51.AAE338 = NVL(AC51.AAE338, 0) + V_AAE338,
               AC51.AAE364 = NVL(AC51.AAE364, 0) + V_AAE364,
               AC51.AAE379 = NVL(AC51.AAE379, 0) + V_AAE379,
               AC51.AAE387 = NVL(AC51.AAE387, 0) + V_AAE387,
               AC51.AAE388 = NVL(AC51.AAE388, 0) + V_AAE388,
               AC51.AAE389 = NVL(AC51.AAE389, 0) + V_AAE389,
               AC51.AAE390 = NVL(AC51.AAE390, 0) + V_AAE390
         WHERE AC51.AAZ116 = aaz116Array(i)
           AND AC51.AAE001 = CURRENTYEAR;
    END LOOP;