结合一个实例来理解一下在SELECT INTO 语句中批量绑定的使用:
SQL> conn lunar/lunar Connected. SQL> SET SERVEROUTPUT ON SQL> Create Or Replace Procedure lunartest2 Is 2 TYPE NumTab IS TABLE OF emp.empno%TYPE; 3 TYPE NameTab IS TABLE OF emp.ename%TYPE; 4 enums NumTab; -- no need to initialize 5 names NameTab; 6 BEGIN 7 SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; 8 FOR i in enums.FIRST..enums.LAST LOOP 9 DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' || names(i)); 10 END LOOP ; 11 END; 12 /
Procedure created.
Elapsed: 00:00:00.08 SQL>
|
这里我们看到,使用SELECT .. BULK COLLECT INTO的方法和传统的SELECT .. INTO的语法基本上变化不大。现在我们看一下执行结果:
SQL> select empno,ename from emp;
EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
14 rows selected.
Elapsed: 00:00:00.00 SQL> exec lunartest2; 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00 SQL>
|
如果我们这里需要输出的记录不是14条,而是140万条甚至更多,那么BULK COLLECT的就会发挥出强悍的优势,这里我们仅仅是在功能上得到验证。