Oracle 大表之间关联update

DECLARE
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
p_id_table dbms_sql.Varchar2_Table;

CURSOR acnt_first_cur IS
SELECT/*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */ t2.service_type, t1.rowid
FROM kfgl_p_web_access_t9 t1,LC_CP.b_serv_t@TO_HUBEI_ODS1_T t2
WHERE t1.acc_nbr = t2.acc_nbr
AND t2.state = 'F0A'
AND t1.par_id =1
ORDER BY t1.rowid;
BEGIN
OPEN acnt_first_cur ;
LOOP
FETCH acnt_first_cur BULK COLLECT INTO p_id_table, row_id_table LIMIT maxrows;
EXIT WHEN row_id_table.count =0;
FORALL i IN 1 .. row_id_table.count
UPDATE kfgl_p_web_access_t9 SET service_type =nvl(p_id_table(i),'/s/t/fix')
WHERE rowid = row_id_table(i) AND par_id =1;
COMMIT ;
END LOOP ;
CLOSE acnt_first_cur;
END ;

由于 forall 语句不能用动态的sql ,而表名又必须变，

所以在执行的时候，把上面的语句都写在一个 V_sql中，然后执行sql语句。

v_sql :='DECLARE
maxrows number default 2000;
row_id_table dbms_sql.Urowid_Table;
p_id_table dbms_sql.Varchar2_Table;

CURSOR acnt_first_cur IS
SELECT/*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */ t2.service_type, t1.rowid
FROM '||v_table_name||' t1,LC_CP.b_serv_t@TO_HUBEI_ODS1_T t2
WHERE t1.acc_nbr = t2.acc_nbr
AND t2.state = ''F0A''
AND t1.par_id ='||v_day_id||'
ORDER BY t1.rowid;
BEGIN
OPEN acnt_first_cur ;
LOOP
FETCH acnt_first_cur BULK COLLECT INTO p_id_table, row_id_table LIMIT maxrows;
EXIT WHEN row_id_table.count =0;
FORALL i IN 1 .. row_id_table.count
UPDATE '||v_table_name||' SET service_type =p_id_table(i)
WHERE rowid = row_id_table(i) AND par_id ='||v_day_id||';
COMMIT ;
END LOOP ;
CLOSE acnt_first_cur;
END ;';

EXECUTE IMMEDIATE v_sql ;

这样就兼顾了快速跟动态了。

转载于:https://www.cnblogs.com/Alex-Zeng/archive/2012/10/23/2736035.html