SQL与集合

SQL与集合

       通常当用户可以声明多个搜索的值时会使用动态构造，如下：

       where SURNAME in ( 'SMITH', 'WILLIAMS')

       或者：

where SURNAME in ( 'SMITH', 'WILLIAMS','DIO','MALMSTEEN')

然后构造成一个语句：

l_sql := 'select * from persons where surname in ('|| lv_list_of_names ||')';

但是每次执行都需要重新构造语句，因此不会使用任何绑定变量，而且存在SQL注入的隐患。

不过，可以使用另一种方法，而不需要使用动态SQL构造，同时还可以使用绑定变量，如下：

SQL> create or replace type varchar2_list as table of varchar2 (255);

  2  /

创建一个转换为集合的函数：

SQL> create or replace

  2  function in_list( p_string in varchar2 ) return varchar2_list as

  3    l_string        long default p_string || ',';

  4    l_data          varchar2_list := varchar2_list();

  5    n               number;

  6  begin

  7    loop

  8      exit when l_string is null;

  9      n := instr( l_string, ',' );

 10      l_data.extend;

 11      l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 )) );

 12      l_string := substr( l_string, n+1 );

 13    end loop;

 14    return l_data;

 15  end;

 16  /

一旦执行后，就可以测试该函数：

SQL> select *

  2  from   table(cast(in_list('SMITH,JONES,BROWN') as varchar2_list));

 

COLUMN_VALUE

--------------------------

SMITH

JONES

BROWN

然后就可以使用静态SQL处理：

SQL> declare

  2    list_of_people varchar2(100) := 'SMITH,WILLIAMS,DIO,MALMSTEEN';

  3  begin

  4    for i in ( select id, date_created

  5               from   person

  6               where  upper(surname) in (

  7                 select *

  8                 from   table(cast(in_list(list_of_people) as varchar2_list))) ) loop

  9      dbms_output.put_line('ID           = '||i.id);

 10      dbms_output.put_line('DATE_CREATED = '||i.date_created);

 11    end loop;

 12  end;

 13  /

ID           = 9000001

DATE_CREATED = 01/OCT/03

ID           = 9000002

DATE_CREATED = 01/OCT/03

ID           = 9000003

DATE_CREATED = 01/OCT/03

ID           = 9000004

DATE_CREATED = 01/OCT/03

ID           = 9000010

DATE_CREATED = 01/OCT/03

ID           = 9000023

DATE_CREATED = 02/OCT/03

ID           = 9000026

DATE_CREATED = 02/OCT/03

 

PL/SQL procedure successfully completed.

此时我们可以重用相同的SQL，不需要动态SQL，不需要文本常量。

也可以不传递文本常量，而传递一个SQL查询，如下：

SQL> create or replace

  2  function in_list( p_string in varchar2 ) return varchar2_list as

  3    type rc is ref cursor;

  4    l_cursor     rc;

  5    l_tmp        long;

  6    l_data       varchar2_list := varchar2_list();

  7  begin

  8    open l_cursor for p_string;

  9    loop

 10        fetch l_cursor into l_tmp;

 11        exit when l_cursor%notfound;

 12        l_data.extend;

 13        l_data(l_data.count) := l_tmp;

 14    end loop;

 15    close l_cursor;

 16    return l_data;

 17  end;

 18  /

然后通过以下进行调用：

SQL> declare

  2    list_of_people varchar2(100) := 'select surname from nominal '||

  3                                    ' where surname like ''S%''';

  4  begin

  5    for i in ( select id, date_created

  6               from   person

  7               where  upper(surname) in (

  8                 select *

  9                 from   table(cast(in_list(list_of_people) as varchar2_list))) ) loop

 10      dbms_output.put_line('ID           = '||i.id);

 11      dbms_output.put_line('DATE_CREATED = '||i.date_created);

 12    end loop;

 13  end;

 14  /

ID           = 9000001

DATE_CREATED = 01/OCT/03

ID           = 9000010

DATE_CREATED = 01/OCT/03

很详细的信息可以查看Application Developer - Object Relational reference，其中包含了PL/SQL的引用。