Gespeicherte Prozeduren

oder im englischen, und auch gebräuchlicher "Stored procedures", sind Funktionen, die direkt im jeweiligen Datenbanksystem abgelegt werden.

Sie umfassen eine, oder auch mehrere SQL- Abfragen, die danach durch den Aufruf der Stored Procedure ausgeführt werden. Durch die Tatsache, dass durch den Einsatz von gespeicherten Prozeduren weniger Daten zwischen der Applikation und der Datenbank transportiert werden, kann es bei größeren Systemen durchaus zur Leistungssteigerung beitragen.

Ein oft übersehener Faktor ist die Erhöhung der Sicherheit. Da die Applikation keine INSERT, UPDATE und DELETE Statements mehr senden muss/ kann, können SQL- Injection Angriffe zumindest im Ansatz verhindert werden.

Anlegen einer gespeicherten Prozedur

mysql> DELIMITER //
mysql> CREATE PROCEDURE radcleanup_stage1()
    -> BEGIN
    -> SELECT frontend.*
    -> FROM radcheck, frontend
    -> WHERE radcheck.UserName = frontend.username
    -> AND radcheck.UserName IN
    -> (
    -> SELECT radcheck.UserName
    -> FROM radcheck
    -> WHERE STR_TO_DATE(radcheck.Value, '%M %d %Y %H:%i:%s CEST') <= DATE_SUB(CURDATE(),INTERVAL 90 DAY)
    -> )
    -> GROUP BY frontend.username;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

Anzeigen vorhandener Prozeduren

mysql> show procedure status;

mysql> SHOW procedure status\G
*************************** 1. row ***************************
Db: radius
Name: radcleanup_stage1
Type: PROCEDURE
Definer: dbroot@localhost
Modified: 2013-09-24 14:06:12
Created: 2013-09-24 14:06:12
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_german1_ci

Anzeigen der Definition einer einzelnen Stored- Procedure

mysql> show create procedure radcleanup_stage1;

mysql> SHOW CREATE PROCEDURE radcleanup_stage1\G
*************************** 1. row ***************************
Procedure: radcleanup_stage1
sql_mode:
Create Procedure: CREATE DEFINER=`dbroot`@`localhost` PROCEDURE `radcleanup_stage1`()
BEGIN
DELETE frontend.*
FROM radcheck, frontend
WHERE radcheck.UserName = frontend.username
AND radcheck.UserName IN
(
SELECT radcheck.UserName
FROM radcheck
WHERE STR_TO_DATE(radcheck.Value, '%M %d %Y %H:%i:%s CEST') <= DATE_SUB(CURDATE(),INTERVAL 180 DAY)
);
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_german1_ci
1 row in set (0.00 sec)

<pre>mysql> SHOW procedure status\G
*************************** 1. row ***************************
Db: radius
Name: radcleanup_stage1
Type: PROCEDURE
Definer: dbroot@localhost
Modified: 2013-09-24 14:06:12
Created: 2013-09-24 14:06:12
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_german1_ci</pre>

mysql> SHOW CREATE PROCEDURE radcleanup_stage1\G
*************************** 1. row ***************************
           Procedure: radcleanup_stage1
            sql_mode:
    Create Procedure: CREATE DEFINER=`dbroot`@`localhost` PROCEDURE `radcleanup_stage1`()
BEGIN
DELETE frontend.*
FROM radcheck, frontend
WHERE radcheck.UserName = frontend.username
AND radcheck.UserName IN
(
SELECT radcheck.UserName
FROM radcheck
WHERE STR_TO_DATE(radcheck.Value, '%M %d %Y %H:%i:%s CEST') <= DATE_SUB(CURDATE(),INTERVAL 180 DAY)
);
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_german1_ci
1 row in set (0.00 sec)

Aufruf einer gespeicherten Prozedur

mysql> call radcleanup_stage1;

Löschen einer Stored Procedure

mysql> drop procedure radcleanup_stage1;