Infotrend online

Ako želimo precizniju kontrolu kod procesiranja upita, možemo deklarirati eksplicitni kursor. Kada upit vraća više od jednog retka, tada obavezno moramo deklarirati eksplicitni kursor. Kursor varijable (REF kursori) su neka vrsta pokazivača na retke - rezultate upita. Koristimo ih kada želimo izvršiti upit u jednom potprogramu, a rezultat procesirati u drugom potprogramu (koji može biti pisan i u drugom jeziku).

Koristeći dinamički SQL, SQL naredbe možemo graditi dinamički za vrijeme izvršavanja programa i tako možemo pisati fleksibilnije aplikacije, kod kojih (aplikacija) puni tekst SQL naredbe može biti nepoznat u vrijeme prevođenja programa. Za dinamički SQL možemo koristiti "stari" paket DBMS_SQL ili "novi" dinamički SQL. U Oracle 11g možemo koristiti nove naredbe DBMS_SQL.TO_CURSOR_NUMBER i DBMS_SQL.TO_REFCURSOR za pretvaranje REF kursora u DBMS_SQL kursor i obrnuto.


1. UVOD
SQL je (visoko) deklarativni programski jezik, koji može vraćati (čitati) ili ažurirati (unositi, mijenjati, brisati) ne samo jedan redak podataka, već čitav skup redaka (skup se može sastojati od nula, jednog ili samo par redaka, ali i od jako velikog broja redaka). Međutim, SQL se u programiranju ne koristi samostalno, već se koristi zajedno sa nekim drugim programskim jezikom, uglavnom proceduralnim. Česti način povezivanja SQL-a sa drugim jezikom je taj da se SQL ugrađuje u drugi jezik, koji tada obično nazivamo jezik-domaćin (host language). Oracle korporacija je krajem 80-tih godina prošlog stoljeća napravila svoj jezik-domaćin PL/SQL (Procedural Language extensions to the Structured Query Language), na temelju programskog jezika ADA 83.

Oracle je tako dobro integrirao proceduralni jezik PL/SQL i jezik SQL, da u praksi često zaboravimo kako, zapravo,  radimo sa dva jezika. No, u stvarnosti su to ipak dva odvojena jezika, što pokazuje i interna realizacija prevoditelja (ili interpretera) u Oracle bazi. Kada baza prevodi PL/SQL programski kod, PL/SQL prevoditelj prevodi one naredbe koje on "razumije" (tj. PL/SQL naredbe), a SQL naredbe šalje SQL prevoditelju.

Međutim, između svakog proceduralnog jezika-domaćina, koji procesira redak po redak, i deklarativnog SQL jezika, koji radi sa skupom redaka, postoji očiti nesklad u radu, pa to vrijedi i za PL/SQL. Taj se nesklad premošćuje pomoću kursora baze podataka (database cursors). Kursor predstavlja neku vrstu logičkog pokazivača na skup redaka koji (skup redaka) baza vraća kao odgovor na postavljeni upit. Proceduralni jezik koristi kursor da bi obrađivao redak po redak odgovora.



2. IMPLICITNI I EKSPLICITNI KURSORI
SUBP sustavi (sustavi za upravljanje bazama podataka) različitih proizvođača mogu se značajno razlikovati ne samo u internoj realizaciji, nego i po svom ponašanju (npr. različit način rada sa transakcijama i zaključavanjem redaka), ali i po dijalektu SQL jezika i po korištenoj terminologiji.

Tako, kursori u Oracle relacijskom sustavu ne moraju značiti isto što i kursori u nekom drugom relacijskom sustavu. Važno je napomenuti da Oracle sustav uvijek radi sa kursorima. No, ti kursori mogu u programskom kodu biti "skriveni" ili mogu biti "vidljivi" - govorimo o implicitnim i eksplicitnim kursorima.

PL/SQL deklarira implicitni kursor onda kada u programskom kodu koristimo DML naredbe (INSERT, UPDATE, DELETE) ili SELECT naredbu koja vraća samo jedan redak:

SELECT stupac1, stupac2
  INTO varijabla1, varijabla2
  FROM tablica …

Kod ovih naredbi nigdje se u kodu ne vidi riječ CURSOR. Međutim, interno Oracle PL/SQL prevoditelj izvodi različite radnje kao što su otvaranje kursora, ugrađivanje varijabli jezika-domaćina (bind variables) u SQL upit, slanje upita SQL prevoditelju, čitanje redaka, zatvaranje kursora.

Postoje različite verzije eksplicitnih kursora. "Najeksplicitnija verzija" prikazana je sljedećim programskim kodom:

DECLARE
  CURSOR art_c IS
    SELECT sifra, naziv
      FROM m_artikli;
  sifra_l m_artikli.sifra%TYPE;
  naziv_l m_artikli.naziv%TYPE;
BEGIN
  OPEN art_c;
  LOOP
    FETCH art_c
     INTO sifra_l, naziv_l;
    EXIT WHEN art_c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
     (sifra_l ||' '|| naziv_l);
  END LOOP;
  CLOSE art_c;
EXCEPTION
  WHEN OTHERS THEN
    IF art_c%ISOPEN THEN
      CLOSE art_c;
    END IF;
    RAISE;
END;
/

Vidimo da se u prikazanom PL/SQL kodu kursor deklarira u deklarativnom dijelu PL/SQL koda (DECLARE), a u izvršnom dijelu (iza BEGIN) se otvara (OPEN), čita (FETCH) i zatvara (CLOSE).

Ako dođe do problema u toku izvršavanja programa, "skače" se na dio za obradu iznimaka (EXCEPTION), gdje se kursor zatvara, ako je bio otvoren.

Napomenimo da deklaracija kursora ne  mora biti u istom dijelu koda u kojem se kursor obrađuje (otvara, čita …), već može biti u nekom drugom (pot)programu, npr. deklaracija kursora može biti u specifikaciji jednog paketa, a kod koji obrađuje kursor može se nalaziti u tijelu drugog paketa. Na taj način omogućeno je da se jedna deklaracija kursora koristi na više mjesta.

U prethodnoj varijanti eksplicitnog kursora morali smo eksplicitno prikazati i npr. otvaranje, čitanje, zatvaranje kursora. Postoji i druga varijanta eksplicitnog kursora, koja je jednostavnija od prethodne:

DECLARE
  CURSOR art_c IS
    SELECT sifra, naziv
      FROM m_artikli;
BEGIN
  FOR redak IN art_c
  LOOP
    DBMS_OUTPUT.PUT_LINE
     (redak.sifra || ' ' ||
      redak.naziv);
  END LOOP;
END;
/

Osim u rijetkim slučajevima, danas je uglavnom preporučljiva ova druga varijanta. Osim što je jednostavnija od prve varijante, druga varijanta ima i bolje performanse, od verzije 10gR2 Oracle baze.

Konačno, postoji i treća varijanta eksplicitnog kursora. Zapravo, u ovoj varijanti niti ne vidimo pojavljivanje ključne riječi CURSOR, ali je to ipak eksplicitni kursor jer (za razliku od naredbe SELECT … INTO…) može čitati više redaka:

BEGIN
  FOR redak IN (
    SELECT sifra, naziv
      FROM m_artikli)
  LOOP
    DBMS_OUTPUT.PUT_LINE
     (redak.sifra || ' ' ||
      redak.naziv);
  END LOOP;
END;
/

Iako je najjednostavnija, ova varijanta ima i jednu manu – deklaracija kursora, tj. SELECT naredba, uvijek je fizički vezana za dio u kojem se vrši procesiranje. To znači da, za razliku od prethodne dvije varijante, ovdje ne možemo imati jednu deklaraciju koju ćemo koristiti na više mjesta. S druge strane, ovakav kod je čitljiviji.

Važno je napomenuti da je jedna od čestih početničkih grešaka korištenje kursora i SQL DML naredbe unutar petlje, iako je dovoljna samo obična SQL DML naredba, kao u sljedećem primjeru:

FOR redak IN (
  SELECT sifra, cijena
    FROM m_artikli
   WHERE cijena < 100)
LOOP
  UPDATE m_artikli
     SET cijena = cijena * 1.1
   WHERE sifra = redak.sifra;
END LOOP;
 
Umjesto toga, naravno, možemo napisati samo:

UPDATE m_artikli
   SET cijena = cijena * 1.1
 WHERE cijena < 100;

Varijanta sa "čistim" SQL-om u ovom je slučaju i jednostavnija od PL/SQL varijante (to nije uvijek tako – ponekad je proceduralni kod kraći i razumljiviji od SQL koda). No, najvažnije je to da je SQL varijanta u pravilu brža, jer Oracle ne mora "šetati" između PL/SQL prevoditelja i SQL prevoditelja (tzv. context switch) u svakoj iteraciji petlje.

3. KURSOR VARIJABLE (REF KURSORI)
Iz prethodnih primjera vidi se da se eksplicitni kursori moraju otvoriti, obrađivati i zatvoriti unutar istog dijela programskog koda, npr. unutar istog potprograma ili unutar istog PL/SQL bloka. Jedino se deklaracija eksplicitnog kursora može nalaziti negdje drugdje.

Za razliku od toga, tzv. kursor varijable mogu se otvoriti u jednom dijelu programskog koda, pa se u istom dijelu mogu čitati, a onda se čitanje može prebaciti na drugi dio programskog koda, npr. drugi potprogram, koji se može nalaziti čak i na drugom računalu.

Kursor varijable su varijable čiji je tip REF kursor (REF kao referenca ili pokazivač), pa se u Oracle literaturi i same varijable često neformalno nazivaju REF kursori. Kursor varijable predstavljaju pokazivač  na "obični" kursor. Budući da je "obični" kursor sam po sebi neka vrsta (logičkog) pokazivača, moglo bi se reći da je kursor varijabla "pokazivač na pokazivač" (naravno, ne u istom smislu kao što je to "pokazivač na pokazivač" u programskim jezicima C i C++).

Kursor varijable, osim što omogućavaju da se obrada kursora vrši u dva ili više dijela programskog koda, omogućavaju i da ista kursor varijabla jedanput pokazuje na jedan kursor, a drugi put na drugi kursor, čime se omogućava fleksibilnije programiranje (još fleksibilnije programiranje moguće je sa tzv. dinamičkim kursor varijablama, koje se prikazuju u 5.točci).

Rad sa kursor varijablama vrlo je sličan radu sa eksplicitnim kursorima koji smo prikazali u prvoj varijanti u 2.točci:

DECLARE
  -- REF kursor tip (jaki)
  TYPE art_rc_type
    IS REF CURSOR
    RETURN m_artikli%ROWTYPE;
  -- kursor varijabla tog tipa
  art_rc art_rc_type;
  -- varijabla ROWTYPE tipa
  art_row m_artikli%ROWTYPE;
BEGIN
  OPEN art_rc FOR
    SELECT * FROM m_artikli;
  LOOP
    FETCH art_rc INTO art_row;
    EXIT WHEN art_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (…);
  END LOOP;
  CLOSE art_rc;
EXCEPTION
  WHEN OTHERS THEN
    IF art_rc%ISOPEN THEN
      CLOSE art_rc;
    END IF;
    RAISE;
END;
/

U prethodnom smo kodu deklarirali tzv. jaki REF kursor tipa, jer smo u deklaraciji REF kursor tipa rekli da kursor varijabla (tog tipa) mora pokazivati na kursor koji mora imati baš određenu strukturu – u ovom slučaju strukturu koja je identična strukturi tablice m_artikli, jer smo u kodu naveli "RETURN m_artikli%ROWTYPE".

Osim jakog REF kursor tipa (i varijabli tog tipa), postoji i slabi REF kursor tip (i varijable tog tipa), kao što pokazuje sljedeći primjer:

DECLARE
  -- REF kursor tip (slabi)
  TYPE art_rc_type
    IS REF CURSOR;
  -- kursor varijabla tog tipa
  art_rc art_rc_type;
  -- varijabla ROWTYPE tipa
  art_row m_artikli%ROWTYPE;
  dob_row m_dobavljaci%ROWTYPE;
BEGIN
  OPEN art_rc FOR
    SELECT * FROM m_artikli;
  …
  CLOSE art_rc;
  OPEN art_rc FOR
    SELECT * FROM m_dobavljaci;
  …
  CLOSE art_rc;
END;
/

U prethodnom primjeru slabi REF kursor tip bio je jednostavno deklariran kao REF CURSOR, bez nastavka RETURN (što označava jaki REF kursor tip). Kursor varijabla slabog tipa mogla se zato koristiti dva puta sa dva sasvim različita kursora - jedan ima strukturu kao tablica m_artikli, a drugi kao tablica m_dobavljaci. Slabi REF kursor tip je, dakle, fleksibilniji nego jaki REF kursor tip. Inače, ne moramo niti definirati vlastiti slabi REF kursor tip, jer postoji predefinirani slabi REF kursor tip SYS_REFCURSOR.

U praksi se REF kursori i kursor varijable najčešće koriste tako da  jedan program (najčešće je to funkcija, ali može biti i procedura sa OUT varijablom) deklarira i otvori kursor varijablu, a drugi program poziva prvi program i, zahvaljujući dobivenoj kursor varijabli, obrađuju kursor (na koji pokazuje kursor varijabla).

Npr., u sljedećem primjeru imamo funkciju koja deklarira, otvara i vraća kursor varijablu, a drugi program poziva tu funkciju i koristi dobivenu kursor varijablu:

CREATE FUNCTION funkcija
  RETURN SYS_REFCURSOR
IS
  -- kursor varijabla
  -- slabog REF kursor tipa
  art_rc SYS_REFCURSOR;
BEGIN
  OPEN art_rc FOR
    SELECT * FROM m_artikli;
  RETURN art_rc;
END;
/

CREATE PROCEDURE procedura IS
  art_rc SYS_REFCURSOR;
  art_row m_artikli%ROWTYPE;
BEGIN
  art_rc := funkcija;
  LOOP
    FETCH art_rc INTO art_row;
    EXIT WHEN art_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (…);
  END LOOP;
  CLOSE art_rc;
END;
/

Važno je primijetiti da prvi program (u ovom slučaju funkcija) ne smije zatvoriti kursor varijablu, jer onda ne bi imao što poslati drugom programu (u ovom slučaju proceduri). S druge strane, "dužnost" je drugog programa, primatelja, da zatvori kursor varijablu.

U praksi su kursor varijable najčešće najbolji način za prenošenje podataka između Oracle baze i klijentskog programa pisanog u Javi (ili nekom sličnom jeziku).

4. DINAMIČKI SQL – PAKET DBMS_SQL
U svim dosadašnjim primjerima programski kod bio je statički, tj. cjelokupan kod bio je formiran već u vrijeme prevođenja programa. Za razliku od toga, dinamički programski kod se kreira za vrijeme izvođenja programa. I statički i dinamički programski kod imaju svoje prednosti i mane. Prednost je dinamičkog koda da omogućava vrlo fleksibilno (generičko) programiranje. Mana je dinamičkog koda da se on provjerava tek u fazi izvođenja, što znači da se u toku izvođenja mogu pojaviti neke greške koje bi se u statičkom kodu otkrile unaprijed, u vrijeme prevođenja.

Dinamički kod (SQL i PL/SQL) u Oracle bazi pojavio se prvi puta u verziji Oracle 7, i to kao paket DBMS_SQL. Treba napomenuti da, iako se paket zove DBMS_SQL, on omogućava ne samo dinamički SQL, nego i dinamički PL/SQL. No, u nastavku prikazujemo primjer korištenja paketa DBMS_SQL za dinamički SQL:

DECLARE 
  stmt_str VARCHAR2(200);
  cur_hdl INT;
  rows_processed INT;
  name VARCHAR2 (10);
  salary INT;
BEGIN
  -- otvaranje kursora
  cur_hdl:=
    dbms_sql.open_cursor;
  -- definiranje SELECT naredbe
  stmt_str :=
    'SELECT ename, sal' ||
    '  FROM emp' ||
    ' WHERE job = :jobname';
  dbms_sql.parse (
    cur_hdl, stmt_str,
    dbms_sql.native);
  dbms_sql.bind_variable (
    cur_hdl, 'jobname',
    'SALESMAN');
  dbms_sql.define_column
    (cur_hdl, 1, name, 200);
  dbms_sql.define_column
    (cur_hdl, 2, salary);
  rows_processed :=
    dbms_sql.execute (cur_hdl);

  LOOP
    IF dbms_sql.fetch_rows
      (cur_hdl) > 0
    THEN
      dbms_sql.column_value
        (cur_hdl, 1, name);
dbms_sql.column_value
       (cur_hdl, 2, salary);

      --

      ELSE
        EXIT;
      END IF;
  END LOOP;
  dbms_sql.close_cursor
    (cur_hdl);
END;
/

Već se na prvi pogled vidi da rad sa paketom DBMS_SQL nije lagan, jer paket DBMS_SQL ne predstavlja "elegantno" proširenje programskog jezika PL/SQL.


5. PRIRODNI DINAMIČKI SQL  I DINAMIČKE KURSOR VARIJABLE
Zbog složenosti rada sa paketom DBMS_SQL, Oracle je u verziji 8i napravio tzv. prirodni dinamički SQL (native dynamic SQL – NDS). Kao i paket DBMS_SQL, tako i prirodni dinamički SQL omogućava ne samo dinamički SQL, već i dinamički PL/SQL.

Prirodni dinamički SQL zapravo čine dvije vrste naredbi. Jedna je naredba EXECUTE IMMEDIATE, a druga naredba je, zapravo, naredba za kreiranje dinamičke kursor varijable. U nastavku slijedi primjer korištenja naredbe EXECUTE IMMEDIATE:

CREATE FUNCTION broj_redaka
  (tablica_p VARCHAR2)
  RETURN NUMBER
IS
  naredba VARCHAR2 (1000);
  broj_redaka NUMBER;
BEGIN
  naredba :=
    'SELECT COUNT (*)' ||
    '  FROM ' || tablica_p;
  EXECUTE IMMEDIATE naredba
    INTO broj_redaka;
  RETURN broj_redaka;
END;
/

Prethodna funkcija prima kao ulazni parametar ime (neke) tablice, te vraća broj redaka te tablice. U ovom je slučaju naredba koja se pozivala sa EXECUTE IMMEDIATE bila vrlo jednostavna SQL naredba, ali to je mogao biti i neki, npr. vrlo dugačak, PL/SQL programski blok.

Druga naredba koja (uz EXECUTE IMMEDIATE) čini prirodni dinamički SQL je dinamička kursor varijable. U nastavku slijedi primjer rada sa dinamičkom kursor varijablom koji je (primjer) ekvivalentan primjeru rada sa DBMS_SQL paketom iz 4.točke:

DECLARE
  cur SYS_REFCURSOR;
  stmt_str VARCHAR2(200);
  name VARCHAR2(20);
  salary NUMBER;
BEGIN
  stmt_str :=
    'SELECT ename, sal' ||
    '  FROM emp' ||
    ' WHERE job = :1';
  OPEN cur FOR stmt_str
    USING 'SALESMAN';
  LOOP
    FETCH cur
      INTO name, salary;
    EXIT WHEN cur%NOTFOUND;
    --
  END LOOP;
  CLOSE cur;
END;
/

Vidimo da je rad sa dinamičkom kursor varijablom skoro istovjetan radu sa statičkom kursor varijablom (3.točka) – jedina razlika je u tome što je ovdje SELECT naredba kreirana dinamički.

Također, vidimo koliko je ovaj primjer jednostavniji od primjera rada sa paketom DBMS_SQL (4.točka). No, paket DBMS_SQL ima i nekih svojih prednosti, pa je zato i "preživio", a u bazi 11g dobio je i neke značajne nove mogućnosti.

Naime, paket DBMS_SQL omogućava tzv. metodu 4 kod rada sa dinamičkim SQL-om. Četiri metode rada sa dinamičkim SQL-om su sljedeće:
- Metoda 1 dozvoljava samo DML naredbe i to bez "bind" varijabli
- Metoda 2 dozvoljava i DML naredbe sa "bind" varijablama
- Metoda 3 dozvoljava i upite,  tj. SELECT naredbu, koja mora imati fiksan broj stupaca i fiksan broj "bind" varijabli
- Metoda 4 dozvoljava da broj stupaca i broj "bind" varijabli u SELECT naredbi bude nepoznat sve do trenutka izvođenja naredbe

Za razliku od paketa DBMS_SQL, prirodni dinamički SQL podržava metodu 4 samo ako koristimo naredbu EXECUTE IMMEDIATE (i to sa dinamičkim PL/SQL blokom, a ne sa dinamičkim SQL-om), dok dinamičke kursor varijable ne podržavaju metodu 4. Međutim, u verziji 11g, Oracle je uveo nove naredbe DBMS_SQL.TO_CURSOR_NUMBER i DBMS_SQL.TO_REFCURSOR, čime je omogućeno da se REF kursor pretvori u DBMS_SQL kursor i obrnuto, pa se onda mogu koristiti mogućnosti paketa DBMS_SQL, a onda (po potrebi) DBMS_SQL kursor ponovno pretvoriti u REF kursor.  Primjer toga dat je u sljedećoj točki.

6. DINAMIČKI REF KURSORI U BAZI 11g
Pretpostavimo da imamo funkciju koja kao povratnu vrijednost (RETURN) vraća  kursor varijablu, ali je kursor varijabla dobivena na temelju dinamički generiranog upita. Željeli bismo ju koristiti, ali kako napraviti "FETCH ref_cur INTO record_variable" kad ne znamo kakva je struktura record varijable?

Problem nije lako rješiv, jer kursor varijabla ne omogućava opis svoje strukture, barem ne na direktan način. Do baze 11g jedino rješenje bilo je da funkcija (ili paket u kojem se ona nalazi) vraća ne samo kursor varijablu, već i upit koji je funkcija (dinamički) generirala. Također, mora se koristiti DBMS_SQL paket i njegove procedure PARSE i DESCRIBE_COLUMNS da bismo mogli napraviti nepoznatu record varijablu.

I u bazi 11g moramo koristiti paket DBMS_SQL, ali funkcija,  koja je dinamički generirala upit, više ne mora vratiti taj upit, već samo kursor varijablu. To se postiže zahvaljujući novim naredbama DBMS_SQL.TO_CURSOR_NUMBER i DBMS_SQL.TO_REFCURSOR, pomoću kojih je moguće REF kursor pretvoriti u DBMS_SQL kursor i obrnuto.

U nastavku prikazujemo samo glavni dio programskog koda, tj. paket dyn_fetch.
Cjelokupni programski kod može se naći u tekstu pod nazivom " Dynamic Ref Cursor with Dynamic Fetch - An 11g Version " koji se nalazi na web stranici firme Quest pod Categories: PL/SQL 2007.

U specifikaciji paketa dyn_fetch deklarirane su globalne varijable g_count i g_record_def, koje služe za prenošenje podataka o broju stupaca i strukturi redaka dinamički kreiranog upita, te signatura procedura describe_columns i fetch_ref_cursor:

CREATE PACKAGE dyn_fetch IS
  g_count      NUMBER;
  g_record_def VARCHAR2(32000);

  PROCEDURE describe_columns
    (p_ref_cur IN OUT
       SYS_REFCURSOR);

   PROCEDURE fetch_ref_cur (
      p_ref_cur SYS_REFCURSOR,
      p_process_def VARCHAR2);
END;
/

U tijelu paketa nalaze se pomoćne konstante, koje označavaju Oracle tipove podataka (npr. tip VARCHAR2 interno ima oznaku 1):

CREATE BODY dyn_fetch IS
  varchar2_type CONSTANT
    PLS_INTEGER := 1;
  number_type CONSTANT
    PLS_INTEGER := 2;
  date_type CONSTANT
    PLS_INTEGER := 12;
  rowid_type CONSTANT
    PLS_INTEGER := 11;
  char_type CONSTANT
    PLS_INTEGER := 96;
  long_type CONSTANT
    PLS_INTEGER := 8;
  raw_type CONSTANT
    PLS_INTEGER := 23;
  mlslabel_type CONSTANT
    PLS_INTEGER := 106;
  clob_type CONSTANT
    PLS_INTEGER := 112;
  blob_type CONSTANT
    PLS_INTEGER := 113;
  bfile_type CONSTANT
    PLS_INTEGER := 114;

Procedura describe_columns dobiva kao ulazni i izlazni parametar (IN OUT) kursor varijablu slabog tipa. Ta se kursor varijabla prvo pretvara u DBMS_SQL kursor, pomoću nove naredbe u 11g DBMS_SQL.TO_CURSOR_NUMBER.

DBMS_SQL kursor se zatim opisuje pomoću naredbe DBMS_SQL.DESCRIBE_COLUMNS3,  tj. nalazi se broj stupaca i struktura redaka dinamički kreiranog upita. Nakon toga se DBMS_SQL kursor ponovno pretvara u kursor varijablu, pomoću nove naredbe u 11g DBMS_SQL.TO_REFCURSOR. Na kraju se u FOR petlji struktura kursor varijable pamti u globalnu varijablu g_record_def:

PROCEDURE describe_columns
  (p_ref_cur IN OUT
     SYS_REFCURSOR)
IS
  l_cur INTEGER;
  l_desc_tab DBMS_SQL.DESC_TAB;
  l_type VARCHAR2 (100);
  l_col_type PLS_INTEGER;
  l_col_max_len   PLS_INTEGER;
  l_col_precision PLS_INTEGER;
  l_col_scale     PLS_INTEGER;
BEGIN
-- novo u 11g
l_cur :=
  DBMS_SQL.TO_CURSOR_NUMBER
    (p_ref_cur);
DBMS_SQL.DESCRIBE_COLUMNS3
  (l_cur, g_count, l_desc_tab);
-- novo u 11g
p_ref_cur :=
  DBMS_SQL.TO_REFCURSOR
    (l_cur);
g_record_def := NULL;
FOR i IN 1..g_count LOOP
  l_col_type :=
    l_desc_tab(i).col_type;
  l_col_max_len :=
    l_desc_tab(i).col_max_len;
  l_col_precision :=
    l_desc_tab(i).col_precision;
  l_col_scale :=
    l_desc_tab(i).col_scale;

  IF l_col_type=varchar2_type
  THEN
    l_type :=
      'VARCHAR2(' ||
      l_col_max_len || ')';
  ELSIF l_col_type=number_type
  THEN
    l_type :=
      'NUMBER(' ||
      l_col_precision || ',' ||
      l_col_scale || ')';
  ELSIF l_col_type = date_type
  THEN
    l_type := 'DATE';
  ...
  END IF;

  g_record_def :=
    g_record_def || ' col_' ||
    i || ' ' || l_type || ',';
END LOOP;

g_record_def :=
  RTRIM (g_record_def, ',');
END; -- describe_columns

Procedura fetch_ref_cur dobiva kao ulazne parametre kursor varijablu i definiciju (nekog) procesa, koja (definicija procesa) dinamički opisuje što se radi sa podacima dobivenim kroz kursor varijablu. U ovoj proceduri koristi se relativno kratak, ali prilično složen dinamički PL/SQL blok, u kojem se, između ostalog, dinamički deklarira tip podataka record_t, na temelju strukture definirane u globalnoj varijabli g_record_def, koju je (globalnu varijablu) napunila prethodna procedura describe_columns. Taj dinamički kreiran PL/SQL blok, zapravo, predstavlja korištenje metode 4 za dinamički SQL (zapravo, u ovom slučaju je to dinamički PL/SQL – kako je prije rečeno, prirodni dinamički SQL ne podržava metodu 4):

PROCEDURE fetch_ref_cur (
  p_ref_cur     SYS_REFCURSOR,
  p_process_def VARCHAR2)
IS
  l_statement VARCHAR2 (32000);
BEGIN
  l_statement :=
  'DECLARE
     TYPE record_t IS RECORD (' ||
       dyn_fetch.g_record_def ||
     ');
     l_record  record_t;
   BEGIN
     LOOP
       FETCH :p_ref_cur
         INTO l_record;
       EXIT WHEN
        :p_ref_cur%NOTFOUND;'||
       p_process_def || '
     END LOOP;
     CLOSE :p_ref_cur;
   END;
  ';

  EXECUTE IMMEDIATE l_statement
    USING p_ref_cur;
END; -- fetch_ref_cur

END; -- dyn_fetch
/

7. ZAKLJUČAK
SUBP sustavi različitih proizvođača mogu se značajno razlikovati po dijalektu SQL jezika i po korištenoj terminologiji. Zbog toga Oracle kursor ne mora biti isto što i kursor u nekom drugom sustavu. No, općenito vrijedi da je kursor neka vrsta logičkog pokazivača na skup redaka koji baza vraća kao odgovor na postavljeni upit. Proceduralni jezik koristi kursor da bi obrađivao redak po redak odgovora.

U Oracle bazi "sve je kursor", tj. i DML naredbe, i SELECT naredba koja vraća samo jedan redak - sve su to kursori, ali implicitni.

Eksplicitni kursori mogu biti statički ili dinamički. Isto tako mogu biti statičke ili dinamičke kursor varijable (REF kursori), koje predstavljaju pokazivače na kursore.

Posebna vrsta kursora, koja je uvijek samo dinamička, je DBMS_SQL kursor. Iako je DBMS_SQL stari paket (još iz baze 7), u bazi 11g dobio je nove mogućnosti, a nove  naredbe DBMS_SQL.TO_CURSOR_NUMBER i DBMS_SQL.TO_REFCURSOR omogućavaju da se (indirektno) i sa dinamičkim kursor varijablama koristi metoda 4 iz dinamičkog SQL-a.

..................................
Literatura:
1. Date, C.J. (2004): An introduction to Database Systems (8.izdanje), Addison-Wesley
2. Date, C.J. (2005): Database in Depth, O'Reilly
3. Date, C.J. (2006): Date on Databases – Writings 2000-2006, Apress
4. Date, C.J. (2007): Logic and Databases – The Roots of Relational Theory, Trafford Publishing
5. De Haan, L. i Koppelaars T. (2007): Applied Mathematics for Database Professionals, Apress
6. Feuerstein S. i Pribyl B. (2005): Oracle PL/SQL Programming (4.izdanje), O'Reilly
7. Kyte, T. (2003): Effective Oracle by Design, McGraw-Hill/Osborne
8. Kyte, T. (2005): Expert Oracle Database Architecture, Apress
9. Radovan, M. (1993): Baza podataka: Relacijski pristup i SQL, Informator
10. Oracle priručnik 11g Release 2 (2007): Advanced Application Developer's Guide
11. Oracle priručnik 11g Release 2 (2007): Concepts
12. Oracle priručnik 11g Release 2 (2007): PL/SQL Language Reference
13. Oracle priručnik 11g Release 2 (2007): PL/SQL Packages and Types Reference
14. Oracle priručnik 11g Release 2 (2007): SQL Language Reference

.................................
O autoru:
Zlatko Sirotić, dipl.ing.
Istra informatički inženjering d.o.o., Pula
e-mail: zlatko.sirotic@iii.hr

Autor radi oko 25 godina na informatičkim poslovima. Najveći dio radnog vijeka proveo je u poduzeću Istra informatički inženjering d.o.o, Pula, gdje radi i sada. Radio je na svim poslovima u izradi softvera: analiza, dizajn, programiranje, uvođenje, obuka korisnika, održavanje. Sa Oracle proizvodima (baza, Designer, Forms, Reports, JDeveloper) radi zadnjih 12 godina.

Objavljivao je stručne radove na kongresima "Hotelska kuća", CASE savjetovanjima, HrOUG konferencijama (Hrvatska udruga Oracle korisnika), časopisima "InfoTrend" i "Ugostiteljstvo i turizam", a neka njegova programska rješanja objavljena su na web stranicama firme Quest i web stranicama firme Oracle.