随笔 - 127  文章 - 26 评论 - 2677 trackbacks - 29

抱歉用了这么渣的标题,其实是一个很简单而且很常见的需求:假设我们有一个学生表,它有一个状态字段:

create table T_STU
(
  STU_ID     VARCHAR2(36) not null,
  NAME       VARCHAR2(255),
  CODE       VARCHAR2(255),
  STATE      NUMBER(10),
  START_YEAR NUMBER(10)
);
alter table T_STU2 add constraint PK_STU2 primary key (STU_ID);
create index IX_STU21 on T_STU2 (STATE);

由一个数字代表学生的各种状态,例如 1 表示“在校”,2 表示“休学”,3 表示“肄业”,5表示“毕业”。
现在想要创建一个查询学生表的存储过程,我们希望它能灵活点儿,可以查询出某几个状态下的学生。例如,假设存储过程叫做 SP_QUERY_STU_BY_STATES,
SP_QUERY_STU_BY_STATES('2,3')
将获得所有休学和肄业的学生。我们从最简单的方法开始,寻求一种相对较好的解决方案。

【方法1】将参数直接放在 in 表达式里面(不可用)

create or replace procedure SP_QUERY_STU_BY_STATES1(
    cur_OUT out SYS_REFCURSOR,
    p_states varchar2
    ) is
begin
open cur_OUT for
select t.* from t_stu t
 where t.state in (p_states);
end SP_QUERY_STU_BY_STATES1;

 调用 SP_QUERY_STU_BY_STATES1('2') 没有问题,但是调用 SP_QUERY_STU_BY_STATES1('2,3')  会报“ORA-01722:无效数字”的错误。因为 Oracle 不能把字符串直接转换为列表。


【方法2】使用动态SQL语句(可用,但是有许多缺点)

将 p_states 作为SQL语句的一部分拼接起来之后再执行,就不会报 ORA-01722 错误了。

create or replace procedure SP_QUERY_STU_BY_STATES2(
    cur_OUT out SYS_REFCURSOR,
    p_states varchar2
    ) 
is
query_string  VARCHAR2(4000);
begin

query_string := 'select /*+RULE*/ t.* from t_stu t
                  where t.state in (' || p_states || ')';
   
dbms_output.put_line(query_string); -- for debug

open cur_OUT for query_string;

end SP_QUERY_STU_BY_STATES2;

注意这里使用增加 /*+RULE*/ 标记的方式强制使用RBO优化器,可以让Oracle利用STATE字段上的索引而提高效率。实测查询状态为2,3的学生(从3,000,000条里取出154条数据),不加 /*+RULE*/ 标记时CBO会使用全表扫描,耗时2.3秒(即使刚刚做完表分析也要耗时0.6秒);加上 /*+RULE*/ 标记利用STATE字段上的索引,耗时0.172秒。所以后面的所有查询都会加上/*+RULE*/ 标记。


虽然方法2可以得到正确的结果,但是它有好几个让人抓狂的缺点。
1. 动态SQL的效率要比静态SQL稍低。
2. 可读性差。SQL语句本身可读性就不好。想象一下,读一个上百行的复杂查询本身就很让人头大了,如果里面还有大量判断语句和字符串拼接,整个代码会丑得让人想吐。
3. 语法错误要到运行时才会暴露出来。
4. 想看执行计划挺不方便的,要使用 dbms_output.put_line() 把生成的SQL输出才能查看执行计划。

总之,动态SQL是非常灵活同时又是非常恶心的方法。记得有一天吃完午饭,突然感觉实在受不了动态SQL了,就憋了半小时,想到了下面这个方法。

【方法3】使用反着的LIKE语句(可用,但效率低下)

create or replace procedure SP_QUERY_STU_BY_STATES3(
    cur_OUT out SYS_REFCURSOR,
    p_states varchar2
    ) is
begin
open cur_OUT for
select /*+RULE*/ * from t_stu t
 where ',' || p_states || ',' like '%,' || t.state || ',%';

end SP_QUERY_STU_BY_STATES3;

上面这段代码可以这么理解:假设 p_states 参数为 '2,3',对于 STATE 字段为 2 或 3 的数据, '2,3' like '%2%' 和 '2,3' like '%3%' 都会被判定为真;对于STATE字段为5的数据,'2,3' like '%5%' 会被判定为假,这样自然就筛选出了状态为2和3的数据。之所以拼接了许多 “,” ,是因为如果有一个状态是12的话,'12,3' like '%2%'也会被判定为真,这样就错误地把不需要的数据也包含进来了。 

这个方法除了写法有些奇怪之外,最大的缺点是性能很差——这种写法会造成全表扫描,类型转换和字符串匹配也要耗费不少的时间。实测获取状态为2,3的数据耗时2.3秒。

【方法4】将字符串转换为数组(可用,而且性能好)

先来看一下最终结果

create or replace procedure SP_QUERY_STU_BY_STATES4(
    cur_OUT out SYS_REFCURSOR,
    p_states varchar2
    ) is
begin
open cur_OUT for
select /*+RULE*/ * from t_stu t
 where t.state in (select column_value from TABLE(f_cstr_to_list(p_states)));
end SP_QUERY_STU_BY_STATES4;

这种方法可以利用STATE上的索引,性能很好。实测获取2,3状态的数据耗时0.171秒。

这个方法的重点在于如何把逗号分隔的字符串状态列表转换为数组。首先,需要定义一个内容为字符串的数组类型 t_strlist

CREATE OR REPLACE Type t_strlist as Table of Varchar2(4000);

由于Oracle没有分割字符串的 split 函数,下面这个将逗号分隔的字符串转换为数组的函数稍稍有些杂乱,我尽量写得可读性好一点,相信并不难看懂。

CREATE OR REPLACE Function f_cstr_to_list
-- 将逗号分隔的字符串分解为列表
(
  cstr    In Varchar2
)
  Return t_strlist
is
  v_start number := 1; -- 迭代搜索开始位置
  v_i number := 1; -- 迭代次数
  v_position number := 0; -- 每次迭代找到的逗号字符的位置
  v_str varchar2(4000); -- 源字符串
  Result t_strlist;
Begin
  Result := t_strlist();
  v_str := cstr || ',';
  
  loop
    v_position := instr(v_str, ',', 1, v_i);
    if(v_position > 0) then
      Result.EXTEND;
      Result(v_i) := substr(v_str, v_start, v_position-v_start);
    
      v_start := v_position + 1;
      v_i := v_i + 1;
    else
      exit;
    end if;
  end loop;
  
  return Result;
End;

 

这种方法除了需要自己写一个自定义函数有些麻烦(当然只需麻烦一次),而且需要冒函数写得不对而引发BUG的风险之外,可以说是非常完美。当然,实战中往往还需要按姓名等字段进行模糊查询,这时的效率如何呢?我们来试试下面这个更为实用一点的存储过程。

create or replace procedure SP_QUERY_STU_BY_NAME_STATES(
    cur_OUT out SYS_REFCURSOR,
    p_name varchar2,
    p_states varchar2
    ) is
begin
open cur_OUT for
select /*+RULE*/ * from t_stu t
 where t.name like '%' || p_name || '%'
   and t.state in (select column_value from TABLE(f_cstr_to_list(p_states)));
end SP_QUERY_STU_BY_NAME_STATES;

看一下执行计划:


Oracle会先使用STATE上的索引将检索范围缩小然后再模糊匹配,效率自然会比较高。实测从3,000,000条数据里获取77条耗时0.11秒。

posted on 2013-08-22 12:06 1-2-3 阅读(...) 评论(...) 编辑 收藏