Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Oracle Tipps & Tricks: Kako da spojim zapise (rekorde) u jedan string? [How to make a string out of records?]

Monday, 21.09.2009 – Dejan

U 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).”