Door de gebruiker gedefinieerde stored procedures in SQL

Je gebruikt Microsoft SQL Server als database. Het concept in deze tutorial is hetzelfde voor de meeste relationele databasesystemen (RDBMS), maar de syntaxis verschilt. Het idee dat hier wordt gebruikt, werkt echter goed voor alle databases.

Een stored procedure is een blok met SQL-instructies in een relationeel databasesysteem (RDBMS). Het wordt doorgaans geschreven door programmeurs, databasebeheerders en data-analisten, en wordt opgeslagen en hergebruikt in meerdere programma’s. Er kunnen ook verschillende typen bestaan, afhankelijk van het RDBMS. De twee belangrijkste stored procedures die je in elk RDBMS aantreft, zijn:

• Door de gebruiker gedefinieerde stored procedure
• Systeem-stored procedure

Een stored procedure is voorbereide, gecompileerde code die wordt opgeslagen of in de cache gezet en vervolgens hergebruikt. Je kunt code cachen voor hergebruik, wat het onderhoud een stuk eenvoudiger maakt: je hoeft het niet op meerdere plekken te wijzigen, wat ook de veiligheid kan verbeteren.

Een tabel maken

Je maakt nu een tabel met de naam table_Employees als volgt.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)
Mogelijk gemaakt door

De tabel bevat informatie over medewerkers in een bepaald bedrijf en heeft de volgende kolommen:

1. EmployeeId: Dit is de primaire sleutel, uniek en van het gegevenstype integer, waarbij de waarde automatisch oploopt.
2. EmployeeFirstName: Dit is de voornaam van een medewerker, van het type tekenreeks, en mag niet leeg zijn.
3. EmployeeLast Name: Dit is de achternaam van een medewerker, van het type tekenreeks, en mag niet leeg zijn.
4. EmployeeGender: Dit geeft het geslacht van de medewerker aan en is ook een tekenreeks.
5. EmployeeDepartmentId: Dit is de id van de afdeling van de medewerker, van het gegevenstype integer.

Je kunt een query schrijven om waarden in de tabel in te voegen. De query om waarden toe te voegen is:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)
Mogelijk gemaakt door

Na het invoegen van de waarden in de tabel is table_Employees aangemaakt.

Een door de gebruiker gedefinieerde stored procedure maken

Met een stored procedure kun je een query één keer schrijven; de query krijgt een naam die kan worden opgeslagen en indien nodig meerdere keren kan worden uitgevoerd. Na het uitvoeren van de query’s zie je een map onder Programmability->Stored Procedure, met daarin het bestand dbo.uprocGetEmployees.

De syntaxis om een stored procedure te maken is:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END
Mogelijk gemaakt door

Je kunt een stored procedure maken met een van de volgende SQL-instructies.

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Gebruik bovendien een naamgevingsconventie anders dan "sp_." voor stored procedures. In het voorbeeld is de stored procedure gemaakt met de naam: uprocGetEmployees.

De stored procedure uitvoeren

Om een stored procedure uit te voeren, kun je een van de drie benaderingen gebruiken en deze als volgt draaien.

• EXEC <>
• EXECUTE <>
• <>

In het bovenstaande programma kun je de procedurenaam uprocGetEmployees gebruiken en selecteren om de query uit te voeren.

Stored procedure met parameters

De stored procedure kan één of meerdere parameters accepteren. Je begrijpt parameters met één waarde makkelijk als je eerst meerdere parameters bekijkt.

De syntaxis om een stored procedure met meerdere parameters te maken is:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END
Mogelijk gemaakt door

In het voorbeeld zie je dat er onderstaand een wijziging in de mapstructuur is.
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Het concept is vergelijkbaar met functies in algemene programmeertalen zoals Python, Java, enzovoort. Het programma hierboven heeft de functie- of procedurenaam "uprocGetEmployeesGenderAndDepartment", met de parameternamen @EmployeeGender en @EmployeeDepartmentId, die worden "aangeroepen" door de vereiste waarde aan onze procedure door te geven. Dat kan op twee manieren en wordt hieronder uitgelegd.

De parameters in dit programma zijn @EmployeeGender en @EmployeeDepartmentId, die worden "aangeroepen" door er een waarde aan door te geven.

De waarde kan op twee manieren worden opgegeven. Je kunt een van beide gebruiken om de stored procedure met meerdere parameters uit te voeren.

• Door de parameternamen in de query te specificeren en de benodigde waarden door te geven:

• Door de positie te gebruiken die overeenkomt met de parameter van de stored procedure in je query en de vereiste waarde door te geven:

De stored procedure wijzigen

Je kunt de stored procedure ook wijzigen met het commando ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END
Mogelijk gemaakt door

Stored procedure verwijderen

Stored procedures kun je snel verwijderen met de volgende commando’s:

DROP PROC <> DROP PROCEDURE <>

Beveiliging toevoegen via encryptie

Het locksymbool links op de onderstaande afbeelding geeft aan dat de stored procedure is versleuteld, zodat alleen geautoriseerde personen er toegang toe hebben.

Conclusie

Je hebt zojuist de basis van user stored procedures geleerd en de behandelde onderwerpen zijn conceptueel vergelijkbaar in elk RDBMS. Je kunt meer leren op DataCamp met de cursus Intermediate SQL Server. Ook de volgende tutorial over stored procedures is een goede aanvulling op je leertraject.