SQL stored procedures: maken en gebruiken

SQL opgeslagen procedures uitvoerbare softwaremodule die kunnen worden opgeslagen database in de vorm van verschillende objecten. Met andere woorden, het is een object dat SQL-instructie bevat. Deze stored procedures kunnen worden uitgevoerd in de client applicatie om een goede prestatie te krijgen. Daarnaast worden dergelijke voorzieningen vaak uit andere scripts of zelfs van een andere sectie.

introductie

Veel mensen geloven dat ze zijn vergelijkbaar met de procedures van de verschillende high-level programmeertalen (respectievelijk, behalve voor MS SQL). Misschien is dat waar. Ze hebben dezelfde parameters, kunnen ze een vergelijkbare waarde te geven. Bovendien, in sommige gevallen, komen ze in contact komen. Zo worden ze gecombineerd met databases van DDL en DML data, alsmede gebruikerfuncties (codenaam - UDF).

Werkelijk opgeslagen SQL procedures uiteenlopende voordelen die hen onderscheiden tussen deze processen. Veiligheid, variabiliteit productiviteit programmering - dit alles trekt gebruikers die werken met databases, meer en meer. Het hoogtepunt van de populariteit kwam procedures voor de jaren 2005-2010, toen ik het programma van de "Microsoft" onder de naam «SQL Server Management Studio». Met zijn hulp, heeft het werken met databases veel gemakkelijker, praktischer en handiger geworden. Jaar na jaar, deze methode voor het overdragen van informatie aan populariteit gewonnen in de programmeeromgeving. Vandaag de dag, MS SQL Server is absoluut normaal programma, dat voor gebruikers om "communiceren" met databases, stond op een lijn met "Excel".

Wanneer u de procedure te bellen, wordt het direct verwerkt door de server zonder onnodige processen en tussenkomst van de gebruiker. verwijdering, de uitvoering, verandering: U kunt dan alle handelingen met de informatie uit te voeren. Over dit alles is in de DDL-exploitant, die in zijn eentje uitvoeren van complexe acties op deze objecten. En het gebeurt allemaal heel snel, en de server niet daadwerkelijk geladen. Deze snelheid en productiviteit kunt u snel grote hoeveelheden informatie over te brengen van de gebruiker naar de server en vice versa.

Om dit werk met de informatie uit te voeren, zijn er verschillende programmeertalen technologieën. Deze omvatten, bijvoorbeeld, PL / SQL uit de database management systeem Oracle, PSQL in InterBase en Firebird systemen, evenals de klassieke "maykrosoftovskih» Transact-SQL. Ieder van hen zijn ontworpen voor het creëren en opgeslagen procedures te voeren, zodat u om hun eigen algoritmes gebruiken op een grote database handlers. Het is noodzakelijk en om ervoor te zorgen dat degenen die het beheer van dergelijke informatie uit te voeren, kunnen alle objecten te beschermen tegen onbevoegde derden en daarom kan het aanmaken, wijzigen of verwijderen van bepaalde gegevens.

produktiviteit

Deze database-objecten kan worden geprogrammeerd op verschillende manieren. Dit stelt gebruikers in staat om het type proces dat het meest geschikt zou zijn, wat tijd en energie bespaart selecteren. Bovendien is de procedure zelf verwerkt, dus enorm veel tijd besteed aan de communicatie tussen de server en de gebruiker te vermijden. De module kan worden geprogrammeerd en gewijzigd in de juiste richting absoluut elk moment. Met name gewezen op de snelheid waarmee de lancering van SQL opgeslagen procedure gebeurt: het proces is sneller andere vergelijkbaar met het, waardoor het een handig en veelzijdig.

veiligheid

Dit soort informatieverwerking anders dan soortgelijke processen doordat deze verbeterde beveiliging. Hiervoor zorgt het feit dat andere gebruikers toegang procedures volledig worden geëlimineerd. Hierdoor kan de beheerder om operaties zelfstandig uit te voeren met hen, zonder angst voor onderschepping of ongeautoriseerde toegang tot de database.

gegevensoverdracht

Communicatie tussen een opgeslagen SQL-procedure en de cliënt applicatie is om de parameters en resultaatwaarden. Dit laatste is niet nodig om de gegevens naar een opgeslagen procedure, maar de informatie (vooral op verzoek van de gebruiker) en verwerkt voor SQL. Eenmaal opgeslagen procedure zijn werk heeft voltooid, stuurt de datapakketten rug (maar nogmaals, indien gewenst) aan de toepassing voor bellen met behulp van een verscheidenheid van methoden die kunnen worden geïmplementeerd als een oproep tot een opgeslagen SQL-procedure en de terugkeer, bijvoorbeeld:

- gegevensoverdracht via Uitvoertype parameter;

- gegevensoverdracht via de return statement;

- Overdracht van gegevens via carrier selectie.

En nu kijken hoe dit proces ziet er hetzelfde uit binnen.

1. Maak EXEC opgeslagen in de SQL-procedure

U kunt een procedure in MS SQL (Management Studio) te creëren. Nadat de procedure is gemaakt, wordt deze weergegeven op een programmeerbare databaseknooppunt, waarin de procedure wordt uitgevoerd door de bediener van de schepping. SQL opgeslagen procedures uit te voeren met behulp EXEC-proces dat de objectnaam bevat.

Bij het maken van de naam komt op de eerste procedure, en maakte vervolgens een of meer parameters die aan hem zijn toegewezen. Parameters kunnen optioneel zijn. Na de parameter (s), dat wil zeggen het lichaam van de procedure, moeten worden geschreven, is het noodzakelijk enkele noodzakelijke handelingen uit te voeren.

Het feit dat het lichaam kan lokale variabelen in te hebben, en deze variabelen zijn lokaal en ten opzichte van de procedures. Met andere woorden, ze kunnen alleen worden overwogen in het lichaam behandelingen Microsoft SQL Server. Opgeslagen procedures in dit geval worden beschouwd als lokaal.

Dus, om een proces te maken, hebben we een procedure naam en ten minste één parameter als een procedure lichaam. Houdt u er rekening mee dat er een uitstekende optie in dit geval is de creatie en implementatie van procedures met een schema naam in de classifier.

De procedure lichaam kan elke vorm van operators SQL, bijvoorbeeld, zoals een tabel, invoegen van één of meer rijen van de tabel gaan op welke soort database karakter, enzovoort. Toch is de procedure lichaam beperkt aantal operaties in het. Enkele van de meest belangrijke beperkingen zijn hieronder opgesomd:

- het lichaam geen andere opgeslagen procedure te creëren;

- moet het lichaam een verkeerde indruk over het object niet maken;

- het lichaam heeft geen triggers te creëren.

2. Stel de variabele in het lichaam van de procedure

U kunt variabelen lokaal op de procedure van het lichaam te maken, en dan zullen ze binnen een procedure lichaam. Een goede praktijk is om een variabele te maken aan het begin van de opgeslagen procedure lichaam. Maar je kunt ook instellen variabelen overal in het lichaam van het object.

Soms kunt u merken dat sommige variabelen zijn ingesteld in dezelfde rij, en elke variabele gescheiden door een komma. Merk ook op dat de variabele wordt voorafgegaan door @. In het lichaam van de procedure kunt u een variabele die u wilt instellen. Bijvoorbeeld kan een variabele @ NAME1 bekend gemaakt aan het einde van de procedure lichaam. Om een waarde aan een variabele toe te wijzen verklaard met behulp van een set van persoonsgegevens. In tegenstelling tot de situatie wanneer er meer dan één variabele gedeclareerd in dezelfde rij, maar een geheel van persoonsgegevens die in deze situatie.

Gebruikers vragen vaak de vraag: "Hoe kan ik meerdere waarden in één overzicht Nou toewijzen in het lichaam van de procedure'?. Interessante vraag, maar dit is veel makkelijker dan je denkt. Het antwoord: met de hulp van dergelijke paren als «Select Var = waarde". U kunt deze paren, gescheiden door komma's te gebruiken.

3. Maak een SQL opgeslagen procedure

In een breed scala aan voorbeelden van mensen te laten zien het creëren van een eenvoudige opgeslagen procedure en het uitvoeren. Echter, kan de procedure zodanig parameters die het aanroepende proces zal het een waarde dicht bij haar (maar niet altijd) moest doen. Als ze overeenkomen, dan begint de bijbehorende processen in het lichaam. Bijvoorbeeld, als u een procedure die de stad en de regio zal nemen van de beller en de gegevens over hoeveel de auteurs verwijzen naar de overeenkomstige stad en de regio terug te creëren. De procedure zal een databasetabel auteurs opzoeking bijvoorbeeld Bars, voor het uitvoeren van deze berekening auteurs. Om deze bestanden te verkrijgen, bijvoorbeeld de Google downloadt de SQL-script met SQL2005 pagina.

In het voorgaande voorbeeld, de procedure neemt twee parameters, die in het Engels voorwaardelijk @State en @City worden genoemd. Het gegevenstype komt overeen met de in het toepassingstype. De procedurebody heeft interne variabelen @TotalAuthors (alle auteurs) en deze variabele wordt gebruikt om het te bekijken. Vervolgens komt een sectie selectie verzoek dat alle punten. Ten slotte wordt de berekende waarde wordt weergegeven in de output venster met behulp van de print-statement.

Hoe maak je een SQL opgeslagen procedure uit te voeren

Er zijn twee manieren om de procedure uit te voeren. De eerste manier is aangegeven door parameters als komma's gescheiden lijst wordt uitgevoerd nadat het procedurenaam. Stel we hebben twee waarden (zoals in het vorige voorbeeld). Deze waarden worden verzameld met behulp variabelen en @State @City procedure. Bij deze werkwijze belangrijke parameters van de overdrachtsvolgorde. Deze methode wordt de transmissiereeks argumenten. Bij de tweede werkwijze worden de parameters reeds direct toegewezen, waarbij de volgorde niet van belang. Deze tweede werkwijze is bekend als de overdracht van genoemde argumenten.

De procedure kan afwijken van het normale. Toch, als in het voorgaande voorbeeld, maar de parameters worden hier alleen verschoven. Dat is @City parameter wordt eerst opgeslagen en @State opgeslagen naast de standaard waarde. De standaardinstelling wordt gewoonlijk afzonderlijk toegewezen. SQL opgeslagen procedures zo eenvoudig parameters. In dit geval, op voorwaarde dat de optie "de UT 'vervangt de standaard waarde van" CA ". In een tweede uitvoering laat alleen een argument waar voor @City en @State optie staat standaard op "CA". Ervaren programmeurs worden erop gewezen dat alle standaard variabelen dichter bij het einde van de parameter lijst bevinden. Anders is de uitvoering niet mogelijk is, dan moet je om te werken met de overdracht van de benoemde argumenten dat hoe langer en ingewikkelder.

4. Stored Procedures SQL Server: manieren om terug te keren

Er zijn drie belangrijke manieren om gegevens die zijn opgeslagen in de zogenaamde procedure te sturen. Ze zijn hieronder opgesomd:

- terugkeerwaarden opgeslagen procedure;

- Output parameter opgeslagen procedures;

- Selecteer een van de opgeslagen procedures.

4.1 Return waarden van SQL stored procedures

In deze procedure, de procedure stelt de waarde van de lokale variabele en stuurt het terug. De procedure kan ook rechtstreeks een constante waarde retourneren. In het volgende voorbeeld hebben we een procedure die het totaal aantal sponsors terug gemaakt. Als we dit vergelijken met de vorige procedure, kun je zien dat de waarde van de afdruk wordt vervangen door het omgekeerde.

Laten we nu eens kijken naar hoe de procedure uit te voeren en de waarde weer te geven, terug te sturen. Het uitvoeren van werkzaamheden moeten opzetten en variabele drukken, die na het proces wordt uitgevoerd. Merk op dat in plaats van het afdrukken van de operator de Select-operator kan gebruiken, bijvoorbeeld, Select @RetValue en OutputValue.

4.2 Output Parameter SQL stored procedures

De respons waarde kan worden gebruikt om één variabele die we hebben gezien in het vorige voorbeeld retourneren. Met behulp van de uitgang kan de procedure om een of meer variabelen te sturen naar de oproepende partij. Output parameter wordt aangeduid als de tijd dit sleutelwoord «Uitvoer» bij het maken van de procedure. Als de parameter is opgegeven als een uitvoer parameter, moet procedureobject toewijzen een waarde. SQL opgeslagen procedures waarvan voorbeelden hieronder te zien is, in dat geval terug naar de laatste informatie.

In dit voorbeeld zullen er twee weekends naam zijn: @TotalAuthors en @TotalNoContract. Ze worden opgegeven in de parameter lijst. Deze variabelen zijn toegewezen waarden in de procedurebody. Toen we de output parameter te gebruiken, kan de beller de in de procedure lichaam waarde te zien.

Verder in de voorgaande scenario twee variabelen gedeclareerd op de waarden die opgeslagen procedures, MS SQL Server als een uitvoer parameter worden geïnstalleerd. Vervolgens wordt de procedure wordt uitgevoerd door het toepassen van de normale waarde «CA» parameter. De volgende parameters worden uitgevoerd en derhalve aangegeven variabelen worden doorgegeven op de voorgeschreven wijze. Houd er rekening mee dat wanneer een variabele uitgang trefwoord ook hier opgegeven. Nadat de procedure is voltooid, worden de waarde die door het gebruik van de output parameters weergegeven op het scherm weergegeven.

4.3 Het selecteren van een SQL opgeslagen procedures

Deze techniek wordt gebruikt om een set in de vorm tabel gegevenswaarden (RecordSet) terug voor het oproepen van een opgeslagen procedure. In dit voorbeeld, SQL opgeslagen procedure met parameters @AuthID vraagt table "auteurs" door het filteren van records geretourneerd door deze parameter @AuthId. Kies een operator bepaalt wat moet worden geretourneerd aan het bellen van een opgeslagen procedure. Wanneer een opgeslagen procedure authid overgedragen terug. Een dergelijke procedure is altijd terug slechts één record of helemaal geen. Echter, de opgeslagen procedure heeft geen beperkingen op de terugkeer van meer dan één binnenkomst. Vaak voorbeelden zijn te vinden waarbij de retourgegevens geselecteerd met parameters de berekende variabelen wordt uitgevoerd door een aantal totale waarden.

Tot slot

Een opgeslagen procedure is een vrij ernstige programma terug of overgedragen, alsmede het noodzakelijke variabelen als gevolg van de cliënttoepassing. Aangezien de opgeslagen procedure wordt uitgevoerd op de server zelf, kunnen gegevensuitwisseling grote volumes tussen de server en de cliënt applicatie (aantal berekeningen) worden vermeden. Dit maakt het mogelijk de belasting op de SQL server, die uiteraard is de hand van de houder te verminderen. Een van de ondersoorten worden opgeslagen procedures T SQL, maar hun studie nodig is om deze betrokken bij de oprichting van een indrukwekkende database. Er is ook een grote, zelfs enorme hoeveelheid nuances die nuttig zijn bij de studie van stored procedures kunnen zijn, maar deze behoefte voor degenen die van plan zijn om strakke programmering, met inbegrip van professioneel.