Bind variables in 'in' condition（在in中动态的绑定参数（参数个数可变））

最新推荐文章于 2023-04-10 12:00:00 发布

bo o ya ka

最新推荐文章于 2023-04-10 12:00:00 发布

阅读量176

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/caroline/archive/2012/09/24/2699482.html

版权

Varying in lists...

在书写SQL语句的过程中，我们可能会遇到，在其中的一张表中会存储一个ID串的字段，那么在写SQL语句的过程中，我们可能会出现以下错误的写法，

1 SELECT * FROM WHERE col IN (SELECT col2 FROM dual)

本文将给出为什么该语法错误的原因：本文也给出了通用的几种处理方法：

网址：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:110612348061

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:494231000346774971

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

http://www.joegarrepy.com/str_package.htm

http://plnet.org/

http://www.cnblogs.com/caroline/archive/2011/12/19/2294056.html?updated=1

使用多种方式处理传递的可变的ID串，将其应用在IN关键字中

1、使用自定义的类型和函数进行转换

自定义类型：

1  create or replace type str2tblType as table of varchar2(4000)

函数：

 1 CREATE OR REPLACE FUNCTION str2tbl(p_str   IN VARCHAR2,
 2                                    p_delim IN VARCHAR2 DEFAULT ',')
 3   RETURN str2tbltype AS
 4   l_str LONG DEFAULT p_str || p_delim;
 5   l_n NUMBER;
 6   l_data str2tbltype := str2tbltype();
 7 BEGIN
 8   LOOP
 9     l_n := instr(l_str, p_delim);
10     EXIT WHEN(nvl(l_n, 0) = 0);
11     l_data.extend;
12     l_data(l_data.count) := ltrim(rtrim(substr(l_str, 1, l_n - 1)));
13     l_str := substr(l_str, l_n + 1);
14   END LOOP;
15   RETURN l_data;
16 END;

备注：函数中返回的集合类型必须是 SQL TYPE,

以下是TOM对于类似问题给出的正确答案：

Tom,
I tried to implement this as a cursor. It works fine in normal SQL but when it's inside a package
it compiles with the following error on the casting of the select "PLS-00513: PL/SQL function
called from SQL must return value of legal SQL type". I am running this on an 8i database

1 CREATE OR REPLACE PACKAGE panel_period_pkg AS
2   TYPE ppidtabletype IS TABLE OF NUMBER;--PL/SQL集合，非SQL中可见
3   FUNCTION alloc_to_dp(p_str               IN VARCHAR2,
4                        p_display_period_id IN NUMBER,
5                        p_user_id           IN VARCHAR2) RETURN VARCHAR2;
6   FUNCTION create_ppa_list_from_string(p_str IN VARCHAR2)
7     RETURN ppidtabletype;
8 END panel_period_pkg;

 1 CREATE OR REPLACE PACKAGE BODY panel_period_pkg AS
 2 
 3   FUNCTION alloc_to_dp(p_str               IN VARCHAR2,
 4                        p_display_period_id IN NUMBER,
 5                        p_user_id           IN VARCHAR2) RETURN VARCHAR2 IS
 6     l_err_msg VARCHAR2(30);
 7     CURSOR c_available_pp(p_str IN VARCHAR2) IS
 8       SELECT *
 9         FROM display_periods
10        WHERE display_period_id IN
11              (SELECT *
12                 FROM THE (SELECT CAST(create_ppa_list_from_string(p_str) AS
13                                       ppidtabletype)
14                             FROM dual)); --This line raises an error
15   BEGIN
16     RETURN l_err_msg;
17   END alloc_to_dp;
18 
19   /* This function takes the panel period id's passed in as a string as returns them */
20   /* as a pl/sql tavle to be used in the main select of panel period availabilty*/
21   FUNCTION create_ppa_list_from_string(p_str IN VARCHAR2)
22     RETURN ppidtabletype IS
23     l_str  LONG DEFAULT p_str || ',';
24     l_n    NUMBER;
25     l_data ppidtabletype := ppidtabletype();--PL/SQL集合类型，SQL中不可见！
26   BEGIN
27     LOOP
28       l_n := instr(l_str, ',');
29       EXIT WHEN(nvl(l_n, 0) = 0);
30       l_data.extend;
31       l_data(l_data.count) := ltrim(rtrim(substr(l_str, 1, l_n - 1)));
32       l_str := substr(l_str, l_n + 1);
33     END LOOP;
34     RETURN l_data;
35   END create_ppa_list_from_string;
36 
37 END panel_period_pkg;

Any suggestions?

以下是TOM给出的答案：

The type must be a SQL type -- not a plsql type.
move the type outside of the package -- it is a rule, it cannot be any other way. The type must be
known to SQL -- not just plsql.

使用如下的语句进行查询：

1 SELECT T.*,VALUE(T)
2   FROM all_users
3  WHERE username IN
4        (SELECT * FROM TABLE(CAST(str2tbl(:txt) AS str2tbltype))) T

也可以使用该语句进行查询：

1 SELECT VALUE(T),T.*
2   FROM all_users
3  WHERE user_id IN
4        (SELECT *
5           FROM THE (SELECT CAST(str2tbl('1, 3, 5, 7, 99') AS mytabletype)
6                       FROM dual)) T

【此处我们需要注意CAST、TABLE、THE三个关键字有所了解】

备注：关键字解释：

TABLE函数的解释：

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations.（由于潜在的瓶颈，一把不再联机事务处理系统中使用table函数） Regular table functions require named row and table types to be created as database objects.

保留关键子COLUMN_VALUE是访问嵌套表中记录行的一种方法

(表中还有一个nested_table_id的隐藏列，他是这样的一个外键，他映射到附表的记录行

THE是SQL的一个保留关键字，利用它可以从嵌套表的一个查询中检索出COLUMN_VALUE列，CAST将显式的将VARRAY类型的USER_ADDRESS转换成嵌套表，该嵌套表被定义为数据库中一个集合类型，在TABLE和THE关键字使用的过程中，都可以使用VALUE(查询的结果集的别名)：

2、使用 connect_by、level、instr、substr

1 WITH data AS
2  (SELECT TRIM(substr(txt,
3                      instr(txt, ',', 1, LEVEL) + 1,
4                      instr(txt, ',', 1, LEVEL + 1) -
5                      instr(txt, ',', 1, LEVEL) - 1)) AS token
6     FROM (SELECT ',' || '1,2,3,4' || ',' txt FROM dual)
7   CONNECT BY LEVEL <=
8              length('1,2,3,4') - length(REPLACE('1,2,3,4', ',', '')) + 1)
9 SELECT * FROM data;

3、将上述的with语句分装到视图中：

视图：

 1 create or replace view IN_LIST
 2   as
 3   SELECT TRIM(substr(txt,
 4                      instr(txt, ',', 1, LEVEL) + 1,
 5                      instr(txt, ',', 1, LEVEL + 1) -
 6                      instr(txt, ',', 1, LEVEL) - 1)) AS token
 7     FROM (SELECT ',' || sys_context('my_ctx', 'txt') || ',' txt FROM dual)
 8   CONNECT BY LEVEL <=
 9              length(sys_context('my_ctx', 'txt')) -
10              length(REPLACE(sys_context('my_ctx', 'txt'), ',', '')) + 1

需要使用的过程设置上下文参数：

1 CREATE OR REPLACE PROCEDURE my_ctx_procedure(p_str IN VARCHAR2) AS
2 BEGIN
3   dbms_session.set_context('my_ctx', 'txt', p_str);
4 END;

在进行查询数据的时候，需要首先调用过程进行赋值，才能查询出正确的参数!

1 exec my_ctx_procedure( :txt )

查询

1 SELECT * FROM all_users WHERE username IN (SELECT * FROM in_list);

4、可以借助管道表函数

具体可参见STR包中使用的split_pipe函数，它借助了split_array来实现了该功能，

SELECT * FROM TABLE(str.split_pipe('a,b,c,d', ','));

此处需要注意，使用管道表函数查询出的列的名字为STRING_VALUE

而使用CAST TABLE查询出的的列的名字为COLUMN_VALUE

5、使用正则表达式（regexp_count是Oracle11G分装的函数）

1 SELECT regexp_substr('a;b;c;d', '[^;]+', 1, LEVEL)
2   FROM dual
3 CONNECT BY LEVEL <= regexp_count('a;b;c;d', '[^;]+');

6、使用动态SQL语句（动态的进行in中参数的拼接）【用于存储过程中】

TOM总结的经典例子：
TOm:
AS a followup to your answer:
---------------------
If you can have AT MOST three items, by all means code:

1 select * from t where state in ( p_state(1), p_state(2), p_state(3) );

If you know the (reasonable) upper bound on the number of elements -- go for it,
just code the explicit binds.
----------
Do you mean defining p_state(1) as IN parameter into cursor and defining the value in the PL/SQL
program
【
create or replace proc1 as
p_state varchar2 types.array;
cursor find_state(p_state(1),p_state(2),p_state(3)) is
select * from table where state in (p_state(1),p_state(2),p_state(3));
】
Is this correct?
Thanks

TOM给出的答案：
I would just code
for x in ( select * from t where state in ( p_state(1), p_state(2), p_state(3) ) )
loop
...
(hate explicit cursors)-非常的讨厌显式的游标
Your syntax is wrong, the "concept" is ok but the syntax is wrong.
概念是正确的，但是语义是错误的！
it would be:
正确的答案是：
【
cursor find_state(a in varchar2, b in varchar2, c in varchar2 ) is
select * from table where state in (a,b,c);
】