MS SQL opgeslagen procedures. Opgeslagen procedures in T-SQL - aanmaken, wijzigen, verwijderen. Een voorbeeld van het maken van een procedure met een invoerparameter

Bij het werken met SQL Servergebruikers kunnen hun eigen procedures creëren die bepaalde acties implementeren. Opgeslagen procedures zijn volwaardige databaseobjecten en daarom wordt elk ervan opgeslagen in een specifieke database. Een directe oproep naar een opgeslagen procedure is alleen mogelijk als dit gebeurt in de context van de database waarin de procedure zich bevindt.

Soorten opgeslagen procedures

IN SQL-server Er zijn verschillende soorten opgeslagen procedures.

    Bijna alle serverbeheeractiviteiten worden met hun hulp uitgevoerd. We kunnen zeggen dat in het systeem opgeslagen procedures een interface zijn die werk biedt met systeemtabellen, wat uiteindelijk neerkomt op het wijzigen, toevoegen, verwijderen en ophalen van gegevens uit systeemtabellen van zowel gebruikers- als systeemdatabases. 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. zijn een soort opgeslagen procedure. Ze worden uitgevoerd wanneer een DML-operator (Data Manipulation Language) op de tafel wordt uitgevoerd. Triggers worden gebruikt om de gegevensintegriteit te controleren en ook om transacties terug te draaien.

Trekker is een gecompileerde SQL-procedure, waarvan de uitvoering wordt bepaald door het optreden van bepaalde gebeurtenissen daarin relationele basis gegevens. Het gebruik van triggers is voor het grootste deel erg handig voor databasegebruikers. Toch brengt het gebruik ervan vaak extra kosten met zich mee voor I/O-bewerkingen. Wanneer dezelfde resultaten (met veel minder overhead) kunnen worden bereikt met behulp van opgeslagen procedures of applicatieprogramma's, is het gebruik van triggers niet praktisch.

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. is een speciale SQL-servertool die wordt gebruikt om de gegevensintegriteit in een database te behouden. Integriteitsbeperkingen, regels en standaardwaarden bereiken mogelijk niet altijd het gewenste functionaliteitsniveau. Het is vaak nodig om complexe algoritmen voor gegevensverificatie te implementeren om de betrouwbaarheid en realiteit ervan te garanderen. Bovendien moet u soms veranderingen in tabelwaarden monitoren, zodat de bijbehorende gegevens indien nodig kunnen worden aangepast. Triggers kunnen worden gezien als een soort filters die in werking treden nadat alle bewerkingen zijn voltooid in overeenstemming met regels, standaardwaarden, enz.

Trekker is een speciaal type opgeslagen procedure dat automatisch door de server wordt gestart wanneer wordt geprobeerd gegevens te wijzigen in tabellen waaraan triggers zijn gekoppeld. Elk Trekker is gekoppeld aan een specifieke tabel. Alle gegevenswijzigingen die het doorvoert, worden als één transactie beschouwd. Als er een fout of schending van de gegevensintegriteit wordt gedetecteerd, wordt de transactie teruggedraaid. Wijzigingen zijn daarom verboden. Alle wijzigingen die al door de trigger zijn aangebracht, worden ook ongedaan gemaakt.

Creëert trekker alleen de database-eigenaar. Met deze beperking kunt u onbedoelde wijzigingen in de structuur van tabellen, manieren om andere objecten eraan te koppelen, enz. voorkomen.

Trekker Het is een zeer nuttige en tegelijkertijd gevaarlijke remedie. Dus als de logica van de werking ervan onjuist is, kunt u gemakkelijk een hele database vernietigen, dus triggers moeten zeer zorgvuldig worden opgespoord.

In tegenstelling tot een reguliere subroutine, trekker wordt impliciet uitgevoerd wanneer er een triggergebeurtenis plaatsvindt, en heeft geen argumenten. Het activeren ervan wordt soms de trekker overhalen genoemd. Met behulp van triggers worden de volgende doelen bereikt:

    het controleren van de juistheid van de ingevoerde gegevens en het uitvoeren van complexe werkzaamheden integriteitsbeperkingen gegevens die moeilijk, zo niet onmogelijk, te onderhouden zijn als er sprake is van integriteitsbeperkingen;

    het geven van waarschuwingen die u eraan herinneren bepaalde acties uit te voeren bij het bijwerken van een tabel die op een bepaalde manier is geïmplementeerd;

    accumulatie van auditinformatie door informatie vast te leggen over wijzigingen aangebracht en de personen die ze hebben uitgevoerd;

    ondersteuning voor replicatie.

Het basisformaat van de opdracht CREATE TRIGGER wordt hieronder weergegeven:

<Определение_триггера>::=

MAAK TRIGGER trigger_naam

VOOR | NA<триггерное_событие>

OP<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Triggergebeurtenissen bestaan ​​uit het invoegen, verwijderen en bijwerken van rijen in een tabel. In het laatste geval kunt u specifieke tabelkolomnamen opgeven voor de triggergebeurtenis. De timing van de trigger wordt bepaald met behulp van de BEFORE-trefwoorden ( Trekker wordt uitgevoerd voordat de bijbehorende gebeurtenissen worden uitgevoerd) of NA (nadat ze zijn uitgevoerd).

De acties die door de trigger worden uitgevoerd, worden gespecificeerd voor elke rij (FOR EACH ROW) waarop de gebeurtenis betrekking heeft, of slechts één keer voor elke gebeurtenis (FOR EACH STATEMENT).

Verkeerd geschreven triggers kunnen tot ernstige problemen leiden, zoals dead locks. Triggers kunnen veel bronnen lange tijd blokkeren, dus u moet er op letten speciale aandacht om toegangsconflicten te minimaliseren.

Trekker kan alleen in de huidige database worden aangemaakt, maar het is mogelijk om toegang te krijgen tot andere databases binnen de trigger, inclusief die op een externe server.

opgeslagen procedure is alleen mogelijk als het wordt uitgevoerd in de context van de database waar de procedure zich bevindt.

Soorten opgeslagen procedures

SQL Server kent verschillende typen opgeslagen procedures.

  • Systeem opgeslagen procedures ontworpen om verschillende administratieve handelingen uit te voeren. Bijna alle serverbeheeractiviteiten worden met hun hulp uitgevoerd. We kunnen dat systemisch noemen opgeslagen procedures zijn een interface die werk biedt met systeemtabellen, wat uiteindelijk neerkomt op het wijzigen, toevoegen, verwijderen en ophalen van gegevens uit systeemtabellen van zowel gebruikers- als systeemdatabases. Systeem opgeslagen procedures hebben het voorvoegsel sp_, worden opgeslagen in de systeemdatabase en kunnen worden aangeroepen in de context van elke andere database.
  • Aangepast opgeslagen procedures bepaalde acties uitvoeren. Opgeslagen procedures– een volwaardig databaseobject. Als gevolg hiervan is elk opgeslagen procedure bevindt zich in een specifieke database waar het wordt uitgevoerd.
  • Tijdelijk opgeslagen procedures bestaan ​​slechts een tijdje, waarna ze automatisch door de server worden vernietigd. Ze zijn onderverdeeld in lokaal en mondiaal. Lokaal tijdelijk opgeslagen procedures kunnen alleen worden gebeld 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, opgeslagen procedures van dit type worden automatisch verwijderd wanneer de gebruiker de verbinding verbreekt of de server opnieuw wordt opgestart of gestopt. Mondiaal tijdelijk opgeslagen procedures zijn beschikbaar voor alle verbindingen 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, wijzigen en verwijderen

Creatie opgeslagen procedure omvat het oplossen van de volgende problemen:

  • het bepalen van het type gemaakt opgeslagen procedure: tijdelijk of op maat. Bovendien kunt u uw eigen systeem samenstellen opgeslagen procedure, geef het een naam voorafgegaan door sp_ en plaats het in systeem basis gegevens. Deze procedure zal beschikbaar zijn in de context van elke lokale serverdatabase;
  • toegangsrechten plannen. Bij het maken opgeslagen procedure er moet rekening mee worden gehouden dat het dezelfde toegangsrechten tot databaseobjecten heeft als de gebruiker die het heeft gemaakt;
  • definitie opgeslagen procedureparameters. Vergelijkbaar met de procedures in de meeste programmeertalen, opgeslagen procedures kan invoer- en uitvoerparameters hebben;
  • code ontwikkeling opgeslagen procedure. De procedurecode kan een reeks SQL-opdrachten bevatten, inclusief aanroepen naar andere opgeslagen procedures.

Een nieuwe maken en een bestaande wijzigen opgeslagen procedure gedaan met behulp van de volgende opdracht:

<определение_процедуры>::= (CREATE | ALTER ) procedurenaam [;nummer] [(@parameternaam data_type ) [=standaard] ][,...n] AS sql_operator [...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. Dus om het gecreëerde te plaatsen opgeslagen procedure in een specifieke database moet u de opdracht CREATE PROCEDURE opgeven in de context van die database. Bij het afkeren van het lichaam opgeslagen procedure verkorte namen kunnen worden gebruikt voor objecten uit dezelfde database, 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.

Het nummer in de naam is identificatienummer opgeslagen procedure, die het op unieke wijze identificeert in een groep procedures. Om het beheer te vergemakkelijken zijn de procedures logischerwijs van hetzelfde type opgeslagen procedures kunnen worden gegroepeerd door ze dezelfde naam maar verschillende identificatienummers te geven.

Om invoer- en uitvoergegevens over te dragen in het gemaakte opgeslagen procedure Er kunnen parameters worden gebruikt waarvan de namen, net als de namen van lokale variabelen, moeten beginnen met het @-symbool. In één opgeslagen procedure U kunt meerdere parameters opgeven, 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.

Om het gegevenstype te bepalen dat overeenkomt opgeslagen procedureparameter, elk type is geschikt SQL-gegevens, inclusief door de gebruiker gedefinieerde bestanden. Het gegevenstype CURSOR kan echter alleen worden gebruikt als uitvoerparameter opgeslagen procedure, d.w.z. aangeeft trefwoord UITGANG.

De aanwezigheid van het trefwoord OUTPUT betekent dat de overeenkomstige parameter bedoeld is om gegevens uit terug te sturen opgeslagen procedure. Dit betekent echter niet dat de parameter niet geschikt is om waarden aan door te geven opgeslagen procedure. Als u het trefwoord OUTPUT opgeeft, krijgt de server opdracht om af te sluiten opgeslagen procedure wijs de huidige waarde van de parameter toe aan de lokale variabele die is opgegeven bij het aanroepen van de procedure als de waarde van de parameter. Houd er rekening mee dat bij het opgeven van het trefwoord OUTPUT de waarde van de overeenkomstige parameter bij het aanroepen van de procedure alleen kan worden ingesteld met behulp van een lokale variabele. Expressies of constanten die zijn toegestaan ​​voor reguliere parameters zijn niet toegestaan.

Het VARYING trefwoord wordt gebruikt in combinatie met

Neem een ​​regel op in uw procedures - SET NOCOUNT ON:

Bij elke DML-expressie stuurt de SQL-server ons zorgvuldig een bericht terug met daarin het aantal verwerkte records. Deze informatie Het kan nuttig voor ons zijn tijdens het debuggen van de code, maar daarna zal het volkomen nutteloos zijn. Door SET NOCOUNT ON te schrijven, schakelen we deze functie uit. Voor opgeslagen procedures die meerdere expressies of/of lussen bevatten deze actie kan een aanzienlijke prestatieverbetering opleveren omdat de hoeveelheid verkeer aanzienlijk wordt verminderd.

Transact-SQL

Gebruik de schemanaam met de objectnaam:

Nou, ik denk dat het duidelijk is. Deze operatie vertelt de server waar hij naar objecten moet zoeken en in plaats van willekeurig door zijn bakken te snuffelen, weet hij meteen waar hij heen moet en wat hij mee moet nemen. Met een groot aantal databases, tabellen en opgeslagen procedures kan het onze tijd en zenuwen aanzienlijk besparen.

Transact-SQL

SELECT * FROM dbo.MyTable --Op deze manier doen is goed -- In plaats van SELECT * FROM MyTable --En op deze manier doen is slecht -- De EXEC-procedure dbo.MyProc aanroepen -- Opnieuw goed -- In plaats van EXEC MyProc --Slecht!

Gebruik niet het voorvoegsel 'sp_' in de naam van uw opgeslagen procedures:

Als onze procedurenaam begint met "sp_", zal SQL Server eerst in de hoofddatabase zoeken. Feit is dat dit voorvoegsel wordt gebruikt voor persoonlijke interne opgeslagen procedures van de server. Daarom kan het gebruik ervan leiden tot extra kosten en zelfs onjuiste resultaten als een procedure met dezelfde naam als de uwe in de database wordt aangetroffen.

Gebruik ALS BESTAAT (SELECT 1) in plaats van ALS BESTAAT (SELECT *):

Om te controleren of er een record in een andere tabel bestaat, gebruiken we de instructie IF EXISTS. Deze uitdrukking retourneert waar als er ten minste één waarde wordt geretourneerd uit de interne expressie, het maakt niet uit “1”, alle kolommen of de tabel. De geretourneerde gegevens worden in principe op geen enkele manier gebruikt. Om het verkeer tijdens de gegevensoverdracht te comprimeren, is het dus logischer om “1” te gebruiken, zoals hieronder weergegeven.

We beschouwen een situatie waarin opgeslagen procedures de prestaties van query's kunnen verslechteren.


Bij het compileren van opgeslagen procedures in MS SQL Server 2000 worden de opgeslagen procedures in de procedurecache geplaatst, wat de prestaties bij het uitvoeren ervan kan verbeteren door de noodzaak voor ontleden, optimalisatie en compilatie van opgeslagen procedurecode.
Aan de andere kant zijn er valkuilen bij het opslaan van de gecompileerde code van een opgeslagen procedure die het tegenovergestelde effect kunnen hebben.
Het is een feit dat bij het compileren van een opgeslagen procedure het uitvoeringsplan van de operators waaruit de procedurecode bestaat dienovereenkomstig wordt gecompileerd. Als de gecompileerde opgeslagen procedure in de cache wordt opgeslagen, wordt het uitvoeringsplan ook in de cache opgeslagen, en daarom zal de opgeslagen procedure dat niet doen; geoptimaliseerd worden voor specifieke situatie en queryparameters.
Laten we een klein experiment doen om dit aan te tonen.

STAP 1. Een databank maken.
Voor het experiment zullen we een aparte database aanmaken.

DATABASE MAKEN test_sp_perf
AAN (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
AANMELDEN (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

STAP 2. Een tabel maken.
MAAK TABEL sp_perf_test(kolom1 int, kolom2 char(5000))

STAP 3. De tabel vullen met testrijen. Er worden opzettelijk dubbele rijen aan een tabel toegevoegd. 10.000 regels met getallen van 1 tot 10.000, en 10.000 regels met getallen 50.000.

VERKLAREN @i int
SET @i=1
TERWIJL(@i<10000)
BEGINNEN
INSERT INTO sp_perf_test(kolom1, kolom2) VALUES(@i,"Test string #"+CAST(@i als char(8)))
INSERT INTO sp_perf_test(kolom1, kolom2) VALUES(50000,"Testreeks #"+CAST(@i als char(8)))
SET @i= @i+1
EINDE

SELECTEER AANTAL(*) UIT sp_perf_test
GAAN

STAP 4. Een niet-geclusterde index maken. Omdat het uitvoeringsplan bij de procedure in de cache wordt opgeslagen, wordt de index voor alle aanroepen op dezelfde manier gebruikt.

MAAK NIET-GECLUSTERDE INDEX CL_perf_test ON sp_perf_test(kolom1)
GAAN

STAP 5. Een opgeslagen procedure maken. De procedure voert eenvoudigweg een SELECT-instructie uit met een voorwaarde.

MAAK PROC proc1 (@param int)
ALS
SELECTEER kolom1, kolom2 VAN sp_perf_test WAAR kolom1=@param
GAAN

STAP 6. Een opgeslagen procedure uitvoeren. Bij het uitvoeren van een kwetsbare procedure wordt specifiek een selectieve parameter gebruikt. Als resultaat van de procedure krijgen we 1 regel. Het uitvoeringsplan geeft het gebruik van een niet-geclusterde index aan, omdat De query is selectief en dit is de beste manier om de rij op te halen. Een procedure die is geoptimaliseerd om een ​​enkele rij op te halen, wordt opgeslagen in de procedurele cache.

EXEC-proc1 1234
GAAN

STAP 7. Voer een opgeslagen procedure uit met een niet-selectieve parameter. De waarde die als parameter wordt gebruikt is 50.000. Er zijn ongeveer 10.000 rijen met deze waarde in de eerste kolom. Daarom is het gebruik van een niet-geclusterde index en de opzoekbewerking voor bladwijzers niet effectief, maar omdat de gecompileerde code met het uitvoeringsplan is opgeslagen in de procedurele cache, dit is wat zal worden gebruikt. Het uitvoeringsplan laat dit zien, evenals het feit dat er voor 9999 rijen een bladwijzeropzoekbewerking is uitgevoerd.

EXEC proc1 50000
GAAN

STAP 8. Het uitvoeren van een selectie van rijen waarbij het eerste veld gelijk is aan 50000. Wanneer u een afzonderlijke query uitvoert, wordt de query geoptimaliseerd en samengesteld met een specifieke waarde voor de eerste kolom. Als gevolg hiervan stelt de queryoptimalisatie vast dat het veld vele malen wordt gedupliceerd en besluit de tabelscanbewerking te gebruiken, wat in dit geval veel efficiënter is dan het gebruik van een niet-geclusterde index.

SELECTEER kolom1, kolom2 VAN sp_perf_test WAAR kolom1=50000
GAAN

We kunnen dus concluderen dat het gebruik van opgeslagen procedures niet altijd de prestaties van query's verbetert. U moet zeer voorzichtig zijn met opgeslagen procedures die werken op resultaten met een variabel aantal rijen en die verschillende uitvoeringsplannen gebruiken.
U kunt het script gebruiken om het experiment op uw MS SQL-server te herhalen.

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 SQL Server-hulpprogramma Managementstudio. 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 kan 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, dat wil zeggen 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 gegevens, 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 overeenkomstige 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 als 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 verlaten 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

Taak 1.

EXEC Count_Books

Controleer het resultaat.

Voorbeeld van het maken van een procedure c 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. Creëren deze procedure in de sectie Stored Procedures van de DB_Books-database via 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 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):

CREATE 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: De procedure retourneert geen gegevens.

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. DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2 №4

Opties voor taken laboratorium werk Algemene bepalingen. In SQL-hulpprogramma Serverbeheer Studio creëren nieuwe pagina voor de code (de knop “Aanvraag aanmaken”). Maak de gemaakte DB_Books-database programmatisch actief met behulp van de Use-instructie. Creëer opgeslagen procedures met behulp van operators

Procedure maken

en bepaalt onafhankelijk 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:

*/ –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"

Lijst met taken

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 er één uit SQL-query, die in het formulier worden weergegeven individuele taken volgens 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 lager 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 groter 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 vanaf 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 met medewerkers weer met aangegeven reden ontslagen.

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 en variëteiten weer voor bloemen met een grotere hoogte 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 de fasen van reparatiewerkzaamheden voor werkzaamheden met een code in 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. Geef de verkoopdatum, het bedrag, de volledige naam van de kassamedewerker en het medicijn weer 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.



GERELATEERDE ARTIKELEN