SQL Opgeslagen Procedure: Automatiseer en Optimaliseer Vragen

Handleidingen
SQL

SQL-opgeslagen procedures zijn sets van SQL-statements die zijn opgeslagen en bewaard in een database. Ze kunnen op verzoek worden uitgevoerd om gegevensmanipulatie- en validatietaken uit te voeren, waardoor de noodzaak om repetitieve SQL-code voor veelvoorkomende bewerkingen te schrijven wordt verminderd. Opgeslagen procedures zijn nuttig bij databasebeheer door efficiëntie en herbruikbaarheid te bevorderen. Bovendien ondersteunen ze verbeterde databasebeveiliging en onderhoudbaarheid. In dit artikel zullen we bespreken hoe SQL-opgeslagen procedures te maken en uit te voeren, veelvoorkomende gebruiksscenario’s en beste praktijken.

Als we beginnen, raad ik ten zeerste aan om de Introductie tot SQL en Leer SQL cursussen van DataCamp te volgen om de basiskennis te leren van het extraheren en analyseren van gegevens met SQL. Ook is de SQL Basics Cheat Sheet, die je kunt downloaden, een handige referentie omdat het alle meest voorkomende SQL-functies bevat.

Wat is een opgeslagen procedure in SQL?

Een opgeslagen procedure in SQL is een verzameling SQL-instructies die zijn opgeslagen binnen de database. Het doel van de SQL-opgeslagen procedure is om een reeks bewerkingen op een database uit te voeren, zoals het opvragen, invoegen, bijwerken of verwijderen van gegevens.

In tegenstelling tot reguliere SQL-query’s, die als afzonderlijke opdrachten worden uitgevoerd, encapsuleren opgeslagen procedures een set SQL-instructies, waardoor het eenvoudig is om de code opnieuw te gebruiken zonder SQL-opdrachten herhaaldelijk te hoeven schrijven.

De voordelen van SQL-opgeslagen procedures zijn onder andere:

  • Hergebruik van code: Zodra een opgeslagen procedure is aangemaakt, kan deze zoveel keer als nodig worden aangeroepen, waardoor redundantie in SQL-code wordt geëlimineerd.
  • Verbeterde prestaties:Opgeslagen procedures worden vaak sneller uitgevoerd omdat ze vooraf zijn gecompileerd en op de database-server zijn opgeslagen, waardoor de netwerklatentie en compilatietijd worden verminderd.
  • Beveiliging: Opgeslagen procedures kunnen de gegevensbeveiliging verbeteren en controle geven over de toegang tot gevoelige gegevens door gebruikers toestemming te geven om een opgeslagen procedure uit te voeren zonder directe toegang tot tabellen.

Basis syntaxis en structuur

De syntaxis voor het maken van een opgeslagen procedure kan enigszins verschillen afhankelijk van het databasesysteem (bijv. MySQL, SQL Server, Oracle). Hieronder staat een algemeen voorbeeld met behulp van SQL Server-syntaxis:

-- Maak een opgeslagen procedure met de naam ProcedureNaam
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL-opdrachten komen hier
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

In de bovenstaande syntaxis;

  • MAAK PROCEDURE: Dit commando wordt gebruikt om een nieuwe opgeslagen procedure te definiëren.

  • ProcedureNaam: De naam die aan de opgeslagen procedure is gegeven. Het moet uniek zijn binnen de database.

  • @Parameter1, @Parameter2: Parameters zijn optioneel; ze stellen de procedure in staat gegevensinvoer te ontvangen. Elke parameter is gedefinieerd met een @-symbool en een gegevenstype (bijv. INT, VARCHAR(50)).

  • ALS BEGIN…EINDIGEN: De SQL-statements binnen BEGIN en EINDE vormen het lichaam van de procedure, waar de hoofdlogica wordt uitgevoerd. De procedure haalt records op uit een tabel op basis van specifieke voorwaarden in dit voorbeeld.

Invoer- en uitvoerparameters

De invoer- en uitvoerparameters stellen je in staat waarden door te geven aan en van een opgeslagen procedure.

Bijvoorbeeld, als @Parameter1 is gedefinieerd als een invoerparameter, kan het een willekeurige waarde krijgen wanneer de procedure wordt aangeroepen, wat van invloed is op de SQL-logica of output. In het onderstaande voorbeeld haalt de parameter @UserID gegevens op die specifiek zijn voor de opgegeven UserID.

-- Maak een procedure om gegevens op te halen voor een specifieke gebruiker op basis van UserID
CREATE PROCEDURE GetUserData 
 -- Invoerparameter: ID van de gebruiker om op te halen
    @UserID INT 
AS
BEGIN
    -- Selecteer alle kolommen van Gebruikers waar UserID overeenkomt met de invoerparameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Outputparameters, gedefinieerd door het trefwoord OUTPUT, stellen een opgeslagen procedure in staat om een waarde terug te sturen naar de aanroepomgeving. Bijvoorbeeld, als een procedure een korting berekent, kan het een outputparameter gebruiken om deze terug te geven aan het aanroepende programma.

-- Maak een procedure om de afgeprijsde prijs te berekenen
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: uiteindelijke prijs na korting
AS
BEGIN
    -- Bereken de uiteindelijke prijs door het toepassen van het kortingspercentage op de oorspronkelijke prijs
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Om deze procedure te bellen, zou je gebruiken;

-- Declareer een variabele om de uiteindelijke prijs na korting op te slaan
DECLARE @FinalPrice DECIMAL(10, 2);
-- Voer de CalculateDiscount procedure uit met een prijs van 100 en een korting van 10%
-- Sla de output op in de @FinalPrice variabele
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Selecteer en toon de uiteindelijke afgeprijsde prijs
SELECT @FinalPrice AS FinalPrice;

Opgeslagen procedures in MySQL

Zoals ik al had vermeld, kan het uitvoeren van een opgeslagen procedure in SQL op verschillende manieren gebeuren, afhankelijk van het databasesysteem en de gebruikte tools.

Het maken van opgeslagen procedures in MySQL

Het maken van een opgeslagen procedure in MySQL omvat het definiëren van de naam van de procedure, parameters en de SQL-statements die deel uitmaken van de body. Het volgende voorbeeld maakt een procedure genaamd GetEmployeeDetails die EmployeeID als invoerparameter heeft en details ophaalt voor die specifieke werknemer.

DELIMITER $
-- Maak een procedure om details op te halen voor een specifieke werknemer op basis van EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Selecteer alle kolommen uit Werknemers waar EmployeeID overeenkomt met de invoerparameter
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Uitvoeren van opgeslagen procedures in MySQL

De meest gebruikelijke manier om een opgeslagen procedure uit te voeren, is met SQL-commando’s. In MySQL gebruiken we het CALL-commando om de opgeslagen procedure uit te voeren.

CALL ProcedureName();

Met de gedefinieerde GetEmployeeDetails-procedure zou de uitvoervraag er als volgt uitzien:

-- Voer de opgeslagen procedure uit om details op te halen voor EmployeeID 101
CALL GetEmployeeDetails(101);

Opgeslagen procedures in SQL Server

SQL Server biedt specifieke syntaxis en commando’s voor het maken, uitvoeren en beheren van opgeslagen procedures. Deze aanpak maakt het gemakkelijker om efficiënte en herbruikbare SQL-routines te bouwen die complexe taken met minimale herhaling kunnen afhandelen.

Opgeslagen procedures maken in SQL Server

Een opgeslagen procedure maken in SQL Server houdt in dat je de naam van de procedure, parameters en de SQL-instructies die het lichaam vormen, definieert. Het volgende voorbeeld maakt een procedure aan genaamd GetEmployeeDetails die @EmployeeID als invoerparameter neemt en details ophaalt voor die specifieke werknemer.

-- Maak een procedure om details op te halen voor een specifieke werknemer op basis van EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Invoerparameter: ID van de werknemer om op te halen
AS
BEGIN
    -- Selecteer alle kolommen uit Employees waar EmployeeID overeenkomt met de invoerparameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Uitvoeren van opgeslagen procedures in SQL Server

In SQL Server roept het EXEC of EXECUTE commando een opgeslagen procedure aan. Het volgende voorbeeld laat zien hoe je de opgeslagen procedure GetEmployeeDetails uitvoert met specifieke invoerparameters.

-- Voer de GetEmployeeDetails-procedure uit met de EmployeeID ingesteld op 102
EXEC GetEmployeeDetails @EmployeeID = 102;

Je kunt de uitvoerparameters ook uitvoeren door de variabele in de opdracht te verklaren. In het volgende voorbeeld wordt @TotalSales verklaard als een variabele om de uitvoer van CalculateTotalSales te ontvangen.

-- Declareren van een variabele om het totale verkoopbedrag op te slaan
DECLARE @TotalSales DECIMAL(10, 2);
-- Voer CalculateTotalSales uit voor SalespersonID 5, sla het resultaat op in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Toon het totale verkoopbedrag
SELECT @TotalSales AS TotalSales;

Ik raad aan om onze cursus Inleiding tot SQL Server te volgen om de verschillende functionaliteiten van SQL Server voor het opvragen van gegevens te begrijpen. Overweeg ook onze volledige SQL Server Developer carrièretrack, die je niet alleen zal uitrusten met de vaardigheden om opgeslagen procedures te maken, bij te werken en uit te voeren, maar je ook zal helpen met aggregatiefuncties, samenvoegen, invoegen en verwijderen van tabellen, en nog veel meer.

Veelvoorkomende toepassingen voor opgeslagen procedures

SQL-opgeslagen procedures zijn nuttig in scenario’s waar herhaalde complexe taken vereist zijn. Hieronder volgen real-world toepassingen van opgeslagen procedures in gegevensbeheer en bedrijfsactiviteiten.

Gegevensvalidatie en integriteitshandhaving

Opgeslagen procedures kunnen worden gebruikt om gegevens te valideren vóór update of invoeging. In het onderstaande voorbeeld controleert een opgeslagen procedure of een e-mail van een klant uniek is voordat een nieuw record wordt ingevoegd in de Customers tabel, waardoor gegevensconsistentie wordt gegarandeerd. Hiermee wordt validatielogica gecentraliseerd binnen de database, waardoor redundantie wordt verminderd en uniforme handhaving over verschillende toepassingen wordt gegarandeerd.

-- Maak een procedure om een nieuwe klant toe te voegen en controleer op duplicaat e-mail
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Controleer of het e-mailadres al bestaat in de Customers tabel
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Gooi een foutmelding als het e-mailadres al in gebruik is
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Voeg nieuwe klantgegevens toe als het e-mailadres uniek is
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Geautomatiseerde gegevensverwerking en rapportage

Je kunt ook opgeslagen procedures gebruiken om reguliere rapporten te genereren of grote datasets te verwerken. Een opgeslagen procedure kan bijvoorbeeld dagelijkse verkoopgegevens van een e-commerceplatform aggregeren en deze opslaan in een rapportagetabel, waardoor het voor teams gemakkelijker wordt om inzicht te krijgen in de verkoop zonder complexe queries uit te voeren.

-- Maak een procedure om een dagelijks verkooprapport te genereren
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Voeg de datum van vandaag en de totale verkoop toe aan de SalesReport-tabel
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Selecteer de huidige datum en de som van de verkopen voor vandaag uit de Sales-tabel
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Transactiemanagement

Door gebruik te maken van opgeslagen procedures, kun je ervoor zorgen dat meerdere bewerkingen worden uitgevoerd als één enkele transactie. In een bankensysteem kan een opgeslagen procedure bijvoorbeeld zowel debet- als creditacties afhandelen bij een geldoverdracht, zodat beide acties samen slagen of falen.

-- Maak een procedure om geld over te maken tussen accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start een transactie om atomaire uitvoering te verzekeren
    -- Trek het gespecificeerde bedrag af van het saldo van de verzender
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Voeg het gespecificeerde bedrag toe aan het saldo van de ontvanger
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Controleer op fouten en maak de transactie ongedaan als er zich een heeft voorgedaan; anders, commit de transactie
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Maak alle wijzigingen ongedaan als er een fout is opgetreden
    ELSE
        COMMIT TRANSACTION;    -- Bevestig wijzigingen als er geen fouten zijn
END;

Toegangscontrole en gegevensbeveiliging

Je kunt ook SQL-stored gebruiken om gegevenstoegang tot gevoelige informatie te controleren. Bijvoorbeeld, een stored procedure kan directe toegang tot tabellen beperken door gebruikers toe te staan een procedure aan te roepen die alleen relevante velden ophaalt, zoals saldi van accounts, zonder transactiedetails.

-- Maak een procedure om het saldo van een account op te halen, met autorisatiecontrole
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Controleer of het account bestaat en eigendom is van de gespecificeerde gebruiker
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Als geautoriseerd, selecteer en retourneer het saldo van het account
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Als ongeautoriseerd, genereer een foutmelding
        THROW 50000, 'Unauthorized access.', 1;
END;

Gegevensmigratie en ETL-processen

Opgeslagen procedures worden ook gebruikt om gegevens tussen systemen te laden, transformeren en migreren. Een opgeslagen procedure kan gegevens automatisch extraheren uit een brondatabase, deze transformeren indien nodig en deze invoegen in een doeltabel, waardoor gegevensintegratie voor rapportage of analyse wordt vereenvoudigd.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extraheren
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transformeren
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Laden
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Best Practices voor Opgeslagen Procedures

Het schrijven van efficiënte en onderhoudbare opgeslagen procedures zorgt ervoor dat uw database optimaal presteert. Hier volgen tips voor het schrijven van opgeslagen procedures voor uw SQL-databases.

  • Gebruik consistente benamingconventies: Gebruik een consistente en beschrijvende benamingindeling om opgeslagen procedures gemakkelijk te identificeren en te begrijpen. Vermijd ook het voorvoegsel sp_ in SQL Server dat is gereserveerd voor systeemprocedures om mogelijke conflicten en prestatieproblemen te voorkomen.

  • Implementeer Foutafhandeling: Omhul SQL-instructies in TRY...CATCH blokken om fouten op te vangen en af te handelen en de gegevensintegriteit te waarborgen.

  • Optimaliseer voor Prestaties: Minimaliseer het gebruik van cursors, omdat deze traag en resource-intensief kunnen zijn. Probeer in plaats daarvan set-gebaseerde bewerkingen te gebruiken, die over het algemeen efficiënter zijn. Indexeer ook de vaak gebruikte kolommen en vermijd complexe joins in grote tabellen om het geheugengebruik te verminderen en de efficiëntie te verbeteren.

  • Parameteriseer Opgeslagen Procedures:Gebruik parameters in plaats van hardcoded waarden om dynamische waarden door te geven aan uw procedure, waardoor deze flexibeler en herbruikbaar wordt.

Bekijk onze Intermediate SQL cursus om meer te leren over het gebruik van aggregatiefuncties en joins om data te filteren. Probeer ook onze vaardigheidssporen SQL Server Fundamentals en SQL Fundamentals om je vaardigheden in het samenvoegen van tabellen en data-analyse te verbeteren.

Conclusie

SQL opgeslagen procedures verbeteren de herbruikbaarheid van code en optimaliseren de prestaties in databasebeheer. Opgeslagen procedures verbeteren ook de databasebeveiliging door gecontroleerde toegang en het waarborgen van data-integriteit. Als data-beoefenaar moedig ik je aan om te oefenen met het maken en uitvoeren van opgeslagen procedures om de beste praktijken voor databasebeheer onder de knie te krijgen.

Als je geïnteresseerd bent in het worden van een bekwame data-analist, bekijk dan onze carrièreroute voor Associate Data Analyst in SQL om de benodigde vaardigheden te leren. De cursus Reporting in SQL is ook geschikt als je wilt leren hoe je professionele dashboards kunt bouwen met SQL. Tot slot raad ik aan om het SQL Associate Certification te behalen om je beheersing van het gebruik van SQL voor data-analyse te demonstreren en op te vallen tussen andere data-professionals.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure