sql - Oracle 过程对在过程之外运行良好的选择查询感到不满

Question

    WITH    Q (L) AS
        (
         SELECT  1 FROM DUAL
         UNION ALL
         SELECT  L + 1
         FROM    Q
         WHERE   L < 99
        )
SELECT  MIN(L)
INTO next_priority
FROM Q
LEFT JOIN gxrdird on gxrdird_priority = L 
 and gxrdird_pidm = aPidm_in and gxrdird_ap_ind = 'Y'
WHERE L NOT IN (select gxrdird_priority 
                  from gxrdird where gxrdird_pidm = aPidm_in);

此查询返回手动运行时我想要的结果。我试图将其放入包过程中，但我得到:

51/5  PL/SQL: SQL Statement ignored 
55/22  PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got - 

这对应于 L 列上的“SELECT L + 1”行。有没有办法在 with 子句中将 L 明确声明为 NUMBER？我已经在谷歌上搜索了一个小时，我发现有几个 with 子句的例子，其中的参数没有将它们声明为任何类型。

这让我抓狂，我无法想出更简单的查询来给我正确的结果。

编辑，添加上下文:

  CURSOR xxx_cur IS
SELECT ROWID, GXRDIRD_PRIORITY
FROM GXRDIRD 
WHERE GXRDIRD_PIDM = aPidm_in 
  AND GXRDIRD_AP_IND = 'A' 
AND GXRDIRD_ATYP_CODE IS NULL 
AND GXRDIRD_ADDR_SEQNO IS NULL
ORDER BY GXRDIRD_PRIORITY DESC;

  xxx_rec   xxx_cur%ROWTYPE;
  next_priority         NUMBER;
BEGIN


OPEN xxx_cur;
LOOP
FETCH xxx_cur INTO xxx_rec;
EXIT WHEN xxx_cur%NOTFOUND;

-- Here we should update that particular row, but we can't just increment it.
WITH    Q (L) AS
        (
         SELECT  1 FROM DUAL
         UNION ALL
         SELECT  L + 1
         FROM    Q
         WHERE   L < 99
        )
SELECT  MIN(L)
INTO next_priority
FROM Q
LEFT JOIN gxrdird on gxrdird_priority = L and gxrdird_pidm = aPidm_in and gxrdird_ap_ind = 'Y'
WHERE L NOT IN (select gxrdird_priority from gxrdird where gxrdird_pidm = aPidm_in);    

-- The above query found the lowest-numbered unused priority, and now we'll set this record to that.
UPDATE GXRDIRD SET GXRDIRD_PRIORITY = next_priority WHERE ROWID = xxx_rec.ROWID;

-- If the above record was originally 7 and the lowest was 15, now 7 is free and will be used if we loop
-- again.
DBMS_OUTPUT.PUT_LINE(OBJECT_NAME || '.P_RESEQUENCE_INACTV_ACCNTS - Changed priority ' || xxx_rec.GXRDIRD_PRIORITY || ' into ' || next_priority);
END LOOP;

第 51 行:WITH Q (L) AS

第 55 行:SELECT L + 1

Answer 1

看起来您正在尝试生成具有连续数字的虚拟行。我首选的方法是:

WITH    Q AS
        (
         SELECT  rownum AS l
         FROM    dual
         CONNECT BY level < 100
        )
SELECT  MIN(L)
INTO next_priority
FROM Q
...

请尝试一下这是否适合您。