Как использовать тип массива в предложении where оператора update

Question

Я хочу, чтобы процедура получала список отключаемых привилегий и обновляла их записи в таблице. Для этого сценария я определил массив как объект базы данных с кодом ниже:

CREATE OR REPLACE TYPE T_DISABLE_LIST IS TABLE OF NUMBER(32)

Затем я определил входной параметр в сигнатуре процедуры и получил переданное значение.

PROCEDURE PRC_ROLE_PRIVILAGE_MANAGEMENT(P_REQ_USER_ID    IN VARCHAR2,
                                        P_DISABLE_LIST   IN T_DISABLE_LIST,
                                        P_RES_DESC       OUT VARCHAR2)
BEGIN
    UPDATE T_ PRIVILAGE p
       SET P.ENABLE_STATUS = 0, P.GRANT_USERID = P_REQ_USER_ID
    WHERE P.ID IN (SELECT * FROM TABLE(P_DISABLE_LIST));

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        RES_DESC := SUBSTR(SQLERRM,1, 400);
END;

Эта процедура будет успешно скомпилирована. Но когда я проверял это, я получил эту ошибку:

ORA-22905: невозможно получить доступ к строкам из не вложенного элемента таблицы

Любое тело может мне помочь? И скажите, почему этот код не работает правильно?

И, наконец, как я могу решить эту проблему?

PS: Моя версия orcale - 9.2!!!

Answer 1

Это предполагает, что вы используете Oracle 10g или более позднюю версию (и было написано до того, как OP выяснил, какую версию они используют)

Использовать MEMBER OF оператор:

UPDATE T_PRIVILAGE
SET    ENABLE_STATUS = 0,
       GRANT_USERID  = P_REQ_USER_ID
WHERE  ID MEMBER OF P_DISABLE_LIST;

Вы также можете использовать COLUMN_VALUE псевдо-колонки:

UPDATE T_PRIVILAGE
SET    ENABLE_STATUS = 0,
       GRANT_USERID  = P_REQ_USER_ID
WHERE  ID IN ( SELECT COLUMN_VALUE FROM TABLE( P_DISABLE_LIST ) );

почему этот код не работает правильно?

SELECT * FROM TABLE( P_DISABLE_LIST )

Выбирает строку из таблицы. Однако таблица генерируется выражением коллекции таблиц, и нет базовой таблицы базы данных для ссылки на строку, поэтому Oracle генерирует ORA-22905 исключение.(будет лежать базовая таблица, если коллекция будет храниться во вложенной таблице; именно поэтому эта ситуация специально упоминается в исключении).

Обновление: PL/SQL решение:

FOR i IN 1 .. P_DISABLE_LIST.COUNT LOOP
  UPDATE T_PRIVILAGE
  SET    ENABLE_STATUS = 0,
         GRANT_USERID  = P_REQ_USER_ID
  WHERE  ID = P_DISABLE_LIST(i);
END LOOP;