Tato kapitola popisuje jak používat uložené procedury
v naší databázové aplikaci. Uložené procedury jsou programy zapsané v jazyku
procedur a spouštěčů, který je specifický pro použitý databázový systém.
Jsou dva základní typy uložených procedur. První typ získává data (podobně
jako dotaz SELECT). Získaná data mohou být ve tvaru datové množiny obsahující
jeden nebo více řádků dat, rozdělených do jednoho nebo více sloupců. Nebo
získaná data mohou mít formu individuálních prvků informací. Druhý typ
nevrací data, ale provádí akci na uložených datech v databázi (např. jako
příkaz DELETE). Některé databázové servery podporují oba typy operací ve
stejné proceduře.
Uložené procedury, které vracejí data to dělají různým
způsobem, závisejícím na složení uložené procedury a použitém databázovém
systému. Některé databáze jako InterBase vracejí všechna data (datové množiny
a individuální prvky informací) výlučně výstupními parametry. Ostatní mají
schopnost vrátit kurzor do dat. A zbývající, jako MS SQL Server nebo Sybase
mohou vracet datové množiny a individuální prvky informací.
V aplikacích C++ Builderu, přístup k uloženým procedurám
je poskytnut komponentami TStoredProc a TQuery. Kterou z
nich můžeme použít pro přístup, je určeno způsobem kódování uložené procedury,
způsobem návratu dat (jsou-li nějaká data vraceny) a použitým databázovým
systémem. Komponenty
TStoredProc a TQuery jsou potomky TDataSet
a dědí tedy od něj základní chování.
Komponenta uložené procedury je používána k provedení
uložené procedury, která nevrací žádná data, k získání individuálních prvků
informací ve formě výstupních parametrů nebo k přenesení vrácené datové
množiny na přiřazenou komponentu datového zdroje (závisí na databázi).
Komponenta uložené procedury umožňuje předávat hodnoty do a vracet z uložené
procedury prostřednictvím parametrů, kde každý parametr je definován ve
vlastnosti
Params. Komponenta uložené procedury také poskytuje metodu
GetResults k donucení uložené proceduru k návratu datové množiny
(některé databázové servery toto vyžadují před vytvořením výsledkové množiny).
Komponenta uložené procedury je preferována pro uložené procedury, které
nevracejí žádná data nebo vracejí data pouze pomocí výstupních parametrů.
Komponenta dotazu je hlavně používána pro spouštění uložených
procedur, které vracejí datové množiny. To zahrnuje uložené procedury InterBase,
které vracejí datové množiny pouze prostřednictvím výstupních parametrů.
Komponenta dotazu může být také použita k provádění uložených procedur,
které nevracejí výsledkové množiny nebo hodnoty výstupních parametrů.
Parametry používáme k předávání hodnot do a k návratu
hodnot z uložených procedur. Hodnoty vstupních parametrů jsou použity na
místech jako je klauzule WHERE příkazu SELECT v uložené proceduře. Výstupní
parametry umožňují uložené proceduře předávat samostatné hodnoty volající
aplikaci. Některé uložené procedury vracejí parametr result. Na
možnosti uložených procedur se musíme podívat na dokumentaci našeho databázového
serveru.
V této kapitole budou popsány následující body:
Kdy je potřeba
použít uloženou proceduru?
Pokud náš server umožňuje definovat uložené procedury, pak
je vhodné v naší aplikaci je používat. Vývojář databázového serveru vytváří
uložené procedury ke zpracování často prováděných úloh v databázi. Jsou
vhodné k provádění operací nad značně velkým počtem řádků v databázových
tabulkách nebo když používají agregační nebo matematické funkce. Pokud
uložené procedury existují na vzdáleném databázovém serveru používaným
naší aplikací, pak můžeme použít jejich výhody ve své aplikaci. Využijeme
funkčnost jimi poskytnutou a zvýšíme výkonnost naší databáze:
-
Přebíráme výhody obvykle rychlejšího zpracování na serveru.
-
Omezujeme množství přenášených dat v naší počítačové síti,
neboť zpracování probíhá na serveru, kde sídlí data.
Např. předpokládejme aplikaci, která potřebuje vypočítat
jedinou hodnotu a to standardní odchylku hodnot ze značně velkého množství
záznamů. K provedení této funkce v naší aplikaci, musíme všechny hodnoty
použité ve výpočtu získat ze serveru, což může značně zvýšit zátěž sítě.
Potom naše aplikace musí provést výpočet. Vše proběhne v naši aplikaci
a výsledkem je jediná hodnota. Mnohem výhodnější je provést výpočet uloženou
proceduru (výpočty proběhnou na serveru, což může být výkonnější počítač
a potom sítí stačí přenést jedinou hodnotu).
Používání uložených procedur
Jak uložená procedura je použita v aplikaci C++ Builderu
závisí na kódování uložené procedury, zda a jak vrací data, specifikách
použitého databázového serveru nebo kombinaci těchto faktorů.
V obecných termínech, pro přístup k uložené proceduře
na serveru, provedeme kroky:
-
Instantizujeme komponentu TStoredProc a volitelně
přiřadíme k uložené proceduře na serveru. Nebo instantizujeme komponentu
TQuery
a vytvoříme obsah její vlastnosti SQL k provedení dotazu SELECT
tvořícího uloženou proceduru nebo příkazu EXECUTE v závislosti na tom,
zda uložená procedura vrací výsledkovou množinu.
-
V případě potřeby poskytneme komponentě uložené procedury
hodnoty vstupních parametru. Když komponenta uložené procedury není přiřazená
k uložené proceduře na serveru, pak musíme poskytnout další informace vstupním
parametrům, jako je jméno parametru a datové typy.
-
Provedení uložené procedury.
Nyní již můžeme zpracovat výsledek a výstupní parametry.
Jako u jiných komponent datových množin, můžeme také zkoumat výsledkovou
datovou množinu ze serveru. Detailní aspekty používání uložených procedur
zahrnují:
Vytváření komponenty
uložené procedury
K vytvoření komponenty uložené procedury pro uloženou proceduru
na databázovém serveru:
-
Umístíme komponentu uložené procedury ze stránky Data
Access Palety komponent do datového modulu.
-
Volitelně nastavíme vlastnost DatabaseName komponenty
uložené procedury na jméno databáze, ve které je uložená procedura definována.
DatabaseName
musí být přezdívka BDE nebo stejná hodnota jako ve vlastnosti DatabaseName
komponenty
TDatabase,
která se může připojit k serveru.
Normálně specifikujeme vlastnost DatabaseName,
ale pokud server databáze je právě nedostupný, pak můžeme vytvořit a nastavit
komponentu uložené procedury s vynecháním DatabaseName a předáním
jména uložené procedury a vstupních, výstupních a výsledkových parametrů
při návrhu.
-
Volitelně nastavíme vlastnost StoredProcName na jméno
použité uložené procedury. Pokud poskytneme hodnotu pro vlastnost DatabaseName,
pak můžeme vybrat jméno uložené procedury z rozbalovacího seznamu vlastnosti.
Jedna komponenta TStoredProc může být použita ke spuštění libovolného
počtu uložených procedur nastavením vlastnosti StoredProcName na
přípustné jméno v aplikaci. Není tedy nutno nastavovat StoredProcName
při návrhu.
-
Dvojitým kliknutím ve sloupci hodnot vlastnosti Params
vyvoláme Editor parametrů uložených procedur ke zkoumání vstupních a výstupních
parametrů pro uloženou proceduru. Pokud nespecifikujeme jméno pro uloženou
proceduru ve třetím kroku, nebo specifikujeme jméno pro uloženou proceduru,
která neexistuje na serveru určeném vlastností DatabaseName (zadanou
v kroku 2), pak po vyvolání editoru parametrů, editor je prázdný.
Ne všechny servery vracejí parametry nebo informace o parametrech.
Podívejte se do dokumentace vašeho serveru k určení zda informace o svých
uložených procedurách jsou vraceny klientským aplikacím.
Poznámka: Pokud nespecifikujeme v kroku 2 vlastnost
DatabaseName,
pak musíme použít Editor parametrů uložených procedur k nastavení parametrů
při návrhu.
Vytváření uložených procedur
Normálně, uložené procedury jsou vytvářeny, když je vytvářena
aplikace a její databáze, pomocí nástrojů dodaných výrobcem databáze. Je
ale také možné vytvářet uložené procedury za běhu. Příkaz SQL použitý k
vytvoření uložené procedury se může na různých databázích značně lišit.
Je potřeba se podívat do dokumentace našeho databázového systému.
Uložené procedury mohou být aplikací vytvářeny za běhu
pomocí příkazu SQL z komponenty TQuery (obvykle příkazu CREATE PROCEDURE).
Pokud uložená procedura používá parametry, pak nastavíme vlastnost ParamCheck
komponenty TQuery na false. To chrání TQuery v rozlišení
parametrů v nové uložené proceduře od parametrů z TQuery samotného.
Poznámka: Můžeme také použít Průzkumníka SQL
k testování, editaci a vytváření uložených procedur na serveru.
Příprava a provádění
uložených procedur
K použití uložené procedury, ji můžeme volitelně připravit
a potom ji provést. Uloženou proceduru můžeme připravit:
-
Během návrhu stiskem OK v Editoru parametrů.
-
Za běhu, voláním metody Prepare komponenty uložené
procedury.
Např. Následující kód připravuje uloženou proceduru pro provádění:
StoredProc1->Prepare();
Poznámka: Pokud naše aplikace mění za běhu
informace o parametrech (např. při použití přetížených procedur Oracle),
pak musíme proceduru připravit znova.
K spuštění připravené uložené procedury, voláme metodu
ExecProc
pro komponentu uložené procedury. Následující kód ukazuje kód připravující
a provádějící uloženou proceduru.
StoredProc1->Params->Items[0]->AsString =
Edit1->Text;
StoredProc1->Prepare();
StoredProc1->ExecProc();
Poznámka: Pokud se pokusíme provést uloženou
proceduru před jejím připravením, pak komponenta uložené procedury je připravena
automaticky a potom po provedení je její příprava zrušena. Pokud plánujeme
několikanásobné provádění uložené procedury, pak je mnohem efektivnější
explicitní volání
Prepare a potom, když již procedura nebude nadále
potřeba, volání
UnPrepare.
Když provedeme uloženou proceduru, pak může vracet všechny
nebo některé z těchto prvků:
-
Datovou množinu obsahující jeden nebo více záznamů, které
mohou být zobrazeny v datových ovladačích přiřazených k uložené proceduře
prostřednictvím komponenty datového zdroje.
-
Výstupní parametry.
-
Výsledný parametr, který obsahuje stavové informace o provedení
uložené procedury.
K určení očekávaných vracených prvků z uložené procedury
na našem serveru, se musíme podívat do dokumentace serveru.
Uložené procedury,
které vracejí výsledkovou množinu
Uložené procedury, které vracejí data v datových množinách,
řádcích a sloupcích dat, mohou být často používány komponentou dotazu.
Nicméně, s databázovým serverem, který podporuje návrat datové množiny
uloženou procedurou, může být komponenta datové množiny použita i k tomuto
účelu.
Návrat výsledkové množiny pomocí TQurey
K získání datové množiny z uložené procedury pomocí komponenty
TQuery:
-
Instantizujeme komponentu dotazu.
-
Do vlastnosti TQuery::SQL zapíšeme dotaz SELECT, který
použije jméno uložené procedury místo jména tabulky.
-
Pokud uložená procedura vyžaduje vstupní parametry, předáme
hodnoty parametrů jako čárkami oddělovaný seznam, uzavřený v závorkách,
následovaný za jménem procedury.
-
Nastavíme vlastnost Active na true nebo vyvoláme
metodu Open.
Např. uložená procedura InterBase GET_EMP_PROJ, akceptuje
hodnotu pomocí vstupního parametru EMP_NO a vrací datovou množinu prostřednictvím
výstupního parametru PROJ_ID.
CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :PROJ_ID
DO
SUSPEND;
END
Příkaz SQL použitý z komponenty dotazu k použití této
uložené procedury bude:
SELECT *
FROM GET_EMP_PROJ(52)
Návrat výsledkové množiny pomocí TStoredProc
K získání datové množiny z uložené procedury pomocí komponenty
TStoredProc:
-
Instantizujeme komponentu uložené procedury.
-
Ve vlastnosti StoredProcName specifikujeme jméno uložené
procedury.
-
Pokud uložená procedura vyžaduje vstupní parametry, pak předáme
hodnoty pro parametry pomocí vlastnosti Params nebo metodou ParamByName.
-
Nastavíme vlastnost Active na true nebo vyvoláme
metodu Open.
Např. uložená procedura Sybase GET_EMPLOYEES, akceptuje parametr
nazvaný @EMP_NO a vrací výsledkovou množinu na základě této hodnoty.
CREATE PROCEDURE GET_EMPLOYEES @EMP_NO SMALLINT
AS SELECT EMP_NAME, EMPLOYEE_NO FROM EMPLOYEE_TABLE
WHERE (EMPLOYEE_NO = @EMP_NO)
Kód C++ Builderu k naplnění parametru hodnotou a aktivování
komponenty uložené procedury je:
StoredProc1->Close();
StoredProc1->ParamByName("EMP_NO")->AsSmallInt
= 52;
StoredProc1->Active = true;
Uložené procedury,
které vracejí data pomocí parametrů
Uložené procedury mohou být určeny k získávání jednotlivých
prvků informací pomocí parametrů, místo k získávání celých řádků dat. Např.
uložená procedura může získávat maximální hodnotu sloupce, k této hodnotě
přičíst 1 a potom vrátit tuto hodnotu aplikaci. To lze opět provádět komponentami
TQuery
a TStoredProc. Preferovanou metodou pro získávání hodnot parametrů
je použití TStoredProc.
Získávání individuálních hodnot pomocí TQuery
Hodnoty parametrů získaných prostřednictvím komponenty
TQuery
přebíráme ve formě jednoho řádku datové množiny, a to i když uložená procedura
vrací jediný parametr. K získání jednotlivých hodnot z parametru uložené
procedury pomocí komponenty TQuery:
-
Instantizujeme komponentu dotazu.
-
Do vlastnosti TQuery::SQL zapíšeme dotaz SELECT, který
používá jméno uložené procedury namísto jména tabulky. Klauzule SELECT
tohoto dotazu může specifikovat jména parametrů jako jména sloupců v tabulce
nebo může jednoduše použít * k získání všech hodnot parametrů.
-
Pokud uložená procedura vyžaduje vstupní parametry, pak předáme
seznam hodnot parametrů oddělený čárkami a uzavřený v závorkách za jméno
procedury.
-
Nastavíme vlastnost Active na true nebo vyvoláme
metodu Open.
Např. uložená procedura GET_HIGH_EMP_NAME z InterBase, získává
abecedně poslední hodnotu ve sloupci LAST_NAME z tabulky nazvané EMPLOYEE.
Uložená procedura vrací tuto hodnotu ve výstupním parametru High_Last_Name.
CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS
BEGIN
SELECT MAX(LAST_NAME)
FROM EMPLOYEE
INTO :High_Last_Name;
SUSPEND;
END
Příkaz SQL použitý z TQuery k použití této uložené
procedury může být:
SELECT High_Last_Name
FROM GET_HIGH_EMP_NAME
Získávání jednotlivých hodnot pomocí TStoredProc
K získání individuálních hodnot výstupních parametrů
z uložené procedury pomocí komponenty TStoredProc:
-
Instantizujeme komponentu uložené procedury.
-
Ve vlastnosti StoredProcName specifikujeme jméno uložené
procedury.
-
Pokud uložená procedura vyžaduje vstupní parametry, pak předáme
hodnoty pro parametry pomocí vlastnosti Params nebo metodou ParamByName.
-
Vyvoláme metodu ExecProc.
-
Použijeme hodnoty jednotlivých výstupních parametrů pomocí
vlastnosti Params nebo metodou ParamByName.
Např. uložená procedura GET_HIGH_EMP_NAME z InterBase, získává
abecedně poslední hodnotu ve sloupci LAST_NAME z tabulky nazvané EMPLOYEE.
Uložená procedura vrací tuto hodnotu ve výstupním parametru High_Last_Name.
CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS
BEGIN
SELECT MAX(LAST_NAME)
FROM EMPLOYEE
INTO :High_Last_Name;
SUSPEND;
END
Kód C++ Builderu k získání hodnoty výstupního parametru
High_Last_Name
a uložit ji do vlastnosti Text editační komponenty:
StoredProc1->StoredProName = "GET_HIGH_EMP_NAME";
StoredProc1->ExecProc();
Edit1->Text = StoredProc1->ParamByName("High_Last_Name")->AsString;
Uložené procedury
provádějící akce na datech
Uložené procedury mohou být kódovány tak, že nevracejí žádná
data a pouze provádějí nějakou akci v databázi. Operace SQL vyvolávající
příkazy INSERT a DELETE jsou dobrým příkladem tohoto typu uložených procedur.
Např. místo umožnění uživateli přímo zrušit řádek, můžeme k tomu použít
uloženou proceduru. To umožňuje uložené proceduře určovat co zrušit a také
zpracovávat aspekty referenční integrity, jako je kaskádovité rušení řádků
v závislých tabulkách.
Provádění akčních uložených procedur pomocí TQuery
K provedení akčních uložených procedur pomocí komponenty
TQuery:
-
Instantizujeme komponentu dotazu.
-
Do vlastnosti TQuery::SQL vložíme příkaz nutný k provedení
uložené procedury a jméno uložené procedury. Příkaz k provedení uložené
procedury se může v různých databázových systémech značně lišit. V InterBase
je to např. příkaz EXECUTE PROCEDURE.
-
Pokud uložená procedura vyžaduje vstupní parametry, předáme
hodnoty parametrů jako čárkami oddělený seznam a zapíšeme jej do závorek
za jméno procedury.
-
Vyvoláme metodu TQuery::ExecSQL.
Např. uložená procedura ADD_EMP_PROJ z InterBase přidává
nový řádek do tabulky EMPLOYEE_PROJECT. Není vrácená žádná datová množina
nebo jednotlivé hodnoty ve výstupních parametrech:
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT,
PROJ_ID CHAR(5)) AS
BEGIN
BEGIN
INSERT INTO EMPLOYEE_PROJECT
(EMP_NO, PROJ_ID)
VALUES (:EMP_NO, :PROJ_ID);
WHEN SQLCODE -530 DO
EXCEPTION
UNKNOWN_EMP_ID;
END
SUSPEND;
END
Příkaz SQL vyvolaný z TQuery k provedení této
procedury může být:
EXECUTE PROCEDURE ADD_EMP_PROJ(20, "GUIDE")
Provedení akční uložené procedury pomocí TStoredProc
K provedení akcí uložené procedury pomocí TStoredProc:
-
Instantizujeme komponentu uložené procedury.
-
Ve vlastnosti StoredProcName specifikujeme jméno uložené
procedury.
-
Pokud uložená procedura vyžaduje vstupní parametry, předáme
hodnoty parametrů pomocí vlastnosti Params nebo metodou ParamByName.
-
Vyvoláme metodu ExecProc.
Např. uložená procedura ADD_EMP_PROJ z InterBase přidává
nový řádek do tabulky EMPLOYEE_PROJECT. Není vrácená žádná datová množina
nebo jednotlivé hodnoty ve výstupních parametrech:
CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT,
PROJ_ID CHAR(5)) AS
BEGIN
BEGIN
INSERT INTO EMPLOYEE_PROJECT
(EMP_NO, PROJ_ID)
VALUES (:EMP_NO, :PROJ_ID);
WHEN SQLCODE -530 DO
EXCEPTION
UNKNOWN_EMP_ID;
END
SUSPEND;
END
Kód C++ Builderu k provedení uložené procedury ADD_EMP_PROJ
je:
StoredProc1->StoredProcName = "ADD_EMP_PROJ";
StoredProc1->ExecProc;
Seznámení s parametry
uložených procedur
Jsou čtyři typy parametrů, které mohou být přiřazeny k uloženým
procedurám:
-
Vstupní parametry, používané k předávání hodnot uloženým
procedurám pro zpracování.
-
Výstupní parametry používané uloženou procedurou k
předávání hodnot aplikaci.
-
Vstupní/výstupní parametry, používané k předávání
hodnot uloženým procedurám ke zpracování a používané uloženou procedurou
k návratu hodnot aplikaci.
-
Parametr result (výsledkový parametr), používají některé
procedury k návratu chybové nebo stavové hodnoty aplikaci. Uložená procedura
může vracet pouze jeden parametr result.
Zda uložená procedura používá jistý typ parametrů závisí
na jazyku implementujícím uloženou proceduru na našem databázovém serveru
a na specifické instanci uložené procedury. Např. jistá uložená procedura
na nějakém serveru může být implementována pomocí vstupních parametrů nebo
nemusí. Na druhé straně, některé použití parametrů závisí na serveru. Např.
uložené procedury MS-SQL Server a Sybase vždy vracejí parametr result,
ale implementace InterBase jej nevrací nikdy.
Přístup k parametrům uložených procedur je poskytnut
objekty TParam ve vlastnosti TStoredProc::Params. Pokud jméno
uložené procedury je specifikováno při návrhu ve vlastnosti StoredProcName,
pak je automaticky vytvořen pro každý parametr objekt TParam a přidán
k vlastnosti Params. Pokud jméno uložené procedury není specifikováno
do spuštění, pak objekty TParams musí být vytvořeny programově po
spuštění. Nespecifikování uložené procedury a manuální vytváření objektů
TParam
umožňuje jedna komponenta TStoredProc, která může být použita s
libovolným počtem dostupných uložených procedur.
Poznámka: Některé uložené procedury vracejí
mimo výstupních a výsledkových parametrů i datovou množinu. Aplikace mohou
zobrazovat záznamy datové množiny v datových ovladačích, ale musí odděleně
zpracovat výstupní a výsledkové parametry.
V této části se budeme zabývat ještě:
Používání vstupních parametrů
Aplikace používá vstupní parametry k předávání jednotlivých
datových hodnot uložené proceduře. Tyto hodnoty jsou pak použity příkazy
SQL v uložené proceduře, jako je porovnávaná hodnota pro klauzuli WHERE.
Pokud uložená procedura požaduje vstupní parametr, pak přiřadíme hodnotu
parametru před provedením uložené procedury.
Pokud uložená procedura vrací datovou množinu a je použita
prostřednictvím dotazu SELECT v komponentě TQuery, předáme hodnoty
vstupních parametrů jako čárkami oddělený seznam uzavřený v závorkách,
za jménem uložené procedury. Např. následující příkaz SQL získává data
z uložené procedury nazvané GET_EMP_PROJ a předává ji hodnotu vstupního
parametru 52.
SELECT PROJ_ID
FROM GET_EMP_PROJ(52)
Pokud uložená procedura je prováděna s komponentou TStoredProc,
pak použijeme vlastnost Params nebo metodu ParamByName k
nastavení každého vstupního parametru. Použijeme vlastnost TParam
příslušející pro datový typ parametru, jako je vlastnost TParam::AsString
pro parametr typu CHAR. Nastavíme hodnoty vstupních parametrů před provedením
nebo aktivováním komponenty TStoredProc. V následujícím příkladu,
parametr EMP_NO (typu SMALLINT) pro uloženou proceduru GET_EMP_PROJ má
přiřazenou hodnotu 52.
StoredProc1->ParamByName("EMP_NO")->AsSmallInt
= 52;
StoredProc1->ExecProc();
Používání
výstupních parametrů
Uložené procedury používají výstupní parametry k předávání
jednotlivých hodnot dat zpět aplikaci, která uloženou proceduru volá. Výstupní
parametry získají své hodnoty po provedení uložené procedury. Výstupní
parametry prohlížíme z aplikace k získání jejich hodnot, po vyvolání metody
TStoredProc::ExecProc.
K odkazování na objekt TParam, který reprezentuje
parametr a k prohlédnutí jeho hodnoty používáme vlastnost TStoredProc::Params
nebo metodu TStoredProc::ParamByName. Např. k získání hodnoty parametru
a uložení jeho hodnoty do vlastnosti Text editační komponenty použijeme:
StoredProc1->ExecProc();
Edit1->Text = StoredProc1->Param[0]->AsString;
Většina uložených procedur vrací jeden nebo více výstupních
parametrů. Výstupní parametry mohou reprezentovat izolované vracené hodnoty
uloženou procedurou, která nevrací datovou množinu, mohou reprezentovat
jednu množinu hodnot vrácenou procedurou, která také vrací datovou množinu
nebo mohou reprezentovat hodnoty, které nemají přímý vztah k jednotlivým
záznamům datové množiny vracené uloženou procedurou. Každá serverová implementace
uložemné procedury se v tomto může lišit.
Poznámka: Zdrojový kód pro uložené procedury
Informixu mohou indikovat, že vracejí výstupní parametry i když v Editoru
parametrů uložené procedury je nemusíme vidět. Informix překládá
výstupní parametry do jednoho záznamu datové množiny, který můžeme zobrazit
v datových ovladačích aplikace.
Používání vstupně/výstupních
parametrů
Vstupně/výstupní parametry slouží jako vstupní i výstupní
parametry. Aplikace použije vstupně/výstupní parametry k předání jednotlivých
datových hodnot uložené proceduře, která je opětovně použije k předání
datových hodnot volající aplikaci. Jako se vstupními parametry, vstupní
hodnota pro vstupně/výstupní parametr musí být dostupná před vyvoláním
uložené procedury.
V příkladu uložené procedury Oracle, je parametr IN_OUTVAR
vstupně/výstupním parametrem:
CREATE OR REPLACE PROCEDURE UPDATE_THE_TABLE
(IN_OUTVAR IN OUT INTEGER)
AS
BEGIN
UPDATE ALLTYPETABLE
SET NUMBER82FLD = IN_OUTVAR
WHERE KEYFIELD = 0;
IN_OUTVAR:=1;
END UPDATE_THE_TABLE;
V kódu programu v C++ Builderu uvedeném dále, IN_OUTVAR
je přiřazena vstupní hodnota, provedena uložená procedura a pak výstupní
hodnota v IN_OUTVAR je uložena do proměnné v paměti.
StoredProc1->ParamByName("IN_OUTVAR")->AsInteger
= 103;
StoredProc1->ExecProc();
IntegerVar = StoredProc1->ParamByName("IN_OUTVAR")->AsInteger;
Používání výsledkového parametru
Mimo vracení výstupních parametrů a datové množiny, některé
uložené procedury také vracejí jeden výsledkový parametr. Výsledkový parametr
je obvykle používán k indikaci chybového stavu nebo počtu záznamů zpracovaných
uloženou procedurou. Musíme se podívat do dokumentace našeho serveru, abychom
zjistili zda náš server podporuje výsledkový parametr. Výsledkovému parametru
je přiřazena hodnota provedením uložené procedury. Výsledkový parametr
zkoumáme v aplikaci k získání jeho hodnoty po vyvolání metody TStoredProc::ExecProc.
K odkazu na objekt TParam, který reprezentuje
výsledkový parametr a zkoumání jeho hodnoty použijeme vlastnost TStoredProc::Params
nebo metodu TStoredProc::ParamByName.
DateVar = StoredProc1->ParamByName("MyOutputParam")->AsDate;
Zpřístupňování parametrů
během návrhu
Pokud se připojíme ke vzdálenému databázovému serveru nastavením
vlastností
DatabaseName a StoredProcName při návrhu, pak
můžeme používat Editor parametrů uložené procedury k zobrazení jmen a datových
typů každého vstupního parametru a můžeme nastavit hodnoty pro vstupní
parametry k předání na server když uloženou proceduru provedeme.
Důležité: Neměníme jména nebo datové typy vstupních
parametrů oznámených serverem nebo při provedení uložené procedury bude
generována výjimka.
Některé servery (např. Informix) neoznamují jména parametrů
nebo datové typy. V těchto případech použijeme Průzkumník SQL nebo výrobcem
serveru dodané utility k prohlédnutí zdrojového kódu uložené procedury
na serveru k určení vstupních parametrů a jejich datových typů.
Pokud při návrhu nezískáme seznam parametrů uložené procedury
na vzdáleném serveru (např. když nejsme připojeni k serveru), pak musíme
vyvolat Editor parametrů uložené procedury, uvést zde každý požadovaný
vstupní parametr a přiřadit mu datový typ a hodnotu.
Nastavování informací o parametrech při návrhu.
Můžeme vyvolat Editor parametrů uložené procedury při
návrhu k nastavení parametrů a jejich hodnot. Editor parametrů umožňuje
nastavovat parametry uložené procedury. Pokud u komponenty TStoredProc
nastavíme vlastnosti
DatabaseName a StoredProcName při návrhu,
pak existující parametry jsou uvedeny v Editoru. Pokud tyto vlastnosti
nenastavíme, pak v seznamu Editoru není uveden žádný parametr a musíme
je přidat manuálně. Dále některé typy databází nevracejí všechny informace
o parametrech, např. typy. Pro tyto databázové systémy, použijeme Průzkumník
SQL k prohlédnutí procedur, určení typů a potom provedeme konfiguraci parametrů
prostřednictvím Editoru a Inspektoru objektů. Kroky k nastavení parametrů
uložených procedur při návrhu jsou tyto:
-
Nepovinně nastavíme vlastnosti DatabaseName a StoredProcName.
-
V Inspektoru objektů, vyvoláme Editor parametrů stisknutím
tlačítka se třemi tečkami ve vlastnosti Params.
-
Pokud vlastnosti DatabaseName a StoredProcName
nejsou nastaveny, v seznamu editoru nejsou uvedeny žádné parametry. Manuálně
přidáme definice parametrů volbou Add v místní nabídce Editoru.
-
Vybíráme jednotlivé parametry v Editoru k zobrazení jejich
vlastností v Inspektoru objektů.
-
Jestliže ve vlastnosti ParamType není automaticky
specifikován typ, pak typ parametru (Input, Output, Input/Output
nebo
Result)
vybereme z rozbalovacího seznamu vlastnosti.
-
Pokud datový typ není automaticky specifikován ve vlastnosti
DataType,
pak vybereme datový typ z rozbalovacího seznamu vlastnosti. (Pro návrat
výsledkové množiny z uložené procedury Oracle nastavíme typ položky na
Cursor.)
-
Vlastnost Value použijeme k případnému nastavení počáteční
hodnoty pro vstupní nebo vstupně/výstupní parametry.
Stisknutím pravého tlačítka myši nad Editorem vyvoláme místní
nabídku pro operování s definicemi parametrů. V závislosti na tom, zda
parametry jsou uvedeny nebo vybrány, povolené volby zahrnují: přidávání
nových parametrů, rušení existujících parametrů, přesun parametrů nahoru
a dolů v seznamu a výběr všech uvedených parametrů.
Můžeme definovat libovolný přidaný TParam, ale
atributy objektu
TParam musí odpovídat atributům parametru uložené
procedury na serveru. K editaci
TParam pro parametr, vybereme parametr
v Editoru a editujeme jeho vlastnosti v Inspektoru objektů.
Poznámka: Sybase, MS-SQL a Informix nevracejí
informace o typu parametrů. K zjištění těchto informací použijeme Průzkumník
SQL.
Poznámka: Informix nevrací informace o datových
typech. K zjištění těchto informací použijeme Průzkumník SQL.
Poznámka: Nikdy nemůžeme nastavovat hodnoty
pro výstupní a výsledkové parametry. Tyto typy parametrů získají své hodnoty
provedením uložené procedury.
Zjišťování informací o parametrech při návrhu
Pokud při návrhu máme přístup k databázovému serveru,
pak jsou dvě možnosti k zobrazení informací o parametrech použitých uloženou
procedurou:
-
Vyvoláme Průzkumník SQL k prohlédnutí zdrojového kódu procedury
na vzdáleném serveru. Zdrojový kód obsahuje deklarace parametrů, které
identifikují jména a datové typy pro každý parametr.
-
Použijeme Inspektor objektů k zobrazení nastavení vlastností
pro jednotlivé objekty TParam.
Průzkumník SQL můžeme použít ke zkoumání uložených procedur
na našem databázovém serveru, pokud používáme přirozené ovladače BDE. Při
použití ovladačů ODBC nelze zkoumat uloženou proceduru pomocí Průzkumníka
SQL. To se týká i Inspektora objektů. Množství informací vrácených o uložené
proceduře v Inspektoru objektů závisí na našem databázovém serveru.
K zobrazení definicí jednotlivých parametrů v Inspektoru
objektů:
-
Vybereme komponentu uložené procedury.
-
Nastavíme vlastnost DatabaseName komponenty uložené
procedury na přezdívku BDE pro náš databázový server (nebo vlastnost DatabaseName
komponenty databáze).
-
Nastavíme vlastnost StoredProcName na jméno uložené
procedury.
-
Stiskneme tlačítko se třemi tečkami pro vlastnost Params
v Inspektoru objektů.
-
Vybíráme jednotlivé parametry v Editoru k zobrazování a nastavení
jejich vlastností v Inspektoru objektů.
Pro některé servery některé nebo všechny informace nemusí
být dostupné.
V Inspektoru objektů, když zobrazujeme jednotlivé objekty
TParam,
pak ParamType indikuje zda vybraný parametr je vstupní, výstupní
atd.. Vlastnost DataType indikuje datový typ hodnoty parametru,
jako je řetězec, celé číslo apod. Vlastnost Value umožňuje zadávat
hodnotu pro vybraný vstupní parametr.
Poznámka: Sybase, MS-SQL a Informix nevracejí
informace o typu parametrů. K zjištění těchto informací použijeme Průzkumník
SQL.
Poznámka: Informix nevrací informace o datových
typech. K zjištění těchto informací použijeme Průzkumník SQL.
Poznámka: Nikdy nemůžeme nastavovat hodnoty
pro výstupní a výsledkové parametry. Tyto typy parametrů získají své hodnoty
provedením uložené procedury.
Vytváření parametrů za běhu
Pokud jméno uložené procedury není specifikováno v StoredProcName
před spuštěním, pak pro parametry nejsou automaticky vytvářeny objekty
TParam
a musíme je vytvářet programově. To můžeme provést instantizací nového
objektu TParam nebo metodou TParams::AddParam.
Např. uložená procedura InterBase GET_EMP_PROJ, uvedená
dále požaduje jeden vstupní parametr (EMP_NO) a jeden výstupní parametr
(PROJ_ID).
CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :PROJ_ID
DO
SUSPEND;
END
Kód C++ Builderu k přiřazení této uložené procedury k
TStoredProc
nazvané StoredProc1 a vytvoření objektů TParam pro dva parametry
je:
{
TParam *P1, *P2;
...
StoredProc1->StoredProcName = "GET_EMP_PROJ";
StoredProc1->Params->Clear();
P1 = new TParam(StoredProc1->Params,
ptInput);
P2 = new TParam(StoredProc1->Params,
ptOutput);
try
{
StoredProc1->Params[0]->Name
= "EMP_NO";
StoredProc1->Params[1]->Name
= "PROJ_ID";
StoredProc1->ParamByName("EMP_NO")->AsSmallInt
= 52;
StoredProc1->ExecProc();
Edit1->Text = StoredProc1->ParamByName("PROJ_ID")->AsString;
}
__finally
{
delete P1;
delete P2;
}
}
Spojování parametrů
Když připravujeme a provádíme uloženou proceduru, pak její
vstupní parametry jsou automaticky spojeny s parametry na serveru. Vlastnost
ParamBindMode
použijeme ke specifikaci způsobu spojení parametrů v naší uložené proceduře
k parametrům na serveru. Implicitně ParamBindMode je nastaven na
pbByName,
což znamená, že parametry z komponenty uložené procedury jsou připojeny
k parametrům na serveru na základě jména. To je nejsnadnější metoda spojování
parametrů.
Některé servery také podporují spojování parametrů pomocí
jejich pořadí. V tomto případě, pořadí ve kterém specifikujeme parametry
v Editoru parametrů je důležité. První námi specifikovaný parametr musí
odpovídat prvnímu parametru na serveru, druhý parametr druhému parametru
na serveru, atd. Pokud náš server podporuje spojování parametrů na základě
pořadí, pak musíme nastavit ParamBindMode na pbByNumber.
Tip: Pokud chceme nastavit ParamBindMode
na pbByNumber, pak musíme specifikovat odpovídající typy parametrů
v odpovídajícím pořadí. Zdrojový kód uložené procedury si můžeme prohlédnout
Průzkumníkem SQL k určení správného pořadí a typů parametrů.
Práce s uloženými procedurami
Oracle
Servery Oracle umožňují překrývání uložených procedur; překryté
procedury jsou různé procedury se stejným jménem. Vlastnost Overload
komponenty uložené procedury povoluje aplikaci specifikovat proceduru k
provedení. Pokud Overload je nulové (implicitně), pak se předpokládá,
že není překrývání. Pokud Overload je 1, pak komponenta uložené
procedury provede první uloženou proceduru, kterou nalezne na serveru Oracle,
který má překrývající jméno; pokud je 2, pak je provedena druhá,
atd.
Poznámka: Překryté uložené procedury mohou
také mít různé vstupní a výstupní parametry.
-
Nyní se podíváme na několik již hotových programů. Netýkají se uložených
procedur, ale řeší různé problémy. První z nich nalezneme v adresáři Program
files\Borland\CBuilder\Examples\DBTask\MdxBuild. Jedná se o jednoduchou
aplikaci budující indexy pro databázové tabulky Paradoxu. Program je používán
k odstranění chyb Index Out Of Date vyskytujících se při spouštění
aplikací používající tabulky Paradoxu. Můžeme přebudovat některé nebo všechny
tabulky dané přezdívky. Při používání programu zvolíme přezdívku obsahující
opravované tabulky. Po zvolení přezdívky je aktualizován seznam tabulek,
kde zvolíme požadovanou tabulku nebo All Tables. K přebudování indexů
na zvolených tabulkách potom stiskneme tlačítko Build Indexes. Na
stavovém řádku je popisován průběh zpracování. Prohlédněte si také tuto
aplikaci a zjistěte jak pracuje.
-
Další program umístěný v adresáři Program files\Borland\CBuilder\Examples\DBTask\QBFDemo
ukazuje jak vytvářet vlastní dotazy. Prohlédněte si tuto aplikaci a zjistěte
jak pracuje.
-
Aplikace umístěná v adresáři Program files\Borland\CBuilder\Examples\DBTask\DBErrors
ukazuje použití datového modulu pro centralizované kódování, používání
vazby 1 : N : M a generování a řízení databázových chyb. Aplikaci vyzkoušejte
a podívejte se jak pracuje.
-
Aplikace z adresáře Program files\Borland\CBuilder\Examples\DBTask\BKQuery
ukazuje použití vícevláknových SQL dotazů. Použivají se zde také zdroje
definované uživatelem. Aplikaci vyzkoušejte a prohlédněte si ji.
-
Aplikace uložená v adresáři Program files\Borland\CBuilder\Examples\DBTask\CSDemos
je klientská aplikace InterBase. V souboru Readme.txt je umístěn
popis jejího spouštění. Aplikaci vyzkoušejte.
-
Poslední aplikace se kterou se v této kapitole budeme zabývat je uložená
v adresáři Program files\Borland\CBuilder\Examples\DBTask\IBDemo.
Tato aplikace ukazuje jak používat události InterBase. Aplikaci vyzkoušejte
a prohlédněte si ji.
   |
18. Práce s uloženými procedurami
|