Mark -- Thanks for the question regarding "In list question with bind variables.", version
originally submitted on 2-May-2000 13:21 Eastern US time, last updated 12-Apr-2005 21:02Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)
Hi Tom,
I am using Pro*C and I need to send an array of strings
in a "where in :" is this possible?
And if not what should I do?
and we said...
In Oracle7, this is rather difficult. You cannot bind in a SINGLE value and
expect it to be treated as MANY values (think about how hard it would be to find
something in an IN LIST that had a "comma" in it for example).
In Oracle8.0 and up, this is easy with object types and nested tables. Just to
see what that will look like, I'll show that - then I'll show the v7
implementation.
ops$tkyte@8i> create or replace type myTableType
as table of number;
2 /
Type created.
ops$tkyte@8i> create or replace function in_list
( p_string in varchar2 ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 l_string long default p_string || ',';
5 l_n number;
6 begin
7
8 loop
9 exit when l_string is null;
10 l_data.extend;
11 l_n := instr( l_string, ',' );
12 l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
13 l_string := substr( l_string, l_n+1 );
14 end loop;
15 return l_data;
16 end;
17 /
Function created.
So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function
returns a SQL Table type as follows:
ops$tkyte@8i>
ops$tkyte@8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a
3 /
COLUMN_VALUE
------------
1
2
3
5
12
Which means we can do that in a subquery:
ops$tkyte@8i> select *
2 from all_users
3 where user_id in
4 ( select * from THE (
select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a )
5 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 20-APR-99
In v7, we can do something very similar (works in 8.0 and up as well) with plsql
tables. Its a little more work but not too much. It would look like this:
ops$tkyte@8i> create or replace package my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2;
4 pragma restrict_references( in_list,
wnds, rnds, wnps, rnps );
5
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count,
wnds, rnds, wnps, rnps );
8
9 pragma restrict_references( my_pkg,
wnds, rnds, wnps, rnps );
10 end;
11 /
Package created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2
4 is
5 l_start number;
6 l_stop number;
7 begin
8 if ( p_idx = 1 ) then
9 l_start := 1;
10 else
11 l_start := instr( p_string, ',', 1, p_idx-1 )+1;
12 end if;
13
14 l_stop := instr( p_string ||',', ',', 1, p_idx );
15
16 return ltrim( rtrim( substr( p_string, l_start,
l_stop-l_start ) ) );
17 end;
18
19 function get_list_count( p_string in varchar2 )
return number
20 is
21 l_cnt number default 0;
22 begin
23 for i in 1 .. 1000 loop
24 exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0;
25 l_cnt := i+1;
26 end loop;
27
28 return l_cnt;
29 end;
30
31 end;
32 /
Package body created.
So, now I can code a query like:
ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i>
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'
PL/SQL procedure successfully completed.
ops$tkyte@8i>
ops$tkyte@8i> select my_pkg.in_list( :bindVar, rownum )
2 from all_objects
3 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
4 /
MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
---------------------------------------
1
3
44
1000
All we need for that to work is a table with MORE rows then we have IN LIST
items -- all_objects is generally a good candidate. It works by using rownum as
an "index" into the plsql table.
So, now we can simply:
ops$tkyte@8i> select *
2 from all_users
3 where user_id in (select my_pkg.in_list(:bindVar,rownum)
4 from all_objects
5 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
6 )
7 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN 1000 24-JUN-99
[@more@]
originally submitted on 2-May-2000 13:21 Eastern US time, last updated 12-Apr-2005 21:02Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)
Hi Tom,
I am using Pro*C and I need to send an array of strings
in a "where in :" is this possible?
And if not what should I do?
and we said...
In Oracle7, this is rather difficult. You cannot bind in a SINGLE value and
expect it to be treated as MANY values (think about how hard it would be to find
something in an IN LIST that had a "comma" in it for example).
In Oracle8.0 and up, this is easy with object types and nested tables. Just to
see what that will look like, I'll show that - then I'll show the v7
implementation.
ops$tkyte@8i> create or replace type myTableType
as table of number;
2 /
Type created.
ops$tkyte@8i> create or replace function in_list
( p_string in varchar2 ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 l_string long default p_string || ',';
5 l_n number;
6 begin
7
8 loop
9 exit when l_string is null;
10 l_data.extend;
11 l_n := instr( l_string, ',' );
12 l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
13 l_string := substr( l_string, l_n+1 );
14 end loop;
15 return l_data;
16 end;
17 /
Function created.
So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function
returns a SQL Table type as follows:
ops$tkyte@8i>
ops$tkyte@8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a
3 /
COLUMN_VALUE
------------
1
2
3
5
12
Which means we can do that in a subquery:
ops$tkyte@8i> select *
2 from all_users
3 where user_id in
4 ( select * from THE (
select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a )
5 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 20-APR-99
In v7, we can do something very similar (works in 8.0 and up as well) with plsql
tables. Its a little more work but not too much. It would look like this:
ops$tkyte@8i> create or replace package my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2;
4 pragma restrict_references( in_list,
wnds, rnds, wnps, rnps );
5
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count,
wnds, rnds, wnps, rnps );
8
9 pragma restrict_references( my_pkg,
wnds, rnds, wnps, rnps );
10 end;
11 /
Package created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2
4 is
5 l_start number;
6 l_stop number;
7 begin
8 if ( p_idx = 1 ) then
9 l_start := 1;
10 else
11 l_start := instr( p_string, ',', 1, p_idx-1 )+1;
12 end if;
13
14 l_stop := instr( p_string ||',', ',', 1, p_idx );
15
16 return ltrim( rtrim( substr( p_string, l_start,
l_stop-l_start ) ) );
17 end;
18
19 function get_list_count( p_string in varchar2 )
return number
20 is
21 l_cnt number default 0;
22 begin
23 for i in 1 .. 1000 loop
24 exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0;
25 l_cnt := i+1;
26 end loop;
27
28 return l_cnt;
29 end;
30
31 end;
32 /
Package body created.
So, now I can code a query like:
ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i>
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'
PL/SQL procedure successfully completed.
ops$tkyte@8i>
ops$tkyte@8i> select my_pkg.in_list( :bindVar, rownum )
2 from all_objects
3 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
4 /
MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
---------------------------------------
1
3
44
1000
All we need for that to work is a table with MORE rows then we have IN LIST
items -- all_objects is generally a good candidate. It works by using rownum as
an "index" into the plsql table.
So, now we can simply:
ops$tkyte@8i> select *
2 from all_users
3 where user_id in (select my_pkg.in_list(:bindVar,rownum)
4 from all_objects
5 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
6 )
7 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN 1000 24-JUN-99
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94317/viewspace-795879/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/94317/viewspace-795879/