Bisa dicoba cara sebagai berikut:
create type tabletype as table of varchar2 (2000);
/
CREATE OR REPLACE function fin_list( p_string in varchar2 ) return tabletype
as
l_string long default p_string || ',';
l_data tabletype := tabletype();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end;
/
create table produk (kategori char(1));
insert into produk values('A');
insert into produk values('B');
insert into produk values('C');
insert into produk values('D');
insert into produk values('E');
insert into produk values('F');
insert into produk values('G');
insert into produk values('H');
insert into produk values('I');
insert into produk values('J');
commit;
select * from produk;
K
-
A
B
C
D
E
F
G
H
I
J
Untuk menampilkan data sesuai yang diinginkan, gunakan query sebagai berikut
(gunakan koma untuk memisahkan kategori). Contoh berikut saya menampilkan
kategori A,B, dan C:
select *
from produk
where kategori in
( select *
from THE ( select cast( fin_list('&kategori') as tabletype ) from dual));
Enter value for kategori: A,B,C
old 5: from THE ( select cast( fin_list('&kategori') as tabletype ) from
dual))
new 5: from THE ( select cast( fin_list('A,B,C') as tabletype ) from
dual))
K
-
A
B
C
Pada contoh berikutnya ini saya hanya menampilkan A,B,C,D dan E:
select *
from produk
where kategori in
( select *
from THE ( select cast( fin_list('&kategori') as tabletype ) from dual));
Enter value for kategori: A,B,C,D,E
old 5: from THE ( select cast( fin_list('&kategori') as tabletype ) from
dual))
new 5: from THE ( select cast( fin_list('A,B,C,D,E') as tabletype ) from
dual))
K
-
A
B
C
D
E
Salam,
Indra Armansyah
[EMAIL PROTECTED]
----- Original Message -----
From: "abang sunda" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, September 13, 2006 3:42 PM
Subject: [indo-oracle] [ask] ttg query
> all, mo nanya dikit.
> misalnya ada data dg 4 katagori
> A,B,C,D.
>
> terus mo dibikin query dg parameter nya katagori tsb.
> misalnya
> select * from table where katagori in
> ('&1','&2','&3','&4');
>
> yg jadi pertanyaan, gimana agar query tsb bisa tetep
> jalan meski user hanya isi 1, atau 2 parameter saja.
>
> pakai nvl bisa gak ya?
> where kategori
> in(nvl('&1',kategori),nvl('&2',kategori)
> dst ?
>
> makasih
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> --
> -----------I.N.D.O - O.R.A.C.L.E---------------
> Keluar: [EMAIL PROTECTED]
> Website: http://indo-oracle.blogspot.com
> Mirror: http://indooracle.wordpress.com
> -----------------------------------------------
>
> Bergabung dengan Indonesia Thin Client User Groups,
> Terminal Server, Citrix, New Moon Caneveral, di:
> http://indo-thin.blogspot.com
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.405 / Virus Database: 268.12.3/446 - Release Date: 9/12/2006
>
>
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.blogspot.com
Mirror: http://indooracle.wordpress.com
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.blogspot.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/indo-oracle/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
