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

REF CURSOR IN and OUT

Tuesday, 10.11.2009 – Noctua4u

Obećao jesam nastavak o tipovima, ali obzirom da sam zatrpan nekim poslovima, ne stižem da dovršim započeto. Lepa stvar je što sam u tim poslovima naučio nešto novo, naravno na ličnim greškama i PIG metodom (metoda Probe I Greške), što se “nadovezuje” na priču o tipovima.

Naime, nedavno sam dobio zadatak da prebacim podatke sa jedne Oracle DB na drugu Oracle DB. Obzirom da se serveri fizički “ne vide”, tj. nisu na istoj mreži, to je nametalo neka rešenja. Takođe, neki od “projektnih zadataka” su takođe uticali na izbor načina prenosa i obrade tih podataka.

Najlogičniji pristup (sa aspekta DBA) koji odgovara svim uslovima je bio:

1. Na SOURCE bazi spakuj podatke u neki RECORDSET koji će preko jedne stored procedure da bude dostupan klijentu
2. Klijent preuzima podatke i samo ih prosleđuje ih drugoj bazi
3. Na TARGET bazi bazi prihvati podatke, isti RECORDSET, isto stored procedurom i obradi ih (i na kraju upiši u odgovarajuće tabele)

Krajnje jednostavno, ne?
E, ne! Sa stane developera nemoguć posao!
Da kratim priču, zbog ograničenja “klijentske arhitekture” susrećemo se sa REF CURSOR-ima.

Šta je REF CURSOR?

Treba praviti razliku između kursora (CURSOR) i kursorske varijable ili kursorske promeljive (REF CURSOR). Kursorska varijabla ukazuje na trenutni red (ROW) u skupu podataka koji se dobija izvršenjem nekog upita. Kursorska varijabla {u daljem textu CVR} je fleksibilnija jer nije vezana za određeni upit (kao CURSOR), već se može koristiti više puta za različite upite.
CVR se može tretirati kao pointer.

Zašto koristiti REF CURSOR?

Logično pitanje koje se nameće iz definicije CVR je: zašto bih koristio REF CURSOR kada mogu da koristim CURSOR ili neki strogo definisani skup podataka ili neki moj tip?
Odgovori su mahom vezani za koncepciju manipulaciju i strukturu podataka:

• Rad sa različitim upitima u različito vreme korišćenjem samo jednog CVR-a. Drugim rečima, jedna CVR se može koristiti za “sakupljanje” podataka iz različitih upita.
• Kada želimo da izvršimo upit u jednom programskom modulu (procedura, funkcija) i procesuriramo rezultate u drugi podprogram (najčešće pisan u drugom jeziku!) na klijentu. Vidi sliku.
  
• Ukoliko postoji PL/SQL engine na strani klijenta nema restrikcija u komunikaciji između klijenta i servera. Npr. možete deklarisati CVR na strani klijenta, otvoriti je i popuniti podacima na strani servera, vratiti klijentu, nastaviti “popunjavanje” na strani klijenta, vratiti serveru da je zatvori itd. Moguće su sve kombinacije.

Posebno je potrebno obratiti pažnju na deo “pisan u drugom jeziku”, videćemo posle i zašto.

Deklaracija REF CURSOR tipa i kursorske varijable (promenljive):

DECLARE
TYPE RFC_Slab IS REF CURSOR; -- Deklarisanje TIPA kursora (slab tip)
TYPE RFC_Jak IS REF CURSOR RETURN odeljenja%ROWTYPE; -- Deklarisanje TIPA kursora (jak tip)
in_cursor RFC_Slab; -- Deklaracija CVR
out_cursor RFC_Jak; -- Deklaracija CVR
BEGIN
OPEN in_cursor FOR SELECT * FROM gradovi;
OPEN out_cursor FOR SELECT * FROM odeljenja;
END;

JAK TIP REF CURSORA ima u deklaraciji RETURN neka_tabela%ROWTYPE što označava kako mora da izgleda u trenutku deklaracije. To praktično znači da bilo koji SQL upit, koji koristi CVR, mora da vrati strukturu (kolone) kao što je ima neka_tabela. O prednosti jakog tipa nad slabim nećemo ovom prilikom, ali jedna od njih je i ta što komplajler može da odredi da li je developer ispravno “upario” podatke iz CVR-a.

SLAB TIP onaj koji nije vezan za neku strukturu podataka i može se koristiti sa bilo kojim upitom.

Ograničenja REF CURSORA (CVR):

Veoma bitno je znati šta NE možete raditi:

1. CVR se ne može deklarisati u paketu (nema stvarnu vrednost već je samo “pointer” na nešto što će se kasnije koristiti). Deklariše se samo TIP podataka kao REF CURSOR!
2. Ne može se prosleđivati preko Remote Procedure Calls (zaboravite DB link) sa jednog na drugi server
3. Ukoliko se prosleđuje kao “bind” ili host varijabla u PL/SQL, ne može se uraditi FETCH na serveru ukoliko CVR nije otvorena u istom pozivu (Praktično: da bi stored procedura mogla da koristi podatke koje joj šaljete u REF CURSORu, morate u okviru poziva i da ga otvorite!)
4. Ne može se testirati jednakost, nejednakost ili da li je NULL pomoću operatora komparacije.
5. Ne može se dodeliti NULL vrednost CVR
6. Kolone ne mogu biti tipa RFC (u CREATE TABLE/VIEW ne možete staviti tip REF CURSOR. Takođe, ništa VARRAY i ostali tipovi)
7. Ne mogu se koristiti u dinamički generisanom SQL-u

Evo još nekih stvari koje su bitne za REF CURSOR a koje NE možete:

1. Iz klijentskih jezika ne možete prihvatiti nikakve podatke ukoliko nisu u REF CURSORu (zapravo, ponekad i možete, ali to zahteva mnogo više akcija nego kod REF CURSOR-a)
2. Ne možete da “prosledite” REF CURSOR natrag u bazu!!! (php, java, perl provereno, a za .Net i ostale samo pročitao u dokumentaciji)

Upotreba REF CURSOR-a kao IN/OUT parametra

Znači, ukoliko morate da, npr. “napunite TREE” komponentu na strani klijenta, neki logičan sled bi bio:

• Pripremite podatke (CONNECT BY PRIOR možda?)
• Smestite ih u REF CURSOR
• Iz klijentskog jezika pozivate neki modul (funkcija, procedura…) koji vam isporučuje podatke
• Dalje radite sa njim (menjate čvorove, potege, veze, šta-god..)

Šta ako baš, baš, morate da pošaljete podatke nazad u bazu (recimo, gore pometnuti TREE koji je izmenjenog oblika)??

Odgovor je jednostavan: NE MOŽE!

Mada…

Evo malog trika koji je primenljiv nad jednom konekcijom u ISTOJ bazi (uzimamo u obzir treću stavku iz dela Zašto koristiti REF CURSOR). Pretpostavimo da ste u okviru paketa deklarisali REF CURSOR i proceduru koja “prihvata” REF CURSOR.

create or replace package test_Pkg as
type cur_test is REF CURSOR;
end test_Pkg;

create or replace procedure testSP(param1 IN test_Pkg.cur_test, param2 OUT NUMBER)
as
begin
FETCH param1 into param2;
-- Mozda insert into neka_tabela... sta god...
end;

Uzimajući u obzir sve gore pomenuto u kodu je moguće uraditi sledeću stvar (kod nije prilagođen jeziku već je samo opisan postupak):

...
# Prethodni otvaranje konekcije i podesavanje parametara
#
# Pripremamo kod za izvrsenje
cmdTxt1 = "begin open :1 for select * from neka_tabela; end;";
# U zavisnosti od jezika se radi BIND za cmdTxt1
# nakon BIND-a, a u zavisnosti od jezika
conn.execute(cmdTxt1);
# Ovim smo otvorili kursor na strani klijenta!!!
# Radimo nesto sa tim podacim
...
# pripremamo kursor za povratak u Bazu
cmdTxt2 = "begin testSP (:1, :2); end;";
# pri cemu bind prvog parametra predstavlja obradjeni REF CURSOR!!!
conn.execute(cmdTxt2);
...

Da ponovim, nije moguće prosleđivati REF CURSOR među bazama! Na meta-linku sam pročitao da je postavljen zahtev za tom mogućnošću u JDBCu… da li će i kada će biti… videćemo.

Do tada, nadam se da će vam ovaj “trik” pomoći.