ORA-00947 函数返回记录表的值不足

Question

所以我正在尝试构建一个函数，该函数返回某些客户端订阅中包含的项目的记录。

所以我一直在建立以下内容：

2种类型：

CREATE OR REPLACE TYPE PGM_ROW AS OBJECT
(
pID      NUMBER(10),
pName    VARCHAR2(300)
);
CREATE OR REPLACE TYPE PGM_TAB AS TABLE OF PGM_ROW;

1 功能：

CREATE OR REPLACE FUNCTION FLOGIN (USER_ID NUMBER) RETURN PGM_TAB
AS  
SELECTED_PGM   PGM_TAB;
BEGIN
FOR RESTRICTION
IN (  SELECT (SELECT LISTAGG (ID_CHANNEL, ',')
WITHIN GROUP (ORDER BY ID_CHANNEL)
FROM (SELECT DISTINCT CHA2.ID_CHANNEL
FROM CHANNELS_ACCESSES CHA2
JOIN CHANNELS CH2
ON CH2.ID = CHA2.ID_CHANNEL
WHERE     CHA2.ID_ACCESS = CMPA.ID_ACCESS
AND CH2.ID_CHANNELS_GROUP = CG.ID))
AS channels,
(SELECT LISTAGG (ID_SUBGENRE, ',')
WITHIN GROUP (ORDER BY ID_SUBGENRE)
FROM (SELECT DISTINCT SGA2.ID_SUBGENRE
FROM SUBGENRES_ACCESSES SGA2
JOIN CHANNELS_ACCESSES CHA2
ON CHA2.ID_ACCESS = SGA2.ID_ACCESS
JOIN CHANNELS CH2
ON CH2.ID = CHA2.ID_CHANNEL
WHERE     SGA2.ID_ACCESS = CMPA.ID_ACCESS
AND CH2.ID_CHANNELS_GROUP = CG.ID))
AS subgenres,
CG.NAME,
A.BEGIN_DATE,
A.END_DATE,
CMP.PREVIEW_ACCESS
FROM USERS U
JOIN COMPANIES_ACCESSES CMPA
ON U.ID_COMPANY = CMPA.ID_COMPANY
JOIN COMPANIES CMP ON CMP.ID = CMPA.ID_COMPANY
JOIN ACCESSES A ON A.ID = CMPA.ID_ACCESS
JOIN CHANNELS_ACCESSES CHA
ON CHA.ID_ACCESS = CMPA.ID_ACCESS
JOIN SUBGENRES_ACCESSES SGA
ON SGA.ID_ACCESS = CMPA.ID_ACCESS
JOIN CHANNELS CH ON CH.ID = CHA.ID_CHANNEL
JOIN CHANNELS_GROUPS CG ON CG.ID = CH.ID_CHANNELS_GROUP
WHERE U.ID = USER_ID
GROUP BY CG.NAME,
A.BEGIN_DATE,
A.END_DATE,
CMPA.ID_ACCESS,
CG.ID,
CMP.PREVIEW_ACCESS)
LOOP
SELECT PFT.ID_PROGRAM, PFT.LOCAL_TITLE
BULK COLLECT INTO SELECTED_PGM
FROM PROGRAMS_FT PFT
WHERE     PFT.ID_CHANNEL IN
(    SELECT TO_NUMBER (
REGEXP_SUBSTR (RESTRICTION.CHANNELS,
'[^,]+',
1,
ROWNUM))
FROM DUAL
CONNECT BY LEVEL <=
TO_NUMBER (
REGEXP_COUNT (RESTRICTION.CHANNELS,
'[^,]+')))
AND PFT.ID_SUBGENRE IN
(    SELECT TO_NUMBER (
REGEXP_SUBSTR (RESTRICTION.SUBGENRES,
'[^,]+',
1,
ROWNUM))
FROM DUAL
CONNECT BY LEVEL <=
TO_NUMBER (
REGEXP_COUNT (RESTRICTION.SUBGENRES,
'[^,]+')))
AND (PFT.LAUNCH_DATE BETWEEN RESTRICTION.BEGIN_DATE
AND RESTRICTION.END_DATE);
END LOOP;
RETURN SELECTED_PGM;
END FLOGIN;

我希望函数 tu 返回一个包含 2 列的表，其中包含用户访问中包含的表 PROGRAMS_FT 中的所有记录。

出于某种原因，我收到编译警告 ORA-000947。 我对错误代码的理解是，当插入的值与接收值的对象的类型不匹配时，就会发生这种情况，我看不出这里的情况如何。

Answer 1

您正在选择两个标量值并尝试将它们放入一个对象中。这不会自动发生，您需要将它们转换为对象：

...
LOOP
SELECT PGM_ROW(PFT.ID_PROGRAM, PFT.LOCAL_TITLE)
BULK COLLECT INTO SELECTED_PGM
FROM PROGRAMS_FT PFT
...

(这是PL/SQL的一个无益的怪癖，它说"没有足够的值"而不是"太多的值"，正如你试图将两件事合二为一时所期望的那样;我敢肯定，我曾经想出了一个相当令人信服的解释/借口，但目前它逃脱了我......

不过，我不确定您的循环是否有意义。假设游标查询返回多行，则每次在循环中替换SELECTED_PGM集合的内容 - 您可能会认为要追加到它，但这不是它的工作原理。因此，您最终将返回仅基于循环的最终迭代的集合。

聚合然后拆分数据似乎也需要很多工作。您也许可以将集合用于这些;但是，您可能可以摆脱游标和循环，并将游标查询与内部查询组合在一起，这将更有效，并允许您对所有组合数据进行一次批量收集。