抱歉用了这么渣的标题，其实是一个很简单而且很常见的需求：假设我们有一个学生表，它有一个状态字段：
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秒。
