批量sql实例分析与比较（Compared with bulk SQL case analysis）.doc

批量sql实例分析与比较（Compared with bulk SQL case analysis） Compared with bulk SQL case analysis Oracle introduced BULK SQL in PL/SQL, to minimize the interaction between PL/SQL and SQL in order to improve the performance of the engine. Specifically, the Oracle BULK SQL FORALL BULK COLLECT, including the statement clause. The former will be multiple statements (usually DML) once sent to the SQL engine; the latter will get the results back to the disposable SQL engine PL/SQL engine. FORALL The following two examples comparing the difference between FORALL and FOR cycle: SQL create table t_bulk as select * from employees; SQL desc t_bulk; Name Type Nullable Default Comments , EMPLOYEE_ID NUMBER (6) Y FIRST_NAME VARCHAR2 (20) Y LAST_NAME VARCHAR2 (25) EMAIL VARCHAR2 (25) PHONE_NUMBER VARCHAR2 (20) Y HIRE_DATE DATE JOB_ID VARCHAR2 (10) SALARY NUMBER (8,2) Y COMMISSION_PCT NUMBER (2,2) Y MANAGER_ID NUMBER (6) Y DEPARTMENT_ID NUMBER (4) Y DECLARE TYPE NumList IS VARRAY (20) OF NUMBER; Depts: NumList = NumList (10, 30, 70); BEGIN FOR I IN depts.FIRST..Depts.LAST LOOP DELETE FROM t_bulk WHERE department_id = depts (I); END LOOP; END; DECLARE TYPE NumList IS VARRAY (20) OF NUMBER; Depts: NumList = NumList (10, 30, 70) - Department numbers; BEGIN FORALL I IN depts.FIRST..Depts.LAST DELETE FROM t_bulk WHERE department_id = depts (I); END; Although the internal implementation mechanism, there is a great difference between the two cycles, but grammatically or very similar. But there is a need to pay attention to small details, the FORALL statement is not a corresponding END statement. Let us look at the abnormal treatment, the use of FORALL: CREATE TABLE t_bulk2 (F1 NUMBER (3)); DECLARE TYPE type1 IS TABLE OF NUMBER; V type1:=type1 (1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001); BEGIN EXECUTE IMMEDIATETRUNCATE TABLE t_bulk2; - FORALL idx IN v. v.last第一.. 插入t_bulk2值（V（IDX））； —— 例外 当别人然后 dbms_output put_line（sqlerrm）； 结束； ora-01438：值大于为此列指定的允许精度 PL/SQL程序顺利完成 SQL SELECT * FROM t_bulk2 ； 一层楼 ----