Opgeslagen procedure - een databaseobject, een set SQL-instructies die één keer wordt gecompileerd en op de server wordt opgeslagen. Opgeslagen procedures lijken sterk op gewone taalprocedures op hoog niveau: ze kunnen invoer- en uitvoerparameters en lokale variabelen hebben, ze kunnen numerieke berekeningen en bewerkingen uitvoeren op tekengegevens, waarvan de resultaten kunnen worden toegewezen aan variabelen en parameters. Opgeslagen procedures kunnen standaard databasebewerkingen uitvoeren (zowel DDL als DML). Bovendien maken opgeslagen procedures lussen en vertakkingen mogelijk, dat wil zeggen dat ze instructies kunnen gebruiken om het uitvoeringsproces te besturen.

Opgeslagen procedures zijn vergelijkbaar met door de gebruiker gedefinieerde functies (UDF's). Het belangrijkste verschil is dat door de gebruiker gedefinieerde functies kunnen worden gebruikt zoals elke andere expressie in een SQL-instructie, terwijl opgeslagen procedures moeten worden aangeroepen met de functie CALL:

CALL-procedure(…)

UITVOEREN procedure(…)

Opgeslagen procedures kunnen meerdere resultaten retourneren, dat wil zeggen de resultaten van een SELECT-query. Dergelijke resultatensets kunnen worden verwerkt met behulp van cursors, andere opgeslagen procedures die een resultaatsetaanwijzer retourneren, of toepassingen. Opgeslagen procedures kunnen ook gedeclareerde variabelen bevatten voor het verwerken van gegevens en cursors, waardoor u meerdere rijen in een tabel kunt herhalen. De SQL-standaard biedt IF, LOOP, REPEAT, CASE en vele andere om mee te werken. Opgeslagen procedures kunnen variabelen accepteren, resultaten retourneren of variabelen wijzigen en retourneren, afhankelijk van waar de variabele is gedeclareerd.

De implementatie van opgeslagen procedures varieert van DBMS tot DBMS. De meeste grote databaseleveranciers ondersteunen ze in een of andere vorm. Afhankelijk van het DBMS kunnen opgeslagen procedures in verschillende programmeertalen worden geïmplementeerd, zoals SQL, Java, C of C++. Opgeslagen procedures die niet in SQL zijn geschreven, kunnen wel of niet zelfstandig SQL-query's uitvoeren.

Achter

Logica delen met andere applicaties. Opgeslagen procedures omvatten functionaliteit; dit biedt connectiviteit voor gegevenstoegang en -beheer voor verschillende applicaties.

Gebruikers isoleren van databasetabellen. Hiermee kunt u toegang geven tot opgeslagen procedures, maar niet tot de tabelgegevens zelf.

Biedt een beschermingsmechanisme. Zoals aangegeven in het vorige punt: als u alleen toegang hebt tot gegevens via opgeslagen procedures, kan niemand anders uw gegevens wissen via de SQL DELETE-opdracht.

Verbeterde uitvoering als gevolg van minder netwerkverkeer. Met behulp van opgeslagen procedures kunnen meerdere query's worden gecombineerd.

Tegen

Verhoogde belasting van de databaseserver vanwege het feit dat het meeste werk aan de serverzijde wordt uitgevoerd en minder aan de clientzijde.

Je zult veel moeten leren. U moet de syntaxis van MySQL-expressies leren om uw opgeslagen procedures te schrijven.

U dupliceert uw applicatielogica op twee plaatsen: servercode en code voor opgeslagen procedures, waardoor het proces van gegevensmanipulatie ingewikkelder wordt.

Migratie van het ene DBMS naar het andere (DB2, SQL Server, etc.) kan tot problemen leiden.

Doel en voordelen van opgeslagen procedures

Opgeslagen procedures verbeteren de prestaties, verbeteren de programmeermogelijkheden en ondersteunen functies voor gegevensbeveiliging.

In plaats van een veelgebruikte query op te slaan, kunnen clients verwijzen naar de bijbehorende opgeslagen procedure. Wanneer een opgeslagen procedure wordt aangeroepen, wordt de inhoud ervan onmiddellijk door de server verwerkt.

Naast het daadwerkelijk uitvoeren van de query, kunt u met opgeslagen procedures ook berekeningen uitvoeren en gegevens manipuleren - het wijzigen, verwijderen, uitvoeren van DDL-instructies (niet in alle DBMS's!) en het aanroepen van andere opgeslagen procedures, en het uitvoeren van complexe transactionele logica. Met één enkele instructie kunt u een complex script oproepen dat is opgenomen in een opgeslagen procedure, waardoor u geen honderden opdrachten over het netwerk hoeft te verzenden en vooral de noodzaak om grote hoeveelheden gegevens van de client naar de server over te dragen, vermijdt.

In de meeste DBMS'en wordt een opgeslagen procedure de eerste keer dat deze wordt uitgevoerd, gecompileerd (geparseerd en wordt een gegevenstoegangsplan gegenereerd). In de toekomst zal de verwerking ervan sneller zijn. Het Oracle DBMS interpreteert opgeslagen procedurecode die is opgeslagen in de datadictionary. Vanaf Oracle 10g wordt de zogenaamde native compilatie van opgeslagen procedurecode in C en vervolgens in de machinecode van de doelmachine ondersteund, waarna, wanneer een opgeslagen procedure wordt aangeroepen, de gecompileerde objectcode ervan direct wordt uitgevoerd.

Programmeermogelijkheden

De gemaakte opgeslagen procedure kan op elk moment worden aangeroepen, wat modulariteit biedt en hergebruik van code stimuleert. Dit laatste maakt de database gemakkelijker te onderhouden, omdat deze geïsoleerd raakt van veranderende bedrijfsregels. U kunt een opgeslagen procedure op elk moment wijzigen in overeenstemming met de nieuwe regels. Hierna zullen alle applicaties die er gebruik van maken automatisch voldoen aan de nieuwe bedrijfsregels, zonder directe wijziging.

Veiligheid

Door het gebruik van opgeslagen procedures kunt u directe gebruikerstoegang tot databasetabellen beperken of volledig elimineren, waardoor gebruikers alleen de machtigingen hebben om opgeslagen procedures uit te voeren die indirecte en strikt gereguleerde toegang tot gegevens bieden. Bovendien ondersteunen sommige DBMS'en tekstversleuteling (wrapping) van een opgeslagen procedure.

Dankzij deze beveiligingsfuncties kan de databasestructuur worden geïsoleerd van de gebruiker, waardoor de database-integriteit en betrouwbaarheid worden gegarandeerd.

De kans op acties zoals SQL-injectie wordt verkleind omdat goed geschreven opgeslagen procedures bovendien invoerparameters controleren voordat de query wordt doorgegeven aan het DBMS.

Opgeslagen procedures implementeren

Opgeslagen procedures worden meestal gemaakt met behulp van de SQL-taal of de specifieke implementatie ervan in het geselecteerde DBMS. Voor deze doeleinden is er bijvoorbeeld in de Microsoft SQL Server DBMS de Transact-SQL-taal, in Oracle - PL/SQL, in InterBase en Firebird - PSQL, in PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, in IBM DB2 - SQL/PL (Engels), in Informix - SPL. MySQL volgt de SQL:2003-standaard vrij nauwkeurig, de taal is vergelijkbaar met SQL/PL.

Sommige DBMS'en staan ​​het gebruik toe van opgeslagen procedures die in elke programmeertaal zijn geschreven en waarmee onafhankelijke uitvoerbare bestanden kunnen worden gemaakt, bijvoorbeeld C++ of Delphi. In de terminologie van Microsoft SQL Server worden dergelijke procedures uitgebreide opgeslagen procedures genoemd en zijn het eenvoudigweg functies die zijn opgenomen in een Win32 DLL. En in Interbase en Firebird hebben functies die worden aangeroepen vanuit DLL/SO bijvoorbeeld een andere naam: UDF (User Defined Function). MS SQL 2005 introduceerde de mogelijkheid om opgeslagen procedures in elke .NET-taal te schrijven, en het is de bedoeling dat uitgebreide opgeslagen procedures in de toekomst worden afgeschaft. Het Oracle DBMS maakt het op zijn beurt mogelijk om opgeslagen procedures in Java te schrijven. In IBM DB2 is het schrijven van opgeslagen procedures en functies in conventionele programmeertalen een traditionele manier, die vanaf het allereerste begin wordt ondersteund, en de SQL-procedurele uitbreiding werd pas in vrij late versies aan dit DBMS toegevoegd, na opname in de ANSI-standaard. Informix ondersteunt ook procedures in Java en C.

In het Oracle DBMS kunnen opgeslagen procedures worden gecombineerd tot zogenaamde pakketten. Een pakket bestaat uit twee delen: een pakketspecificatie, die de definitie van een opgeslagen procedure specificeert, en een pakketbody, die de implementatie ervan bevat. Met Oracle kunt u dus de interface van de programmacode scheiden van de implementatie ervan.

In het IBM DB2 DBMS kunnen opgeslagen procedures worden gecombineerd tot modules.

Syntaxis

PROCEDURE MAKEN `p2`()

SQL-BEVEILIGINGSDEFINER

OPMERKING "Een procedure"

SELECTEER "Hallo wereld!";

Het eerste deel van de code creëert een opgeslagen procedure. De volgende bevat optionele parameters. Dan komt de naam en ten slotte de hoofdtekst van de procedure zelf.

4 kenmerken van een opgeslagen procedure:

Taal: Voor draagbaarheidsdoeleinden is de standaard SQL.

Deterministisch: als de procedure altijd hetzelfde resultaat retourneert en dezelfde invoerparameters gebruikt. Dit is voor het replicatie- en registratieproces. De standaardwaarde is NIET DETERMINISTISCH.

SQL-beveiliging: tijdens het gesprek worden de gebruikersrechten gecontroleerd. INVOKER is de gebruiker die de opgeslagen procedure aanroept. DEFINER is de “maker” van de procedure. De standaardwaarde is DEFINER.

Opmerking: voor documentatiedoeleinden is de standaardwaarde ""

Een opgeslagen procedure oproepen

CALL opgeslagen_procedurenaam (param1, param2, ....)

CALL procedure1(10, "stringparameter", @parameter_var);

Een opgeslagen procedure wijzigen

MySQL heeft een ALTER PROCEDURE-statement voor het wijzigen van procedures, maar deze is alleen geschikt voor het wijzigen van bepaalde kenmerken. Als u de parameters of de hoofdtekst van een procedure moet wijzigen, moet u deze verwijderen en opnieuw maken.

Verwijderingopgeslagenprocedures

VALPROCEDURE INDIEN BESTAAT p2;

Dit is een eenvoudig commando. De instructie IF EXISTS signaleert een fout als een dergelijke procedure niet bestaat.

Opties

CREATE PROCEDURE proc1(): lege parameterlijst

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): één invoerparameter. Het woord IN is optioneel omdat de standaardparameters IN (in) zijn.

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): één parameter geretourneerd.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): één parameter, zowel invoer als retour.

De syntaxis van de variabeledeclaratie ziet er als volgt uit:

DECLARE varname DATA-TYPE DEFAULT standaardwaarde;

Opgeslagen SQL-procedures zijn uitvoerbare programmamodules die kunnen worden opgeslagen in de vorm van verschillende objecten. Met andere woorden: het is een object dat SQL-instructies bevat. Deze opgeslagen procedures kunnen in de applicatieclient worden uitgevoerd om goede prestaties te verkrijgen. Bovendien worden dergelijke objecten vaak aangeroepen vanuit andere scripts of zelfs vanuit een andere sectie.

Invoering

Veel mensen geloven dat ze vergelijkbaar zijn met verschillende procedures (respectievelijk behalve MS SQL). Misschien is dit waar. Ze hebben vergelijkbare parameters en kunnen vergelijkbare waarden produceren. Bovendien raken ze elkaar in sommige gevallen. Ze worden bijvoorbeeld gecombineerd met DDL- en DML-databases, evenals gebruikersfuncties (codenaam UDF).

In werkelijkheid hebben opgeslagen SQL-procedures een breed scala aan voordelen die hen onderscheiden van soortgelijke processen. Beveiliging, programmeerflexibiliteit, productiviteit - dit alles trekt steeds meer gebruikers aan die met databases werken. De piekpopulariteit van procedures vond plaats in 2005-2010, toen een programma van Microsoft genaamd "SQL Server Management Studio" werd uitgebracht. Met zijn hulp is het werken met databases veel eenvoudiger, praktischer en handiger geworden. Van jaar tot jaar werd deze populair onder programmeurs. Tegenwoordig is het een absoluut bekend programma, dat voor gebruikers die "communiceren" met databases vergelijkbaar is met Excel.

Wanneer een procedure wordt aangeroepen, wordt deze onmiddellijk door de server zelf verwerkt, zonder onnodige processen of tussenkomst van de gebruiker. Hierna kunt u elke verwijdering, uitvoering of wijziging uitvoeren. Voor dit alles is de DDL-operator verantwoordelijk, die alleen de meest complexe handelingen uitvoert om objecten te verwerken. Bovendien gebeurt dit allemaal heel snel en wordt de server niet daadwerkelijk geladen. Dankzij deze snelheid en prestatie kunt u zeer snel grote hoeveelheden informatie overbrengen van de gebruiker naar de server en omgekeerd.

Om deze technologie voor het werken met informatie te implementeren, zijn er verschillende programmeertalen. Deze omvatten bijvoorbeeld PL/SQL van Oracle, PSQL in de InterBase- en Firebird-systemen, maar ook de klassieke Microsoft Transact-SQL. Ze zijn allemaal ontworpen voor het maken en uitvoeren van opgeslagen procedures, waardoor grote databaseprocessors hun eigen algoritmen kunnen gebruiken. Dit is ook nodig zodat degenen die dergelijke informatie beheren, alle objecten kunnen beschermen tegen ongeoorloofde toegang door derden en dienovereenkomstig tegen het aanmaken, wijzigen of verwijderen van bepaalde gegevens.

Productiviteit

Deze databaseobjecten kunnen op verschillende manieren worden geprogrammeerd. Hierdoor kunnen gebruikers het type methode kiezen dat het meest geschikt is, wat moeite en tijd bespaart. Bovendien wordt de procedure zelf verwerkt, waardoor de enorme tijd die wordt besteed aan de communicatie tussen de server en de gebruiker wordt vermeden. Bovendien kan de module op ieder moment opnieuw worden geprogrammeerd en in de gewenste richting worden gewijzigd. Het is vooral de moeite waard om de snelheid op te merken waarmee de opgeslagen SQL-procedure wordt gestart: dit proces vindt sneller plaats dan andere soortgelijke processen, wat het handig en universeel maakt.

Veiligheid

Dit type informatieverwerking verschilt van soortgelijke processen doordat het een verhoogde veiligheid garandeert. Dit wordt gewaarborgd doordat toegang tot procedures door andere gebruikers volledig kan worden uitgesloten. Hierdoor kan de beheerder zelfstandig operaties met hen uitvoeren, zonder angst voor onderschepping van informatie of ongeautoriseerde toegang tot de database.

Data overdracht

De relatie tussen de opgeslagen SQL-procedure en de clienttoepassing is het gebruik van parameters en retourwaarden. Deze laatste hoeft de gegevens niet door te geven aan de opgeslagen procedure, maar deze informatie wordt (voornamelijk op verzoek van de gebruiker) verwerkt voor SQL. Nadat de opgeslagen procedure zijn werk heeft voltooid, stuurt deze datapakketten terug (maar ook optioneel) naar de applicatie die deze heeft aangeroepen, met behulp van verschillende methoden die kunnen worden gebruikt om de opgeslagen SQL-procedure aan te roepen of terug te keren, bijvoorbeeld:

Gegevens overbrengen met behulp van een parameter Uitgangstype;

Gegevens doorgeven via de retouroperator;

Gegevens doorgeven met behulp van de select-operator.

Laten we nu eens kijken hoe dit proces er van binnenuit uitziet.

1. Maak een opgeslagen EXEC-procedure in SQL

U kunt een procedure aanmaken in MS SQL (Managment Studio). Nadat de procedure is aangemaakt, wordt deze vermeld in het programmeerbare knooppunt van de database, waar de creatieprocedure door de operator wordt uitgevoerd. Voor de uitvoering gebruiken in SQL opgeslagen procedures een EXEC-proces dat de naam van het object zelf bevat.

Wanneer u een procedure maakt, verschijnt eerst de naam ervan, gevolgd door een of meer parameters die eraan zijn toegewezen. Parameters kunnen optioneel zijn. Nadat de parameter(s), dat wil zeggen de hoofdtekst van de procedure, zijn geschreven, moeten enkele noodzakelijke bewerkingen worden uitgevoerd.

Het punt is dat een lichaam lokale variabelen kan bevatten, en deze variabelen zijn ook lokaal in relatie tot procedures. Met andere woorden: ze kunnen alleen worden bekeken binnen de hoofdtekst van een Microsoft SQL Server-procedure. Opgeslagen procedures worden in dit geval als lokaal beschouwd.

Om een ​​procedure te maken, hebben we dus de naam van de procedure nodig en ten minste één parameter als hoofdtekst van de procedure. Houd er rekening mee dat het in dit geval een goede optie is om een ​​procedure met de schemanaam in de classifier te maken en uit te voeren.

De hoofdtekst van de procedure kan van welke aard dan ook zijn, zoals het maken van een tabel, het invoegen van een of meer rijen van een tabel, het vaststellen van het type en de aard van de database, enzovoort. Het procedureorgaan verbiedt echter dat bepaalde handelingen binnen het procedureorgaan worden uitgevoerd. Enkele van de belangrijke beperkingen worden hieronder opgesomd:

Het lichaam mag geen andere opgeslagen procedure aanmaken;

Het lichaam mag geen verkeerde indruk van het object wekken;

Het lichaam mag geen triggers creëren.

2. Een variabele instellen in de hoofdtekst van de procedure

U kunt variabelen lokaal maken voor de hoofdtekst van de procedure, zodat ze uitsluitend in de hoofdtekst van de procedure zullen voorkomen. Het is een goede gewoonte om variabelen aan het begin van de opgeslagen proceduretekst te maken. Maar u kunt ook overal in de hoofdtekst van een bepaald object variabelen instellen.

Soms zult u merken dat er meerdere variabelen op één regel staan, en dat elke variabeleparameter door een komma wordt gescheiden. Merk ook op dat de variabele wordt voorafgegaan door @. In de hoofdtekst van de procedure kunt u de variabele instellen waar u maar wilt. De variabele @NAME1 kan bijvoorbeeld aan het einde van de proceduretekst worden gedeclareerd. Om een ​​waarde aan een gedeclareerde variabele toe te kennen, wordt een set persoonlijke gegevens gebruikt. In tegenstelling tot de situatie waarin meer dan één variabele op dezelfde regel wordt gedeclareerd, wordt in deze situatie slechts één set persoonlijke gegevens gebruikt.

Gebruikers stellen vaak de vraag: “Hoe kan ik meerdere waarden toewijzen in één statement in de hoofdtekst van een procedure?” Goed. Het is een interessante vraag, maar het is veel gemakkelijker te doen dan je denkt. Antwoord: Gebruik paren zoals "Selecteer Var = waarde". U kunt deze paren gebruiken door ze te scheiden met een komma.

Verschillende voorbeelden laten zien dat mensen een eenvoudige opgeslagen procedure maken en deze uitvoeren. Een procedure kan echter zodanige parameters accepteren dat het proces dat de procedure aanroept waarden heeft die er dichtbij liggen (maar niet altijd). Als ze samenvallen, beginnen overeenkomstige processen in het lichaam. Als u bijvoorbeeld een procedure maakt die een stad en regio van de beller accepteert en gegevens retourneert over hoeveel auteurs tot de overeenkomstige stad en regio behoren. De procedure zal de auteurstabellen van de database, zoals Pubs, doorzoeken om deze auteurtelling uit te voeren. Om deze databases te verkrijgen downloadt Google bijvoorbeeld het SQL-script van de SQL2005-pagina.

In het vorige voorbeeld gebruikt de procedure twee parameters, die in het Engels gewoonlijk @State en @City worden genoemd. Het gegevenstype komt overeen met het type dat in de applicatie is gedefinieerd. De hoofdtekst van de procedure bevat interne variabelen @TotalAuthors, en deze variabele wordt gebruikt om het aantal auteurs weer te geven. Vervolgens komt de sectie voor het selecteren van zoekopdrachten, die alles berekent. Ten slotte wordt de berekende waarde afgedrukt in het uitvoervenster met behulp van de printinstructie.

Hoe u een opgeslagen procedure in SQL uitvoert

Er zijn twee manieren om de procedure uit te voeren. De eerste manier laat, door parameters door te geven, zien hoe een door komma's gescheiden lijst wordt uitgevoerd na de procedurenaam. Laten we zeggen dat we twee waarden hebben (zoals in het vorige voorbeeld). Deze waarden worden verzameld met behulp van de procedureparametervariabelen @State en @City. Bij deze methode voor het doorgeven van parameters is de volgorde belangrijk. Deze methode wordt het doorgeven van ordinale argumenten genoemd. Bij de tweede methode worden de parameters al direct toegewezen, en in dit geval is de volgorde niet belangrijk. Deze tweede methode staat bekend als het doorgeven van benoemde argumenten.

De procedure kan enigszins afwijken van de gebruikelijke procedure. Alles is hetzelfde als in het vorige voorbeeld, maar alleen hier zijn de parameters verschoven. Dat wil zeggen dat de parameter @City eerst wordt opgeslagen en @State naast de standaardwaarde. De standaardparameter wordt doorgaans afzonderlijk gemarkeerd. In SQL opgeslagen procedures worden alleen als parameters doorgegeven. In dit geval vervangt de parameter "UT" de standaardwaarde "CA". Bij de tweede uitvoering wordt slechts één argumentwaarde doorgegeven voor de parameter @City en krijgt de parameter @State de standaardwaarde 'CA'. Ervaren programmeurs adviseren dat alle variabelen standaard aan het einde van de parameterlijst moeten staan. Anders is uitvoering niet mogelijk, en dan moet je werken met het doorgeven van benoemde argumenten, wat langer en complexer is.

4. Opgeslagen SQL Server-procedures: retourmethoden

Er zijn drie belangrijke manieren om gegevens te verzenden in een aangeroepen opgeslagen procedure. Ze staan ​​hieronder vermeld:

Retourneer de waarde van een opgeslagen procedure;

Uitvoer van opgeslagen procedureparameters;

Een van de opgeslagen procedures selecteren.

4.1 Waarden retourneren uit in SQL opgeslagen procedures

Bij deze techniek wijst een procedure een waarde toe aan een lokale variabele en retourneert deze. Een procedure kan ook rechtstreeks een constante waarde retourneren. In het volgende voorbeeld hebben we een procedure gemaakt die het totale aantal auteurs retourneert. Als u deze procedure vergelijkt met de vorige, ziet u dat de afdrukwaarde omgekeerd is.

Laten we nu eens kijken hoe we een procedure kunnen uitvoeren en de retourwaarde ervan kunnen afdrukken. Voor het uitvoeren van de procedure is het instellen van een variabele en het afdrukken vereist, wat na dit hele proces wordt uitgevoerd. Houd er rekening mee dat u in plaats van een printinstructie een Select-instructie kunt gebruiken, zoals Select @RetValue en OutputValue.

4.2 SQL opgeslagen procedureparameteruitvoer

De responswaarde kan worden gebruikt om een ​​enkele variabele te retourneren, zoals we in het vorige voorbeeld hebben gezien. Door de parameter Output te gebruiken, kan een procedure een of meer variabelewaarden naar de aanroeper sturen. De uitvoerparameter wordt bij het maken van een procedure precies aangeduid met dit trefwoord “Uitvoer”. Als een parameter als uitvoerparameter wordt opgegeven, moet het procedureobject daaraan een waarde toekennen. Opgeslagen SQL-procedures, waarvan u hieronder voorbeelden kunt zien, komen in dit geval terug met samenvattende informatie.

In ons voorbeeld zijn er twee uitvoernamen: @TotalAuthors en @TotalNoContract. Ze worden aangegeven in de lijst met parameters. Deze variabelen wijzen waarden toe binnen de hoofdtekst van de procedure. Wanneer we uitvoerparameters gebruiken, kan de aanroeper de waarde zien die is ingesteld in de hoofdtekst van de procedure.

Ook worden in het vorige scenario twee variabelen gedeclareerd om de waarden te zien die de MS SQL Server opgeslagen procedures in de uitvoerparameter hebben ingesteld. Vervolgens wordt de procedure uitgevoerd door de normale waarde van de parameter "CA" op te geven. De volgende parameters zijn uitvoerparameters en daarom worden de gedeclareerde variabelen in de opgegeven volgorde doorgegeven. Houd er rekening mee dat bij het doorgeven van variabelen hier ook het uitvoertrefwoord wordt ingesteld. Nadat de procedure met succes is voltooid, worden de waarden die door de uitvoerparameters worden geretourneerd, weergegeven in het berichtenvenster.

4.3 Een van de opgeslagen SQL-procedures selecteren

Deze techniek wordt gebruikt om een ​​set waarden als datatabel (RecordSet) terug te sturen naar de aanroepende opgeslagen procedure. In dit voorbeeld bevraagt ​​de opgeslagen SQL-procedure met @AuthID-parameters de tabel Auteurs door de geretourneerde records te filteren met behulp van die @AuthId-parameter. De Select-instructie bepaalt wat moet worden teruggestuurd naar de aanroeper van de opgeslagen procedure. Wanneer de opgeslagen procedure wordt uitgevoerd, wordt de AuthId teruggestuurd. Deze procedure retourneert hier altijd slechts één record of helemaal geen record. Maar een opgeslagen procedure kent geen beperkingen voor het retourneren van meer dan één record. Het is niet ongebruikelijk om voorbeelden te zien waarin gegevens worden geretourneerd met behulp van geselecteerde parameters met berekende variabelen door meerdere totalen op te geven.

Eindelijk

Een opgeslagen procedure is een redelijk serieuze programmamodule die terugkeert of passeert, en ook de nodige variabelen instelt dankzij de clientapplicatie. Omdat de opgeslagen procedure zichzelf op de server draait, kunnen enorme hoeveelheden gegevensuitwisseling tussen de server en de clientapplicatie (voor sommige berekeningen) worden vermeden. Hiermee kunt u de belasting van SQL-servers verminderen, wat uiteraard ten goede komt aan de eigenaren. Een van de subtypen zijn opgeslagen T SQL-procedures, maar hun onderzoek is noodzakelijk voor degenen die indrukwekkende databases maken. Er zijn ook een groot, zelfs enorm aantal nuances die nuttig kunnen zijn bij het bestuderen van opgeslagen procedures, maar dit is meer nodig voor degenen die van plan zijn betrokken te raken bij programmeren, ook professioneel.

Opgeslagen procedure is een speciaal type Transact-SQL-instructiepakket gemaakt met behulp van de SQL-taal en procedurele extensies. Het belangrijkste verschil tussen een pakket en een opgeslagen procedure is dat deze laatste wordt opgeslagen als een databaseobject. Met andere woorden: opgeslagen procedures worden aan de serverzijde opgeslagen om de prestaties en consistentie van herhaalbare taken te verbeteren.

De Database Engine ondersteunt opgeslagen procedures en systeemprocedures. Opgeslagen procedures worden op dezelfde manier gemaakt als alle andere databaseobjecten, d.w.z. met behulp van DDL-taal. Systeemprocedures worden geleverd door de Database Engine en kunnen worden gebruikt om toegang te krijgen tot informatie in de systeemcatalogus en deze te wijzigen.

Wanneer u een opgeslagen procedure maakt, kunt u een optionele lijst met parameters definiëren. Op deze manier accepteert de procedure de juiste argumenten telkens wanneer deze wordt aangeroepen. Opgeslagen procedures kunnen een waarde retourneren die door de gebruiker gedefinieerde informatie bevat of, in geval van een fout, een passend foutbericht.

De opgeslagen procedure wordt vooraf gecompileerd voordat deze als object in de database wordt opgeslagen. De vooraf gecompileerde vorm van de procedure wordt opgeslagen in de database en wordt elke keer gebruikt wanneer deze wordt aangeroepen. Deze eigenschap van opgeslagen procedures biedt het belangrijke voordeel dat het (in bijna alle gevallen) herhaalde procedurecompilaties elimineert en overeenkomstige prestatieverbeteringen bereikt. Deze eigenschap van opgeslagen procedures heeft ook een positief effect op de hoeveelheid gegevens die wordt uitgewisseld tussen het databasesysteem en applicaties. In het bijzonder kan het aanroepen van een opgeslagen procedure die enkele duizenden bytes groot is, minder dan 50 bytes vereisen. Wanneer meerdere gebruikers repetitieve taken uitvoeren met behulp van opgeslagen procedures, kan het cumulatieve effect van deze besparingen behoorlijk aanzienlijk zijn.

Opgeslagen procedures kunnen ook voor de volgende doeleinden worden gebruikt:

om een ​​logboek van acties met databasetabellen te maken.

Het gebruik van opgeslagen procedures biedt een niveau van beveiligingscontrole dat veel verder gaat dan de beveiliging die wordt geboden door het gebruik van GRANT- en REVOKE-instructies, die verschillende toegangsrechten aan gebruikers verlenen. Dit is mogelijk omdat de autorisatie om een ​​opgeslagen procedure uit te voeren onafhankelijk is van de autorisatie om de objecten in de opgeslagen procedure te wijzigen, zoals beschreven in de volgende sectie.

Opgeslagen procedures die logboeken van tabelschrijf- en/of leesbewerkingen creëren, bieden een extra optie voor databasebeveiliging. Met behulp van dergelijke procedures kan de databasebeheerder toezicht houden op wijzigingen die door gebruikers of toepassingsprogramma's in de database zijn aangebracht.

Opgeslagen procedures maken en uitvoeren

Opgeslagen procedures worden gemaakt met behulp van een instructie PROCEDURE MAKEN, die de volgende syntaxis heeft:

CREATE PROC proc_name [((@param1) type1 [ VARIËREN] [= standaard1] )] (, ...) AS batch | EXTERNE NAAM methodenaam Syntaxisconventies

De parameter schema_name specificeert de naam van het schema dat is toegewezen door de eigenaar van de gemaakte opgeslagen procedure. De parameter proc_name specificeert de naam van de opgeslagen procedure. De parameter @param1 is een procedureparameter (formeel argument) waarvan het gegevenstype wordt bepaald door de parameter type1. Procedureparameters zijn lokaal binnen de procedure, net zoals lokale variabelen lokaal zijn binnen het pakket. Procedureparameters zijn waarden die door de beller aan de procedure worden doorgegeven om daarin te worden gebruikt. De parameter default1 specificeert de standaardwaarde voor de corresponderende procedureparameter. (De standaardwaarde kan ook NULL zijn.)

OUTPUT-optie geeft aan dat een procedureparameter een retourparameter is en kan worden gebruikt om een ​​waarde uit een opgeslagen procedure terug te sturen naar de aanroepende procedure of het systeem.

Zoals eerder vermeld, wordt de vooraf gecompileerde vorm van een procedure opgeslagen in de database en gebruikt elke keer dat deze wordt aangeroepen. Als de opgeslagen procedure om de een of andere reden elke keer dat deze wordt aangeroepen, moet worden gecompileerd, gebruikt u bij het declareren van de procedure MET HERCOMPILE-optie. Het gebruik van de optie WITH RECOMPILE doet een van de belangrijkste voordelen van opgeslagen procedures teniet: de prestatieverbetering als gevolg van een enkele compilatie. Daarom moet de optie WITH RECOMPILE alleen worden gebruikt als de databaseobjecten die door de opgeslagen procedure worden gebruikt regelmatig worden gewijzigd.

EXECUTE AS-clausule definieert de beveiligingscontext waarin de opgeslagen procedure moet worden uitgevoerd nadat deze is aangeroepen. Door deze context in te stellen, kan de Database Engine de selectie van gebruikersaccounts beheren om de toegangsrechten tot de objecten waarnaar door de opgeslagen procedure wordt verwezen, te verifiëren.

Standaard kunnen alleen leden met de vaste serverrol sysadmin en de vaste databaserollen db_owner of db_ddladmin de instructie CREATE PROCEDURE gebruiken. Maar leden van deze rollen kunnen dit recht met behulp van de verklaring aan andere gebruikers toewijzen SUBSIDIECREATIEPROCEDURE.

Het onderstaande voorbeeld laat zien hoe u een eenvoudige opgeslagen procedure kunt maken om met de Project-tabel te werken:

GEBRUIK SampleDb; GA PROCEDURE MAKEN VerhoogBudget (@percent INT=5) ALS UPDATE Project SET Budget = Budget + Budget * @percent/100;

Zoals eerder vermeld, gebruikt u om twee pakketten te scheiden GO-instructies. De instructie CREATE PROCEDURE kan niet worden gecombineerd met andere Transact-SQL-instructies in dezelfde batch. De opgeslagen procedure VerhoogBudget verhoogt de budgetten voor alle projecten met een bepaald percentage, bepaald door de parameter @percent. De procedure definieert ook een standaardpercentagewaarde (5) die wordt gebruikt als dit argument niet aanwezig is wanneer de procedure wordt uitgevoerd.

Opgeslagen procedures hebben toegang tot tabellen die niet bestaan. Met deze eigenschap kunt u fouten in de procedurecode opsporen zonder eerst de juiste tabellen te maken of zelfs maar verbinding te maken met de doelserver.

In tegenstelling tot primair opgeslagen procedures, die altijd in de huidige database worden opgeslagen, is het mogelijk om tijdelijk opgeslagen procedures te maken die altijd in de tijdelijke systeemdatabase tempdb worden opgeslagen. Een reden om tijdelijk opgeslagen procedures te maken kan zijn om te voorkomen dat een specifieke groep instructies herhaaldelijk wordt uitgevoerd bij het verbinden met een database. U kunt lokale of globale tijdelijke procedures creëren. Om dit te doen, wordt de naam van de lokale procedure gespecificeerd met een enkel #-teken (#proc_name), en de naam van de globale procedure wordt gespecificeerd met een dubbel teken (##proc_name).

Een lokaal tijdelijk opgeslagen procedure kan alleen worden uitgevoerd door de gebruiker die deze heeft gemaakt, en alleen als deze is verbonden met de database waarin deze is gemaakt. Een globale tijdelijke procedure kan door alle gebruikers worden uitgevoerd, maar alleen totdat de laatste verbinding waarop deze wordt uitgevoerd (meestal de verbinding van de maker van de procedure) wordt beëindigd.

De levenscyclus van een opgeslagen procedure bestaat uit twee fasen: de creatie en de uitvoering ervan. Elke procedure wordt één keer gemaakt en vele malen uitgevoerd. De opgeslagen procedure wordt uitgevoerd met behulp van UITVOEREN instructies een gebruiker die de eigenaar is van een procedure of het recht EXECUTE heeft om toegang te krijgen tot die procedure. De EXECUTE-instructie heeft de volgende syntaxis:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] waarde | [@parameter1=] @variabele ] | DEFAULT).. Syntaxisconventies

Met uitzondering van de parameter return_status hebben alle parameters van de instructie EXECUTE dezelfde logische betekenis als dezelfde parameters van de instructie CREATE PROCEDURE. De parameter return_status specificeert een geheel getalvariabele die de retourstatus van de procedure opslaat. Er kan een waarde aan een parameter worden toegewezen met behulp van een constante (waarde) of een lokale variabele (@variabele). De volgorde van de waarden van benoemde parameters is niet belangrijk, maar de waarden van niet nader genoemde parameters moeten worden opgegeven in de volgorde waarin ze zijn gedefinieerd in de instructie CREATE PROCEDURE.

DEFAULT-clausule levert de standaardwaarde voor een procedureparameter die is opgegeven in de proceduredefinitie. Wanneer een procedure een waarde verwacht voor een parameter waarvoor geen standaardwaarde is gedefinieerd en de parameter ontbreekt of het sleutelwoord DEFAULT is opgegeven, treedt er een fout op.

Wanneer de EXECUTE-instructie de eerste instructie van een batch is, kan het trefwoord EXECUTE worden weggelaten. Het is echter veiliger om dit woord in elk pakket op te nemen. Het gebruik van de EXECUTE-instructie wordt weergegeven in het onderstaande voorbeeld:

GEBRUIK SampleDb; UITVOEREN Budget verhogen 10;

De instructie EXECUTE in dit voorbeeld voert de opgeslagen procedure VerhoogBudget uit, waardoor het budget van alle projecten met 10% wordt verhoogd.

In het onderstaande voorbeeld ziet u hoe u een opgeslagen procedure maakt om gegevens in de tabellen Employee en Works_on te verwerken:

De voorbeeldprocedure ModifyEmpId illustreert het gebruik van opgeslagen procedures als onderdeel van het proces voor het handhaven van referentiële integriteit (in dit geval tussen de tabellen Employee en Works_on). Een soortgelijke opgeslagen procedure kan worden gebruikt binnen een triggerdefinitie, die feitelijk referentiële integriteit biedt.

In het volgende voorbeeld ziet u het gebruik van een OUTPUT-clausule in een opgeslagen procedure:

Deze opgeslagen procedure kan worden uitgevoerd met behulp van de volgende instructies:

VERKLAREN @quantityDeleteEmployee INT; UITVOEREN VerwijderEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Verwijderde werknemers: " + convert(nvarchar(30), @quantityDeleteEmployee);

Deze procedure telt het aantal projecten waaraan de medewerker met personeelsnummer @empId werkt en wijst de resulterende waarde toe aan de parameter ©counter. Nadat alle rijen voor een bepaald personeelsnummer zijn verwijderd uit de tabellen Employee en Works_on, wordt de berekende waarde toegewezen aan de variabele @quantityDeleteEmployee.

De parameterwaarde wordt alleen geretourneerd naar de aanroepprocedure als de optie OUTPUT is opgegeven. In het bovenstaande voorbeeld geeft de procedure DeleteEmployee de parameter @counter door aan de aanroepende procedure, waardoor de opgeslagen procedure een waarde retourneert aan het systeem. Daarom moet de parameter @counter worden opgegeven in zowel de optie OUTPUT bij het declareren van een procedure als in de instructie EXECUTE bij het aanroepen ervan.

WITH RESULTS SETS-clausule van EXECUTE-instructie

In SQL Server 2012 voert u voor de instructie EXECUTE in WITH RESULTS SETS-clausule, waarmee u, wanneer aan bepaalde voorwaarden is voldaan, de vorm van de resultatenset van een opgeslagen procedure kunt wijzigen.

De volgende twee voorbeelden zullen deze zin helpen verklaren. Het eerste voorbeeld is een inleidend voorbeeld dat laat zien hoe het resultaat eruit zou kunnen zien als de clausule WITH RESULTS SETS wordt weggelaten:

De procedure WerknemersInDept is een eenvoudige procedure die de personeelsnummers en achternamen weergeeft van alle medewerkers die op een bepaalde afdeling werken. Het afdelingsnummer is een procedureparameter en moet worden opgegeven bij het oproepen ervan. Het uitvoeren van deze procedure levert een tabel op met twee kolommen waarvan de koppen overeenkomen met de namen van de overeenkomstige kolommen in de databasetabel, d.w.z. Id en Achternaam. Om de kopteksten van resultaatkolommen (evenals hun gegevenstype) te wijzigen, gebruikt SQL Server 2012 de nieuwe WITH RESULTS SETS-clausule. De toepassing van deze zin wordt weergegeven in het onderstaande voorbeeld:

GEBRUIK SampleDb; EXEC EmployeesInDept "d1" MET RESULTAATSETS (( INT NOT NULL, [Achternaam] CHAR(20) NOT NULL));

Het resultaat van het uitvoeren van een opgeslagen procedure die op deze manier wordt aangeroepen, is als volgt:

Zoals u kunt zien, kunt u door een opgeslagen procedure uit te voeren met behulp van de clausule WITH RESULT SETS in de instructie EXECUTE de namen en gegevenstypen wijzigen van de kolommen in de resultatenset die door de procedure wordt geproduceerd. Deze nieuwe functionaliteit biedt dus meer flexibiliteit bij het uitvoeren van opgeslagen procedures en het plaatsen van hun resultaten in een nieuwe tabel.

De structuur van opgeslagen procedures wijzigen

De Database Engine ondersteunt ook de instructie WIJZIG PROCEDURE om de structuur van opgeslagen procedures te wijzigen. De instructie ALTER PROCEDURE wordt doorgaans gebruikt om Transact-SQL-instructies binnen een procedure te wijzigen. Alle parameters van de instructie ALTER PROCEDURE hebben dezelfde betekenis als dezelfde parameters van de instructie CREATE PROCEDURE. Het belangrijkste doel van het gebruik van deze verklaring is om te voorkomen dat bestaande rechten voor opgeslagen procedures worden overschreven.

De database-engine ondersteunt CURSOR-gegevenstype. Dit gegevenstype wordt gebruikt om cursors in opgeslagen procedures te declareren. Cursor is een programmeerconstructie die wordt gebruikt om de resultaten van een zoekopdracht (meestal een reeks rijen) op te slaan en gebruikers in staat te stellen dat resultaat rij voor rij weer te geven.

Gebruik om één of een groep opgeslagen procedures te verwijderen DROP PROCEDURE-instructie. Alleen de eigenaar of leden van de vaste rollen db_owner en sysadmin kunnen een opgeslagen procedure verwijderen.

Opgeslagen procedures en de runtime van de gemeenschappelijke taal

SQL Server ondersteunt de Common Language Runtime (CLR), waarmee u verschillende databaseobjecten (opgeslagen procedures, door de gebruiker gedefinieerde functies, triggers, door de gebruiker gedefinieerde aggregaties en aangepaste gegevenstypen) kunt ontwikkelen met behulp van C# en Visual Basic. Met de CLR kunt u deze objecten ook uitvoeren met behulp van het algemene runtime-systeem.

De gemeenschappelijke taalruntime wordt in- en uitgeschakeld met behulp van de optie clr_enabled systeemprocedure sp_configure, dat wordt gelanceerd voor uitvoering via instructie HERCONFIGUREER. In het volgende voorbeeld ziet u hoe u de systeemprocedure sp_configure kunt gebruiken om de CLR in te schakelen:

GEBRUIK SampleDb; EXEC sp_configure "clr_enabled",1 HERCONFIGUREER

Om een ​​procedure te maken, compileren en opslaan met behulp van de CLR, moet u de volgende reeks stappen in de aangegeven volgorde uitvoeren:

Maak een opgeslagen procedure in C# of Visual Basic en compileer deze vervolgens met de juiste compiler.

Instructies gebruiken CREËER MONTAGE, maak het bijbehorende uitvoerbare bestand.

Voer de procedure uit met behulp van de EXECUTE-instructie.

Onderstaande figuur toont een grafisch diagram van de eerder geschetste stappen. Hieronder volgt een meer gedetailleerde beschrijving van dit proces.

Maak eerst het benodigde programma in een ontwikkelomgeving zoals Visual Studio. Compileer het voltooide programma in objectcode met behulp van een C#- of Visual Basic-compiler. Deze code wordt opgeslagen in een Dynamic-Link Library-bestand (.dll), dat dient als bron voor de instructie CREATE ASSEMBLY, waarmee de tussenliggende uitvoerbare code wordt gemaakt. Voer vervolgens een CREATE PROCEDURE-instructie uit om de uitvoerende code op te slaan als een databaseobject. Voer ten slotte de procedure uit met behulp van de bekende EXECUTE-instructie.

Het onderstaande voorbeeld toont de broncode voor een opgeslagen procedure in C#:

System.Data.SqlClient gebruiken; met behulp van Microsoft.SqlServer.Server; openbare gedeeltelijke klasse StoredProcedures ( public static int CountEmployees() ( int rijen; SqlConnection verbinding = new SqlConnection("Context Connection=true"); verbinding.Open(); SqlCommand cmd = verbinding.CreateCommand(); cmd.CommandText = "select count(*) as "Aantal werknemers" " + "van werknemer"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

Met deze procedure wordt een query geïmplementeerd om het aantal rijen in de tabel Werknemer te tellen. Met behulp van richtlijnen aan het begin van een programma specificeert u de naamruimten die nodig zijn om het programma uit te voeren. Door deze richtlijnen te gebruiken, kunt u klassenamen in de broncode opgeven zonder expliciet de bijbehorende naamruimten op te geven. Vervolgens wordt de klasse StoredProcedures gedefinieerd, waarvoor SqlProcedure-kenmerk, wat de compiler informeert dat deze klasse een opgeslagen procedure is. De methode CountEmployees() wordt gedefinieerd in de klassecode. Er wordt een verbinding met het databasesysteem tot stand gebracht via een exemplaar van de klasse SQL-verbinding. Om een ​​verbinding te openen, wordt de Open()-methode van deze instantie gebruikt. A CreateCommand()-methode Hiermee kunt u toegang krijgen tot een exemplaar van een klasse SQLCommnd, waaraan de vereiste SQL-opdracht wordt doorgegeven.

In het volgende codefragment:

Cmd.CommandText = "selecteer aantal(*) als "Aantal werknemers" " + "van werknemer";

gebruikt een SELECT-instructie om het aantal rijen in de tabel Werknemer te tellen en het resultaat weer te geven. De opdrachttekst wordt opgegeven door de eigenschap CommandText van de cmd-variabele in te stellen op de instantie die wordt geretourneerd door de methode CreateCommand(). Vervolgens wordt gebeld ExecuteScalar()-methode SQLCommand-instantie. Deze methode retourneert een scalaire waarde die wordt geconverteerd naar een gegevenstype met gehele getallen en wordt toegewezen aan de variabele rijen.

U kunt deze code nu compileren met Visual Studio. Ik heb deze klasse toegevoegd aan een project met de naam CLRStoredProcedures, zodat Visual Studio een assembly met dezelfde naam zal compileren met de extensie *.dll. Het onderstaande voorbeeld toont de volgende stap bij het maken van een opgeslagen procedure: het maken van de uitvoerbare code. Voordat u de code in dit voorbeeld uitvoert, moet u de locatie van het gecompileerde dll-bestand kennen (meestal in de map Debug van het project).

GEBRUIK SampleDb; GA MONTAGE CLRStoredProcedures MAKEN VAN "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" MET PERMISSION_SET = VEILIG

De instructie CREATE ASSEMBLY neemt beheerde code als invoer en creëert een corresponderend object waarop u opgeslagen CLR-procedures, door de gebruiker gedefinieerde functies en triggers kunt maken. Deze instructie heeft de volgende syntaxis:

CREATE ASSEMBLY assembly_name [AUTHORISATION eigenaar_naam] FROM (dll_file) Syntaxisconventies

De parameter assembly_name specificeert de naam van de assembly. De optionele AUTHORIZATION-clausule specificeert de rolnaam als eigenaar van deze assembly. De FROM-clausule specificeert het pad waar de te laden assembly zich bevindt.

WITH PERMISSION_SET-clausule is een zeer belangrijke clausule van de instructie CREATE ASSEMBLY en moet altijd worden opgegeven. Het definieert de set machtigingen die aan de assemblagecode zijn verleend. De SAFE-machtigingenset is het meest beperkend. Assemblagecode die deze rechten heeft, heeft geen toegang tot externe systeembronnen zoals bestanden. Met de EXTERNAL_ACCESS-rechtenset krijgt assemblagecode toegang tot bepaalde externe systeembronnen, terwijl de UNSAFE-rechtenset onbeperkte toegang tot bronnen zowel binnen als buiten het databasesysteem mogelijk maakt.

Om assemblagecode-informatie op te slaan, moet de gebruiker een CREATE ASSEMBLY-instructie kunnen geven. De eigenaar van de assembly is de gebruiker (of rol) die de instructie uitvoert. U kunt een andere gebruiker eigenaar van de assembly maken door de AUTHORIZATION-clausule van de CREATE SCHEMA-instructie te gebruiken.

De database-engine ondersteunt ook ALTER ASSEMBLY- en DROP ASSEMBLY-instructies. ALTER ASSEMBLY-verklaring gebruikt om de assembly bij te werken naar de nieuwste versie. Met deze instructie worden ook bestanden toegevoegd of verwijderd die zijn gekoppeld aan de overeenkomstige assembly. DROP MONTAGE-instructie Verwijdert de opgegeven assembly en alle bijbehorende bestanden uit de huidige database.

In het onderstaande voorbeeld ziet u hoe u een opgeslagen procedure maakt op basis van de beheerde code die u eerder hebt geïmplementeerd:

GEBRUIK SampleDb; GA PROCEDURE MAKEN CountEmployees ALS EXTERNE NAAM CLRStoredProcedures.StoredProcedures.CountEmployees

De CREATE PROCEDURE-instructie in het voorbeeld verschilt van dezelfde instructie in de voorgaande voorbeelden doordat deze de instructie bevat EXTERNE NAAM-parameter. Deze optie geeft aan dat de code wordt gegenereerd door de runtime van de gemeenschappelijke taal. De naam in deze zin bestaat uit drie delen:

assemblagenaam.klassenaam.methodenaam

assembly_name - geeft de naam van de assembly aan;

class_name - geeft de naam van de algemene klasse aan;

method_name - optioneel deel, specificeert de naam van de methode die binnen de klasse is gedefinieerd.

De uitvoering van de CountEmployees-procedure wordt weergegeven in het onderstaande voorbeeld:

GEBRUIK SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Return 7

De PRINT-instructie retourneert het huidige aantal rijen in de tabel Werknemer.

Opgeslagen procedure opgeslagen procedure) is een benoemd databaseprogrammaobject. SQL Server heeft verschillende soorten opgeslagen procedures.

Systeem opgeslagen procedures in het systeem opgeslagen procedures) worden geleverd door DBMS-ontwikkelaars en worden gebruikt om acties uit te voeren met de systeemdirectory of om systeeminformatie te verkrijgen. Hun namen beginnen meestal met het voorvoegsel "sp_". U voert alle soorten opgeslagen procedures uit met de opdracht EXECUTE, die kan worden ingekort tot EXEC. De opgeslagen procedure sp_helplogins, uitgevoerd zonder parameters, produceert bijvoorbeeld twee rapporten over accountnamen (Engels) logins) en de corresponderende gebruikers in elke database (Engels) gebruikers).

EXEC sp_helplogins;

Om een ​​idee te geven van de acties die worden uitgevoerd met behulp van in het systeem opgeslagen procedures, Tabel 10.6 toont enkele voorbeelden. In totaal zijn er meer dan duizend door het systeem opgeslagen procedures in SQL Server.

Tabel 10.6

Voorbeelden van opgeslagen procedures in SQL Server-systeem

De gebruiker kan opgeslagen procedures creëren in gebruikersdatabases en in de database voor tijdelijke objecten. In het laatste geval zal de opgeslagen procedure dat zijn tijdelijk. Net als bij tijdelijke tabellen moet de naam van een tijdelijk opgeslagen procedure beginnen met het voorvoegsel "#" als het een lokale tijdelijk opgeslagen procedure is, of met "##" als het een globale procedure is. Een lokale tijdelijke procedure kan alleen worden gebruikt binnen de verbinding waarin deze is aangemaakt, een globale procedure kan ook binnen andere verbindingen worden gebruikt.

Programmeerbare objecten van SQL Server kunnen worden gemaakt met behulp van Transact-SQL-hulpmiddelen of -assemblages (Engels) assembly) in de CRL-omgeving (Common Language Runtime) van Microsoft.Net Framework. Deze tutorial behandelt alleen de eerste methode.

Om opgeslagen procedures te maken, gebruikt u de instructie CREATE PROCEDURE (kan worden afgekort tot PROC), waarvan het formaat hieronder wordt gegeven:

CREËREN (PROC I PROCEDURE) proc_name [; nummer ]

[(gparametergegevenstype)

[“standaard] |

[MET [ ,...N ] ]

[VOOR REPLICATIE]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [END ] )

Als een opgeslagen procedure (of trigger, functie, weergave) wordt gemaakt met de optie ENCRYPTIE, wordt de code ervan zodanig getransformeerd dat de tekst onleesbaar wordt. Tegelijkertijd is, zoals opgemerkt in, het gebruikte algoritme overgebracht van eerdere versies van SQL Server en kan het niet als een betrouwbaar beveiligingsalgoritme worden beschouwd - er zijn hulpprogramma's waarmee u snel de omgekeerde conversie kunt uitvoeren.

De optie RECOMPILE geeft aan dat het systeem de tekst opnieuw compileert telkens wanneer de procedure wordt aangeroepen. In het normale geval wordt de procedure die bij de eerste run is gecompileerd, opgeslagen in de cache, wat betere prestaties mogelijk maakt.

EXECUTE AS specificeert de beveiligingscontext waarin de procedure moet worden uitgevoerd. Vervolgens een van de waarden f CALLER | ZELF | EIGENAAR | "gebruikersnaam"). CALLER is de standaardwaarde en betekent dat de code wordt uitgevoerd in de beveiligingscontext van de gebruiker die deze module aanroept. Dienovereenkomstig moet de gebruiker niet alleen machtigingen hebben voor het programmeerbare object zelf, maar ook voor andere databaseobjecten die erdoor worden beïnvloed. UITVOEREN ALS ZELF betekent het gebruik van de context van de gebruiker die het programmeerbare object maakt of wijzigt. OWNER specificeert dat de code zal worden uitgevoerd in de context van de huidige eigenaar van de procedure. Als er geen eigenaar voor wordt opgegeven, wordt aangenomen dat de eigenaar is van het schema waartoe het behoort. Met EXECUTE AS "gebruikersnaam" kunt u de gebruikersnaam expliciet opgeven (tussen enkele aanhalingstekens).

Voor een procedure kunnen parameters worden opgegeven. Dit zijn lokale variabelen die worden gebruikt om waarden door te geven aan een procedure. Als een parameter wordt gedeclareerd met het trefwoord OUTPUT (of kortweg OUT), is het een uitvoerwaarde: de waarde die eraan wordt gegeven in de procedure nadat deze is voltooid, kan worden gebruikt door het programma dat de procedure heeft aangeroepen. Het READONLY-sleutelwoord betekent dat de waarde van de parameter niet binnen de opgeslagen procedure kan worden gewijzigd.

Aan parameters kunnen standaardwaarden worden toegewezen, die worden gebruikt als de parameterwaarde niet expliciet wordt opgegeven bij het aanroepen van de procedure. Laten we eens kijken naar een voorbeeld:

CREËER PROC surma (@a int, @b int=0,

©resultaat int OUTPUT) AS

SET @resultaat=0a+0b

We hebben een procedure gemaakt met drie parameters, en de parameter @b heeft de standaardwaarde =0, en de parameter @result is een uitvoerparameter: deze retourneert de waarde naar het oproepende programma. De uitgevoerde acties zijn vrij eenvoudig: de uitvoerparameter ontvangt de waarde van de som van twee invoerparameters.

Wanneer u in SQL Server Management Studio werkt, kunt u de gemaakte opgeslagen procedure vinden in de sectie Programmeerbare databaseobjecten (Engels) Programmeerbaarheid) in de subsectie voor opgeslagen procedures (Fig. 10.2).

Bij het aanroepen van een procedure kunt u zowel variabelen als constanten als invoerparameters gebruiken. Laten we naar twee voorbeelden kijken. In het eerste geval worden de invoerparameters van de procedure expliciet gespecificeerd als constanten, en wordt het sleutelwoord OUTPUT gespecificeerd voor de uitvoerparameter in de aanroep. De tweede optie gebruikt de waarde van een variabele als de eerste invoerparameter en specificeert dat de standaardwaarde moet worden gebruikt voor de tweede parameter met behulp van het trefwoord DEFAULT:

Rijst. 10.2.

VERKLAREN @с int;

EXEC summa 10.5,@c UITGANG;

AFDRUKKEN 0c; – 15 wordt weergegeven

VERKLAREN Gi int = 5;

– gebruik bij het bellen de standaardwaarde

EXEC summa Gi,DEFAULT, 0c UITGANG;

AFDRUKKEN 0c; – 5 wordt weergegeven

Laten we nu een voorbeeld bekijken met de analyse van de retourcode waarmee de procedure eindigt. Stel dat we moeten berekenen hoeveel boeken in de Bookl-tabel in een bepaalde periode van jaren zijn gepubliceerd. Bovendien, als het eerste jaar groter is dan het laatste jaar, retourneert de procedure “1” en telt deze niet mee. Anders tellen we het aantal boeken en retourneren we 0:

CREËER PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

ALS 0EersteJaar>0AfgelopenJaar RETOUR 1

SET @result= (SELECTEER AANTAL(*) UIT dbo.Bookl

WAAR TUSSEN 0FirsYear EN 0LastYear);

Laten we een variant van het aanroepen van deze procedure overwegen, waarbij de retourcode wordt opgeslagen in de gehele variabele 0ret, waarna de waarde ervan wordt geanalyseerd (in dit geval zal deze 1 zijn). De CAST-functie die in de PRINT-instructie wordt gebruikt, wordt gebruikt om de waarde van de gehele variabele Gres naar een stringtype te converteren:

VERKLAREN 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres UIT;

IF 0ret=l PRINT "Beginjaar is groter dan eindjaar"

PRINT "Aantal boeken" + CAST(Gres als varchar(20))

Opgeslagen procedures kunnen niet alleen gegevens uit een tabel lezen, maar ook gegevens wijzigen en zelfs tabellen en een aantal andere databaseobjecten maken.

U kunt echter geen schema's, functies, triggers, procedures en views maken op basis van een opgeslagen procedure.

Het volgende voorbeeld illustreert zowel deze mogelijkheden als problemen met betrekking tot de reikwijdte van tijdelijke objecten. De volgende opgeslagen procedure controleert op het bestaan ​​van tijdelijke tabel #TaL2; als deze tabel niet bestaat, wordt deze gemaakt. Hierna worden de waarden van twee kolommen ingevoerd in tabel #TaL2 en wordt de inhoud van de tabel weergegeven met behulp van de SELECT-instructie:

MAAK PROC Mijn_Procl (@id int, @name varchar(30))

ALS OBJECT_ID("tempdb.dbo.#Tab21) NULL IS

INSERT INTO dbo.#Tab2 (id, naam)VALUES (0id,0naam)

SELECTEER * VAN dbo. #Tab2 –№1

Voordat we de opgeslagen procedure voor de eerste keer aanroepen, maken we de tijdelijke tabel #TaL2 die daarin wordt gebruikt. Let op de EXEC-operator. In de voorgaande voorbeelden werden parameters doorgegeven aan de procedure “op positie”, maar in dit geval wordt een ander formaat gebruikt voor het doorgeven van parameters – “op naam”, de naam van de parameter en zijn waarde worden expliciet aangegeven:

MAAK TABEL dbo.#Tab2 (id int, naam varchar(30));

EXEC Mijn_Procl 0naam = "lvan", 0id = 2;

SELECTEER * VAN dbo.#Tab2; –№2

In het bovenstaande voorbeeld wordt de SELECT-instructie twee keer verwerkt: de eerste keer – binnen de procedure, de tweede keer – vanuit het aanroepende codefragment (gemarkeerd met de opmerking “Nr. 2”).

Vóór de tweede aanroep van de procedure verwijderen we de tijdelijke tabel #TaL2. Vervolgens wordt er een tijdelijke tabel met dezelfde naam gemaakt op basis van de opgeslagen procedure:

DROP TABLE dbo.#Tab2;

EXEC Mijn_Procl 0naam = "Ivan", 0id = 2;

SELECTEER * VAN dbo.#Tab2; –№2

In dit geval zal alleen de SELECT-instructie die zich in de procedure bevindt (met de opmerking “Xa 1”) gegevens weergeven. Het uitvoeren van SELECT "No. 2" zal resulteren in een fout, omdat de tijdelijke tabel die in de opgeslagen procedure is gemaakt al uit de tempdb-database zal worden verwijderd op het moment dat de procedure terugkeert.

U kunt een opgeslagen procedure verwijderen met behulp van de instructie DROP PROCEDURE. Het formaat ervan wordt hieronder weergegeven. U kunt meerdere opgeslagen procedures verwijderen met één instructie, waarbij u ze gescheiden door komma's opsomt:

DROP (PROC I-PROCEDURE) (procedure) [

Laten we bijvoorbeeld de eerder gemaakte summa-procedure verwijderen:

DROP PROC summa;

U kunt wijzigingen aanbrengen in een bestaande procedure (en deze zelfs opnieuw definiëren) met behulp van de instructie ALTER PROCEDURE (toegestaan

afkorting PROC). Met uitzondering van het trefwoord ALTER is het formaat van de instructie in wezen hetzelfde als dat van CREATE PROCEDURE. Laten we bijvoorbeeld de dbo-procedure wijzigen. rownum, waardoor het wordt uitgevoerd in de beveiligingscontext van de eigenaar:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

MET UITVOEREN ALS Eigenaar – installeerbare optie

ALS 0EersteJaar>0LaatsteJaar RETOUR 1 ANDERS BEGINT

SET 0result= (SELECTEER AANTAL(*) UIT dbo.Bookl

WAAR TUSSEN SFirsYear EN SLastYear);

In sommige gevallen kan het nodig zijn om dynamisch een opdracht te genereren en deze uit te voeren op de databaseserver. Dit probleem kan ook worden opgelost met behulp van de EXEC-operator. In het onderstaande voorbeeld worden records uit de Bookl-tabel opgehaald als het attribuut Year gelijk is aan de waarde die door de variabele is opgegeven:

VERKLAREN 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

De uitvoering van dynamisch gegenereerde instructies schept de voorwaarden voor het implementeren van computeraanvallen zoals “SQL-injectie” (Engels) SQL injectie). De essentie van de aanval is dat de aanvaller zijn eigen SQL-code in een dynamisch gegenereerde query injecteert. Dit gebeurt doorgaans wanneer de parameters die worden vervangen, worden overgenomen uit de resultaten van gebruikersinvoer.

Laten we het vorige voorbeeld enigszins wijzigen:

VERKLAREN 0у varchar(100);

SET 0у="2ООО"; – wij hebben dit ontvangen van de gebruiker

Als we ervan uitgaan dat we de stringwaarde die in de SET-instructie is toegewezen, van de gebruiker hebben ontvangen (op welke manier dan ook, bijvoorbeeld via een webapplicatie), dan illustreert het voorbeeld het ‘normale’ gedrag van onze code.

VERKLAREN 0у varchar(100);

SET 0у="2000; VERWIJDEREN UIT dbo.Book2"; – injectie

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

In dergelijke gevallen wordt aanbevolen, indien mogelijk, de door het systeem opgeslagen procedure sp_executcsql te gebruiken, waarmee u het type parameters kunt bepalen, wat een van de obstakels is voor SQL-injecties. Laten we, zonder het formaat in detail te bekijken, eens kijken naar een voorbeeld dat lijkt op het eerder gepresenteerde voorbeeld:

UITVOEREN sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Hiermee wordt expliciet het type parameter opgegeven dat in de query wordt gebruikt, en SQL Server beheert deze tijdens de uitvoering. De letter "N" vóór de aanhalingstekens geeft aan dat dit een letterlijke Unicode-constante is, zoals vereist door de procedure. Aan een parameter kan niet alleen een constante waarde worden toegekend, maar ook de waarde van een andere variabele.

Doel van het werk– leer opgeslagen procedures op de databaseserver te maken en te gebruiken.

1. Doorloop alle voorbeelden en analyseer de resultaten van hun uitvoering in het hulpprogramma SQL Server Management Studio. Controleren van de aanwezigheid van aangemaakte procedures in de huidige database.

2. Het voltooien van alle voorbeelden en taken tijdens laboratoriumwerk.

3. Afzonderlijke taken voltooien volgens opties.

Uitleg voor het uitvoeren van de werkzaamheden

Om het programmeren van opgeslagen procedures onder de knie te krijgen, gebruiken we een voorbeelddatabase genaamd DB_Boeken, dat is gemaakt in laboratoriumwerk nr. 1. Let bij het voltooien van voorbeelden en taken op de overeenstemming van de namen van de database, tabellen en andere projectobjecten.

Opgeslagen procedures zijn een reeks opdrachten die bestaan ​​uit een of meer SQL-instructies of -functies en die in gecompileerde vorm in een database zijn opgeslagen.

Soorten opgeslagen procedures

In het systeem opgeslagen procedures zijn ontworpen om verschillende administratieve acties uit te voeren. Bijna alle serverbeheeractiviteiten worden met hun hulp uitgevoerd. We kunnen zeggen dat in het systeem opgeslagen procedures een interface zijn die werk met systeemtabellen mogelijk maakt. In het systeem opgeslagen procedures worden voorafgegaan door sp_, worden opgeslagen in de systeemdatabase en kunnen worden aangeroepen in de context van elke andere database.

Op maat opgeslagen procedures implementeren bepaalde acties. Opgeslagen procedures zijn een volwaardig databaseobject. Als gevolg hiervan bevindt elke opgeslagen procedure zich in een specifieke database, waar deze wordt uitgevoerd.

Tijdelijk opgeslagen procedures bestaan ​​slechts korte tijd, waarna ze automatisch door de server worden vernietigd. Ze zijn onderverdeeld in lokaal en mondiaal. Lokaal tijdelijk opgeslagen procedures kunnen alleen worden aangeroepen vanuit de verbinding waarin ze zijn gemaakt. Wanneer u een dergelijke procedure maakt, moet u deze een naam geven die begint met een enkel #-teken. Zoals alle tijdelijke objecten worden dit soort opgeslagen procedures automatisch verwijderd wanneer de gebruiker de verbinding verbreekt of de server opnieuw wordt opgestart of gestopt. Globale tijdelijk opgeslagen procedures zijn beschikbaar voor elke verbinding vanaf een server die dezelfde procedure heeft. Om het te definiëren, geeft u het gewoon een naam die begint met de tekens ##. Deze procedures worden verwijderd wanneer de server opnieuw wordt opgestart of gestopt, of wanneer de verbinding in de context waarin ze zijn gemaakt, wordt verbroken.

Opgeslagen procedures maken en wijzigen

Bij het maken van een opgeslagen procedure moeten de volgende problemen worden opgelost: het plannen van toegangsrechten. Wanneer u een opgeslagen procedure maakt, moet u er rekening mee houden dat deze dezelfde toegangsrechten tot databaseobjecten heeft als de gebruiker die deze heeft gemaakt; het definiëren van de parameters van een opgeslagen procedure; opgeslagen procedures kunnen invoer- en uitvoerparameters hebben; ontwikkeling van opgeslagen procedurecodes. De procedurecode kan een reeks SQL-opdrachten bevatten, inclusief aanroepen naar andere opgeslagen procedures.

De syntaxis van de operator voor het maken van een nieuwe of het wijzigen van een bestaande opgeslagen procedure in MS SQL Server-notatie:

( CREATE | ALTER ) PROC[ EDURE] procedurenaam [ ;nummer] [ ( @parameter_name data_type ) [ VARIËREN ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ MET ( HERCOMPILE | ENCRYPTIE | HERCOMPILE, ENCRYPTIE ) ] [ VOOR REPLICATIE] AS sql_statement [ ... n]

Laten we eens kijken naar de parameters van deze opdracht.

Met behulp van de voorvoegsels sp_, #, ## kan de gemaakte procedure worden gedefinieerd als een systeemprocedure of een tijdelijke procedure. Zoals u kunt zien aan de syntaxis van de opdracht, is het niet toegestaan ​​om de naam op te geven van de eigenaar die eigenaar is van de gemaakte procedure, evenals de naam van de database waar deze zich zou moeten bevinden. Om de opgeslagen procedure die u aan het maken bent in een specifieke database te plaatsen, moet u daarom de opdracht CREATE PROCEDURE in de context van die database opgeven. Wanneer u objecten van dezelfde database benadert vanuit de hoofdtekst van een opgeslagen procedure, kunt u verkorte namen gebruiken, d.w.z. zonder de databasenaam op te geven. Wanneer u toegang wilt krijgen tot objecten die zich in andere databases bevinden, is het opgeven van de databasenaam verplicht.

Om invoer- en uitvoergegevens door te geven, moeten de parameternamen in de opgeslagen procedure die u maakt, beginnen met het @-teken. U kunt meerdere parameters opgeven in één opgeslagen procedure, gescheiden door komma's. De hoofdtekst van een procedure mag geen lokale variabelen gebruiken waarvan de namen samenvallen met de namen van de parameters van deze procedure. Elk SQL-gegevenstype, inclusief door de gebruiker gedefinieerde gegevenstypes, is geschikt voor het bepalen van het gegevenstype van opgeslagen procedureparameters. Het gegevenstype CURSOR kan echter alleen worden gebruikt als uitvoerparameter van een opgeslagen procedure, d.w.z. door het trefwoord OUTPUT op te geven.

De aanwezigheid van het trefwoord OUTPUT betekent dat de overeenkomstige parameter bedoeld is om gegevens uit een opgeslagen procedure terug te sturen. Dit betekent echter niet dat de parameter niet geschikt is om waarden door te geven aan een opgeslagen procedure. Door het trefwoord OUTPUT op te geven, krijgt de server de opdracht om bij het afsluiten van een opgeslagen procedure de huidige parameterwaarde toe te wijzen aan de lokale variabele die als parameterwaarde was opgegeven toen de procedure werd aangeroepen. Houd er rekening mee dat bij het opgeven van het trefwoord OUTPUT de waarde van de corresponderende parameter bij het aanroepen van de procedure alleen kan worden ingesteld met behulp van een lokale variabele. Expressies of constanten die zijn toegestaan ​​voor normale parameters zijn niet toegestaan. Het trefwoord VARYING wordt gebruikt in combinatie met de parameter OUTPUT, die van het type CURSOR is. Het specificeert dat de uitvoer de resultatenset zal zijn.

Het sleutelwoord DEFAULT vertegenwoordigt de waarde die de corresponderende parameter standaard zal aannemen. Wanneer u een procedure aanroept, hoeft u dus niet expliciet de waarde van de overeenkomstige parameter op te geven.

Omdat de server het query-uitvoeringsplan en de gecompileerde code in de cache opslaat, worden de kant-en-klare waarden gebruikt de volgende keer dat de procedure wordt aangeroepen. In sommige gevallen is het echter nog steeds nodig om de procedurecode opnieuw te compileren. Door het trefwoord RECOMPILE op te geven, krijgt het systeem de opdracht om elke keer dat deze wordt aangeroepen een uitvoeringsplan voor de opgeslagen procedure te maken.

De parameter FOR REPLICATION is vereist bij het repliceren van gegevens en het opnemen van de gemaakte opgeslagen procedure als artikel in een publicatie. Het sleutelwoord ENCRYPTION instrueert de server om de opgeslagen procedurecode te coderen, wat bescherming kan bieden tegen het gebruik van bedrijfseigen algoritmen die de opgeslagen procedure implementeren. Het AS-sleutelwoord wordt aan het begin van de opgeslagen procedure zelf geplaatst. De proceduretekst kan bijna alle SQL-opdrachten gebruiken, transacties declareren, vergrendelingen instellen en andere opgeslagen procedures aanroepen. U kunt een opgeslagen procedure afsluiten met de opdracht RETURN.

Een opgeslagen procedure verwijderen

DROP-PROCEDURE (procedurenaam) [,...n]

Een opgeslagen procedure uitvoeren

Om een ​​opgeslagen procedure uit te voeren, gebruikt u het commando: [ [ EXEC [UTE] procedurenaam [ ;nummer] [ [ @parameternaam= ] ( waarde | @variabele_naam) [ OUTPUT ] | [ STANDAARD ] ] [ ,... n]

Als de opgeslagen procedureaanroep niet de enige opdracht in de batch is, is de opdracht EXECUTE vereist. Bovendien is dit commando vereist om een ​​procedure aan te roepen vanuit de hoofdtekst van een andere procedure of trigger.

Het gebruik van het sleutelwoord OUTPUT bij het aanroepen van een procedure is alleen toegestaan ​​voor parameters die zijn gedeclareerd toen de procedure werd gemaakt met het sleutelwoord OUTPUT.

Wanneer het sleutelwoord DEFAULT wordt opgegeven voor een parameter bij het aanroepen van een procedure, wordt de standaardwaarde gebruikt. Uiteraard is het opgegeven woord DEFAULT alleen toegestaan ​​voor die parameters waarvoor een standaardwaarde is gedefinieerd.

De syntaxis van de opdracht EXECUTE laat zien dat parameternamen kunnen worden weggelaten bij het aanroepen van een procedure. In dit geval moet de gebruiker echter de waarden voor de parameters opgeven in dezelfde volgorde waarin ze werden vermeld bij het maken van de procedure. U kunt geen standaardwaarde aan een parameter toewijzen door deze eenvoudigweg weg te laten tijdens de opsomming. Als u parameters wilt weglaten die een standaardwaarde hebben, volstaat het om de parameternamen expliciet op te geven bij het aanroepen van de opgeslagen procedure. Bovendien kunt u op deze manier parameters en hun waarden in willekeurige volgorde weergeven.

Houd er rekening mee dat bij het aanroepen van een procedure parameternamen met waarden of alleen waarden zonder parameternaam worden opgegeven. Het combineren ervan is niet toegestaan.

RETURN gebruiken in een opgeslagen procedure

Hiermee kunt u de procedure op elk gewenst moment afsluiten volgens een bepaalde voorwaarde, en kunt u ook het resultaat van de procedure als een getal overbrengen, aan de hand waarvan u de kwaliteit en juistheid van de procedure kunt beoordelen. Een voorbeeld van het maken van een procedure zonder parameters:

MAAK PROCEDURE Count_Books AS SELECT COUNT (Code_book) UIT Boeken GO

Oefening 1.

EXEC Count_Books

Controleer het resultaat.

Een voorbeeld van het maken van een procedure met een invoerparameter:

PROCEDURE MAKEN Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) UIT Boeken WAAR Pagina's>= @Count_pages GAAN

Taak 2. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdracht

EXEC Aantal_Boeken_Pagina's 100

Controleer het resultaat.

Een voorbeeld van het maken van een procedure met invoerparameters:

PROCEDURE MAKEN Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) UIT Boeken WAAR Pagina's>= @Count_pages EN Titel_boek ZOALS @Title GO

Taak 3. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdracht

EXEC Count_Books_Title 100, "P%"

Controleer het resultaat.

Een voorbeeld van het maken van een procedure met invoerparameters en een uitvoerparameter:

PROCEDURE MAKEN Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) UIT Boeken WAAR Pagina's>= @Count_pages EN Title_book LIKE @Title GO

Taak 4. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Uitvoeren met behulp van de commandoset:

SQL> Declareer @q als int EXEC Count_Books_Itogo 100, "P%", @q uitvoer selecteer @q

Controleer het resultaat.

Een voorbeeld van het maken van een procedure met invoerparameters en RETURN:

CREËER PROCEDURE controlenaam @param INT AS IF (SELECTEER Naam_auteur VAN auteurs WAAR Code_auteur = @param) = "Pushkin A.S." RETOUR 1 ANDERS RETOUR 2

Taak 5. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdrachten:

DECLARE @return_status INT EXEC @return_status = controlenaam 1 SELECT "Return Status" = @return_status

Een voorbeeld van het maken van een procedure zonder parameters om de waarde van een sleutelveld in de tabel Aankopen te verdubbelen:

CREËER PROC update_proc ALS UPDATE Aankopen SET Code_purchase = Code_purchase* 2

Taak 6. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdracht

EXEC-update_proc

Een voorbeeld van een procedure met een invoerparameter om alle informatie over een specifieke auteur te verkrijgen:

CREËER PROC select_author @k CHAR (30 ) AS SELECT * FROM Auteurs WAAR naam_auteur= @k

Taak 7.

EXEC select_author "Pushkin A.S." of select_author @k= "Pushkin A.S." of EXEC select_author @k= "Pushkin A.S."

Een voorbeeld van het maken van een procedure met een invoerparameter en een standaardwaarde om de waarde van een sleutelveld in de tabel Aankopen met een bepaald aantal keren te verhogen (standaard 2 keer):

CREËER PROC update_proc @p INT = 2 AS UPDATE Aankopen SET Code_purchase = Code_purchase * @p

De procedure retourneert geen gegevens.

Taak 8. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdrachten:

EXEC update_proc 4 of EXEC update_proc @p = 4 of EXEC update_proc --de standaardwaarde wordt gebruikt.

Een voorbeeld van het maken van een procedure met invoer- en uitvoerparameters. Creëer een procedure om het aantal voltooide bestellingen gedurende een bepaalde periode te bepalen:

CREËER PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Aankopen WAAR Date_order TUSSEN @d1 EN @d2 SET @c = ISNULL(@c, 0 )

Taak 9. Maak deze procedure in de sectie Stored Procedures van de DB_Books-database met behulp van het hulpprogramma SQL Server Management Studio. Voer het uit met behulp van de opdrachten:

DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2

Opties voor taken voor laboratoriumwerk nr. 4

Algemene bepalingen. Maak in het hulpprogramma SQL Server Management Studio een nieuwe pagina voor de code (de knop "Create Query"). Maak de gemaakte DB_Books-database programmatisch actief met behulp van de Use-instructie. Maak opgeslagen procedures met behulp van Procedure-instructies maken en definieer zelf de namen van de procedures. Elke procedure voert één SQL-query uit die in het tweede lab is uitgevoerd. Bovendien moet de SQL-code van de zoekopdrachten worden gewijzigd, zodat ze de waarden van de velden waarmee wordt gezocht, kunnen verzenden.

Bijvoorbeeld de initiële taak en het verzoek in laboratoriumwerk nr. 2:

/*Selecteer uit de leverancierslijst (Leveringstabel) de namen van bedrijven, telefoonnummers en INN (velden Naam_bedrijf, Telefoon en INN), waarvan de bedrijfsnaam (veld Naam_bedrijf) “OJSC MIR” is.

SELECTEER Naam_bedrijf, Telefoon, INN VAN Leveringen WAAR Naam_bedrijf = "OJSC MIR"

*/ –In dit werk wordt de volgende procedure gemaakt:

CREËER PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Leveringen WAAR Naam_bedrijf = @comp

–Gebruik de opdracht om de procedure te starten:

EXEC select_name_company "JSC MIR"

Takenlijst

Maak een nieuw programma in SQL Server Management Studio. Maak programmatisch de individuele database actief die is gemaakt in laboratoriumwerk nr. 1 met behulp van de Use-instructie. Maak opgeslagen procedures met behulp van Procedure-instructies maken en definieer zelf de namen van de procedures. Elke procedure voert één SQL-query uit, die wordt gepresenteerd in de vorm van afzonderlijke taken, afhankelijk van de opties.

Optie 1

1. Geef een lijst weer van werknemers die minstens één kind hebben.

2. Geef een lijst weer van kinderen die in de opgegeven periode cadeaus hebben ontvangen.

3. Geef een lijst weer van ouders die minderjarige kinderen hebben.

4. Geef informatie weer over geschenken met een waarde groter dan het opgegeven aantal, gesorteerd op datum.

Optie 2

1. Geef een lijst met apparaten met het opgegeven type weer.

2. Geef het aantal gerepareerde apparaten weer en de totale reparatiekosten van de opgegeven technicus.

3. Geef een lijst met apparaateigenaren en het aantal verzoeken weer, gesorteerd op het aantal verzoeken in aflopende volgorde.

4. Geef informatie weer over vakmensen met een rang groter dan het opgegeven aantal of met een aanwervingsdatum die lager is dan de opgegeven datum.

Optie 3

2. Geef een lijst weer met verkoopcodes waarmee bloemen zijn verkocht voor een bedrag dat groter is dan het opgegeven aantal.

3. Geef de verkoopdatum, het bedrag, de verkoper en de bloem weer volgens de opgegeven verkoopcode.

4. Geef een lijst met bloemen weer en de variëteit voor bloemen met een hoogte groter dan het opgegeven aantal of bloeiend.

Optie 4

1. Geef een lijst met medicijnen weer met de gespecificeerde gebruiksindicatie.

2. Geef een lijst weer met leveringsdata waarvoor meer dan het opgegeven aantal van het gelijknamige medicijn is verkocht.

3. Geef de leverdatum, het bedrag, de volledige naam van de manager van de leverancier en de naam van het medicijn weer met een ontvangstcode die groter is dan het opgegeven nummer.

Optie 5

2. Geef een lijst weer met buiten gebruik gestelde apparatuur om de opgegeven reden.

3. Geef de datum van ontvangst, de naam van het materieel, de volledige naam van de verantwoordelijke persoon en de datum van afschrijving weer voor materieel dat gedurende de aangegeven periode is afgeschreven.

4. Geef een lijst weer met apparatuur van een bepaald type of met een ontvangstdatum groter dan een bepaalde waarde

Optie 6

1. Geef een lijst weer met gerechten met een gewicht groter dan het opgegeven aantal.

2. Geef een lijst weer met producten waarvan de naam het opgegeven woordfragment bevat.

3. Geef het volume van het product weer, de naam van het gerecht, de naam van het product met de schotelcode van de opgegeven beginwaarde tot een bepaalde eindwaarde.

4. Geef de bereidingsvolgorde van een gerecht weer en de naam van een gerecht met de hoeveelheid koolhydraten groter dan een bepaalde waarde of de hoeveelheid calorieën groter dan een opgegeven waarde.

Optie 7

1. Geef een lijst weer van medewerkers met de opgegeven functie.

3. Geef de registratiedatum, het documenttype, de volledige naam van de registrar en de naam van de organisatie weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst weer met geregistreerde documenten met een specifiek documenttype of met een registratiedatum groter dan de opgegeven waarde.

Optie 8

1. Geef een lijst weer van medewerkers met de opgegeven ontslagreden.

3. Geef de datum van registratie, reden van ontslag en volledige naam van de werknemer weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

Optie 9

1. Geef een lijst weer van werknemers die verlof van het opgegeven type hebben opgenomen.

2. Toon een lijst met documenten met een registratiedatum in de opgegeven periode.

3. Geef de registratiedatum, het type verlof en de volledige naam van de werknemer weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst met geregistreerde documenten weer met een documentcode binnen het opgegeven bereik.

Optie 10

1. Geef een lijst weer van medewerkers met de opgegeven functie.

2. Geef een lijst met documenten weer waarvan de inhoud het opgegeven woordfragment bevat.

3. Geef de registratiedatum, het documenttype, de volledige naam van de afzender en de naam van de organisatie weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst weer met geregistreerde documenten met het opgegeven documenttype of met een documentcode kleiner dan een bepaalde waarde.

Optie 11

1. Geef een lijst weer met werknemers die aan de opgegeven functie zijn toegewezen.

2. Toon een lijst met documenten met een registratiedatum in de opgegeven periode.

3. Geef de registratiedatum, functie en volledige naam van de medewerker weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst met geregistreerde documenten weer met een documentcode binnen het opgegeven bereik.

Optie 12

3. Geef een lijst weer van mensen die materiaal hebben gehuurd en het aantal verzoeken, gesorteerd op aantal verzoeken in aflopende volgorde.

Optie 13

1. Geef een lijst weer met apparatuur van het opgegeven type. 2. Geef een lijst weer met apparatuur die door een specifieke medewerker is afgeschreven.

3. Geef de hoeveelheid buiten gebruik gestelde apparatuur weer, gegroepeerd op apparaattype.

4. Geef informatie weer over werknemers met een inhuurdatum die later ligt dan een bepaalde datum.

Optie 14

1. Print een lijst met bloemen met het opgegeven bladtype.

2. Geef een lijst weer met ontvangstcodes waarvoor bloemen zijn verkocht voor bedragen boven een bepaalde waarde.

3. Geef de ontvangstdatum, het bedrag, de naam van de leverancier en de kleuren weer met een specifieke leverancierscode.

4. Geef een lijst met bloemen weer en de variëteit voor bloemen met een hoogte groter dan een bepaald aantal of bloeiend.

Optie 15

1. Geef een lijst weer van klanten die tijdens de opgegeven periode in de kamers hebben ingecheckt.

2. Geef het totale bedrag aan betalingen voor kamers voor elke klant weer.

3. Toon de aankomstdatum, het kamertype en de volledige naam van de klanten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst weer met geregistreerde klanten in kamers van een bepaald type.

Optie 16

1. Geef een lijst weer met apparatuur van het opgegeven type.

2. Geef een lijst weer met apparatuur die door een specifieke klant is gehuurd.

3. Geef een lijst weer van mensen die materiaal hebben gehuurd en het aantal verzoeken, gesorteerd op aantal verzoeken in aflopende volgorde.

4. Geef informatie over klanten weer, gesorteerd op adres.

Optie 17

1. Geef een lijst weer met waardevolle spullen met een aankoopprijs groter dan een bepaalde waarde of een garantieperiode groter dan een bepaald aantal.

2. Geef een lijst weer met locaties van materiële activa waarvan de namen het opgegeven woord bevatten.

3. Geef de som van de waarde van waarden weer met een code in het opgegeven bereik.

4. Geef een lijst weer van financieel verantwoordelijke personen met de datum van indiensttreding binnen het opgegeven bereik.

Optie 18

1. Geef een lijst weer met reparaties die door een specifieke technicus zijn uitgevoerd.

2. Geef een lijst weer met werkfasen die deel uitmaken van het werk waarvan de titel het opgegeven woord bevat.

3. Geef de som weer van de kosten van reparatiewerkfasen voor werk met een code binnen het opgegeven bereik.

4. Geef een lijst met voormannen weer met de aanwervingsdatum binnen het opgegeven bereik.

Optie 19

1. Geef een lijst met medicijnen met een specifieke indicatie weer.

2. Geef een lijst weer met ontvangstnummers waarvoor meer dan een bepaald aantal medicijnen is verkocht.

3. Zet de verkoopdatum, het bedrag, de naam van de kassamedewerker en het medicijn op de kassabon met het opgegeven nummer.

4. Geef een lijst met medicijnen en meeteenheden weer voor medicijnen met een hoeveelheid in de verpakking die groter is dan het opgegeven aantal of een medicijncode die kleiner is dan een bepaalde waarde.

Optie 20

1. Geef een lijst weer van medewerkers met de opgegeven functie.

2. Geef een lijst met documenten weer waarvan de inhoud het opgegeven woordfragment bevat.

3. Geef de registratiedatum, het documenttype, de volledige naam van de executeur en het feit van uitvoering weer voor documenten die tijdens de opgegeven periode zijn geregistreerd.

4. Geef een lijst met geregistreerde documenten weer met het opgegeven documenttype of de documentcode binnen een bepaald bereik.