DROP TABLE t1 purge;
CREATE TABLE t1 (item VARCHAR2(10),JAN NUMBER,FEB NUMBER, MAR NUMBER, APR
NUMBER,
MAY NUMBER, JUN NUMBER, JUL NUMBER, AUG NUMBER,
SEP NUMBER, OCT NUMBER, NOV NUMBER,DES NUMBER);
INSERT INTO t1 VALUES ('Mangga',1,4,8, 3, 2, 9, 4, 8, 3, 8, 1,9);
INSERT INTO t1 VALUES ('Jeruk',3,7,0, 6, 2, 9, 2, 7, 1, 8 ,4,2);
CREATE OR REPLACE FUNCTION get_t1_name (
namatab varchar2, posisi number ) RETURN SYS_REFCURSOR AS
CURSOR c1 IS
SELECT column_name
FROM user_tab_columns
where TABLE_NAME=UPPER(namatab) AND column_id =posisi;
nama_kol VARCHAR2(10);
pref SYS_REFCURSOR ;
BEGIN
OPEN c1;
FETCH c1 INTO nama_kol;
CLOSE c1;
OPEN pref FOR 'SELECT ' || nama_kol || ' FROM ' || namatab ||' WHERE
item='||'''Mangga''';
RETURN pref;
END;
/
SQL> Select SEP from t1 where item='Mangga';
SEP
----------
3
SQL> Select JAN from t1 where item='Mangga';
JAN
----------
1
SQL> Select get_t1_name('t1',1+9) from dual;
GET_T1_NAME('T1',1+9
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
SEP
----------
3
SQL> Select get_t1_name('t1',1+1) from dual;
GET_T1_NAME('T1',1+1
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
JAN
----------
1
===atau
SQL> CREATE OR REPLACE PROCEDURE get_t2_name (
2 namatab varchar2, posisi number, pref OUT SYS_REFCURSOR) AS
3
4 CURSOR c1 IS
5 SELECT column_name
6 FROM user_tab_columns
7 where TABLE_NAME=UPPER(namatab) AND column_id =posisi;
8
9 nama_kol VARCHAR2(10);
10
11 BEGIN
12 OPEN c1;
13 FETCH c1 INTO nama_kol;
14 CLOSE c1;
15 OPEN pref FOR 'SELECT ' || nama_kol || ' FROM ' || namatab ||' WHERE
item='||'''Mangga''';
16 END;
17 /
Procedure created.
SQL> VAR recku REFCURSOR
SQL> EXEC get_t2_name ( 't1', 1+9, :recku);
PL/SQL procedure successfully completed.
SQL> PRINT recku
SEP
----------
3
SQL> EXEC get_t2_name ( 't1', 1+1, :recku);
PL/SQL procedure successfully completed.
SQL> PRINT recku
JAN
----------
1
ferry sends.
----- Original Message -----
From: "Djeni Djelani" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, September 29, 2005 1:58 PM
Subject: [indo-oracle] HELP Sql
> Halo all
> Saya punya table seperti ini
>
> Item JAN FEB MAR APR MAY JUN JUL AUG
> SEP OCT NOV DES
>
> Mangga 1 4 8 3 2 9 4 8 3 8 1
> 9
> Jeruk 3 7 0 6 2 9 2 7 1 8 4
> 2
>
> Jika ingin mengambil Jumlah Item Mangga di bulan SEP syntax nya :
>
> Select SEP from Table_x where item="Mangga'
>
> Utk Bulan Jan := Select JAN from Table_x where item='Mangga';
>
> Permasalahannya :
> Saya Ingin mengambil data Item "Mangga" dengan Kolom bulan sebagai
> parameternya, bagaimana caranya ya?
>
> Ada yang bisa memberi masukan tidak?
>
> Thx....
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/PhFolB/TM
--------------------------------------------------------------------~->
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.lizt.org (NEW)
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> 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/
