MySQL Stored Procedures: Creating, Calling and Managing Procedures

MySQL Stored Procedure Example

Procedure:

mysql> DELIMITER //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

-> BEGIN

-> SELECT COUNT(*) INTO param1 FROM t;

-> END//

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL simpleproc(@a);

Account Count Procedure

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

--DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `materiala_utzipro`
(dni CHAR(9), material INT (11))
BEGIN
-- Declare variables:
    DECLARE ikaslenan char(9);
    DECLARE ikasle_pena date;
    DECLARE ikaslematerial INT (11);
    DECLARE erabilimaterial INT (11);
    DECLARE erabilidata DATE;
    DECLARE pena DATE;
    DECLARE gaur DATE;
    DECLARE mezua1 varchar(100);
    DECLARE mezua2 varchar(100);
    DECLARE mezua3 varchar(100);
    DECLARE ondo varchar(100);
    DECLARE mezua4 varchar(100);
    SET mezua1 = 'You have entered incorrect data';
    SET mezua2 = 'This material is not available!!';
    SET mezua3 = 'You are late';
    SET ondo = 'Your request has been fulfilled';
    SET gaur = CURDATE();
    SET mezua4 = 'A penalty has been applied to you!';
-- Check student and material code
    SELECT ikasleak.nan INTO ikaslenan FROM ikasleak
    WHERE ikasleak.nan = dni;
    SELECT materiala.kode_materiala INTO ikaslematerial FROM materiala
    WHERE materiala.kode_materiala = material;
    IF ikaslenan IS NULL OR ikaslematerial IS NULL THEN SELECT mezua1;
    ELSE
    -- Check that the material is not currently taken
    SELECT erabili.materiala_kode_materiala INTO erabilimaterial FROM erabili
    WHERE erabili.materiala_kode_materiala = material;
    IF erabilimaterial IS NULL THEN SELECT mezua2;
    ELSE
    DELETE FROM erabili WHERE erabili.materiala_kode_materiala = material;
    SELECT ikasleak.penalizazioa INTO pena FROM ikasleak
    WHERE ikasleak.nan = dni; SELECT pena;
    IF pena > gaur THEN SELECT mezua4; ELSE SELECT ondo; END IF;
    END IF;
    END IF;
    END;;
DELIMITER ;