Waar zijn SQL-opgeslagen procedures voor? Eerste gegevens voor voorbeelden. Een van de opgeslagen SQL-procedures selecteren

Opgeslagen procedures

Het onderwerp van dit hoofdstuk is een van de krachtigste hulpmiddelen die worden aangeboden aan ontwikkelaars van databasetoepassingen. InterBase-gegevens om bedrijfslogica te implementeren Met Stoied-procedures kunt u een aanzienlijk deel van de toepassingslogica op databaseniveau implementeren en zo de prestaties van de hele toepassing verhogen, de gegevensverwerking centraliseren en de hoeveelheid code verminderen die nodig is om de taken uit te voeren Bijna alles is genoeg complexe applicatie databases zijn niet compleet zonder het gebruik van opgeslagen procedures.
Naast deze bekende voordelen van het gebruik van opgeslagen procedures, die de meeste relationele DBMS'en gemeen hebben, kunnen opgeslagen procedures van InterBase fungeren als bijna volwaardige datasets, waardoor ze de resultaten die ze retourneren in reguliere SQL-query's kunnen gebruiken.
Vaak beschouwen beginnende ontwikkelaars opgeslagen procedures eenvoudig als een reeks specifieke SQL-query's die iets in de database doen, en men is van mening dat het werken met opgeslagen procedures veel moeilijker is dan het implementeren van dezelfde functionaliteit in een clienttoepassing in de taal hoog niveau
Dus wat zijn opgeslagen procedures in InterBase?
Een opgeslagen procedure (SP) is een onderdeel van de metagegevens van de database, een subroutine die is gecompileerd in de interne representatie van InterBase, geschreven in speciale taal, waarvan de compiler is ingebouwd in de kern van de InteiBase-server
De opgeslagen procedure kan worden aangeroepen vanuit: client toepassingen, van triggers en andere opgeslagen procedures. Een opgeslagen procedure wordt uitgevoerd binnen het serverproces en kan gegevens in de database manipuleren, evenals de resultaten van de uitvoering terugsturen naar de client die het heeft aangeroepen (d.w.z. trigger, CP, applicatie)
De basis van de krachtige mogelijkheden die inherent zijn aan de SP is een procedurele programmeertaal die zowel gewijzigde instructies van gewone SQL, zoals INSERT, UPDATE en SELECT, als vertakkings- en looping-tools (IF, WHILE), evenals foutafhandeling omvat tools en uitzonderlijke situaties Met de taal voor opgeslagen procedures kunt u complexe algoritmen implementeren voor het werken met gegevens, en door de focus op het werken met relationele gegevens is HP veel compacter dan vergelijkbare procedures in traditionele talen.
Opgemerkt moet worden dat voor triggers dezelfde programmeertaal wordt gebruikt, met uitzondering van een aantal functies en beperkingen. Verschillen tussen de subset van de taal die wordt gebruikt in triggers en de XPS-taal worden in detail besproken in het hoofdstuk "Triggers" (Deel 1).

Een voorbeeld van een eenvoudige opgeslagen procedure

Dit is het moment om de eerste opgeslagen procedure te maken en deze te gebruiken om het proces van het maken van opgeslagen procedures te leren. Maar eerst moeten een paar woorden worden gezegd over het werken met opgeslagen procedures: feit is dat de extreem slechte standaardtools voor het ontwikkelen en debuggen van opgeslagen procedures hun glorie te danken hebben aan de obscure en onhandige CP-tool. In de InterBase-documentatie wordt aanbevolen om procedures te maken met behulp van SQL-scriptbestanden die de tekst van de SP bevatten, die worden ingevoerd in de isql-interpreter, en zo de SP aan te maken en te wijzigen. de tekst van de procedure compileren in BLR Als er een fout optreedt, zal isql een bericht weergeven op welke regel van het SQL-scriptbestand deze fout is opgetreden. Corrigeer de fout en herhaal alles opnieuw. Over debuggen in modern begrip Dit woord, dat wil zeggen het uitvoeringsspoor, met de mogelijkheid om de tussenliggende waarden van variabelen te zien, staat helemaal niet ter discussie. Het is duidelijk dat deze benadering niet bijdraagt ​​aan de groei van de aantrekkelijkheid van opgeslagen procedures in de ogen van de ontwikkelaar.
Naast de standaard minimalistische benadering van HP-ontwikkeling<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
De syntaxis van de opgeslagen procedure wordt als volgt beschreven:

PROCEDURE MAKEN naam
[(param datatype [, param datatype ...])]
)]
ALS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
BEGINNEN
< compound_statement>
[< compound_statement> ...]
EINDE
< compound_statement> = (uitspraak;)

Het ziet er behoorlijk omvangrijk uit en kan zelfs omslachtig zijn, maar in feite is alles heel eenvoudig. Laten we, om geleidelijk de syntaxis onder de knie te krijgen, eens kijken naar geleidelijk meer gecompliceerde voorbeelden.
Dus, hier is een voorbeeld van een zeer eenvoudige opgeslagen procedure die twee getallen als invoer neemt, ze optelt en het resultaat retourneert:

CREATE PROCEDURE SP_Add (first_arg DUBBELE PRECISIE,
second_arg DUBBELE PRECISIE)
RETOUREN (Resultaat DUBBELE PRECISIE)
ALS
BEGINNEN
Resultaat = eerste_arg + tweede_arg;
OPSCHORTEN;
EINDE

Zoals u kunt zien, is alles eenvoudig: na de opdracht CREATE PROCEDURE wordt de naam van de nieuw gemaakte procedure aangegeven (die uniek moet zijn in de database) - in in dit geval SP_Add, dan worden de invoerparameters van de SP - first_arg en second_arg - tussen haakjes weergegeven, gescheiden door komma's, met een aanduiding van hun type.
De lijst met invoerparameters is optioneel. CREATE-instructie PROCEDURE - er zijn gevallen waarin de procedure alle gegevens voor zijn werking ontvangt door middel van query's naar de tabellen in de hoofdtekst van de procedure.

Alle scalaire InteiBase-gegevenstypen worden gebruikt in opgeslagen procedures. Er kunnen geen arrays en door de gebruiker gedefinieerde typen worden gebruikt - domeinen

Vervolgens komt het RETURNS-sleutelwoord, waarna de geretourneerde parameters tussen haakjes worden weergegeven en hun typen aangeven - in dit geval slechts één - Resultaat.
Als de procedure geen parameters moet retourneren, ontbreken het woord RETURNS en de lijst met geretourneerde parameters.
De RETURNSQ wordt gevolgd door het AS-sleutelwoord. Voordat trefwoord AS gaat titel, en na hem - techo procedures.
De hoofdtekst van een opgeslagen procedure is een lijst met beschrijvingen van de interne (lokale) variabelen (indien aanwezig, we zullen ze hieronder in meer detail bespreken), gescheiden door een puntkomma (;), en een blok met instructies tussen operatorhaken BEGIN EINDE. In dit geval is de hoofdtekst van de SP heel eenvoudig - we vragen om twee invoerargumenten toe te voegen en hun resultaat toe te wijzen aan de uitvoer, en roepen dan de opdracht SUSPEND op. Even later zullen we de essentie van de werking van deze opdracht uitleggen, maar voor nu zullen we alleen opmerken dat het nodig is om de geretourneerde parameters door te geven aan waar de opgeslagen procedure vandaan werd aangeroepen.

Scheiders in opgeslagen procedures

Merk op dat een instructie binnen een procedure eindigt met een puntkomma (;). Zoals u weet, is de puntkomma het standaard scheidingsteken voor opdrachten in SQL - het is een signaal voor de SQL-interpreter dat de opdrachttekst volledig is ingevoerd en dat deze moet worden verwerkt. Zou het kunnen dat wanneer het een puntkomma in het midden van de SP detecteert, de SQL-interpreter ervan uitgaat dat het commando volledig is ingevoerd en zal proberen een deel van de opgeslagen procedure uit te voeren? Deze veronderstelling is logisch. Inderdaad, als u een bestand maakt waarin u het bovenstaande voorbeeld schrijft, een opdracht toevoegt om verbinding te maken vanuit de database en probeert dit SQL-script uit te voeren met behulp van de isql-interpreter, wordt er een fout geretourneerd met betrekking tot het onverwachte, volgens de interpreter, einde van de opdracht om een ​​opgeslagen procedure te maken. Als u opgeslagen procedures maakt met behulp van SQL-scriptbestanden, zonder gespecialiseerde InterBase-ontwikkeltools te gebruiken, wijzigt u vóór elke opdracht om CP te maken (hetzelfde geldt voor triggers), het scheidingsteken voor de scriptopdracht in een ander teken dan een puntkomma, en na HP tekst in herstel het terug. Isql-opdracht wijzigen van scheidingsteken SQL-instructies, ziet eruit als:

TERMIJN INSTELLEN

Voor typisch geval het maken van een opgeslagen procedure ziet er als volgt uit:

TERMIJN INSTELLEN ^;
PROCEDURE MAKEN some_procedure
... . .
EINDE
^
TERMIJN INSTELLEN; ^

Opgeslagen procedure-oproep

Maar terug naar onze opgeslagen procedure. Nu het is gemaakt, moet je het op de een of andere manier aanroepen, er parameters aan doorgeven en de geretourneerde resultaten krijgen. Het is heel eenvoudig om dit te doen - u hoeft alleen een SQL-query in de volgende vorm te schrijven:

KIES *
VANAF Sp_add (181.35, 23.09)

Deze zoekopdracht geeft ons één rij met slechts één resultaatveld, dat de som van de getallen 181.35 en 23.09 bevat, d.w.z. 204.44.
Onze procedure kan dus worden gebruikt in conventionele SQL-query's uitgevoerd als in client programma's, en in andere HP of triggers. Dit gebruik van onze procedure werd mogelijk gemaakt door het gebruik van de opdracht SUSPEND aan het einde van de opgeslagen procedure.
Het feit is dat er in InterBase (en in al zijn klonen) twee soorten opgeslagen procedures zijn: selecteerbare procedures en uitvoerbare procedures. Het verschil in de werking van deze twee typen SP is dat ophaalprocedures gewoonlijk veel sets uitvoerparameters retourneren, regel voor regel gegroepeerd, in de vorm van een gegevensset, en de uitgevoerde procedures kunnen ofwel helemaal geen parameters retourneren, of retourneer slechts één set uitvoerparameters, vermeld in Returns, waar een enkele regel parameters. Select-procedures worden aangeroepen in SELECT-query's en uitvoerbare procedures worden aangeroepen met UITVOEREN commando's PROCEDURE.
Beide typen opgeslagen procedures hebben dezelfde aanmaaksyntaxis en zijn formeel niet verschillend, dus elke uitvoerbare procedure kan worden aangeroepen in een SELECT-query en elke selectieprocedure kan worden aangeroepen met EXECUTE PROCEDURE. De vraag is hoe HP zich zal gedragen wanneer verschillende soorten telefoongesprek. Met andere woorden, het verschil zit in de opzet van de procedure voor: van een bepaald type telefoongesprek. Dat wil zeggen, een selectieprocedure is speciaal gemaakt om te worden aangeroepen vanuit een SELECT-query, en een uitvoerbare procedure is speciaal gemaakt om te worden aangeroepen met behulp van EXECUTE PROCEDURE. Laten we eens kijken wat de verschillen zijn in het ontwerp van deze twee typen HP.
Om te begrijpen hoe de bemonsteringsprocedure werkt, moet je wat dieper in de theorie duiken. Laten we ons een typische SQL-query voorstellen, zoals SELECT ID, NAME FROM Table_example. Als resultaat van de uitvoering ervan krijgen we aan de uitgang een tabel die bestaat uit twee kolommen (ID en NAME) en een bepaald aantal rijen (gelijk aan het aantal rijen in de tabel Table_example). De tabel die als resultaat van deze query wordt geretourneerd, wordt ook een set genoemd SQL-gegevens Laten we eens nadenken over hoe de dataset wordt gevormd tijdens de uitvoering van deze query. De server, die de query heeft ontvangen, bepaalt tot welke tabellen deze behoort en zoekt vervolgens uit welke subset van records uit deze tabellen in het queryresultaat moet worden opgenomen. Vervolgens leest de server elk record dat voldoet aan de queryresultaten, selecteert de vereiste velden eruit (in ons geval zijn dit ID en NAME) en stuurt ze naar de client. Daarna wordt het proces opnieuw herhaald - en zo verder voor elk geselecteerd item.
Al deze uitweidingen zijn nodig om de beste lezer te laten begrijpen dat alle SQL-datasets regel voor regel worden gevormd, ook in opgeslagen procedures! En het belangrijkste verschil tussen ophaalprocedures en uitvoerbare procedures is dat de eerste zijn ontworpen om veel rijen te retourneren, terwijl de laatste slechts voor één rij zijn. Daarom worden ze op verschillende manieren gebruikt: een select-procedure wordt aangeroepen met het SELECT-commando, dat de procedure "vereist" om alle records te geven die het kan retourneren. De uitvoerbare procedure wordt aangeroepen met EXECUTE PROCEDURE, die slechts één regel van de CP "verwijdert", en de rest negeert (zelfs als die er zijn!).
Laten we eens kijken naar een voorbeeld van een ophaalprocedure om het duidelijker te maken. Laten we voor> vergeving een opgeslagen procedure maken die precies hetzelfde werkt als: SELECT-query ID, NAME FROM Table_Example, dat wil zeggen, het selecteert gewoon de ID- en NAME-velden uit de hele tabel. Hier is dit voorbeeld:

PROCEDURE MAKEN Simple_Select_SP
GEEFT TERUG (
procID INTEGER,
procNAME VARCHAR (80))
ALS
BEGINNEN
VOOR
SELECT ID, NAME FROM table_example
INTO: procID,: procNAME
DOEN
BEGINNEN
OPSCHORTEN;
EINDE
EINDE

Laten we de stappen van deze routine, genaamd Simple_Select_SP, doorlopen. Zoals u kunt zien, heeft het geen invoerparameters en heeft het twee uitvoerparameters - ID en NAAM. Het meest interessante ligt natuurlijk in de inhoud van de procedure. De FOR SELECT-constructie wordt hier gebruikt:

VOOR
SELECT ID, NAME FROM table_example
INTO: procID,: procNAME
DOEN
BEGINNEN

/ * doe iets met de variabelen procID en procName * /

EINDE

Dit stukje code betekent het volgende: plaats voor elke rij geselecteerd uit Table_example de geselecteerde waarden in de procID- en procName-variabelen en voer vervolgens een actie uit met deze variabelen.
Je zou een verbaasd gezicht kunnen trekken en je afvragen: "Variabelen? Welke andere variabelen? 9" Het is een beetje een verrassing in dit hoofdstuk dat we variabelen kunnen gebruiken in opgeslagen procedures. In de XPS-taal kunt u uw eigen lokale variabelen declareren binnen een procedure, of invoer- en uitvoerparameters als variabelen gebruiken.
Om een ​​lokale variabele in een opgeslagen procedure te declareren, moet u de beschrijving ervan na het AS-sleutelwoord en vóór het eerste BEGIN-woord plaatsen. De beschrijving van een lokale variabele ziet er als volgt uit:

VERKLAREN VARIABEL ;

Als u bijvoorbeeld een integere lokale variabele Mylnt wilt declareren, plaatst u de volgende beschrijving tussen AS en BEGIN

VERKLAREN VARIABELE Mylnt INTEGER;

De variabelen in ons voorbeeld beginnen met een dubbele punt. Dit wordt gedaan omdat ze toegankelijk zijn binnen SQL- FOR commando's SELECT, om onderscheid te maken tussen velden in tabellen die worden gebruikt in SELECT, en variabelen, moet daarom voorafgaan aan de laatste dubbele punt. Variabelen kunnen immers exact dezelfde naam hebben als velden in tabellen!
Maar de dubbele punt voor de variabelenaam mag alleen worden gebruikt in SQL-query's. Buiten teksten wordt toegang tot een variabele zonder dubbele punt gedaan, bijvoorbeeld:

procName = "Een naam";

Maar terug naar de kern van onze procedure. De clausule FOR SELECT retourneert gegevens niet in de vorm van een tabel - een set gegevens, maar één regel tegelijk. Elk geretourneerd veld moet in zijn eigen variabele worden geplaatst: ID => procID, NAME => procName. In het DO-gedeelte worden deze variabelen naar de klant gestuurd die procedure> p> heeft aangeroepen met behulp van de opdracht SUSPEND
De opdracht FOR SELECT ... DO doorloopt dus de records die zijn geselecteerd in het SELECT-gedeelte van de opdracht. In de body van de lus gevormd door het DO-deel, wordt het volgende gegenereerde record overgedragen aan de client met behulp van het SUSPEND-commando.
De ophaalprocedure is dus ontworpen om een ​​of meer rijen te retourneren, waarvoor een lus is georganiseerd in de SP-body die de resulterende variabele parameters vult. En aan het einde van de hoofdtekst van deze cyclus is er altijd een SUSPEND-opdracht, die de volgende regel met gegevens naar de client terugstuurt.

Lussen en vertakkingsoperators

Naast het FOR SELECT ... DO-commando, dat een lus organiseert over de records van een selectie, is er nog een ander type lus - WHILE ... DO, waarmee u een lus kunt organiseren op basis van het controleren van eventuele voorwaarden. Hier is een voorbeeld van een CP die een WHILE .. DO-lus gebruikt. Deze routine retourneert de kwadraten van gehele getallen tussen 0 en 99:

MAAK PROCEDJRE QUAD
RETOUREN (QUADRAT INTEGER)
ALS
VERKLAREN VARIABELE I INTEGER;
BEGINNEN
ik = 1;
Terwijl ik<100) DO
BEGINNEN
QUADRAT = ik * ik;
ik = ik + 1;
OPSCHORTEN;
EINDE
EINDE

Als resultaat van het uitvoeren van de SELECT FROM QUAD-query, krijgen we een tabel met één QUADRAT-kolom, waarin kwadraten van gehele getallen van 1 tot 99 zullen zijn
Naast het herhalen van de resultaten van een SQL-selectie en een klassieke lus, gebruikt de taal voor opgeslagen procedures de IF ... THEN..ELSE-operator, waarmee u vertakkingen kunt organiseren afhankelijk van de uitvoering van sommige \-woorden. vergelijkbaar met de meeste vertakkende operators in programmeertalen op hoog niveau, zoals Pascal en C.
Laten we eens kijken naar een complexer voorbeeld van een opgeslagen procedure die het volgende doet.

1. Berekent de gemiddelde prijs in de tabel Table_example (zie hoofdstuk "Tabellen Primaire sleutels en generatoren")
2. Vervolgens voert het voor elk record in de tabel de volgende controle uit of: huidige prijs(PRICE) groter is dan de gemiddelde prijs, stelt de prijs vervolgens in op de waarde van de gemiddelde prijs, plus een bepaald vast percentage
3. Als de bestaande prijs lager is dan of gelijk is aan de gemiddelde prijs, wordt de prijs gelijk gesteld aan de vorige prijs, plus de helft van het verschil tussen de vorige en gemiddelde prijs.
4. Retourneert alle gewijzigde rijen in de tabel.

Laten we eerst de naam van de SP definiëren, evenals de invoer- en uitvoerparameters.Dit alles is geschreven in de kop van de opgeslagen procedure.

PROCEDURE MAKEN Prijsverhoging (
DUBBELE PRECISIE met 2 procent meer)
RETOUREN (ID INTEGER, NAAM VARCHAR (SO), nieuwe_prijs DOUBLE
PRECISIE) ALS

De procedure krijgt de naam VerhoogPrices, het heeft één invoerparameter Peiceni21nciease van het type DOUBLE PRECISION en 3 uitvoerparameters - ID, NAME en new_pnce. Merk op dat de eerste twee output parameters dezelfde namen hebben als de velden in het Table_example waar we mee gaan werken Dit is toegestaan ​​door de regels van de opgeslagen procedure taal.
Nu moeten we een lokale variabele declareren die zal worden gebruikt om de gemiddelde waarde op te slaan. Deze declaratie ziet er als volgt uit:

VERKLAREN VARIABELE avg_price DUBBELE PRECISIE;

Laten we nu verder gaan met de hoofdtekst van de opgeslagen procedure. Open de hoofdtekst van de HP het BEGIN-sleutelwoord.
Eerst moeten we de eerste stap van ons algoritme uitvoeren - de gemiddelde prijs berekenen. Om dit te doen, gebruiken we een query van de volgende vorm:

SELECT AVG (Prijs_l)
VAN Tabel_Voorbeeld
INTO: gemiddelde_prijs, -

Dit verzoek gebruikt geaggregeerde functie AVG, die de gemiddelde waarde van het veld PRICE_1 tussen de geselecteerde queryrijen retourneert - in ons geval de gemiddelde waarde van PRICE_1 over de hele tabel Tabel_voorbeeld. De waarde die door de query wordt geretourneerd, wordt in de variabele avg_price geplaatst. Merk op dat de variabele avg_pnce wordt voorafgegaan door een dubbele punt - om deze te onderscheiden van de velden die in de query worden gebruikt.
Functie van dit verzoek is dat het altijd precies één record retourneert. Dergelijke zoekopdrachten worden singleton-query's genoemd en alleen dergelijke selecties kunnen worden gebruikt in opgeslagen procedures. Als de query meer dan één rij retourneert, moet deze worden opgemaakt in de vorm van een FOR SELECT ... DO-structuur, die een lus organiseert om elke geretourneerde rij te verwerken
Dus we kregen de gemiddelde waarde van de prijs. Nu moet je de hele tabel doornemen, de prijswaarde in elk record vergelijken met de gemiddelde prijs en nemen gepaste actie
Vanaf het begin organiseren we de iteratie over elk record uit de tabel Table_example.

VOOR
SELECT ID, NAAM, PRIJS_1
VAN Tabel_Voorbeeld
INTO: ID,: NAAM,: nieuwe_prijs
DOEN
BEGINNEN
/ * _ versteen hier elk record * /
EINDE

Wanneer deze constructie wordt uitgevoerd, worden de gegevens regel voor regel uit de Table_example gehaald en worden de veldwaarden in elke regel toegewezen aan de ID-, NAME- en new_pnce-variabelen. U zult natuurlijk onthouden dat deze variabelen als out-parameters worden gedeclareerd, maar u hoeft zich geen zorgen te maken dat de geselecteerde gegevens als resultaten worden geretourneerd: het feit dat er iets is toegewezen aan de out-parameters betekent niet dat de client die de HP belt ontvangt deze waarden onmiddellijk. ! Parameters worden alleen doorgegeven wanneer het SUSPEND-commando wordt uitgevoerd, en daarvoor kunnen we de uitvoerparameters gebruiken als gewone variabelen - in ons voorbeeld doen we dat precies met de parameter new_price.
Dus binnen de body van de BEGIN .. .END-lus kunnen we de waarden van elke regel verwerken. Zoals u zich herinnert, moeten we uitzoeken hoe de huidige prijs zich verhoudt tot het gemiddelde en passende maatregelen nemen. We zullen deze vergelijkingsprocedure implementeren met behulp van de IF-verklaring:

IF (new_price> avg_price) THEN / * als de bestaande prijs hoger is dan de gemiddelde prijs * /
BEGINNEN
/ * dan installeren nieuwe prijs gelijk aan de gemiddelde prijs plus een vast percentage * /
new_price = (avg_price + avg_price * (Percent2Increase / 100));
UPDATE Tabel_voorbeeld
PRIJS INSTELLEN_1 =: nieuwe_prijs
WAAR-ID =: ID;
EINDE
ANDERS
BEGINNEN
/ * Als de bestaande prijs lager is dan of gelijk is aan de gemiddelde prijs, stel de prijs dan gelijk aan de vorige prijs, plus de helft van het verschil tussen de vorige en de gemiddelde prijs * /
new_price = (new_pnce + ((avg_pnce new_price) / 2));
UPDATE Tabel_voorbeeld
PRIJS INSTELLEN_1 =: nieuwe_prijs
WAAR-ID = .ID;
EINDE

Zoals u kunt zien, is het resultaat een vrij grote IF-constructie, die moeilijk te begrijpen zou zijn als de opmerkingen tussen / ** / symbolen er niet waren.
Om de prijs te wijzigen op basis van het berekende verschil, gebruiken we de UPDATE-verklaring, waarmee we kunnen wijzigen bestaande records- een of meer. Om eenduidig ​​aan te geven in welk record de prijs gewijzigd moet worden, gebruiken we het veld in de WHERE conditie hoofdsleutel vergelijken met de waarde van de variabele die de ID-waarde opslaat voor huidig ​​record: ID =: ID. Merk op dat de variabele ID wordt voorafgegaan door een dubbele punt.
Na het uitvoeren van de IF ... THEN ... ELSE constructie, bevatten de variabelen ID, NAME en new_price gegevens die we terug moeten geven aan de client \ naar de beller van de procedure. Om dit te doen, moet u na de IF de opdracht SUSPEND invoegen, die de gegevens naar de plaats stuurt van waaruit de SP werd gebeld.Tijdens de overdracht wordt de procedure onderbroken en wanneer de SP nodig is nieuwe invoer, dan zal het opnieuw worden voortgezet - en dit zal doorgaan totdat FOR SELECT ... DO alle records van zijn query herhaalt.
Opgemerkt moet worden dat naast het SUSPEND-commando, dat alleen de opgeslagen procedure opschort, er een EXIT-commando is, dat de opgeslagen procedure beëindigt nadat de string is doorgegeven. Het EXIT-commando wordt echter zelden gebruikt, omdat het vooral nodig is om de cyclus te onderbreken wanneer een voorwaarde wordt bereikt.
In dit geval, in het geval dat de procedure werd aangeroepen door de SELECT-instructie en voltooid door EXIT, wordt de laatst opgehaalde rij niet geretourneerd. Dat wil zeggen, als u de procedure moet onderbreken en toch> deze string krijgt, moet u de reeks gebruiken

OPSCHORTEN;
UITGANG;

Het belangrijkste doel van EXIT is om singleton-datasets, retourparameters, te krijgen door de EXECUTE PROCEDURE aan te roepen. In dit geval worden de waarden van de uitvoerparameters ingesteld, maar de SQL-gegevensset wordt niet daaruit gevormd en eindigt de procedure.
Laten we de volledige tekst van onze opgeslagen procedure opschrijven, zodat we de logica ervan in één oogopslag kunnen vastleggen:

PROCEDURE MAKEN Prijsverhoging (
Percent2Verhogen DUBBELE PRECISIE)
RETOUREN (ID INTEGER, NAAM VARCHAR (80),
new_price DUBBELE PRECISIE) AS
VERKLAREN VARIABELE avg_price DUBBELE PRECISIE;
BEGINNEN
SELECT AVG (Prijs_l)
VAN Tabel_Voorbeeld
INTO: gemiddelde_prijs;
VOOR
SELECT ID, NAAM, PRIJS_1
VAN Tabel_Voorbeeld
INTO: ID,: NAAM,: nieuwe_prijs
DOEN
BEGINNEN
/ * verwerk hier elk record * /
IF (new_pnce> avg_price) THEN / * als de bestaande prijs hoger is dan de gemiddelde prijs * /
BEGINNEN
/ * stel een nieuwe prijs in gelijk aan de gemiddelde prijs plus een vast percentage * /
new_price = (avg_price + avg_price * (Percent2lncrease / 100));
UPDATE Tabel_voorbeeld
PRIJS INSTELLEN_1 =: nieuwe_prijs
WAAR-ID =: ID;
EINDE
ANDERS
BEGINNEN
/ * Als de bestaande prijs lager is dan of gelijk is aan de gemiddelde prijs, dan stelt de prijs gelijk aan de vorige prijs, plus de helft van het verschil tussen de vorige en de gemiddelde prijs * /
new_price = (new_price + ((avg_price - new_price) / 2));
UPDATE Tabel_voorbeeld
PRIJS INSTELLEN_1 =: nieuwe_prijs
WAAR-ID =: ID;
EINDE
OPSCHORTEN;
EINDE
EINDE

Deze opgeslagen voorbeeldprocedure illustreert het gebruik van elementaire opgeslagen procedures en triggertaalconstructies. Vervolgens bekijken we hoe we opgeslagen procedures kunnen gebruiken om enkele veelvoorkomende problemen op te lossen.

Recursieve opgeslagen procedures

Opgeslagen procedures van InterBase kunnen recursief zijn. Dit betekent dat u zichzelf kunt oproepen vanuit een opgeslagen procedure. Maximaal 1000 niveaus van nesting van opgeslagen procedures zijn toegestaan, maar onthoud dat vrije bronnen op de server mogelijk opraken voordat de maximale HP-nesting is bereikt.
Een van de meest voorkomende toepassingen van opgeslagen procedures is het manipuleren van boomstructuren die zijn opgeslagen in een database. Bomen worden vaak gebruikt in inventaris, warehousing, HR en andere veelvoorkomende toepassingen.
Laten we eens kijken naar een voorbeeld van een opgeslagen procedure die alle producten van een bepaald type selecteert, beginnend op een specifiek nesting-niveau.
Stel dat we de volgende probleemstelling hebben: we hebben een catalogus van goederen met: hiërarchische structuur van deze soort:

Goederen
- Huishoudelijke apparaten
- Koelkasten
- Driekamer
- Tweekamer
- Enkele kamer
- Wasmachines
- Verticaal
- Frontaal
- Klassiek
- Smal
- Computer technologie
....

Deze structuur van de directory van productcategorieën kan vertakkingen van verschillende diepten hebben. en ook opbouwen in de tijd. Het is onze taak ervoor te zorgen dat alle leaf-elementen worden geselecteerd uit de map met "de volledige naam uitvouwen", beginnend bij elk knooppunt. Als we bijvoorbeeld het knooppunt "Wasmachines" selecteren, moeten we de volgende categorieën krijgen:

Wasmachines - Verticaal
Wasmachines - Front Classic
Wasmachines - voorzijde smal

Laten we de structuur van tabellen definiëren voor het opslaan van informatie in de goederencatalogus. We gebruiken een vereenvoudigd schema om de boom in één tabel te organiseren:

MAAK TABEL GoodsTree
(ID_GOOD INTEGER NIET NULL,
ID_PARENT_GOED INTEGER,
GOOD_NAME VARCHAR (80),
beperking pkGooci primaire sleutel (ID_GOOD));

We maken één tabel GoodsTree, waarin er slechts 3 velden zijn: ID_GOOD - slimme categorie-ID, ID_PARENT_GOOD - identificatie van de bovenliggende stad voor deze categorie en GOOD_NAME - naam van de categorie. Om de integriteit van de gegevens in deze tabel te waarborgen, leggen we een externe sleutelbeperking op aan deze tabel:

WIJZIG TABEL GoodsTree
ADD CONSTRAINT FK_goodstree
BUITENLANDSE SLEUTEL (ID_PARENT_GOOD)
REFERENTIES GOODSTPEE (ID__GOOD)

De tabel verwijst naar zichzelf en de gegeven externe sleutel houdt dat bij. zodat er geen verwijzingen naar niet-bestaande ouders in de tabel zijn, en voorkomt ook pogingen om productcategorieën met afstammelingen te verwijderen.
Laten we de volgende gegevens in onze tabel plaatsen:

Nu we een plek hebben om de gegevens op te slaan, kunnen we beginnen met het maken van een opgeslagen procedure die alle "uiteindelijke" productcategorieën in een "uitgebreide" vorm weergeeft, bijvoorbeeld voor de categorie "Driekamer". voor-en achternaam de categorie zal eruit zien als "Huishoudelijke apparaten Koelkasten met drie compartimenten".
Opgeslagen procedures die boomachtige structuren verwerken hebben hun eigen terminologie. Elk element van de boom wordt een knoop genoemd; en de relatie tussen knooppunten die naar elkaar verwijzen, wordt een ouder-kindrelatie genoemd. De knooppunten die zich helemaal aan het einde van de boom bevinden en geen kinderen hebben, worden "bladeren" genoemd.
Een opgeslagen procedure hebben invoerparameter er zal een categorie-ID zijn, van waaruit we zullen moeten beginnen met ontvouwen. De opgeslagen procedure ziet er als volgt uit:

PROCEDURE MAKEN GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR (1000),
ID_CHILD_GOOD INTEGER)
ALS
VERKLAREN VARIABELE CURR_CHILD_NAME VARCHAR (80);
BEGINNEN
/ * 0 extern organiseren For loop SELECT op directe afstammelingen van het product met ID_GOOD = ID_GOOD2SHOW * /
VOOR SELECT gtl.id_good, gtl.good_name
VAN GoodsTree gtl
WAAR gtl.id_parent_good =: ID_good2show
INTO: ID_CHILD_GOOD,: full_goods_name
DOEN
BEGINNEN
/ "Controleren met de EXISTS-functie, die TRUE retourneert als de query tussen haakjes ten minste één rij retourneert. Als het gevonden knooppunt met ID_PARENT_GOOD = ID_CHILD_GOOD geen nakomelingen heeft, dan is het een" blad "van de boom en komt in de resultaten * /
ALS (NIET BESTAAT (
KIES * UIT GoodsTree
WAAR GoodsTree.id_parent_good =: id_child_good))
DAN
BEGINNEN
/ * Geef het "blad" van de boom door aan de resultaten * /
OPSCHORTEN;
EINDE
ANDERS
/ * Voor knooppunten met kinderen * /
BEGINNEN
/ * sla de naam van het bovenliggende knooppunt op in een tijdelijke variabele * /
CURR_CHILD_NAME = volledige_goederennaam;
/ * voer deze routine recursief uit * /
VOOR
SELECT ID_CHILD_GOOD, volledige_goederennaam
VAN GETFULLNAME (: ID_CHILD_GOOD)
INTO: ID_CHILD_GOOD,: full_goods_name
BEGINNEN
/ * voeg de naam van het bovenliggende knooppunt toe aan het gevonden knooppunt., de naam van het kind met behulp van de tekenreeksaaneenschakeling || * /
full_goods_name = CURR_CHILD_NAME | "" | volledige_goederennaam, -
OPSCHORTEN; / * retourneer de volledige naam van het product * /
EINDE
EINDE
EINDE
EINDE

Als we voldoen aan deze procedure met de invoerparameter ID_GOOD2SHOW = 1, dan krijgen we het volgende:

Zoals je kunt zien, hebben we met behulp van een recursieve opgeslagen procedure door de hele categorieboom gelopen en de volledige naam weergegeven van de "blad" -categorieën die zich helemaal aan de uiteinden van de takken bevinden.

Conclusie

Dit besluit de bespreking van de belangrijkste kenmerken van de taal voor opgeslagen procedures. Het is duidelijk dat het onmogelijk is om de ontwikkeling van opgeslagen procedures volledig onder de knie te krijgen in één hoofdstuk, maar hier hebben we geprobeerd de basisconcepten die verband houden met opgeslagen procedures te presenteren en uit te leggen. De beschreven CP-constructies en ontwerptechnieken kunnen in de meeste databasetoepassingen worden toegepast.
Deel belangrijke zaken met betrekking tot de ontwikkeling van opgeslagen procedures zullen worden beschreven in het volgende hoofdstuk - "Geavanceerde functies van de InterBase-taal voor opgeslagen procedures", die is gewijd aan het afhandelen van uitzonderingen, het afhandelen van fouten in opgeslagen procedures en het werken met arrays.

Er wordt rekening gehouden met een situatie waarin opgeslagen procedures de prestaties van query's kunnen verslechteren.

Bij het compileren van opgeslagen procedures in MS SQL-server 2000 opgeslagen procedures worden in een 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, in de opslag van de gecompileerde code van de opgeslagen procedure ligt onderwater rotsen wat het tegenovergestelde effect kan hebben.
Het feit is dat bij het compileren van een opgeslagen procedure het uitvoeringsplan van die operators die de procedurecode vormen, wordt gecompileerd, respectievelijk, als de gecompileerde opgeslagen procedure in de cache wordt opgeslagen, dan wordt het uitvoeringsplan in de cache opgeslagen en daarom zal de opgeslagen procedure niet worden geoptimaliseerd voor een specifieke situatie en queryparameters.
Zal een klein experiment doen om dit aan te tonen.

STAP 1... Database aanmaken.
Laten we een aparte database maken voor het experiment.

MAAK DATABASE test_sp_perf
AAN (NAAM = "test_data", FILENAME = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
AANMELDEN (NAAM = "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 tafel vullen met testlijnen. Dubbele rijen zijn opzettelijk aan de tabel toegevoegd. 10.000 regels genummerd van 1 tot 10.000 en 10.000 regels genummerd 50.000.

VERKLAREN @i int
SET @ i = 1
Terwijl ik<10000)
BEGINNEN
INSERT INTO sp_perf_test (column1, column2) VALUES (@i, "Test string #" + CAST (@i as char (8)))
INSERT INTO sp_perf_test (column1, column2) VALUES (50000, "Test string #" + CAST (@i as char (8)))
SET @ i = @ ik + 1
EINDE

SELECTEER AANTAL (*) VAN sp_perf_test
Gaan

STAP 4. Maak een niet-geclusterde index. Aangezien het uitvoeringsplan met de procedure in de cache wordt opgeslagen, wordt de index voor alle aanroepen hetzelfde gebruikt.

MAAK NIET-GECLUSTERDE INDEX CL_perf_test OP 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
SELECT kolom1, kolom2 FROM sp_perf_test WAAR [e-mail beveiligd]
Gaan

STAP 6. Een opgeslagen procedure uitvoeren. Bij het starten van een kwetsbare procedure wordt speciaal 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 is de beste manier om een ​​rij op te halen. Een procedure die is geoptimaliseerd voor het ophalen van één rij wordt opgeslagen in de procedurecache.

EXEC proc1 1234
Gaan

STAP 7. Een opgeslagen procedure uitvoeren met een niet-selectieve parameter. Als parameter wordt de waarde 50.000 gebruikt. Regels met een dergelijke waarde van de eerste kolom van respectievelijk ongeveer 10.000, waarbij een niet-geclusterde index en de bladwijzerzoekbewerking worden gebruikt, zijn niet effectief, maar aangezien de gecompileerde code met het uitvoeringsplan wordt opgeslagen in de procedurecache, zal deze worden gebruikt. Het uitvoeringsplan laat dit zien, evenals het feit dat de bladwijzerzoekbewerking werd uitgevoerd voor 9999 regels.

EXEC proc1 50.000
Gaan

STAP 8. Het ophalen van rijen met het eerste veld gelijk aan 50.000. Door een afzonderlijke query uit te voeren, wordt de query geoptimaliseerd en gecompileerd met de specifieke waarde van de eerste kolom. Als gevolg hiervan detecteert de query-optimizer 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.

SELECT column1, column2 FROM sp_perf_test WHERE column1 = 50000
Gaan

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

Opgeslagen procedure - een database-object, een set SQL-instructies die een keer wordt gecompileerd en op de server wordt opgeslagen. Opgeslagen procedures lijken erg op gewone procedures in talen op hoog niveau, ze kunnen invoer- en uitvoerparameters en lokale variabelen hebben, ze kunnen numerieke berekeningen en bewerkingen uitvoeren op symbolische gegevens, waarvan de resultaten kunnen worden toegewezen aan variabelen en parameters. Opgeslagen procedures kunnen standaard databasebewerkingen uitvoeren (zowel DDL als DML). Bovendien zijn lussen en vertakkingen mogelijk in opgeslagen procedures, 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 UDF's kunnen worden gebruikt zoals elke andere expressie in een SQL-query, terwijl opgeslagen procedures moeten worden aangeroepen met behulp van de CALL-functie:

OPROEP-procedure (...)

UITVOEREN procedure (...)

Opgeslagen procedures kunnen meerdere resultaten retourneren, dat wil zeggen de resultaten van een SELECT-query. Dergelijke resultaatsets kunnen worden verwerkt met behulp van cursors, andere opgeslagen procedures die een resultaatset-aanwijzer retourneren, of door toepassingen. Opgeslagen procedures kunnen ook gedeclareerde variabelen bevatten voor het verwerken van gegevens en cursors, waarmee u meerdere rijen in een tabel kunt doorlopen. De SQL-standaard biedt IF-, LOOP-, REPEAT-, CASE-instructies 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 verschilt van DBMS tot DBMS. De meeste grote databaseleveranciers ondersteunen ze in een of andere vorm. Afhankelijk van het DBMS kunnen opgeslagen procedures geïmplementeerd worden in verschillende programmeertalen zoals SQL, Java, C of C++. Opgeslagen procedures die niet in SQL zijn geschreven, kunnen al dan niet zelfstandig SQL-query's uitvoeren.

Per

Logica delen met andere applicaties. Opgeslagen procedures kapselen functionaliteit in; dit zorgt voor consistentie in gegevenstoegang en -beheer tussen verschillende applicaties.

Isolatie van gebruikers uit databasetabellen. Hiermee kunt u toegang geven tot opgeslagen procedures, maar niet tot de daadwerkelijke tabelgegevens.

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

Verbeterde prestaties als gevolg van verminderd netwerkverkeer. Met 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 gedaan en minder aan de clientzijde.

Er is veel te leren. U moet de syntaxis van MySQL-expressies leren om uw opgeslagen procedures te kunnen schrijven.

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

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

Doel en voordelen van opgeslagen procedures

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

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

Naast de daadwerkelijke uitvoering van query's, kunnen met opgeslagen procedures ook berekeningen en gegevensmanipulatie worden uitgevoerd - het wijzigen, verwijderen, uitvoeren van DDL-instructies (niet in alle DBMS'en!) en het aanroepen van andere opgeslagen procedures, waarbij complexe transactielogica wordt uitgevoerd. Met één enkele operator kunt u een complex script aanroepen dat is opgenomen in een opgeslagen procedure, waardoor het verzenden van honderden opdrachten over het netwerk en in het bijzonder de noodzaak om grote hoeveelheden gegevens van de client naar de server over te dragen, wordt vermeden.

In de meeste DBMS'en wordt de eerste keer dat u een opgeslagen procedure uitvoert, gecompileerd (parseert en genereert een gegevenstoegangsplan). In de toekomst wordt de verwerking ervan sneller uitgevoerd. Oracle DBMS interpreteert de opgeslagen procedurele code die is opgeslagen in de datadictionary. Vanaf versie Oracle 10g wordt de zogenaamde native compilatie van opgeslagen procedurele code in C en vervolgens in de machinecode van de doelmachine ondersteund, waarna, wanneer de opgeslagen procedure wordt aangeroepen, de gecompileerde objectcode direct wordt uitgevoerd.

Programmeermogelijkheden

Eenmaal gemaakt, kan de opgeslagen procedure op elk moment worden aangeroepen, wat zorgt voor modulariteit en hergebruik van code aanmoedigt. Dit laatste maakt het gemakkelijker om de database te onderhouden omdat deze geïsoleerd raakt van veranderende bedrijfsregels. U kunt de opgeslagen procedure op elk moment wijzigen in overeenstemming met de nieuwe regels. Daarna voldoen alle applicaties die er gebruik van maken automatisch aan de nieuwe bedrijfsregels zonder directe wijziging.

Veiligheid

Met behulp van opgeslagen procedures kunt u directe gebruikerstoegang tot databasetabellen beperken of volledig uitsluiten, zodat gebruikers alleen machtigingen hebben om opgeslagen procedures uit te voeren die indirecte en sterk gereguleerde toegang tot gegevens bieden. Bovendien ondersteunen sommige DBMS'en tekstversleuteling (wrapping) van de opgeslagen procedure.

Met deze beveiligingsfuncties kunt u de structuur van de database isoleren van de gebruiker, wat de integriteit en betrouwbaarheid van de database garandeert.

De kans op acties zoals "SQL-injectie" wordt verminderd omdat goed geschreven opgeslagen procedures bovendien invoerparameters valideren voordat een query naar het DBMS wordt verzonden.

Implementatie van opgeslagen procedures

Opgeslagen procedures worden meestal gemaakt met behulp van de SQL-taal of een specifieke implementatie ervan in het geselecteerde DBMS. Voor deze doeleinden is er in de Microsoft SQL Server DBMS bijvoorbeeld 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), Informix - SPL. MySQL volgt de SQL: 2003-standaard nauwkeurig genoeg, de taal is vergelijkbaar met SQL / PL.

In sommige DBMS'en is het mogelijk om opgeslagen procedures te gebruiken die zijn geschreven in elke programmeertaal die onafhankelijke uitvoerbare bestanden kan maken, bijvoorbeeld in C++ of Delphi. In de terminologie van Microsoft SQL Server worden dergelijke procedures uitgebreide opgeslagen procedures genoemd en zijn ze gewoon functies in een Win32-DLL. Interbase en Firebird hebben bijvoorbeeld een andere naam voor functies die worden aangeroepen vanuit DLL / SO - UDF (User Defined Function). In MS SQL 2005 werd het mogelijk om opgeslagen procedures in elke .NET-taal te schrijven, en het is de bedoeling om in de toekomst uitgebreide opgeslagen procedures af te schaffen. Oracle DBMS maakt het op zijn beurt mogelijk om opgeslagen procedures in de Java-taal te schrijven. In IBM DB2 was het schrijven van opgeslagen procedures en functies in gangbare programmeertalen de traditionele manier, vanaf het begin ondersteund, en de procedurele SQL-extensie werd pas in vrij late versies aan dit DBMS toegevoegd, na opname in de ANSI-standaard. Ook worden Java- en C-procedures ondersteund door Informix.

In 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 pakkethoofdtekst, waar de implementatie zich bevindt. Op deze manier stelt Oracle u in staat om de programmeerinterface los te koppelen van de implementatie.

In IBM DB2 kunnen opgeslagen procedures worden gecombineerd tot modules.

Syntaxis

PROCEDURE MAKEN `p2` ()

SQL-BEVEILIGINGSDEFINER

OPMERKING "Een procedure"

SELECTEER "Hallo wereld!";

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

4 kenmerken van een opgeslagen procedure:

Taal: Voor portabiliteitsdoeleinden is SQL standaard gespecificeerd.

Deterministisch: als de procedure altijd hetzelfde resultaat oplevert en dezelfde invoerparameters gebruikt. Dit is voor het replicatie- en registratieproces. De standaardinstelling 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 standaardinstelling is DEFINER.

Opmerking: voor documentatiedoeleinden is de standaard ""

Opgeslagen procedure-oproep

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

OPROEP procedure1 (10, "string parameter", @parameter_var);

Een opgeslagen procedure wijzigen

MySQL biedt een ALTER PROCEDURE-instructie voor het wijzigen van procedures, maar het is geschikt voor het wijzigen van slechts een paar kenmerken. Als u de parameters of de hoofdtekst van de procedure moet wijzigen, moet u deze verwijderen en opnieuw maken.

Verwijderenopgeslagenprocedures

AFVALPROCEDURE INDIEN BESTAAT p2;

Dit is een eenvoudig commando. De IF EXISTS-expressie vangt een fout op als een dergelijke procedure niet bestaat.

Opties

PROCEDURE MAKEN proc1 (): lege parameterlijst

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

PROCEDURE MAKEN proc1 (OUT varname DATA-TYPE): één retourparameter.

PROCEDURE MAKEN proc1 (INOUT varname DATA-TYPE): één parameter, zowel invoerend als terugkerend.

De syntaxis voor het declareren van een variabele ziet er als volgt uit:

DECLARE varname DATA-TYPE STANDAARD standaardwaarde;

Neem in uw procedures de regel - STEL NOCOUNT IN OP:

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

Transactie-SQL

Gebruik de schemanaam met de objectnaam:

Nou, hier denk ik dat het duidelijk is. Deze bewerking 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 moet meenemen. Met een groot aantal databases, tabellen en opgeslagen procedures kan het onze tijd en zenuwen aanzienlijk besparen.

Transactie-SQL

SELECT * FROM dbo.MyTable --Het is goed om dit te doen - In plaats van SELECT * FROM MyTable - En het is slecht om dat te doen -- De EXEC-procedure dbo.MyProc aanroepen - Weer goed --In plaats van EXEC MyProc --Slecht!

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

Als onze procedurenaam begint met "sp_", zal SQL Server eerst in de hoofddatabase kijken. Het punt is dat dit voorvoegsel wordt gebruikt voor persoonlijke interne server opgeslagen procedures. Daarom kan het gebruik ervan leiden tot extra kosten en zelfs een onjuist resultaat als een procedure met dezelfde naam als de uwe in de database wordt gevonden.

Gebruik IF EXISTS (SELECT 1) in plaats van IF EXISTS (SELECT *):

Om te controleren op een record in een andere tabel, gebruiken we de instructie IF EXISTS. Deze expressie retourneert true als ten minste één waarde wordt geretourneerd door de innerlijke expressie, het maakt niet uit "1", alle kolommen of een 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 wordt weergegeven.