Как использовать Collection в качестве таблицы для UPDATE с предложением IN
Мне нужен этот фрагмент кода в хранимой процедуре, чтобы я мог передать массив идентификаторов и обновить связанные записи. Мне интересно, нужно ли мне использовать цикл, а не использовать предложение IN в sp.
SET SERVEROUTPUT ON
DECLARE
P_IDS PKGINFO.t_ids; --type: table of NUMBER index by pls_integer;
P_RESULT NUMBER;
BEGIN
p_IDS(1) := 12345;
--this works fine:
for i in ( select * from table(p_ids))
loop
UPDATE TABLE1
SET FD1 = 'test'
WHERE P_ID = i.column_value;
end loop;
--this works fine too:
SELECT COUNT(*) INTO p_RESULT FROM TABLE1
WHERE P_ID IN (SELECT * FROM TABLE (p_ids));
--but this does not work, why????? how to make it work?
UPDATE TABLE1
SET FD1 = 'test'
WHERE P_ID IN (SELECT * FROM TABLE (p_ids));
END;
--==================PKGINFO.t_ids==================
CREATE OR REPLACE package dbname.PKGINFO as
-- package created to perform Associative array calls
type t_ids is table of NUMBER index by pls_integer;
end PKGINFO;
/
Я ожидал, что UPDATE может использовать предложение IN, но оно выдает ошибку INVALID TYPE.
Ответы 1
До недавнего времени Oracle не разрешал типы PL/SQL в операторах SQL, в том числе в выражение коллекции таблиц. Похоже, вы используете версию, в которой добавлена поддержка select, но (пока) нет для update. Если бы он у вас был, вы могли бы использовать тип уровня схемы. Также обратите внимание на member of.
С тем типом, который у вас есть сейчас, вы можете использовать FORALL, что будет более эффективно, чем цикл с отдельными обновлениями::
FORALL i IN p_ids.first..p_ids.last
UPDATE TABLE1
SET FD1 = 'test'
WHERE P_ID = p_ids(i);
приятно это знать... я нашел исходный пост, где я нашел код... он тоже может не работать? stackoverflow.com/a/243011/723979
@shrimprice - нет, это сработает, потому что он использует тип уровня схемы, а не тип, объявленный PL/SQL. Я немного неправильно понял вашу проблему, извините. Я обновил поясняющую часть своего ответа. Какую версию Oracle вы используете, 12cR1? FORALL все равно должно работать...
Спасибо за FORALL... однако внутри цикла мне нужно сделать одно UPDATE и одну INSERT для разных таблиц. Поэтому мне нужно будет использовать FORALL дважды. Будет ли этот способ по-прежнему более эффективным?
Да, это все равно будет эффективнее, чем цикл с построчными обновлениями и вставками. FORALL — это единая массовая операция.
...Сегодня произошло кое-что интересное. Код работал, когда я задал этот вопрос. Однако сегодня sp перемещен в другую среду, которая, как я думал, должна быть такой же. Теперь выдает ошибку: ERROR-ORA-21700: объект не существует или помечен для удаления. Я понял, что на этот раз не работает часть «SELECT»! Спасибо за ваш пост, так что я знаю, что select не поддерживается в некоторых версиях ..... Итак, у вас есть какие-либо предложения о том, как использовать SELECT?
Я нашел еще один интересный пост здесь: stackoverflow.com/questions/50821267/… ты все равно знаешь ответ?
Пожалуйста, отредактируйте свой вопрос, используя кнопку
editчуть ниже тегов, и включите в вопрос утверждение, которое вы используете для созданияPKGINFO.T_IDS. Спасибо.