email tisk

2008-03-10 20:23:31 | zobrazeno: 17015x
Praktické použití uložených procedur a triggerů na SŘBD: MySql5, Firebird a Oracle. Ukázka volání sql dotazů a uložených procedur z Php5.

Úvod

Tento článek by měl být hlavně praktickou ukázkou použití uložených procedur a triggerů na SŘBD MySql5. Zkusím se i zmínit o rozdílech oproti Oracle a Firebird.

ERA model, aneb na čem to celé ukázat

Ukázky budou přímo z mojí semestrální práce z předmětu DB2 na Fav ZČU v Plzni. Úkolem této semestrální práce je vytvořit webovou aplikaci, která bude obsahovat minimálně 6 tabulek, 2 triggery a 2 uložené procedury. Jako téma semestrální práce bylo zvoleno: Útulek. Tato aplikace by měla umožnit jednoduchou správu zvířátek v útulku, evidenci ubytovacích kapacit a přehled o příjmech a výdajích.

Následující ERA model by měl dostatečně jasně znázornit datovou strukturu, která bude pro ukázkové příklady používána.

 

 

SQL skipt pro vygenerování tabulek - klidněte to můžete přeskočit

Vím, že je to trošku nuda, neboť určitě si každý dokáže představit, jak by tabulky dle ERA modelu měly vypadat. Avšak vzhledem k tomu, že tento článek může číst i začátečník, považuji za vhodné sem SQL dotazy pro vytvoření tabulek vložit. Pokud Vás to nezajímá, tak to klidně přeskočte. Jen se podívejde na konec kódu, kde je zápis referenční integrity.

- tato tabulka bude sloužit pro vlastní evidenci zvířátek v útulku:

Create table `utulek_zviratka` (
`id` Int NOT NULL AUTO_INCREMENT,
`type_id` Int NOT NULL,
`home_id` Int NOT NULL,
`name` Varchar(30) NOT NULL,
`type_note` Varchar(30),
`note` Text,
`obr` Varchar(30) DEFAULT 'neni.jpg',
`created_date` Datetime,
Index `AI_id` (`id`),
Primary Key (`id`,`type_id`,`home_id`)) ENGINE = MyISAM;

 

- uložení uživatelů - zaměstnaců útulku a sponzorů, v type je č. práva: 0 sponzor, 1 brigádník, 3 správce, 9 admin

Create table `utulek_users` (
`id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
`login` Varchar(50) NOT NULL,
`password` Varchar(100) NOT NULL,
`name` Varchar(50) NOT NULL,
`surname` Varchar(50) NOT NULL,
`type` Int DEFAULT 0,
UNIQUE (`login`),
Index `AI_id` (`id`),
Primary Key (`id`)) ENGINE = MyISAM;

 

- tato tabulka slouží k evidenci ubytovacích kapacit:

 Create table `utulek_zviratka_ubytovani` (
    `id` Int NOT NULL AUTO_INCREMENT,
    `home_name` Varchar(30) NOT NULL,
    `home_type` Varchar(30),
    `capacity` Int UNSIGNED DEFAULT 1,
    `inside` Int DEFAULT 0,
    `price` Int DEFAULT 0,
    UNIQUE (`home_name`),
    Index `AI_id` (`id`),
 Primary Key (`id`)) ENGINE = MyISAM;

 

- evidence typů zvířátek - př. kočka, pes apod. Druh zvířete se píše do pole type_note v tabulce utulek_zviratka.

Create table `utulek_zviratka_types` (
    `id` Int NOT NULL AUTO_INCREMENT,
    `type_name` Varchar(30) NOT NULL,
    `home_type` Varchar(30),
    `food_type` Varchar(30),
    `food_number` Float UNSIGNED DEFAULT 0.1,
    UNIQUE (`type_name`),
    Index `AI_id` (`id`),
 Primary Key (`id`)) ENGINE = `price` Int UNSIGNED DEFAULT 0,
    UNIQUE (`food_name`),
    Index `AI_id` (`id`),
 Primary Key (`id`)) ENGINE = MyISAM;

 

- seznam pravidelných sponzorských darů - částka a období, kdy daný sponzor pravidelně posílá peníze

Create table `utulek_users_sponzori` (
    `id` Int NOT NULL AUTO_INCREMENT,
    `user_id` Int UNSIGNED NOT NULL,
    `amount` Int DEFAULT 0,
    `period` Int UNSIGNED DEFAULT 0,
    Index `AI_id` (`id`),
 Primary Key (`id`,`user_id`)) ENGINE = MyISAM;

 

- Tabulka pro rozklad vazby M:N mezi tabulkami zviratka a zviratka_jidlo, teoreticky se předpokládá, že jedno zvíře může žrát více typů potravy. Tato tabulka je vytvořena uměle bez nějakého hlubšího významu, jen z důvodu, že v semestrální práci musí být rozklad vazby M:N.

Create table `utulek_zviratko_zere` (
`id` Int NOT NULL AUTO_INCREMENT,
jidlo_id` Int NOT NULL,
`zvire_id` Int,
`type_id` Int,
home_id` Int,
Primary Key (`id`,`jidlo_id`)) ENGINE = MyISAM;

 

- zajištění referenční integrity:

Alter table `utulek_users_sponzori` add Foreign Key (`user_id`) references `utulek_users` (`id`) on delete  restrict on update  restrict;
Alter table `utulek_zviratko_zere` add Foreign Key (`zvire_id`,`type_id`,`home_id`) references `utulek_zviratka` (`id`,`type_id`,`home_id`) on delete  restrict on update  restrict;
Alter table `utulek_zviratka` add Foreign Key (`home_id`) references `utulek_zviratka_ubytovani` (`id`) on delete  restrict on update  restrict;
Alter table `utulek_zviratka` add Foreign Key (`type_id`) references `utulek_zviratka_types` (`id`) on delete  restrict on update  restrict;
Alter table `utulek_zviratko_zere` add Foreign Key (`jidlo_id`) references `utulek_zviratka_jidlo` (`id`) on delete  restrict on update  restrict;

 

Uložené procedury

Uložené procedury jsou kusy kódu, který je uložený přímo na serveru v databázi. Slouží hlavně k vykonávání pravidelných operací, které zajišťují referenční integritu. Může se jednat např. o  kontrolu kapacity ubytování, než do ní vložíme nové zvířátko apod. Přestože se uložené procedury jeví jako poměrně silným nástrojem, který bychom měli hojně používat, tak jsme na omylu. Uložené procedury se vyplatí pouze v případě volání většího množství sql dotazů, nebo nutnosti automaticky zpracovat hodně dat bez zásahu uživatele. Pokud uložená procedura bude provádět pouze nějakou jednoduchou operaci, tak bude Vaši aplikaci akorát zdržovat !

Vytvoření uložené procedury s parametrem

Při vytváření uložené procedury z konzole musíme napřed použít příkaz DELIMITER, kterým určíme oddělení jednotlivých sql dotazů. Pokud použijeme PhpMyAdmin, tak pouze pod oknem pro provedení sql dotazu nastavte Delimiter: //. V samotném okně pro vykonání sql dotazu pak již příkaz delimiter nepoužívejte.

Tato procedura se bude volat z těla triggeru. Takto to funguje na MySql5.

DELIMITER //
CREATE PROCEDURE delete_animal_from_zviratko_zere(cislo int)
begin
DELETE from utulek_zviratko_zere WHERE `zvire_id` = cislo;
end;//

DELIMITER ;

Vzhledem k tomu, že v MySQL5 nelze zrušit akci z těla triggeru, tak si vytvoříme uloženou proceduru pro přidání zvířátka do útulku. Poznámka: akci, pro kterou je trigger definován, lze z těla triggeru zrušit pouze vyvoláním chyby. Toto řešení se mi moc nelíbí a proto vytvářím uloženou proceduru pro vložení zvířátka do útulku. Nejdříve Vám ukážu kód pro vytvoření uložené procedury:

create procedure insert_animal(
name varchar(30), obr varchar(30), type_note varchar(30),
note text,
typeid int,
homeid int,
jidloid int,
OUT result varchar(50))
begin
DECLARE zvire_id INT(10);
DECLARE varhome_id INT(10);
DECLARE varinside INT(10);
DECLARE varcapacity INT(10);

select home_id, inside, capacity into varhome_id, varinside, varcapacity from utulek_obsazenost where `home_id`=homeid;

if (varcapacity > varinside) then
begin
insert into utulek_zviratka
(`name`, `obr`, `type_note`, `note`, `type_id`,`home_id`, `created_date`) values
(name, obr, type_note, note, typeid, homeid, now());

set zvire_id = LAST_INSERT_ID();
insert into utulek_zviratko_zere (`jidlo_id`,`zvire_id`) values (jidloid, zvire_id);

set result = zvire_id;
end;
else set result = "Nevejde se";
end if;
end;//

Komentář: parametry procedury mohou být buď vstupní, nebo výstupní. Formát vložení: [IN/OUT] [jméno proměnné] [typ]. Pokud není uvedeno IN/OUT, tak se automaticky jedná o vstupní proměnnou. Pomocné proměnné se musí v těle procedury deklarovat přímo s klíčovým slovem DECLARE. Po vložení zvířete příkazem INSERT si můžeme vyžádat ID aktuálně vloženého zvířete s pomocí MySQL funkce: LAST_INSERT_ID(). Tuto funkci musíme volat ihned po zavolání INSERT, abychom zabránili tomu, že nám mezitím někdo jiný vloží do databáze další záznam.

Výstupem procedury je buď ID zvířete, pokud se vložení podaří, nebo text "Nevejde se", pokud se do útulku zvíře nejvejde.

 

Volání uložené procedury

Uloženou proceduru můžeme volat přímo: CALL název procedury(parametry, @vystup);
Výsledek zjistíme příkazem SELECT: select @vystup;

 

Volání uložené procedury z Php5

Protože nežijeme v ideálním světe, tak z Php5 volání uložených procedur tak snadné nebude. Musíme k tomu využít rozšíření mysqli, které nám umožní využívat výhod MySql5.

Volání vypadá následovně:

// PRIDAT ZVIRE PRES ULOZENOU PROCEDURU
$mysqli = new mysqli("localhost", "root", "", "martanek");

// zkontrolovat pripojeni
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}


// vytvorit pripraveny sql dotaz = prepared statement
// POZNAMKA: fakt se to musi udelat takto, jinak to nefunguje
$stmt = $mysqli->stmt_init();

// volat proceduru insert_animal se 7mi vstupnimi parametry a 1 vystupnim parametrem
if ($stmt->prepare("CALL insert_animal(?, ?, ?, ?, ?, ?, ?, @vysledek);"))
{
/* bind parameters for markers, i - integer, d - double, s - string, b - blob */
// 1. parametr: typy vstupnich promennych, musi byt stejne jako v definici procedury, dalsi parametry: promenne
$stmt->bind_param("ssssiii", $name, $obr, $type_note, $note, $type_id, $home_id, $food_id);

// vykonat tento pripraveny sql dotaz
$stmt->execute();

// nyni se potrebuju dostat k vysledku:
$stmt = $mysqli->prepare("SELECT @vysledek");
$vysledek = "";

// navazani vystupni promenne na statement
$stmt->bind_result($vysledek);
$stmt->execute(); // POZOR tohle tady musi byt, musim ten statement zavolat

/* dojde k navazani vysledku ulozene procedury do danych promennych */
$stmt->fetch();
}

 

Triggery

Triggery jsou skripty, které se na daném SŘBD vykonávání před/po provedení dané operace nad tabulkou, pro kterou jsou definovány.

Příklad asi mluví za vše (MySQL5):

CREATE TRIGGER after_delete_user 
AFTER DELETE ON utulek_users
FOR EACH ROW
BEGIN
DELETE from utulek_users_sponzori WHERE `user_id` = old.id;
END;//

Trigger může být definován: AFTER/BEFORE INSERT/DELETE/UPDATE on název_tabulky FOR: pro jaké záznamy.

K původním datům mazaného záznamu můžeme přistoupit přes: OLD - u triggeru např. AFTER DELETE. K nově vkládaným datům lze přistoupit přes NEW - např. u triggeru BEFORE INSERT.

 

Další příklad ukazuje použití triggeru po přidání nového zvířete. Musím upozornit na možnost uložit data z SQL dotazu do proměnné: select count(*) into inside. Pokud chcete do proměnné něco přiřadit, musíte použít: SET.

CREATE TRIGGER after_insert_new_zvire 
AFTER INSERT ON utulek_zviratka
FOR EACH ROW
BEGIN
DECLARE inside INT(10);
select count(*) into inside from utulek_zviratka where home_id=new.home_id;
update `utulek_zviratka_ubytovani` set `inside`=inside where id=new.home_id limit 1;
END;//

 

Ukázka volání uložené procedury z těla triggeru:

CREATE TRIGGER after_delete_new_zvire 
AFTER DELETE ON utulek_zviratka
FOR EACH ROW
BEGIN
DECLARE inside INT(10);
select count(*) into inside from utulek_zviratka where home_id=old.home_id;

update `utulek_zviratka_ubytovani` set `inside`=inside where id=old.home_id limit 1;
call delete_animal_from_zviratko_zere(old.id);
END;//

 

 

Oracle

Oracle a triggery

O čem je následující text?
Níže naleznete přehled funkcionality triggerů a uložených procedur v SŘBD. Nechci kopírovat a překládat manuál, jen chci shrnout hlavní rozdíly mezi MySQL5 a Oracle. Jedná se spíše o teorii, než množství příkladů pro Oracle. Praktický přehled funkcionality Oracle naleznete v dalším článku.

Co to je trigger ?
Trigger je procedura, která je automaticky spouštěna, když se něco stane. Mohu ho definovat na DML (modifikace) i DDL (vytváření) operace. A navíc mohu říct, jestli se má provést před, nebo po dané operaci. (BEFORE, AFTER) Zajímavostí je, že trigger se může jmenovat stejně jako tabulka (avšak není to doporučováno) a že lze definovat libovolný počet triggerů na stejnou tabulku i událost. Avšak pozor: nelze určit pořadí triggerů, ve kterém budou volány a triggery nesmí na sobě vzájemně záviset. V triggerech se také nesmí využívat rekurzivní volání. V těle triggeru lze provádět libovolné SQL dotazy - INSERT, UPDATE, DELETE. Avšak pokud chcete použít dotaz SELECT, musíte ho využít v kombinaci s klíčovým slovem INTO, které zajistí uložení načtených dat do lokálních proměnných, nebo kurzoru.

Typy triggerů

  • DML triggery na tabulkách

  • INSTEAD OF triggery na pohledech (views)

  • Systémové triggery na DATABASE or SCHEMA: DATABASE - triggery se spustí na každou událost pro všechny uživatele, SCHEMA - triggery se spustí na každou událost pro zvoleného uživatele 

Kontrola systémových událostí s využitím triggerů

Můžeme definovat triggery pro DML příkazy (DELETE, INSERT, UPDATE), DDL příkazy (CREATE, ALTER, DROP), nebo databázové operace (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN). Triggery se budou tedy spouštět např. na všechny tabulky, pro které jsou definovány a do které se budeme snažit něco vkládat, měnit, nebo pokud z ní budeme chtít mazat.

Zajímavost: lze definovat update trigger, který se bude spoštět pouze v případě updatu konkrétních sloupců.
Ukázka (Zamestnanci - tabulka, name - sloupec):

.. BEFORE DELETE OR INSERT OR UPDATE OF name ON Zamestnanci ... 

 

Kdy je trigger volán ?

Triggery jsou volány buď na systémové události, nebo na DML a DDL příkazy. Trigger lze definovat pouze na jednu, nebo i na více událostí. To znamená, že můžeme trigger definovat buď pouze na INSERT, nebo i na INSERT, UPDATE i DELETE zároveň. Případně na libovolnou kombinaci těchto tří.

Pokud budeme trigger volat na více událostí zároveň, tak by bylo dobré v těle triggeru poznat, která z těchto událostí nám daný trigger spustila. Lze to udělat takto:

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;

 Ukázka triggeru. Lze použít klíčových slov :old a :new pro přístup k novým/starým datům v datém řádku:

CREATE OR REPLACE TRIGGER plat_zmeny
BEFORE DELETE OR INSERT OR UPDATE ON zamestnanci
FOR EACH ROW
WHEN (new.cislo_zamestnance > 0)
DECLARE
plat_zmena number;
BEGIN
plat_zmena := :new.plat - :old.plat;
dbms_output.put('Puvodni mzda: ' || :old.plat);
dbms_output.put(' Nova mzda: ' || :new.plat);
dbms_output.put_line(' Rozdil ' || plat_zmena);
END;
/

Tento trigger vypíše na dbms output informaci vždy, když se změní plat zaměstnance. Všimněte si podmínky WHEN (new.cislo_zamestnance > 0). To znamená, že trigger se spustí pouze v případě, že máme k dispozici číslo zaměstnance. Pokud není k dispozici, tak se pravděpodobně jedná o vložení nového zaměstnance, nebo nějakou chybu = nemá cenu volat trigger pro výpočet změny mzdy.
Poznámka: abyste viděli tento výstup triggeru, tak se např. v SQLDeveloperu musíte přepnout na záložku dbms output.

 

Zpracování vyjímek

Triggery a ošetření vyjímek

CREATE OR REPLACE TRIGGER Example
BEFORE UPDATE ON Emp_tab
FOR EACH ROW
BEGIN
  UPDATE ... WHERE ID = :new.ID:
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO Emp_log2
    VALUES ('x');
END;

Others zachytí všechny výjimky.

Zrušení akce - vyvovální exception

V těle triggeru lze definovat podmínku, která např. akci vkládání zruší.
Př.

If (cena < :new.cena) then
      raise_application_error(-20001, 'Cena mimo rozsah.');
end if;

Číslo chyby si mohu definovat sám, ale každá musí mít jiné číslo, abychom se na to mohli odkazovat např. z manuálu.

Vypnutí - zapnutí triggeru

ALTER TRIGGER nazev ENABLE/DISABLE;

Poznámky: V těle triggeru můžeme např. přidávání nového záznamu zastavit s využitím WHEN. Další informace lze nalézt v manuálu: Oracle Application Developer’s Guide –Fundamentals.

Další použití triggerů:  mezi hlavní důvody použití triggerů patří automatické generování a vkládání např. interního primárního klíče. V triggeru můžeme klíč generovat např. jako MAX(id) + 1, což je maximální hodnota zvětšená o jedna. Avšak lepší řešení je využití Sekvencí.

 

Sekvence, aneb všude jinde se tomu říká generátory

Sekvence slouží pro automatické generování nových hodnot. Automaticky řeší paralelní přístup a další záležitosti, které bychom jinak museli řešit explicitně.

CREATE SEQUENCE jméno
   [INCREMENT BY] <celé číslo>   /* default 1 */
   [START WITH]   <celé číslo>   /* default 1 */
   [MAXVALUE]     <celé číslo>   /* default 10^27 */
   [MINVALUE]     <celé číslo>   /* default 1 */
   [CYCLE | NOCYCLE]             /* default NOCYCLE */ 

Význam jednotlivých klauzulí je následující :

  • INCREMENT BY - hodnota o kterou má být čítač zvětšen (může být i záporná)
  • START WITH - hodnota od které má čítač začít (hodí se chceme-li sekvenci použít pro generování klíče do tabulky, ve které jsou již data)
  • MAXVALUE - nejvyšší povolená hodnota čítače
  • NOCYCLE - při překročení MAXVALUE nastane chyba
  • CYCLE - při překročení MAXVALUE začne čítač znova od MINVALUE

Každá sekvence obsahuje dva pseudosloupce, CURRVAL a NEXTVAL. Pseudosloupec CURRVAL poskytuje aktuální hodnotu čítače sekvence, NEXTVAL aktuální hodnotu čítače zvedne o požadovaný přírůstek a tuto hodnotu poskytuje.

Ukázka ze cv DB2 - č. 6:

CREATE TABLE pracoviste (
  id_prac  NUMBER(5)    PRIMARY KEY,
  nazev    VARCHAR2(30) NOT NULL
);

CREATE SEQUENCE seq_id_prac MAXVALUE 99999

CREATE TRIGGER trbi_pracoviste
  BEFORE INSERT ON pracoviste
  FOR EACH ROW
BEGIN
   SELECT seq_id_prac.NEXTVAL INTO :new.id_prac FROM DUAL;
END;
/

Nyní můžeme otestovat funkčnost automatického číslování :

INSERT INTO pracoviste (nazev) VALUES ('KIV');
INSERT INTO pracoviste VALUES (NULL, 'CIV'); 

Poznámka: tento kousek o sekvencích byl vykopírován ze cvičení DB2 - č. 6.

 

 Další využití triggerů

V těle triggeru lze volat např. i uložené procedury. Lze to udělat takto: call nazev_procedury(parametry);. Dokonce můžeme v těle triggeru volat i procedury z jiných programovacích jazyků - např. z Javy:

Trigger:

CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS language Java
name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab
FOR EACH ROW
CALL Before_delete (:old.Id, :old.Ename)
/

Skript v jazyce Java - v souboru thjvTriggers.java:

import java.sql.*
import java.io.*
import oracle.sql.*
import oracle.oracore.*
public class thjvTriggers
{
public state void
beforeDelete (NUMBER old_id, CHAR old_name)
Throws SQLException, CoreException
{
Connection conn = JDBCConnection.defaultConnection();
Statement stmt = conn.CreateStatement();
String sql = "insert into logtab values
("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE');
stmt.executeUpdate (sql);
stmt.close();
return;
}
}

Tato ukázka byla částečně převzata z manuálu k Oracle.

 

Firebird

Triggery se u Firebirdu, stejně jako u Oracle, používají hlavně v kombinaci s generátory např. pro generování ID. Firebird ani Oracle nic typu AUTO INCREMENT, jako to známe z MySQL, neumí.

Ukázka vytvoření tabulky na firebirdu:

CREATE TABLE smlouvy(
ID INTEGER NOT NULL,
nazev VARCHAR(200) CHARACTER SET WIN1250,
druh VARCHAR(50) CHARACTER SET WIN1250,
PRIMARY KEY (ID)
);

 

Přidání práv pro uživatele admin k tabulce smlouvy. Jinak má práva na tuto tabulku pouze uživatel, který ji vytvořil.

 GRANT SELECT, UPDATE, INSERT, DELETE on smlouvy to admin;

Pokud chceme používat interní primární klíč, tak si musíme založit generátor, který nám ho vytvoří. 

 CREATE GENERATOR GEN_smlouvy;  SET TERM ^ ; 

 

Vytvoření triggeru, který nám zajistí volání generátoru a vkládání ID do tabulky při založení nového záznamu. GEN_ID - volání generátoru (název, kolik kódů chci vrátit). 

CREATE TRIGGER TRI_smlouvy FOR smlouvy
ACTIVE BEFORE INSERT POSITION 0 AS
begin
new.ID = GEN_ID(GEN_smlouvy,1);
end;

SET TERM ; ^

 

Poznámka: tento článek ještě není dokončen. Byl zveřejněn dříve, aby tyto poznatky mohli používat kolegové z FAV. Z tohoto důvodu, prosím, omluvte horší kvalitu textu, překlepy a případné hrubky. Celý článek jsem psal v noci, avšak na kvalitu ukázkových kódu to nemá vliv. Všechny ukázky kódu jsou naprosto funkční.