【PL/SQL】初試 bulk collect

SQL> create table yang(last_name varchar2(20),first_name varchar2(10),salary number(10));
 
Table created
Executed in 1.388 seconds

SQL> begin
  2  for i in 1000..100999 loop
  3  insert into yang (last_name,first_name,salary) values('qilong'||(i-1000),'yang'||(100999-i),i);
  4  end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed
Executed in 4.852 seconds

SQL> select count(*) from yang;
  COUNT(*)
----------
    100000
Executed in 0.047 seconds
SQL> select count(1) from yang;
 
  COUNT(1)
----------
    100000
Executed in 0.032 seconds
---常規的distinct用法。
SQL> select count (distinct last_name) from yang;
COUNT(DISTINCTLAST_NAME)
------------------------
                  100000
Executed in 0.124 seconds
SQL>

-----使用遊標
SQL> declare
  2    all_rows number(10);
  3    temp_last_name yang.last_name%type;
  4  begin
  5    all_rows:=0;
  6    temp_last_name:=' ';
  7    for cur in (select last_name from yang order by last_name) loop
  8        if cur.last_name!=temp_last_name then
  9         all_rows:=all_rows+1;
 10        end if;
 11        temp_last_name:=cur.last_name;
 12    end loop;
 13    dbms_output.put_line('all_rows are '||all_rows);
 14  end;
 15  /
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.156 seconds

遊標需要0.156 秒才能查出該表中有100000個不重複的Last_name值，所耗時間是Distinct查詢多0.032秒。
--使用Bulk Collect批查詢來實現
SQL> declare
  2    all_rows number(10);
  3    --首先，定義一個Index-by表資料型別
  4    type last_name_tab is table of yang.last_name%type index by binary_integer;
  5    last_name_arr last_name_tab;
  6    --定義一個Index-by表集合變
  7    temp_last_name yang.last_name%type;
  8  begin
  9    all_rows:=0;
 10    temp_last_name:=' ';
 11    --使用Bulk Collect批查詢來充填集合變數
 12    select last_name bulk collect into last_name_arr from yang;
 13    for i in 1..last_name_arr.count loop
 14        if temp_last_name!=last_name_arr(i) then
 15         all_rows:=all_rows+1;
 16        end if;
 17        temp_last_name:=last_name_arr(i);
 18    end loop;
 19   dbms_output.put_line('all_rows are '||all_rows);
 20  end;
 21  /
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.078 seconds
--從上面執行結果，我們可以看到，
Bulk Collect批查詢只需要0.078秒就能查出該表中有100000個不重複的Last_name值，
所耗時間只有遊標查詢的1/2，同時它比Distinct常規查詢的速度也要快。