Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1182521
  • 博文数量: 398
  • 博客积分: 10110
  • 博客等级: 上将
  • 技术积分: 4055
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-23 20:01
个人简介

新博客http://www.cnblogs.com/zhjh256 欢迎访问

文章分类

全部博文(398)

文章存档

2012年(1)

2011年(41)

2010年(16)

2009年(98)

2008年(142)

2007年(100)

我的朋友

分类: Oracle

2008-01-06 21:13:50

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的引用。

 

 

阅读(945) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~