我的第一个ORACLE方法

功能: 提取参数生成sql;

CREATE OR REPLACE FUNCTION fnGetExpSql(p_data     varchar2,
                                          p_name     varchar2,
                                          p_dataType varchar2)
/**
  * 根据比较符和参数生成SQL表达式
  * @param p_data 参数值列表,格式:参数名,比较符,参数值;参数名,比较符,参数值...
                例如：
                month,EQUAL,10;canton,IN,东城区,西城区,海淀区,朝阳区;area,BETWEEN,100,300
  * @param p_name 想要提取的参数名
  * @param p_dataType 数据类型：date,varchar2,integer等
  * @return sql表达式,例如:between 100 and 300
  *
  * @author 
  * @since 2018-11-27
  */
 Return varchar2 is
  l_start    int;
  l_end      int;
  l_operator varchar2(20);
  l_expSql   varchar2(4000);
  l_value    varchar2(2000);
  l_value1   varchar2(2000);
  l_value2   varchar2(2000);
  l_dataType varchar2(20);
  l_data     varchar2(4000);

begin
  --传入空值时
  if (Trim(p_data) is null or Trim(p_name) is null) then
      Return(' is null or 1 = 1 ');
  end if;
  --将参数格式化
  l_dataType := lower(p_dataType);
  l_data     := lower(p_data);
  --获取运算符号
  l_start    := instr(p_data, lower(p_name) || ',', 1, 1);
    --找不到l_name时
    if l_start = 0 then
      Return(' is null or 1 = 1 ');
    end if;
  l_start    := l_start + 1 + LENGTH(p_name);
  l_end      := instr(l_data, ',', l_start, 1);
  l_operator  := substr(l_data, l_start, l_end - l_start);
  --获取参数值
  l_start    := l_end + 1;
  l_end      := instr(l_data, ';', l_start, 1);
  if l_end != 0 then                             --①不是最后一组参数时
    l_value  := substr(l_data, l_start, l_end - l_start);
  else                                           --②是最后一组参数时
    l_value  := substr(l_data, l_start);
  end if;
  --根据运算符号获取输出sql
  -- 1. EQUAL OR NOT EQUAL
  if  instr(l_operator, 'equal')>0 then
    if (l_operator = 'not_equal') then
      if (l_dataType = 'date') then
        l_expSql := 'not ';
      else
        l_expSql := '!';
      end if;
    end if;
    if l_dataType = 'varchar2' then
      l_expSql := l_expSql || '= ''' || l_value || '''';
    elsif l_dataType = 'date' then
      l_expSql := l_expSql || 'between to_date('''||substr(l_value, 1, 10)||
               ''',''yyyy-mm-dd'') and to_date('''||substr(l_value, 1, 10)||
               ''',''yyyy-mm-dd'')+1';
    else
      l_expSql := l_expSql || ' = ' || l_value;
    end if;
  -- 2. LESS OR GREATER less_equal GREATER_EQUAL
  elsif (l_operator = 'less' or l_operator = 'greater' or
        l_operator = 'less_equal' or l_operator = 'greater_equal') then
    if (l_operator = 'less') then
      l_expSql := ' < ';
    elsif (l_operator = 'less_equal') then
      l_expSql := ' <= ';
    elsif (l_operator = 'greater') then
      l_expSql := ' > ';
    else
      l_expSql := ' >= ';
    end if;
    if l_dataType = 'varchar2' then
      l_expSql := l_expSql || '''' || l_value || '''';
    elsif l_dataType = 'date' then
      l_expSql := l_expSql || 'to_date(''' || substr(l_value, 1, 10) ||
                   ''',''yyyy-mm-dd'')';
    else
      l_expSql := l_expSql || l_value;
    end if;
  -- 3. LESS OR GREATER LESS_EQUAL GREATER_EQUAL
  elsif (l_operator = 'between') then
    l_start  := instr(l_value, ',', 1);
    l_value1 := substr(l_value, 1, l_start-1);
    l_value2 := substr(l_value, l_start+1);
    if l_dataType = 'varchar2' then
      l_expSql := 'between ''' || l_value1 || ''' and ''' || l_value2 || '''';
    elsif l_dataType = 'date' then
      l_expSql := 'between to_date(''' || substr(l_value1, 1, 10) ||
       ''',''yyyy-mm-dd'') and to_date(''' || substr(l_value2, 1, 10) ||
        ''',''yyyy-mm-dd'')+1';
    else
      l_expSql := 'between '|| l_value1 || ' and ' || l_value2;
    end if;
  -- 4. is_null or is_not_null
  elsif l_operator = 'is_null' or l_operator = 'is_not_null' then
    l_expSql := replace(l_operator, '_', ' ');
  -- 5. in
  elsif l_operator = 'in' then
    --select '''' ||rtrim(fnconcat(column_value || ''','''), ''',''') || '''' into l_value from table(fnsplit(l_value));
    --l_expSql := 'in (' || l_value || ')';
    l_expSql := 'in (''' || replace(l_value,',',''',''') || ''')';
  -- 6. not_in
  elsif l_operator = 'not_in' then
    --select '''' ||rtrim(fnconcat(column_value || ''','''), ''',''') || '''' into l_value from table(fnsplit(l_value));
    --l_expSql := 'not in (' || l_value || ')';
    l_expSql := 'not in (''' || replace(l_value,',',''',''') || ''')';
  -- 7. like or not_like  
  elsif l_operator = 'like' or l_operator = 'not_like' then
    l_expSql := replace(l_operator, '_', ' ') || ' ''%' || l_value || '%''';
  end if;
  --添加一个空格,以避免直接追加在sql语句后面时出错
  return ' '||l_expSql;
end;