Tárolt eljárások

• A tárolt eljárások olyan SQL kódok, amelyeket el tudunk menteni, hogy később újra és újra fel tudjuk használni azokat.

  • Hasznos például olyan lekérdezések esetén, amelyeket gyakran meg kell írni. Ilyen esetben célszerűbb elmenteni a kódot eljárásként és csak meghívni azt.

• Fontos: Az eljárások és a függvények definíciójában és használatában is jelentős eltérések találhatók különböző adatbáziskezelő rendszerek között.

• Az eljárások támogatják a paraméterek fogadását is.

  • IN: Bemenő értékek.
  • OUT: Visszaadott értékek. Hasonlóan működik, mint a return kulcsszó. Az OUT paraméterben eltárolt értékek visszaadásra kerülnek a hívó fél számára.
  • INOUT: Néhány adatbáziskezelő motor támogatja, hogy egy paraméter mindkét irányba adhasson adatot. Ilyenkor az eljárás futása után a paraméterben tárolt adat visszaadásra is kerül.

• Eljárásokat a CREATE PROCEDURE paranccsal tudunk definiálni. Ehhez azonban a DELIMITER paranccsal meg kell változtatnunk az alapértelmezett parancshatároló karaktert.

  DELIMITER //
  CREATE PROCEDURE procedure_name()
  BEGIN
  	sql_parancsok;
  END//
  DELIMITER ;
  

• Egy már létrehozott eljárást az CALL paranccsal tudunk végrehajtani.

  CALL procedure_name();
  

• Paraméterek megadásához az eljárás definiálásakor, a név után zárójelben kell megadnunk a paraméterek nevét annak adattípusával.

  DELIMITER //
  CREATE PROCEDURE selectallcustomers (IN cityname varchar(30))
  BEGIN
  	SELECT * FROM customers WHERE city = cityname;
  END//
  DELIMITER ;
  

• Természetesen a paraméterrel rendelkező eljárásoknál azok meghívásakor meg kell adnunk az eljárás argumentumait, figyelve azok sorrendjére.

  CALL selectallcustomers('London');
  

• Több paramétert is megadhatunk, azokat egymástól vesszővel , elválasztva.

  DELIMITER //
  CREATE PROCEDURE selectallcustomers (IN cityname varchar(30), IN pcode varchar(10))
  BEGIN
  	SELECT * FROM customers WHERE city = cityname AND postalcode = pcode;
  END//
  DELIMITER ;
  

  CALL selectallcustomers('London','WA1 1DP');
  

Feladat: Népsűrűség megye alapján

• Hozzunk létre egy tárolt eljárást nepsuruseg néven, amely lekérdezi az mo_telepulesek adatbázis varosok táblából Pest megye népsűrűségét 3 tizedesjegyre kerekítve megye_nepsuruseg aliassal.

  mo_telepulesek.sql

  SELECT ROUND(SUM(nepesseg) / SUM(terulet),3) AS megye_nepsuruseg FROM varosok
  WHERE megye = 'Pest';
  

  DELIMITER //
  CREATE PROCEDURE nepsuruseg()
  BEGIN
      SELECT ROUND(SUM(nepesseg) / SUM(terulet),3) AS megye_nepsuruseg FROM varosok
      WHERE megye = 'Pest';
  END//
  DELIMITER ;
  

  CALL nepsuruseg();
  

• Az előző lépések alapján hozzunk létre egy új tárolt eljárást nepsuruseg2 néven, amely az eredetitől annyiban tér el, hogy paraméterként fogadja annak a megyének a nevét, amelynek a népsűrűségét szeretnénk kiszámolni.

  DELIMITER //
  CREATE PROCEDURE nepsuruseg2(IN megyenev varchar(255))
  BEGIN
      SELECT ROUND(SUM(nepesseg) / SUM(terulet),3) AS megye_nepsuruseg FROM varosok
      WHERE megye = megyenev;
  END//
  DELIMITER ;
  

  CALL nepsuruseg2('Pest');
  CALL nepsuruseg2('Hajdú-Bihar');
  

Feladat: Átlagos népesség

• Hozzunk létre egy eljárást avgnepesseg néven, amely kilistázza az mo_telepulesek adatbázis varosok táblájából a megyéket és az átlagos népességüket atlagos_nepesseg néven.

  DELIMITER //
  CREATE PROCEDURE avgnepesseg()
  BEGIN
      SELECT megye, AVG(nepesseg) AS atlag_nepesseg FROM varosok
      GROUP BY megye;
  END//
  DELIMITER ;
  

  CALL avgnepesseg();
  

Felhasználó által definiált függvények

• Az SQL lehetőséget kínál arra, hogy a más programnyelveken már használt függvényekhez hasonlóan a felhasználó saját függvényeket definiáljon.

  • Ezzel elkerülhető, hogy ugyanazt a kódot újra és újra meg kelljen írnia a felhasználónak amikor használni akarja.

• A függvények esetében megadhatjuk, hogy milyen bemenő adatokon történjen a munkavégzés, illetve, hogy az outputként kapott adat vagy adatok milyen típusúak és szerkezetűek legyenek.

  • Emellett még a jogosultságok kezelésével a függvények használatát korlátozhatjuk is.

• Bár a függvények hasznos eszköznek bizonyulnak, sok esetben a használatuk kikerülhető. Érdemes mindig megfontolni, hogy célszerűbb-e szoftver oldalon kódolni az adott munkafolyamatot.

• A függvények működési elve:

  • Paramétereket fogad.
  • A paraméterként fogadott adatokkal műveleteket hajt végre (SQL parancsok).
  • A műveletek eredményeképp kapott értéket vagy értékeket visszaadja.

• Függvényt létrehozni a CREATE FUNCTION paranccsal lehetséges:

  DELIMITER $$
  CREATE FUNCTION [database_name.]function_name (parameters)
  RETURNS data_type
  BEGIN
      SQL statements;
      RETURN value;
  END$$
  DELIMITER ;
  

  DELIMITER $$
  CREATE FUNCTION teszt (
  	nepessegteszt int
  )
  RETURNS CHAR(4)
  BEGIN
  		DECLARE return_value CHAR(4);
      IF (nepessegteszt > 10) THEN SET return_value = 'igen';
      END IF;
      IF (nepessegteszt < 10) THEN SET return_value = 'nem';
      END IF;
   
      RETURN return_value;
  END$$
  DELIMITER ;
  

  DELIMITER $$
  CREATE FUNCTION east_or_west (
  	long DECIMAL(9,6)
  )
  RETURNS CHAR(4)
  BEGIN
  			DECLARE return_value CHAR(4);
  			SET return_value = 'same';
  	    IF (long > 0.00) THEN SET return_value = 'east';	
  	    ELSEIF (long < 0.00) THEN SET return_value = 'west';
  	    END IF;
   
  	    RETURN return_value;
  END$$
  DELIMITER ;
  

  • Ebben az esetben a függvény egyszerű skaláris adattal fog visszatérni.