Migráció III.
2008. október 11., szombat by Zoltan Tanczos
Visszatérési értékek
A visszatérési értékek okozzák a legizgalmasabb problémákat a migráció során. SQL Server alatt egy tárolt eljárás háromféle módon képes adatokat visszaadni a hívója felé:
Egy példa ezen módok bemutatására:
CREATE PROCEDURE sp_demo (@something int out)
AS
begin
set @something = 5
select 7
return 9
end
Az eljárást futtatva az alábbi eredményt kapjuk:
Az első eredményhalmazt a tárolt eljárás adta vissza (SELECT 7), a második pedig az @i, @j változók tartalmát mutatja.
Mint azt már egy korábbi, migrációról szóló bejegyzésben írtam, a migráció egyik célja az lenne, hogy a .NET-es vastag kliens lényegében kizárólag tárolt eljárás hívásokkal kommunikál az adatbázissal. Mind a módosító, mind a lekérdező funkciókat tárolt eljárások valósítják meg: az eljárás által visszaadott eredményhalmazt a kliens valahogy feldolgozza (pl.: megjeleníti táblázatos formában). De természetesen vannak olyan eljárások is, amik nem adnak vissza semmit sem, hanem csak az adatbázisban módosítanak valamit.
Oracle adatbáziskezelő alatt viszont egy tárolt eljárás csak kimenő paraméterekkel képes adatokat visszaadni. Nincs sem visszatérési érték, sem eredményhalmaz. Eredményhalmazokat kurzor referenciákkal (REF CURSOR) lehet emulálni. Az Oracle Migration Workbench is ezt a módszert követi: ha egy T-SQL tárolt eljárás eredményhalmazokat adna vissza, azt úgy fordítja át PL/SQL-re, hogy felvesz annyi darab kurzor referencia kimenő paramétert, ahány eredményhalmazt az eljárás visszaad. Például, a fenti sp_demo eljárás PL/SQL-es változata így nézne ki:
CREATE OR REPLACE FUNCTION sp_demo
(
v_something OUT NUMBER,
cv_1 IN OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
BEGIN
v_something := 5;
OPEN cv_1 FOR
SELECT 7
FROM DUAL ;
RETURN 9;
END;
Hoppá! Az OMWB függvényt csinált az eljárásunkból! Ez azért van, mert visszatérési értéke csak és kizárólag függvényeknek lehet PL/SQL-ben. Viszont egy függvény és egy tárolt eljárás között számos lényegi különbség van, amik miatt nem szerencsés csak úgy egyikből a másikat csinálni. Egy függvény nem végezhet adatmódosító (DML) utasításokat SQL utasításokban. Csupán azért alakította a migráló eszköz függvénnyé, mert csak azoknak lehet visszatérési értéke.
Tehát annyit már most is kijelenthetünk, hogy ha vannak olyan T-SQL tárolt eljárásaink, amiknek van visszatérési értékük, akkor célszerű elgondolkozni azon, hogy hogyan alakítsuk át őket. Például felvehetünk kimenő paramétereket, vagy, ha eddig valójában függvényként használtuk az eljárást, akkor alakítsuk is át függvénnyé.
Az eredményhalmazok kérdése viszont trükkösebb. Ugyanis a kimenő paramétereket az eljárás hívásakor explicit fel kell venni. Ha egy eljárás hív egy másikat, akkor a hívó eljárásban is deklarálni kell egy kurzor változót, amit paraméterül adunk a másik eljárásnak. Ha viszont .NET alól szeretnénk ilyen eljárásokat meghívni, akkor a paraméterlistába kell felvenni a kimenő kurzor típusú paramétert:
cmd.Parameters.Add("cv_1", OracleType.Cursor).Direction =
ParameterDirection.Output;
Ha ezt elmulasztjuk, akkor a hívás exception-t fog dobni, PLS-00306 hibaüzenettel: "wrong number or types of arguments in call to '*spname*'". Ugyanez igaz fordítva is, azaz ha olyan eljárásnál próbálnánk hozzáadni, aminek valójában nincs ilyen paramétere.
És ezzel eljutottunk egy igen kemény problémához: a migráció célja ugye az, hogy ugyanaz a kliens legyen használható SQL Server és Oracle adatbáziskezelő esetén is. SQL Servernek nem kell explicit megmondani, hogy a tárolt eljárás fog-e visszaadni eredményhalmazt, Oracle-nek viszont igen. Egy T-SQL tárolt eljárás tetszőleges számú eredményhalmazt is visszaadhat, egy PL/SQL-es viszont pontosan annyit, ahány kimenő kurzor változó paramétere van.
Mivel a migráció során úgyis hozzá kell nyúlni minden egyes tárolt eljárás kódjához (ha másért nem, akkor azért, hogy megnézzük, úgy működik-e, ahogy szeretnénk), ezért arra a kompromisszumos megoldásra jutottunk, hogy:
Az eljárások egymást is hívják, ezért a kódot ilyen esetekben is módosítani kell. Mit nyerünk ezzel? Azt, hogy a kliens kód megtarthatja az univerzális jellegét, és nem kell kliens oldalon is minden egyes eljáráshíváshoz hozzányúlni. (Pl.: ott is megmondhatnánk explicit módon, hogy a hívott eljárás vissza fog-e adni eredményhalmazt, vagy sem, és ennek hatására SQL Server esetén nem történne semmi, Oracle esetén viszont felkerülne a paraméterlistába a kimenő kurzor változó.)
Az MSDN-en szerepel egy viszonylag részletes leírás arról, hogy hogyan kell .NET alatt PL/SQL tárolt eljárásokat hívni, illetve érdekes olvasmány még Vadim Tropashko kurzorokról szóló blogbejegyzése. Ennek a cikknek a végén szerepel egy kemény kijelentés:
It seem that the genesis of “a better cursor demand” is a stream of programmers from SQL Server world, where a procedure which doesn’t even bother to declare its outputs as a cursor like this
CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO
Its a pity that TSQL designers give an impression of not understanding the difference between a procedure and a query.
Hát, ez kicsit úgy hangzik, mintha az Oracle megoldása lenne az egyetlen járható út.
Készítettem egy buta kis Windows-os alkalmazást, ami azt próbálja demonstrálni, hogy a kliens oldalon milyen változtatások szükségesek ha SQL Server helyett Oracle adatbáziskezelőt akarunk használni. Az egyedüli különbség (a használt osztályokat leszámítva) a kurzor paraméter deklarációja. A kód letölthető innen.
A visszatérési értékek okozzák a legizgalmasabb problémákat a migráció során. SQL Server alatt egy tárolt eljárás háromféle módon képes adatokat visszaadni a hívója felé:
- kimenő paraméterekkel: CREATE PROCEDURE sp_demo (@something int OUT)
- visszatérési értékkel: return @someValue
- úgynevezett eredményhalmazokkal (result set-ekkel): egy T-SQL tárolt eljárás képes visszaadni egy tetszőleges SELECT utasítás által visszaadott eredményhalmazt
Egy példa ezen módok bemutatására:
CREATE PROCEDURE sp_demo (@something int out)
AS
begin
set @something = 5
select 7
return 9
end
Az eljárást futtatva az alábbi eredményt kapjuk:
Az első eredményhalmazt a tárolt eljárás adta vissza (SELECT 7), a második pedig az @i, @j változók tartalmát mutatja.
Mint azt már egy korábbi, migrációról szóló bejegyzésben írtam, a migráció egyik célja az lenne, hogy a .NET-es vastag kliens lényegében kizárólag tárolt eljárás hívásokkal kommunikál az adatbázissal. Mind a módosító, mind a lekérdező funkciókat tárolt eljárások valósítják meg: az eljárás által visszaadott eredményhalmazt a kliens valahogy feldolgozza (pl.: megjeleníti táblázatos formában). De természetesen vannak olyan eljárások is, amik nem adnak vissza semmit sem, hanem csak az adatbázisban módosítanak valamit.
Oracle adatbáziskezelő alatt viszont egy tárolt eljárás csak kimenő paraméterekkel képes adatokat visszaadni. Nincs sem visszatérési érték, sem eredményhalmaz. Eredményhalmazokat kurzor referenciákkal (REF CURSOR) lehet emulálni. Az Oracle Migration Workbench is ezt a módszert követi: ha egy T-SQL tárolt eljárás eredményhalmazokat adna vissza, azt úgy fordítja át PL/SQL-re, hogy felvesz annyi darab kurzor referencia kimenő paramétert, ahány eredményhalmazt az eljárás visszaad. Például, a fenti sp_demo eljárás PL/SQL-es változata így nézne ki:
CREATE OR REPLACE FUNCTION sp_demo
(
v_something OUT NUMBER,
cv_1 IN OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
BEGIN
v_something := 5;
OPEN cv_1 FOR
SELECT 7
FROM DUAL ;
RETURN 9;
END;
Hoppá! Az OMWB függvényt csinált az eljárásunkból! Ez azért van, mert visszatérési értéke csak és kizárólag függvényeknek lehet PL/SQL-ben. Viszont egy függvény és egy tárolt eljárás között számos lényegi különbség van, amik miatt nem szerencsés csak úgy egyikből a másikat csinálni. Egy függvény nem végezhet adatmódosító (DML) utasításokat SQL utasításokban. Csupán azért alakította a migráló eszköz függvénnyé, mert csak azoknak lehet visszatérési értéke.
Tehát annyit már most is kijelenthetünk, hogy ha vannak olyan T-SQL tárolt eljárásaink, amiknek van visszatérési értékük, akkor célszerű elgondolkozni azon, hogy hogyan alakítsuk át őket. Például felvehetünk kimenő paramétereket, vagy, ha eddig valójában függvényként használtuk az eljárást, akkor alakítsuk is át függvénnyé.
Az eredményhalmazok kérdése viszont trükkösebb. Ugyanis a kimenő paramétereket az eljárás hívásakor explicit fel kell venni. Ha egy eljárás hív egy másikat, akkor a hívó eljárásban is deklarálni kell egy kurzor változót, amit paraméterül adunk a másik eljárásnak. Ha viszont .NET alól szeretnénk ilyen eljárásokat meghívni, akkor a paraméterlistába kell felvenni a kimenő kurzor típusú paramétert:
cmd.Parameters.Add("cv_1", OracleType.Cursor).Direction =
ParameterDirection.Output;
Ha ezt elmulasztjuk, akkor a hívás exception-t fog dobni, PLS-00306 hibaüzenettel: "wrong number or types of arguments in call to '*spname*'". Ugyanez igaz fordítva is, azaz ha olyan eljárásnál próbálnánk hozzáadni, aminek valójában nincs ilyen paramétere.
És ezzel eljutottunk egy igen kemény problémához: a migráció célja ugye az, hogy ugyanaz a kliens legyen használható SQL Server és Oracle adatbáziskezelő esetén is. SQL Servernek nem kell explicit megmondani, hogy a tárolt eljárás fog-e visszaadni eredményhalmazt, Oracle-nek viszont igen. Egy T-SQL tárolt eljárás tetszőleges számú eredményhalmazt is visszaadhat, egy PL/SQL-es viszont pontosan annyit, ahány kimenő kurzor változó paramétere van.
Mivel a migráció során úgyis hozzá kell nyúlni minden egyes tárolt eljárás kódjához (ha másért nem, akkor azért, hogy megnézzük, úgy működik-e, ahogy szeretnénk), ezért arra a kompromisszumos megoldásra jutottunk, hogy:
- minden tárolt eljárásnak kötelezően van egy cv_1 nevű kimenő paramétere, a paraméterlista első helyén, attól függetlenül, hogy visszaad-e eredményhalmazt, vagy sem
- azon tárolt eljárások esetében, amik több eredményhalmazt is visszaadnának, újragondoljuk a funkciójukat, és szükség esetén feldaraboljuk több eljárássá, azaz eljárásonként pontosan egy visszaadott eredményhalmaz.
Az eljárások egymást is hívják, ezért a kódot ilyen esetekben is módosítani kell. Mit nyerünk ezzel? Azt, hogy a kliens kód megtarthatja az univerzális jellegét, és nem kell kliens oldalon is minden egyes eljáráshíváshoz hozzányúlni. (Pl.: ott is megmondhatnánk explicit módon, hogy a hívott eljárás vissza fog-e adni eredményhalmazt, vagy sem, és ennek hatására SQL Server esetén nem történne semmi, Oracle esetén viszont felkerülne a paraméterlistába a kimenő kurzor változó.)
Az MSDN-en szerepel egy viszonylag részletes leírás arról, hogy hogyan kell .NET alatt PL/SQL tárolt eljárásokat hívni, illetve érdekes olvasmány még Vadim Tropashko kurzorokról szóló blogbejegyzése. Ennek a cikknek a végén szerepel egy kemény kijelentés:
It seem that the genesis of “a better cursor demand” is a stream of programmers from SQL Server world, where a procedure which doesn’t even bother to declare its outputs as a cursor like this
CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO
Its a pity that TSQL designers give an impression of not understanding the difference between a procedure and a query.
Hát, ez kicsit úgy hangzik, mintha az Oracle megoldása lenne az egyetlen járható út.
Készítettem egy buta kis Windows-os alkalmazást, ami azt próbálja demonstrálni, hogy a kliens oldalon milyen változtatások szükségesek ha SQL Server helyett Oracle adatbáziskezelőt akarunk használni. Az egyedüli különbség (a használt osztályokat leszámítva) a kurzor paraméter deklarációja. A kód letölthető innen.
