Oracle Tipps & Tricks: Kako da spojim zapise (rekorde) u jedan string? [How to make a string out of records?]
September 21, 2009 – DejanU ovom članku ću napisati rješenje za problem - kako spojiti više zapisa u jedan string.
Kao primjer ću uzeti jedan “real world case”, kojeg i ja lično imam u svakodnevnoj praksi.
Naime, imam tabelu, koja sadrži popis aplikacija i podatke o osobama, koje rade na toj aplikaciji. Kada nešto mijenjam u bazi, moram putem E-Maila obavijestiti sve osobe zadužene za određenu aplikaciju. Naravno da bih mogao napisati jednu petlju i pomoću UTL_MAIL slati pojedinačno E-Mail svakome, ali sam za ovu, a i druge svrhe, odlučio napisati funkciju, koja mi vraća selektovane zapise (u ovom slučaju ime, prezime i E-Mail adresu) spojene u jedan string.
Potrebno je najprije kreirati uskladištenu proceduru ili funkciju, koja prima dva parametra (REF CURSOR i delimiter), a kao rezultat vraća “zaljepljeni” string, tj. sve selektovane zapise spojene u jedan string.
CREATE OR REPLACE FUNCTION sfJoinRecords
(
pCursor IN sys_refcursor,
pDelimiter IN VARCHAR2 := ','
) RETURN VARCHAR2
IS
lvTempValue VARCHAR2(32767);
lvReturnValue VARCHAR2(32767);
BEGIN
LOOP
FETCH pCursor INTO lvTempValue;
EXIT WHEN pCursor%NOTFOUND;
IF lvReturnValue IS NOT NULL THEN
lvReturnValue := lvReturnValue || pDelimiter;
END IF;
lvReturnValue := lvReturnValue || lvTempValue;
END LOOP;
CLOSE pCursor;
RETURN lvReturnValue;
END sfJoinRecords;
/
Korištenje ove funkcije izgleda ovako:
SELECT ap.first_name,
ap.last_name,
ap.email_address
FROM tb_apps_persons ap
WHERE ap.app_id = 8
AND ap.email_address IS NOT NULL;
FIRST_NAME LAST_NAME EMAIL_ADDRESS ---------- ---------- ----------------------------------- Pero Peric pero.peric@bazepodataka.net Marko Markovic marko.markovic@bazepodataka.net Laza Lazic laza.lazic@bazepodataka.net
SELECT DISTINCT
ap.first_name ||' '||
ap.last_name ||' <' ||
ap.email_address ||'>'
as persons
FROM tb_apps_persons ap
WHERE ap.app_id = 8
AND ap.email_address IS NOT NULL;
PERSONS -------------------------------------------------- Laza Lazic <laza.lazic@bazepodataka.net> Pero Peric <pero.peric@bazepodataka.net> Marko Markovic <marko.markovic@bazepodataka.net>
SELECT sfJoinRecords(
CURSOR(
SELECT DISTINCT
ap.first_name ||' '||
ap.last_name ||' <' ||
ap.email_address ||'>'
FROM tb_apps_persons ap
WHERE ap.app_id = 8
AND ap.email_address IS NOT NULL),
',') as persons
FROM dual;
PERSONS ------------------------------------------------------------------------------------------------------------------- Laza Lazic <laza.lazic@bazepodataka.net>,Pero Peric <pero.peric@bazepodataka.net>,Marko Markovic <marko.markovic@bazepodataka.net>
Naravno da se ova funkcija može koristiti za bezbroj drugih stvari, pa ako ju budete koristili u praksi, napišite u komentaru za šta ju koristite, kako bi i drugi dobili ideju ili iskoristili tu metodu.
Još jedna veoma bitna napomena: ukoliko funkciju koristite u kompleksnijim DML upitima u sklopu nekog paketa, onda ćete vjerovatno morati u specifikaciju paketa (package header) dodati ovo ograničenje:
PRAGMA RESTRICT_REFERENCES(sfJoinRecords, WNDS);
Objašnjenje za WNDS: “Asserts that the subprogram writes no database state (does not modify database tables).”
