Memorizza il risultato della query meno con più colonne in una variabile in Oracle PL / SQL
Sto usando il codice seguente per recuperare gli ID progetto extra presenti in TABLE_ONE rispetto a TABLE_TWO ( risultato dell'archivio di origine della query meno (elenco di varchar) in una variabile in Oracle PL / SQL )
DECLARE
l_missing_id_list SYS.ODCINUMBERLIST;
BEGIN
SELECT project_id
BULK COLLECT INTO l_missing_id_list
FROM
(
SELECT t1.project_id FROM table_one t1
MINUS
SELECT t2.project_id FROM table_two t2 );
FORALL i IN l_missing_id_list.FIRST..l_missing_id_list.LAST
INSERT INTO table_two VALUES ( l_missing_id_list(i) );
COMMIT;
-- Values are now inserted and you have the list of IDs in l_missing_id_list to add to your email.
END;
Ora voglio aggiungere i nomi dei progetti corrispondenti anche alla seconda tabella. Qualsiasi project_id aggiuntivo presente in TABLE_ONE rispetto a TABLE_TWO deve essere inserito in TABLE_TWO insieme ai corrispondenti nomi di progetto. Come farlo? Si noti che è necessario memorizzare il risultato della query meno in una variabile poiché eseguirò più passaggi con il risultato.
Risposte
SYS.ODCINUMBERLIST
è solo un elenco in cui ogni record ha un singolo numero. È necessario utilizzare un nuovo tipo di record per memorizzare i più campi in ogni riga.
È possibile definire un tipo di record da utilizzare al posto di SYS.ODCINUMBERLIST
. Ma sarei propenso a spostare l' SELECT
istruzione in modo che sia un cursore esplicito, in modo da poter definire un nuovo tipo come cursore %ROWTYPE
. In questo modo, il tipo di record e l'istruzione select sono sempre coerenti, ma cambia leggermente la struttura del codice.
Questo è l'approccio:
DECLARE
CURSOR c_select IS
SELECT project_id, project_name
FROM
(
SELECT t1.project_id, t1.project_name FROM table_one t1
MINUS
SELECT t2.project_id, t2.project_name FROM table_two t2 );
TYPE l_missing_row_list_typ IS TABLE OF c_select%ROWTYPE;
l_missing_row_list l_missing_row_list_typ;
BEGIN
OPEN c_select;
FETCH c_select BULK COLLECT INTO l_missing_row_list;
CLOSE c_select;
FORALL i IN l_missing_row_list.FIRST..l_missing_row_list.LAST
INSERT INTO table_two VALUES ( l_missing_row_list(i).project_id, l_missing_row_list(i).project_name );
COMMIT;
-- Values are now inserted and you have the list of IDs in l_missing_row_list to add to your email.
END;
Una nota sul BULK COLLECT
(sia qui che nel tuo post originale) - i dati che stai raccogliendo in blocco sono archiviati nella memoria PGA. Quindi non utilizzare questo codice così com'è se c'è una modifica il numero di nuovi record sarà irragionevole (forse più di qualche migliaio).