近日,接触到ORACLE 存储过程的优化问题,业务过程非常简单。 该存储过程的主要DML操作就是UPDATE,需要根据某个条件对数 据表的某个字段进行更新,其中使用到游标。之后写了个测试的例 子,以下具体介绍。 测试环境: OS:WINDOWS XP +Intel(R) 2CPU(1.60GHz) + 1GB 数据库: ORACLE10g 测试数据:10万行 存储过程代码: CREATE OR REPLACE PROCEDURE prc_update_0 IS TYPE tab_id IS TABLE OF ROWID; CURSOR cur_sky IS SELECT ROWID FROM TBL_USER_AUTH WHERE IMSI IS NOT NULL; BEGIN OPEN cur_sky ; FETCH cur_sky BULK COLLECT INTO v_rowid ; FORALL i IN 1..v_rowid.COUNT UPDATE TBL_USER_AUTH SET MPXYZDATAE='nsofsofmslfd' WHERE ROWID = v_rowid(i); COMMIT; CLOSE cur_sky; EXCEPTION WHEN others THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Update failed !'); END; /
CREATE OR REPLACE PROCEDURE prc_update_1 IS v_rowid ROWID; CURSOR cur_sky IS SELECT ROWID FROM TBL_USER_AUTH WHERE IMSI IS NOT NULL; BEGIN OPEN cur_sky ; LOOP FETCH cur_sky INTO v_rowid ; EXIT WHEN cur_sky%NOTFOUND; UPDATE TBL_USER_AUTH SET MPXYZDATAE='nsofsofmslfd' WHERE ROWID = v_rowid; END LOOP; COMMIT; CLOSE cur_sky; EXCEPTION WHEN others THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Update failed !'); END; /