Oracle Uygulamaları: PL/SQL de satırdan sutuna , sutundan satıra çevirme, crosstab , row to column , column to row

SELECT u.user_name

FROM fnd_user u

WHERE u.creation_date > SYSDATE – 120

Şeklinde yazılan sorguda sonuç

Ahmet

Mehmet

Veli

…

Olsun. Amacımız oluşan bu listeyi tek satirda araya bizim belirleyeceğimiz bir ayraç ile birleştirerek

Ahmet,Mehmet,Veli, ….

gibi getirmek ise şu yöntemi uygulayabiliriz…

Öncelikle liste için bir tip belirleriz…

CREATE TYPE TListe IS TABLE OF VARCHAR2 (4000);

Bir de bu listeyi belirleyeceğimiz ayraç ile birleştiren fonksiyonu oluştururuz…

CREATE FUNCTION apps.birlestir (p_liste IN tliste, p_separator IN VARCHAR2)

RETURN VARCHAR2 IS

ret VARCHAR2 (4000);

BEGIN

FOR j IN 1 .. p_liste.LAST LOOP

IF j = 1 THEN

ret := p_liste (j);

ELSE

ret := ret || p_separator || p_liste (j);

END IF;

END LOOP;

RETURN ret;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END;

Şimdi sorgumuza geçebiliriz…

SELECT apps.birlestir (CAST (MULTISET (SELECT u.user_name

FROM fnd_user u

WHERE u.creation_date > SYSDATE - 120) AS apps.tliste),

',')

isimler

FROM DUAL;

Sonuç :

isimler

Ahmet,Mehmet,Veli,…

Olur…

Satirdan sutuna geçirmenin diger bir yöntemi de

SELECT TRUNC (f.creation_date) tarih, RTRIM (XMLAGG (XMLELEMENT (e, f.user_name || '-' || f.user_id || ',')).EXTRACT ('//text()'), ',') isimler

FROM fnd_user f

GROUP BY TRUNC (creation_date);

Tarih isimler

01.01.2011 Ahmet-1,Mehmet-2

01.02.2011 Veli-3, Mustafa-4

Gibi sonuclar olacaktir…

Ayraç ile birleştirilmiş bir alandan istenen parçaların getirilmesinin sağlanmasi

Örneğin;

Ne 30/1 - 12630037%76|100/36 den - 101721%24

Yukaridaki alanda ayraç “|” olarak belirlenmiş..

Biz bu ayraca gore

Ne 30/1 - 12630037%76

100/36 den - 101721%24

Değerlerinin alınmasını istersek su fonksiyonu yazabiliriz…

CREATE OR REPLACE FUNCTION APPS.explode (

p_seperator IN VARCHAR2,

p_string IN VARCHAR2,

p_count NUMBER

)

RETURN VARCHAR2

l_string LONG DEFAULT p_string || p_seperator;

TYPE listtable is TABLE OF VARCHAR2 (255);

l_data listtable := listtable ();

n NUMBER;

RESULT VARCHAR2 (255);

BEGIN

LOOP

EXIT WHEN l_string IS NULL;

n := INSTR (l_string, p_seperator);

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;

RESULT := l_data (p_count);

RETURN RESULT;

EXCEPTION

WHEN OTHERS

THEN

RETURN RESULT;

END;

FROM DUAL;

Çıktı :

Orj ilk ikinci

Ne 30/1 - 12630037%76|100/36 den - 101721%24 Ne 30/1 - 12630037%76 100/36 den - 101721%24

Bu örneği bir üst level a alirsak…

Ayraç ile ayırmanın en fazla 8 adet olabileceğini bilerek eklenen herbir değeri satir satir göstermek isteyelim…

FROM dual k

Bu sorgu ile tek satıra düşüyoruz…

SELECT ROWNUM,CASE

WHEN ROWNUM = 1 THEN ilk

WHEN ROWNUM = 2 THEN iki

WHEN ROWNUM = 3 THEN uc

WHEN ROWNUM = 4 THEN dort

WHEN ROWNUM = 5 THEN bes

WHEN ROWNUM = 6 THEN alti

WHEN ROWNUM = 7 THEN yedi

WHEN ROWNUM = 8 THEN sekiz

END bilgi

FROM dual k)

CONNECT BY LEVEL <= 8

Çıktı:

1 TEKSTURE - 3R28884%7,31

2 TEKSTURE - 010015504KREM01%25,27

3 TEKSTURE - 010007255BEJ01%4,65

4 TEKSTURE - 3R28978%16,46

5 TEKSTURE - 3R28978%4,3

6 TEKSTURE - 3R2

Şeklinde olur…

Procedure ise

CREATE FUNCTION APPS.explode (

p_seperator IN VARCHAR2,

p_string IN VARCHAR2,

p_count NUMBER

)

RETURN VARCHAR2

l_string LONG DEFAULT p_string || p_seperator;

TYPE listtable is TABLE OF VARCHAR2 (255);

l_data listtable := listtable ();

n NUMBER;

RESULT VARCHAR2 (255);

BEGIN

LOOP

EXIT WHEN l_string IS NULL;

n := INSTR (l_string, p_seperator);

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;

RESULT := l_data (p_count);

RETURN RESULT;

EXCEPTION

WHEN OTHERS

THEN

RETURN RESULT;

END;

Random veriye ihtiyaç duyulursa

SELECT rownum,TRUNC (DBMS_RANDOM.VALUE (1, 100)) a,

DBMS_RANDOM.STRING ('U', 20) b, DBMS_RANDOM.STRING ('L', 20) c,

DBMS_RANDOM.STRING ('A', 20) d, DBMS_RANDOM.STRING ('P', 20) e,

DBMS_RANDOM.VALUE (1, 100) f, DBMS_RANDOM.VALUE g,

DBMS_RANDOM.random h

FROM DUAL

CONNECT BY LEVEL <= 7

U - Upper case

L - Lower case

A - Alphanumeric

X - Alphanumeric with upper case alphabets.

P - Printable characters only.

--------

SELECT DISTINCT k.ad, wm_concat (DISTINCT k.soyad) soyadlar, COUNT (DISTINCT k.soyad)
FROM sevk.kullanici k
GROUP BY k.ad
ORDER BY COUNT (DISTINCT k.soyad) DESC

wm_concat kullanarakta yukarıdaki gibi bir sorgu çalıştırılabilir...

Oracle Uygulamaları

Sayfalar

29 Haziran 2011 Çarşamba

PL/SQL de satırdan sutuna , sutundan satıra çevirme, crosstab , row to column , column to row

Hiç yorum yok: