姓张的小子 —— 博客

Exists和In的一个代替方案

Posted by eDWARD at 16:55

项目需要，所以学习了一下如何想办法替代exists和in带来的性能问题，现给出一种方案：
首先定义两个TYPE，一个用来装INT，一个用来装VARCHAR

create or replace type numTableType as table of number;
create or replace type vartabletype is  table of varchar2(2000);

然后定义两个函数：

create or replace function str2numList( p_string in varchar2 ) return numTableType
as
 v_str long default p_string || ‘,’;
 v_n number;
 v_data numTableType := numTableType();
begin
 loop
 v_n := to_number(instr( v_str, ‘,’ ));
 exit when (nvl(v_n,0) = 0);
 v_data.extend;
 v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
 v_str := substr( v_str, v_n+1 );
 end loop;
 return v_data;
end;

create or replace function str2varList( p_string in varchar2 ) return VarTableType
 as
 v_str long default p_string || ‘,’;
 v_n varchar2(2000);
 v_data VarTableType := VarTableType();
 begin
  loop
   v_n :=instr( v_str, ‘,’ );
 exit when (nvl(v_n,0) = 0);
 v_data.extend;
  v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
 v_str := substr( v_str, v_n+1 );
  end loop;
  return v_data;
 end;
 

上面这两个函数分别接受用‘，’分隔的INT或VARCHAR串，然后填充到刚才建立的两个TYPE里，这样就可以联合查询而不用IN了。
最后在SQL里的使用方法如下，存错过程类似，所以就不举例了：）
select b.BidWordId, b.BidWord, b.MaxPrice, b.Title, b.Description, b.DisplayUrl, b.LinkUrl, b.Onlinestate, b.Validstate, c.Custstat, c.Custid from AuditList a, BidWord b, Customers c, (select COLUMN_VALUE from the (select cast(str2numList(?) as numTableType) from dual)) d where a.BidWordID = b.BidWordID and b.CustID = c.CustID and a.AuditState = 40 and a.CompleteType = 10 and a.TicketID = d.COLUMN_VALUE order by a.CompleteTime desc

Filed In 学习路上 | Study | Tags: 数据库 |