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;

【方法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;

虽然方法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;

这个方法除了写法有些奇怪之外，最大的缺点是性能很差——这种写法会造成全表扫描，类型转换和字符串匹配也要耗费不少的时间。实测获取状态为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;

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

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;

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;