Bulk Cursor mit Update
Tipp 5
Thema: Allgemeines
Datenbank-Version: RDBMS 8.x
Erstellt am 07.10.19
Bearbeitet am 07.10.19
DECLARE
TYPE big_tab_type IS TABLE OF
scott.big_tab.object_id%TYPE;
mybig_tab big_tab_type;
summe NUMBER:=0;
BEGIN
SELECT object_id BULK COLLECT INTO mybig_tab
FROM scott.big_tab;
FORALL i IN mybig_tab.first .. mybig_tab.last
UPDATE emp2 set object_name=object_name
WHERE object_id=mybig_tab(i);
END;
/
REM Zweite Variante (mit zwei Arrays!)
DECLARE
TYPE emp_tab_type IS TABLE OF scott.emp%ROWTYPE;
TYPE pk_tab_type IS TABLE OF scott.emp.empno%TYPE; -- INDEX BY BINARY_INTEGER;
emp_tab emp_tab_type;
emp_pk pk_tab_type;
BEGIN
SELECT e.* BULK COLLECT INTO emp_tab FROM scott.emp e;
emp_pk:=pk_tab_type();
FOR i IN emp_tab.first .. emp_tab.last LOOP
emp_pk.extEND;
emp_pk(i):=emp_tab(i).empno;
END LOOP;
FORALL i IN emp_pk.first .. emp_pk.last
UPDATE emp
SET row=emp_tab(i)
WHERE empno = emp_pk(i);
END;
/