【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常规查询的速度也要快。 
数据库小技能：PL/SQL中书写定义sql 触发器由数据库管理系统负责调用和执行，通过触发触发器所监听的事物来实现触发器的调用。 表级别的触发器（对于整个数据库表做监听） 行级别的触发器（对于表中的每一行做监听）
数据库小技能：PL/SQL（Procedure Language/SQL,过程化SQL语言）预备知识 过程化SQL语言（Procedural Language/SQL）是Oracle数据库对SQL语句的扩展：在普通SQL语句的使用上增加了编程语言的特点，把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载