Stored Procedures

Maken en gebruiken van Stored Procedures

Elke opgeslagen routine (functie of procedure) bestaat uit drie elementen:

• Invoerparameters of argumenten, die dienen als input voor de routine.
• Uitvoerparameters of retourwaarden, die de uitvoer zijn die door de routine geretourneerd zijn
• Het hoofdgedeelte die de SQL opdrachten bevatten die moeten worden uitgevoerd

Om een opgeslagen routine te maken, moet de gebruiker een CREATE ROUTINE bevoegdheid hebben. Om een opgeslagen routine uit te voeren, moet de gebruiker een EXECUTE bevoegdheid hebben. Om te beginnen, laten we een eenvoudig voorbeeld van een opgeslagen procedure zien, die geen gebruik maakt van argumenten maakt of retourwaarden teruggeeft.

CREATE PROCEDURE count_airports
AS
    SELECT COUNT(AirportID) FROM airport;

Command(s) completed successfully.

EXEC count_airports;

(1 row(s) affected)

DELIMITER //
CREATE PROCEDURE count_airports()
BEGIN
    SELECT COUNT(AirportID) FROM airport;

END //
(0 row(s) affected)

CALL count_airports();

(1 row(s) returned)

Om een opgeslagen procedure te definiëren, gebruikt men het CREATE PROCEDURE-commando. Dit commando wordt gevolgd door de naam van de opgeslagen procedure en haakjes. Eventuele invoer- en uitvoerargumenten verschijnen binnen deze haakjes, en het hoofdgedeelte van de procedure volgt. Routinenamen mogen niet langer zijn dan 64 tekens, en namen die speciale tekens bevatten of in zijn geheel bestaan uit cijfers of gereserveerde woorden (MySQL) moeten worden aangehaald met de accent-grave/backtick (`) operator.

Kan ik de ingebouwde functies van MySQL overschrijven door het creëren van nieuwe met dezelfde naam? Nee, als algemene regel moet je voorkomen dat men gebruik maakt van bestaande ingebouwde functienamen als namen voor uw opgeslagen routines; Echter, als je dit moet doen, laat MySQL dit toe zolang er een extra ruimte moet tussen de procedure- of de functienaam en de haakjes die volgen.

Het hoofdgedeelte van een procedure kan SQL-opdrachten, variabele definities, conditionele testen, loops, en error handlers bevatten.

In het voorgaande voorbeeld wordt de procedure omsloten door BEGIN en END markeertekens. Deze BEGIN en END blokken zijn alleen verplicht als het hoofdgedeelte van de procedure complexe besturingsstructuren bevat; in alle andere gevallen (bijvoorbeeld het voorgaande voorbeeld, waarin slechts één SELECT bevat), zijn zij optioneel. Echter, is het een goede gewoonte om hen in ieder geval op te nemen zodat het hoofdgedeelte van de procedure duidelijk afgebakend is.

Let  ook op in MySQL met het gebruik van het DELIMITER commando in het vorige voorbeeld. Deze wordt door MySQL gebruikt om het opdrachtscheidingsteken te veranderen. Dit is om zeker te zijn dat het ‘;’-teken die worden gebruikt om opdrachten af te sluiten niet gebruikt wordt om de procedure voortijdig te beëindigen. Het scheidingsteken zal naar zijn normale waarde worden gewijzigd zodra de volledig gedefinieerde procedure door de server geaccepteerd is. Natuurlijk, is het definiëren van een opgeslagen procedure slechts het halve werk, de andere helft is gebruik hiervan. MySQL biedt de CALL-opdacht en MSSQL biedt de EXEC-opdracht om een opgeslagen procedure aan te roepen. Dit commando moet worden gevolgd door de naam van de procedure (en argumenten, indien aanwezig).

In ons volgende voorbeeld wordt met behulp van een opgeslagen procedures gebruik gemaakt om een tabel aan te maken (CREATE TABLE) en te verwijderen (DROP TABLE)

CREATE PROCEDURE create_log_table
AS
    CREATE TABLE log 
    (
    RecordID INT NOT NULL IDENTITY(1,1) 
        PRIMARY KEY, 
    ByUser NVARCHAR(50), Note NVARCHAR(MAX), 
    EventTime DATETIME2(7)
    );
;

Command(s) completed successfully.

EXEC [dbo].[create_log_table];

Command(s) completed successfully.

EXEC [dbo].[create_log_table];

(Msg 2714, Level 16, State 6, Procedure create_log_table, 
Line 14 There is already an object named 'log' in the database.)

CREATE PROCEDURE remove_log_table
AS
    DROP TABLE log;
;

Command(s) completed successfully.

EXEC [dbo].[remove_log_table];

Command(s) completed successfully.

EXEC [dbo].[remove_log_table];

(Msg 3701, Level 11, State 5, Procedure remove_log_table, Line 3 
Cannot drop the table 'log', because it does not exist or 
you do not have permission.)

DELIMITER //
CREATE PROCEDURE create_log_table()
BEGIN
    CREATE TABLE log 
    (
    RecordID int(11) NOT NULL AUTO_INCREMENT,
    ByUser varchar(50) NOT NULL,
    Note text NOT NULL,
    EventTime timestamp NOT NULL 
      DEFAULT CURRENT_TIMESTAMP ON 
        UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (RecordID)
    );
END //

(0 row(s) affected)

CALL create_log_table();

(0 row(s) affected)

CALL create_log_table();

(Error Code: 1050. Table 'log' already exists)

DELIMITER //
CREATE PROCEDURE remove_log_table()
BEGIN
    DROP TABLE log;
END //

(0 row(s) affected)

CALL remove_log_table;

(0 row(s) affected)

CALL remove_log_table;

(Error Code: 1051. Unknown table 'log')

Om een opgeslagen procedure te verwijderen, gebruikt u de DROP PROCEDURE-instructie met de procedurenaam als argument:

DROP PROCEDURE count_airports;

Command(s) completed successfully.

DROP PROCEDURE count_airports;

(0 row(s) affected)

Kan ik het hoofdgedeelte van een procedure bewerken nadat het is aangemaakt? Nee, MySQL biedt wel een ALTER PROCEDURE, ALTER FUNCTION commando, maar dat momenteel alleen wijzigingen toelaat aan de kenmerken, maar niet aan het hoofdgedeelte de procedure. Om het hoofdgedeelte van een procedure te veranderen is het noodzakelijk om de procedure eerst te verwijderen en dan opnieuw aan te maken. MSSQL kent wel het gebruik het ALTER commando.

ALTER PROCEDURE [dbo].[count_airports]
AS
    SELECT COUNT(AirportID) FROM airport;
;

Command(s) completed successfully.

ALTER FUNCTION [dbo].[today]()
RETURNS VARCHAR(255)
BEGIN
    RETURN FORMAT(GETDATE(),'dd/MM/yy')
END;

Command(s) completed successfully.

DROP PROCEDURE count_airports;

(0 row(s) affected)

DELIMITER //
CREATE PROCEDURE count_airports()
BEGIN
    SELECT COUNT(AirportID) FROM airport;
END //

(0 row(s) affected)

DROP FUNCTION today();

(0 row(s) affected)

DELIMITER //
CREATE FUNCTION today()
RETURNS VARCHAR(255)
BEGIN
    RETURN DATE_FORMAT(NOW(),'%D %M %Y');
END //

(0 row(s) affected)

In MySQL kunt het hoofdgedeelte van een specifieke opgeslagen procedure te bekijken met behulp van het SHOW CREATE PROCEDURE commando dat gevolgd wordt met de naam van de procedure als argument. Dit is een opdracht met restricties en zal enkel worden uitgevoerd alleen als u de maker van de procedure bent of dat u SELECT privileges heeft op de proc grant tabel (privileges worden besproken in meer detail in hoofdstuk 11). In MsSQL kunt u het hoofdgedeelte van een procedure of functie laten zien door de ingebouwde functie die u moet aanroepen met EXEC sp_helptext gevolgd door de procedure of functienaam.

EXEC sp_helptext [today];

Command(s) completed successfully.

SHOW CREATE FUNCTION today;
(1 row(s) returned)

Om een lijst van alle opgeslagen procedures of functies op de server te bekijken, kunt u in MySQL het commando SHOW PROCEDURE STATUS / SHOW FUNCTION STATUS gebruiken eventueel gevolgd door een WHERE-clause. In MSSQL kunt u een SELECT FROM INFORMATION_SCHEMA_ROUTINES gebruiken eventueel gevolgd door een WHERE-clause.

SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES; 

(6 row(s) affected)

SHOW PROCEDURE STATUS;

(1 row(s) returned)

Input and Output Parameters

IN Parameters

Het IN sleutelwoord wordt enkel gebruikt door MySQL om de invoerparameters van een opgeslagen procedure te markeren. MSSQL gaat er van uit dat het om inputparameters gaat tenzij anders gespecificeerd. Het IN-sleutelwoord wordt gevolgd door de naam van de parameter en het gegevenstype (die één van de MySQL ingebouwde datatypes kan zijn). De volgende procedure illustreert het gebruik van de input parameters door het definiëren van een opgeslagen procedure die een numerieke identificatie van de luchthaven accepteert en de bijbehorende naam van de luchthaven retourneert.

CREATE PROCEDURE get_airport_name(@aid INT)
AS
SELECT AirportName FROM airport WHERE AirportID = @aid;

Command(s) completed successfully.

EXEC [dbo].[get_airport_name] @aid = 129;

(1 row(s) returned)

EXEC [dbo].[get_airport_name] @aid = 201;

(1 row(s) returned)

DELIMITER //
CREATE PROCEDURE get_airport_name(IN aid INT)
BEGIN
SELECT AirportName FROM airport WHERE AirportID = aid;
END //

(0 row(s) affected)

CALL get_airport_name(129);

(1 row(s) returned)

CALL get_airport_name(201);

(1 row(s) returned)

U kunt ook meerdere IN-parameters gebruiken gebruiken. Hier is een voorbeeld, dat een opgeslagen procedure gebruikt om een nieuw type vliegtuig record in te voegen:

CREATE PROCEDURE add_aircraft_type
    (
    @aid INT,
    @atype VARCHAR(255)
    )
AS
    INSERT INTO aircrafttype (AircraftTypeID, AircraftName)
        VALUES(@aid, @atype);
        SELECT AircraftTypeID, AircraftName
        FROM aircrafttype
        WHERE AircraftTypeID = @aid;
;

Command(s) completed successfully.

EXEC add_aircraft_type 
    @aid = 711, @atype = 'Boeing 777';

(1 row(s) returned)

DELIMITER //
CREATE PROCEDURE add_aircraft_type
    (
    IN aid INT,
    IN atype VARCHAR(255)
    )
BEGIN
    INSERT INTO aircrafttype 
    (AircraftTypeID, AircraftName)
        VALUES(aid, atype);
        SELECT AircraftTypeID, AircraftName
        FROM aircrafttype
        WHERE AircraftTypeID = aid;
END //

(0 row(s) affected)

CALL add_aircraft_type(711, 'Boeing 777');

(1 row(s) returned)

Opgeslagen routines worden altijd geassocieerd met een specifieke MySQL-database (gewoonlijk die in gebruik op het moment dat de routine wordt gedefinieerd). Om aan te geven dat een routine moet worden geassocieerd met een andere database, worden uitgevoerd, worden gewijzigd, of een routine die behoort tot een andere database te verwijderen, moet men het voorvoegsel van de naam van de database in routinenaam, in uw CREATE, CALL of DROP commando gebruiken.

OUT Parameters

Het OUT-sleutelwoord wordt gebruikt om de uitvoer parameters van een procedure te markeren. Zoals met het IN-sleutelwoord, wordt het gevolgd door een parameternaam en het gegevenstype en wordt automatisch geïnitialiseerd op NULL in het hoofdgedeelte van de procedure. Hier is een herziening van het vorige voorbeeld, waarin de naam van de luchthaven in de output parameter wordt geplaatst in plaats van het weer te geven. Merk op dat in MySQL de procedure de SELECT INTO opdracht gebruikt om het resultaat van de bevraging aan een variabele toe te wijzen. Het is nu mogelijk om de procedure aan te roepen en het resultaat van de uitvoer op te slaan in een sessievariabele voor later gebruik.

CREATE PROCEDURE get_airport_name 
    @aid INT, @aname VARCHAR(255) OUTPUT
AS
SELECT @aname = AirportName
FROM airport
WHERE AirportID = @aid
;

Command(s) completed successfully.

DECLARE @get VARCHAR(255);
EXEC [dbo].[get_airport_name] '201', @get OUTPUT
SELECT @get;

(1 row(s) returned)

DELIMITER //
CREATE PROCEDURE get_airport_name(
IN aid INT,
OUT aname VARCHAR(255)
)
BEGIN
SELECT AirportName INTO aname
FROM airport
WHERE AirportID = aid;
END //

(0 row(s) affected)

CALL get_airport_name(201, @var);

(1 row(s) affected)

SELECT @var;

(1 row(s) returned)

In MySQL kunt u direct in het hoofdgedeelte van de procedure de uitvoerwaarde in een sessie variabele schrijven. Hier is een herziening van het vorige voorbeeld, waaruit dit blijkt:

DELIMITER //
CREATE PROCEDURE get_airport_name
(
IN aid INT
)
BEGIN
    SELECT AirportName INTO @aname
    FROM airport
    WHERE AirportID = aid;
END //

(0 row(s) affected)

CALL get_airport_name(201);

(1 row(s) returned)

SELECT @aname;

(1 row(s) returned)

INOUT Parameters

Het INOUT sleutelwoord wordt gebruikt voor parameters die dienen als zowel invoer als uitvoer, en heeft dezelfde syntax als de IN en OUT sleutelwoorden. Dit wordt meestal gebruikt voor parameters die waarschijnlijk in de loop van de procedure te modificeren zijn. Hier is een eenvoudig voorbeeld, dat dit laat zien:

DELIMITER //
CREATE PROCEDURE add_one
(
INOUT num INT
)
BEGIN
    SELECT (num+1) INTO num;
END //

(0 row(s) affected)

SET @a = 9;
CALL add_one(@a)

(1 row(s) affected)

SELECT @a;

(1 row(s) affected)