Ms sql server procedurer. Ændring af en lagret procedure til T-SQL - ALTER PROCEDURE-sætning
Lagret procedure er en speciel type Transact-SQL-sætningspakke, der er oprettet ved hjælp af SQL-sproget og procedureudvidelser. Den største forskel mellem en pakke og en lagret procedure er, at sidstnævnte er gemt som et databaseobjekt. Med andre ord gemmes lagrede procedurer på serversiden for at forbedre ydeevnen og konsistensen af gentagelige opgaver.
Databasemotoren understøtter lagrede procedurer og systemprocedurer. Lagrede procedurer oprettes på samme måde som alle andre databaseobjekter, dvs. bruger DDL sprog. Systemprocedurer leveres af databasemotoren og kan bruges til at få adgang til og ændre information i systemkataloget.
Når du opretter en lagret procedure, kan du definere en valgfri liste over parametre. På denne måde vil proceduren acceptere de relevante argumenter, hver gang den kaldes. Lagrede procedurer kan returnere en værdi, der indeholder brugerdefinerede oplysninger eller, i tilfælde af en fejl, en passende fejlmeddelelse.
Den lagrede procedure er prækompileret, før den gemmes som et objekt i databasen. Den prækompilerede form af proceduren gemmes i databasen og bruges hver gang den kaldes. Denne egenskab ved lagrede procedurer giver den vigtige fordel at eliminere (i næsten alle tilfælde) gentagne procedurekompilationer og opnå tilsvarende præstationsforbedringer. Denne egenskab ved lagrede procedurer har også en positiv effekt på mængden af data, der udveksles mellem databasesystemet og applikationer. Især kan det kræve mindre end 50 bytes at kalde en lagret procedure, der er flere tusinde bytes stor. Når flere brugere udfører gentagne opgaver ved hjælp af lagrede procedurer, kan den kumulative effekt af disse besparelser være ganske betydelig.
Lagrede procedurer kan også bruges til følgende formål:
at oprette en log over handlinger med databasetabeller.
Brug af lagrede procedurer giver et niveau af sikkerhedskontrol, der går langt ud over den sikkerhed, der ydes ved at bruge GRANT- og REVOKE-sætninger, som giver forskellige adgangsrettigheder til brugere. Dette er muligt, fordi autorisationen til at udføre en lagret procedure er uafhængig af autorisationen til at ændre objekterne indeholdt i den lagrede procedure, som beskrevet i næste afsnit.
Lagrede procedurer, der opretter logfiler for tabelskrive- og/eller læseoperationer yderligere mulighed sikring af databasesikkerhed. Ved at bruge sådanne procedurer kan databaseadministratoren overvåge ændringer foretaget af databasen af brugere eller applikationsprogrammer.
Oprettelse og udførelse af lagrede procedurer
Lagrede procedurer oprettes ved hjælp af en erklæring OPRET PROCEDURE, som har følgende syntaks:
OPRET PROC proc_navn [((@param1) type1 [ VARIERENDE] [= default1] )] (, ...) AS batch | EKSTERNT NAVN metodenavn Syntakskonventioner
Parameteren schema_name angiver navnet på det skema, der er tildelt af ejeren af den oprettede lagrede procedure. Parameteren proc_name angiver navnet på den lagrede procedure. Parameteren @param1 er en procedureparameter (formelt argument), hvis datatype bestemmes af parameteren type1. Procedureparametre er lokale i proceduren, ligesom lokale variabler er lokale i pakken. Procedureparametre er værdier, der overføres af den, der ringer, til proceduren til brug i den. Standard1-parameteren angiver standardværdien for den tilsvarende procedureparameter. (Standardværdien kan også være NULL.)
OUTPUT mulighed angiver, at en procedureparameter er en returparameter og kan bruges til at returnere en værdi fra en lagret procedure til den kaldende procedure eller systemet.
Som tidligere nævnt gemmes den prækompilerede form af en procedure i databasen og bruges hver gang den kaldes. Hvis den lagrede procedure af en eller anden grund skal kompileres hver gang den kaldes, skal du bruge MED REKOMPILERING mulighed. Brug af WITH RECOMPILE-indstillingen negerer en af de mest vigtige fordele Lagrede procedurer: Forbedret ydeevne med en enkelt kompilering. Derfor bør indstillingen WITH RECOMPILE kun bruges, når databaseobjekterne, der bruges af den lagrede procedure, ofte ændres.
UDFØR SOM-klausulen definerer den sikkerhedskontekst, som den lagrede procedure skal udføres i, efter at den er kaldt. Ved at indstille denne kontekst kan databasemotoren kontrollere valget af brugerkonti for at verificere adgangstilladelser til de objekter, der refereres til af den lagrede procedure.
Som standard er det kun medlemmer af den faste serverrolle sysadmin og de faste databaseroller db_owner eller db_ddladmin, der kan bruge CREATE PROCEDURE-sætningen. Men medlemmer af disse roller kan tildele denne rettighed til andre brugere ved hjælp af erklæringen PROCEDURE FOR OPRETNING TIL TILBUD.
Eksemplet nedenfor viser, hvordan man opretter en simpel lagret procedure til at arbejde med projekttabellen:
BRUG SampleDb; GÅ OPRET PROCEDURE Øg budget (@procent INT=5) SOM OPDATERING Projekt SÆT Budget = Budget + Budget * @procent/100;
Som nævnt tidligere, for at adskille to pakker, brug GO instruktioner. CREATE PROCEDURE-sætningen kan ikke kombineres med andre Transact-SQL-sætninger i samme batch. Den lagrede procedure for IncreaseBudget øger budgetterne for alle projekter med en vis procentdel, bestemt af parameteren @percent. Proceduren definerer også en standardprocentværdi (5), der bruges, hvis dette argument ikke er til stede, når proceduren kører.
Lagrede procedurer kan få adgang til tabeller, der ikke eksisterer. Denne egenskab giver dig mulighed for at fejlsøge procedurekode uden først at oprette de relevante tabeller eller endda oprette forbindelse til destinationsserveren.
I modsætning til primære lagrede procedurer, som altid er gemt i den aktuelle database, er det muligt at oprette midlertidige lagrede procedurer, der altid er placeret i en midlertidig system base tempdb data. En grund til at oprette midlertidige lagrede procedurer kan være at undgå gentagen eksekvering. bestemt gruppe instruktioner ved tilslutning til databasen. Du kan oprette lokale eller globale midlertidige procedurer. For at gøre dette angives navnet på den lokale procedure med et enkelt #-tegn (#proc_name), og navnet på den globale procedure er angivet med et dobbelttegn (##proc_name).
En lokal midlertidig lagret procedure kan kun udføres af den bruger, der har oprettet den, og kun mens den er forbundet til databasen, hvor den blev oprettet. En global midlertidig procedure kan udføres af alle brugere, men kun indtil den er fuldført sidste forbindelse hvor den udføres (normalt forbindelsen fra procedurens skaber).
Livscyklussen for en lagret procedure består af to faser: dens oprettelse og dens udførelse. Hver procedure oprettes én gang og udføres mange gange. Den lagrede procedure udføres vha UDFØR instruktionerne en bruger, der er ejer af en procedure eller har EXECUTE-rettigheder til at få adgang til denne procedure. EXECUTE-sætningen har følgende syntaks:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] værdi | [@parameter1=] @variabel ] | DEFAULT).. Syntakskonventioner
Med undtagelse af return_status-parameteren har alle parametre i EXECUTE-sætningen samme logiske betydning som parametrene med samme navn OPRET udsagn PROCEDURE. Return_status-parameteren angiver en heltalsvariabel, der gemmer procedurens returneringsstatus. En værdi kan tildeles en parameter ved hjælp af enten en konstant (værdi) eller en lokal variabel (@variable). Rækkefølgen af værdierne af navngivne parametre er ikke vigtig, men værdierne af unavngivne parametre skal angives i den rækkefølge, som de er defineret i CREATE PROCEDURE-sætningen.
DEFAULT klausul angiver standardværdien for en procedureparameter, der blev angivet i proceduredefinitionen. Når en procedure forventer en værdi for en parameter, for hvilken der ikke er defineret en standardværdi, og parameteren mangler, eller nøgleordet DEFAULT er angivet, opstår der en fejl.
Når EXECUTE-sætningen er den første sætning i en batch, kan nøgleordet EXECUTE udelades. Det er dog mere sikkert at inkludere dette ord i hver pakke. Brugen af EXECUTE-sætningen er vist i eksemplet nedenfor:
BRUG SampleDb; UDFØR Forøg Budget 10;
EXECUTE-sætningen i dette eksempel udfører den lagrede IncreaseBudget-procedure, som øger budgettet for alle projekter med 10 %.
Eksemplet nedenfor viser, hvordan man opretter en lagret procedure til at behandle data i tabellerne Employee og Works_on:
ModifyEmpId-eksempelproceduren illustrerer brugen af lagrede procedurer som en del af den referentielle integritetsproces (i I dette tilfælde mellem tabellerne Employee og Works_on). En lignende lagret procedure kan bruges inde i en triggerdefinition, som faktisk giver referentiel integritet.
Følgende eksempel viser brugen af en OUTPUT-sætning i en lagret procedure:
Denne lagrede procedure kan udføres ved hjælp af følgende instruktioner:
DECLARE @quantityDeleteEmployee INT; UDFØR DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Slettede medarbejdere: " + convert(nvarchar(30), @quantityDeleteEmployee);
Denne procedure tæller antallet af projekter, som medarbejderen med personalenummer @empId arbejder på, og tildeler den resulterende værdi til parameteren ©tæller. Efter at alle rækker for et givet personalenummer er slettet fra tabellerne Employee og Works_on, tildeles den beregnede værdi til @quantityDeleteEmployee-variablen.
Parameterværdien returneres kun til opkaldsproceduren, hvis OUTPUT-indstillingen er angivet. I eksemplet ovenfor sender DeleteEmployee-proceduren @counter-parameteren til opkaldsproceduren, og den lagrede procedure returnerer derfor en værdi til systemet. Derfor skal @counter-parameteren angives både i OUTPUT-indstillingen, når en procedure erklæres, og i EXECUTE-sætningen, når den kaldes.
WITH RESULTS SETS-sætning af EXECUTE-sætningen
I SQL Server 2012 for EXECUTE-sætning er indtastet MED RESULTATSÆT klausul, hvorigennem du, når visse betingelser er opfyldt, kan ændre formen for resultatsættet af en lagret procedure.
De følgende to eksempler vil hjælpe med at forklare denne sætning. Det første eksempel er et indledende eksempel, der viser, hvordan resultatet kan se ud, når WITH RESULTS SETS-sætningen er udeladt:
EmployeesInDept-proceduren er enkel procedure, som viser personalenumre og navne på alle medarbejdere, der arbejder i en bestemt afdeling. Afdelingsnummeret er en procedureparameter og skal angives ved opkald. Udførelse af denne procedure producerer en tabel med to kolonner, hvis overskrifter matcher navnene på de tilsvarende kolonner i databasetabellen, dvs. Id og efternavn. For at ændre overskrifterne på resultatkolonner (såvel som deres datatype) bruger SQL Server 2012 den nye WITH RESULTS SETS-klausul. Anvendelsen af denne sætning er vist i eksemplet nedenfor:
BRUG SampleDb; EXEC EmployeesInDept "d1" MED RESULTATSÆT ((INT NOT NULL, [LastName] CHAR(20) NOT NULL));
Resultatet af at udføre en lagret procedure kaldet på denne måde vil være som følger:
Som du kan se, giver kørsel af en lagret procedure ved hjælp af WITH RESULT SETS-sætningen i EXECUTE-sætningen dig mulighed for at ændre navnene og datatyperne for kolonnerne i resultatsættet, der er produceret af proceduren. Denne nye funktionalitet giver således større fleksibilitet til at udføre lagrede procedurer og placere deres resultater i en ny tabel.
Ændring af strukturen af lagrede procedurer
Databasemotoren understøtter også instruktionen ÆNDRING AF PROCEDURE at ændre strukturen af lagrede procedurer. ALTER PROCEDURE-sætningen bruges typisk til at ændre Transact-SQL-sætninger i en procedure. Alle parametre i ALTER PROCEDURE-sætningen har samme betydning som de samme parametre i CREATE PROCEDURE-sætningen. Hovedformålet med at bruge denne erklæring er at undgå at tilsidesætte eksisterende lagrede procedurerettigheder.
Databasemotoren understøtter CURSOR datatype. Denne datatype bruges til at angive markører i lagrede procedurer. cursoren er en programmeringskonstruktion, der bruges til at gemme resultaterne af en forespørgsel (normalt et sæt rækker) og tillade brugere at vise dette resultat række for række.
For at slette en eller en gruppe af lagrede procedurer, brug DROP PROCEDURE instruktion. Kun ejeren eller medlemmerne af de faste roller db_owner og sysadmin kan slette en lagret procedure.
Lagrede procedurer og det fælles sprogs runtime
SQL Server understøtter Common Language Runtime (CLR), som giver dig mulighed for at udvikle forskellige databaseobjekter (lagrede procedurer, brugerdefinerede funktioner, triggere, brugerdefinerede aggregeringer og brugerdefinerede datatyper) ved hjælp af C# og Visual Basic. CLR giver dig også mulighed for at udføre disse objekter ved hjælp af det fælles runtime-system.
Det fælles sprogs runtime aktiveres og deaktiveres ved hjælp af indstillingen clr_enabled system procedure sp_configure, som lanceres til udførelse ved instruktion GENKONFIGURER. Følgende eksempel viser, hvordan du kan bruge sp_configure-systemproceduren til at aktivere CLR:
BRUG SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
For at oprette, kompilere og gemme en procedure ved hjælp af CLR, skal du udføre følgende sekvens af trin i den viste rækkefølge:
Opret en lagret procedure i C# eller Visual Basic, og kompilér den derefter ved hjælp af den relevante compiler.
Brug af instruktioner OPRET FORSÆTNING, opret den tilsvarende eksekverbare fil.
Udfør proceduren ved hjælp af EXECUTE-sætningen.
Billedet nedenfor viser grafisk diagram de tidligere skitserede trin. Det følgende er en mere detaljeret beskrivelse af denne proces.
Opret først det nødvendige program i et udviklingsmiljø som f.eks Visual Studio. Udarbejde færdigt program ind i objektkode ved hjælp af C# eller Visual Basic-kompileren. Denne kode er gemt i en .dll-fil (dynamic-link library), som fungerer som kilden til CREATE ASSEMBLY-sætningen, som opretter den mellemliggende eksekverbare kode. Udgiv derefter en CREATE PROCEDURE-sætning for at gemme den eksekverende kode som et databaseobjekt. Kør endelig proceduren ved hjælp af den velkendte EXECUTE-sætning.
Eksemplet nedenfor viser kildekoden til en lagret procedure i C#:
Brug af System.Data.SqlClient; bruger Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select tælle(*) som "Antal medarbejdere" " + "fra medarbejder"; rækker = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )
Denne procedure implementerer en forespørgsel til at tælle antallet af rækker i tabellen medarbejder. Ved at bruge direktiver i begyndelsen af et program specificeres de navneområder, der kræves for at udføre programmet. Ved at bruge disse direktiver kan du specificere klassenavne i kildekoden uden eksplicit at angive de tilsvarende navneområder. Dernæst defineres StoredProcedures-klassen, for hvilken SqlProcedure attribut, som informerer compileren om, at denne klasse er en lagret procedure. CountEmployees()-metoden er defineret inde i klassekoden. En forbindelse til databasesystemet etableres gennem en forekomst af klassen SQL-forbindelse. For at åbne en forbindelse bruges Open()-metoden for denne instans. EN CreateCommand() metode giver dig adgang til en forekomst af en klasse SqlCommnd, som den nødvendige SQL-kommando sendes til.
I følgende kodestykke:
Cmd.CommandText = "vælg count(*) som "Antal medarbejdere" " + "fra medarbejder";
bruger en SELECT-sætning til at tælle antallet af rækker i Employee-tabellen og vise resultatet. Kommandoteksten angives ved at indstille CommandText-egenskaben for cmd-variablen til den forekomst, der returneres af CreateCommand()-metoden. Næste hedder det ExecuteScalar() metode SqlCommand-forekomst. Denne metode returnerer en skalarværdi, som konverteres til heltalstype int-data og tildelt rækkevariablen.
Du kan nu kompilere denne kode ved hjælp af Visual Studio. Jeg føjede denne klasse til et projekt kaldet CLRStoredProcedures, så Visual Studio vil kompilere en samling af samme navn med en *.dll-udvidelse. Eksemplet nedenfor viser det næste trin i oprettelse af en lagret procedure: oprettelse af den eksekverbare kode. Før du kører koden i dette eksempel, skal du kende placeringen af den kompilerede dll-fil (normalt placeret i Debug-mappen i projektet).
BRUG SampleDb; GÅ OPRET FORSÆTNING CLRStoredProcedures FRA "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" MED PERMISSION_SET = SAFE
CREATE ASSEMBLY-sætningen tager administreret kode som input og opretter et tilsvarende objekt, hvorpå du kan oprette CLR-lagrede procedurer, brugerdefinerede funktioner og triggere. Denne instruktion har følgende syntaks:
OPRET ASSEMBLY assembly_name [ AUTHORIZATION ejernavn ] FRA (dll_file) Syntakskonventioner
Parameteren assembly_name angiver navnet på samlingen. Den valgfri AUTHORIZATION-klausul specificerer rollenavnet som ejer af denne forsamling. FROM-sætningen specificerer stien, hvor samlingen, der skal indlæses, er placeret.
WITH PERMISSION_SET klausul er en meget vigtig del af CREATE ASSEMBLY-sætningen og skal altid specificeres. Den definerer det sæt tilladelser, der er givet til assembly-koden. SAFE-tilladelsessættet er det mest restriktive. Monteringskode, der har disse rettigheder, kan ikke få adgang til eksterne systemressourcer såsom filer. EXTERNAL_ACCESS-rettighedssættet tillader samlingskode at få adgang til visse eksterne systemressourcer, mens UNSAFE-rettighedssættet tillader ubegrænset adgang til ressourcer både i og uden for databasesystemet.
For at gemme monteringskodeoplysninger skal brugeren være i stand til at udstede en CREATE ASSEMBLY-erklæring. Ejeren af samlingen er brugeren (eller rollen), der udfører instruktionen. Du kan gøre en anden bruger til ejeren af samlingen ved at bruge AUTHORIZATION-sætningen i CREATE SCHEMA-sætningen.
Databasemotoren understøtter også ALTER ASSEMBLY og DROP ASSEMBLY-sætninger. ÆNDRING AF SAMLING erklæring bruges til at opdatere samlingen til den nyeste version. Denne instruktion tilføjer eller fjerner også filer forbundet med den tilsvarende samling. DROP MONTERING instruktion Fjerner den angivne samling og alle dens tilknyttede filer fra den aktuelle database.
Eksemplet nedenfor viser, hvordan du opretter en lagret procedure baseret på den administrerede kode, du implementerede tidligere:
BRUG SampleDb; GÅ OPRET PROCEDURE CountEmployees SOM EKSTERNT NAVN CLRStoredProcedures.StoredProcedures.CountEmployees
CREATE PROCEDURE-instruktionen i eksemplet adskiller sig fra den samme instruktion i de foregående eksempler ved, at den indeholder EXTERNAL NAME parameter. Denne indstilling angiver, at koden genereres af den fælles sprog-runtime. Navnet i denne sætning består af tre dele:
samlingsnavn.klassenavn.metodenavn
assembly_name - angiver navnet på samlingen;
klasse_navn - angiver navnet på den generelle klasse;
metodenavn - valgfri del, angiver navnet på metoden, der er defineret inde i klassen.
Udførelsen af CountEmployees-proceduren er vist i eksemplet nedenfor:
BRUG SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Return 7
PRINT-sætningen returnerer det aktuelle antal rækker i tabellen medarbejder.
Lagret procedure - et databaseobjekt, som er et sæt SQL-instruktioner, der kompileres én gang og gemmes på serveren. Lagrede procedurer ligner meget almindelige sprogprocedurer på højt niveau, de kan have input- og outputparametre og lokale variabler, de kan udføre numeriske beregninger og operationer på tegndata, hvis resultater kan tildeles variabler og parametre. Lagrede procedurer kan udføre standard databaseoperationer (både DDL og DML). Derudover tillader lagrede procedurer sløjfer og forgreninger, det vil sige, de kan bruge instruktioner til at styre udførelsesprocessen.
Lagrede procedurer ligner brugerdefinerede funktioner (UDF'er). Den største forskel er, at brugerdefinerede funktioner kan bruges som ethvert andet udtryk i en SQL-sætning, mens lagrede procedurer skal kaldes ved hjælp af CALL-funktionen:
CALL procedure (...)
UDFØR procedure(...)
Lagrede procedurer kan returnere flere resultater, det vil sige resultaterne af en SELECT-forespørgsel. Sådanne resultatsæt kan behandles ved hjælp af markører, andre lagrede procedurer, der returnerer en resultatsætmarkør, eller applikationer. Lagrede procedurer kan også indeholde deklarerede variabler til behandling af data og markører, som giver dig mulighed for at sløjfe over flere rækker i en tabel. SQL-standarden giver IF, LOOP, REPEAT, CASE og mange andre at arbejde med. Lagrede procedurer kan acceptere variabler, returnere resultater eller ændre variable og returnere dem, afhængigt af hvor variablen er deklareret.
Implementeringen af lagrede procedurer varierer fra et DBMS til et andet. De fleste større databaseleverandører understøtter dem i en eller anden form. Afhængigt af DBMS kan lagrede procedurer implementeres på forskellige sprog programmering som SQL, Java, C eller C++. Lagrede procedurer, der ikke er skrevet i SQL, udfører muligvis SQL-forespørgsler alene.
Bag
Deling af logik med andre applikationer. Lagrede procedurer indkapsler funktionalitet; dette giver forbindelse til dataadgang og administration på tværs af forskellige applikationer.
Isolering af brugere fra databasetabeller. Dette giver dig mulighed for at give adgang til lagrede procedurer, men ikke til selve tabeldataene.
Giver en beskyttelsesmekanisme. Som i det foregående punkt, hvis du kun kan få adgang til data gennem lagrede procedurer, kan ingen andre slette dine data gennem SQL DELETE-kommandoen.
Forbedret udførelse som følge af reduceret netværkstrafik. Ved at bruge lagrede procedurer kan flere forespørgsler kombineres.
Mod
Øget belastning på databaseserveren på grund af, at det meste af arbejdet udføres på serversiden, og mindre på klientsiden.
Du bliver nødt til at lære meget. Du skal lære MySQL-udtrykssyntaks for at skrive dine lagrede procedurer.
Du dublerer din ansøgningslogik to steder: serverkode og kode til lagrede procedurer, hvorved processen med datamanipulation kompliceres.
Migrering fra et DBMS til et andet (DB2, SQL Server osv.) kan føre til problemer.
Formål og fordele ved lagrede procedurer
Lagrede procedurer forbedrer ydeevnen, forbedrer programmeringsmulighederne og understøtter datasikkerhedsfunktioner.
I stedet for at gemme en ofte brugt forespørgsel, kan klienter referere til den tilsvarende lagrede procedure. Når en lagret procedure kaldes, behandles dens indhold straks af serveren.
Udover faktisk at udføre forespørgslen, giver lagrede procedurer dig også mulighed for at udføre beregninger og manipulere data - ændre, slette, udføre DDL-sætninger (ikke i alle DBMS'er!) og kalde andre lagrede procedurer og udføre kompleks transaktionslogik. En enkelt sætning giver dig mulighed for at kalde et komplekst script indeholdt i en lagret procedure, og undgår at sende hundredvis af kommandoer på tværs af netværket og især behovet for at overføre store mængder data fra klienten til serveren.
I de fleste DBMS'er kompileres den første gang, en lagret procedure køres (parses og en dataadgangsplan genereres). I fremtiden er behandlingen hurtigere. I Oracle DBMS den lagrede procedurekode, der er lagret i dataordbogen, fortolkes. Startende med Oracle 10g understøttes den såkaldte native kompilering af lagret procedurekode i C og derefter ind i målmaskinens maskinkode, hvorefter, når en lagret procedure kaldes, udføres dens kompilerede objektkode direkte.
Programmeringsmuligheder
Den oprettede lagrede procedure kan kaldes til enhver tid, hvilket giver modularitet og tilskynder til genbrug af kode. Sidstnævnte gør databasen nemmere at vedligeholde, da den bliver isoleret fra ændrede forretningsregler. Du kan til enhver tid ændre en lagret procedure i overensstemmelse med de nye regler. Herefter vil alle applikationer, der bruger det, automatisk komme i overensstemmelse med de nye forretningsregler uden direkte ændringer.
Sikkerhed
Brugen af lagrede procedurer giver dig mulighed for at begrænse eller helt eliminere direkte brugeradgang til databasetabeller, så brugerne kun har tilladelser til at udføre lagrede procedurer, der giver indirekte og strengt reguleret adgang til data. Derudover understøtter nogle DBMS'er tekstkryptering (ombrydning) af en lagret procedure.
Disse sikkerhedsfunktioner gør det muligt at isolere databasestrukturen fra brugeren, hvilket sikrer databasens integritet og pålidelighed.
Sandsynligheden for handlinger såsom SQL-injektion er reduceret, fordi velskrevne lagrede procedurer desuden kontrollerer inputparametre, før forespørgslen sendes til DBMS.
Implementering af lagrede procedurer
Lagrede procedurer oprettes typisk vha SQL sprog eller dens specifikke implementering i det valgte DBMS. Til disse formål er der for eksempel i Microsoft SQL Server DBMS Transact-SQL sproget, i Oracle - PL/SQL, i InterBase og Firebird - PSQL, i PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, i IBM DB2 - SQL/PL (engelsk), i Informix - SPL. MySQL følger SQL:2003-standarden ganske tæt, dens sprog ligner SQL/PL.
Nogle DBMS'er tillader brugen af lagrede procedurer skrevet i et hvilket som helst programmeringssprog, der er i stand til at skabe uafhængige eksekverbare filer, for eksempel i C++ eller Delphi. I Microsoft SQL Server-terminologi kaldes sådanne procedurer udvidede lagrede procedurer og er simpelthen funktioner indeholdt i en Win32 DLL. Og for eksempel i Interbase og Firebird har funktioner kaldet fra DLL/SO et andet navn - UDF (User Defined Function). MS SQL 2005 introducerede muligheden for at skrive lagrede procedurer på ethvert .NET-sprog, og udvidede lagrede procedurer er planlagt til at blive opgivet i fremtiden. Oracle DBMS tillader til gengæld at skrive lagrede procedurer i Java. I IBM DB2 er skrivning af lagrede procedurer og funktioner i konventionelle programmeringssprog en traditionel måde, understøttet helt fra begyndelsen, og SQL-procedureudvidelsen blev kun tilføjet til denne DBMS i forholdsvis sene versioner, efter dens medtagelse i ANSI-standarden. Informix understøtter også procedurer i Java og C.
I Oracle DBMS kan lagrede procedurer kombineres til såkaldte pakker. En pakke består af to dele - en pakkespecifikation, som specificerer definitionen af en lagret procedure, og en pakketekst, som indeholder dens implementering. Så Oracle måde giver dig mulighed for at adskille grænsefladen programkode fra dens gennemførelse.
I IBM DB2 DBMS kan lagrede procedurer kombineres til moduler.
Syntaks
OPRET PROCEDURE `p2`()
SQL SIKKERHEDSDEFINER
KOMMENTAR "En procedure"
VÆLG "Hej verden!";
Den første del af koden opretter en lagret procedure. Den næste indeholder valgfri parametre. Så kommer navnet og til sidst selve proceduren.
4 karakteristika ved en lagret procedure:
Sprog: Af hensyn til portabilitet er standarden SQL.
Deterministisk: hvis proceduren altid returnerer det samme resultat og tager de samme inputparametre. Dette er til replikering og registreringsprocessen. Standardværdien er IKKE DETERMINISTISK.
SQL-sikkerhed: brugerrettigheder kontrolleres under opkaldet. INVOKER er brugeren, der kalder den lagrede procedure. DEFINER er "skaberen" af proceduren. Standardværdien er DEFINER.
Kommentar: Til dokumentationsformål er standardværdien ""
Opkald til en lagret procedure
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10, "streng parameter" , @parameter_var);
Ændring af en lagret procedure
MySQL har en ALTER PROCEDURE-sætning til at ændre procedurer, men den er kun egnet til at ændre visse karakteristika. Hvis du har brug for at ændre parametrene eller brødteksten for en procedure, bør du slette og genskabe den.
Fjernelsegemtprocedurer
DROP PROCEDURE HVIS FINDER p2;
Dette er en simpel kommando. IF EXISTS-sætningen fanger en fejl, hvis en sådan procedure ikke eksisterer.
Muligheder
OPRET PROCEDURE proc1(): tom liste parametre
CREATE PROCEDURE proc1 (IN varnavn DATA-TYPE): én inputparameter. Ordet IN er valgfrit, fordi standardparametrene er IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): én parameter returneret.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): én parameter, både input og return.
Variabelerklæringens syntaks ser sådan ud:
DECLARE varname DATA-TYPE DEFAULT standardværdi;
Begrebet lagrede procedurer er defineret. Giver eksempler på oprettelse, ændring og brug af lagrede procedurer med parametre. Definitionen af input- og outputparametre er givet. Eksempler på oprettelse og opkald af lagrede procedurer er givet.Konceptet med en lagret procedure
Lagrede procedurer er grupper af indbyrdes forbundne SQL-sætninger, hvis brug gør programmørens arbejde lettere og mere fleksibelt, da gemt procedure er ofte meget enklere end en sekvens af individuelle SQL-sætninger. Lagrede procedurer er et sæt kommandoer, der består af en eller flere SQL-sætninger eller funktioner, og som er gemt i en kompileret form i en database. Udførelse i databasen lagrede procedurer I stedet for individuelle SQL-sætninger har brugeren følgende fordele:
- de nødvendige operatører er allerede indeholdt i databasen;
- de passerede alle scenen parsing og er i eksekverbart format; Før udførelse af en lagret procedure SQL Server genererer en eksekveringsplan for den, udfører dens optimering og kompilering;
- lagrede procedurer support modulær programmering, da de giver dig mulighed for at dele store opgaver op i selvstændige, mindre og nemmere at administrere dele;
- lagrede procedurer kan forårsage andre lagrede procedurer og funktioner;
- lagrede procedurer kan kaldes fra andre typer applikationsprogrammer;
- som regel, lagrede procedurer udføre hurtigere end en sekvens af individuelle udsagn;
- lagrede procedurer nemmere at bruge: de kan bestå af snesevis eller hundredvis af kommandoer, men for at køre dem skal du blot angive navnet på den ønskede gemt procedure. Dette giver dig mulighed for at reducere størrelsen af den anmodning, der sendes fra klienten til serveren, og dermed belastningen på netværket.
Lagring af procedurer på samme sted, hvor de udføres, reducerer mængden af data, der overføres over netværket og forbedrer den overordnede systemydeevne. Ansøgning lagrede procedurer forenkler vedligeholdelsen software systemer og foretage ændringer i dem. Typisk er alle integritetsbegrænsninger i form af regler og databehandlingsalgoritmer implementeret på databaseserveren og er tilgængelige for slutapplikationen som et sæt lagrede procedurer, som repræsenterer databehandlingsgrænsefladen. For at sikre dataintegritet, såvel som af sikkerhedsmæssige årsager, modtager applikationen normalt ikke direkte adgang til dataene - alt arbejde med det udføres ved at ringe til bestemte lagrede procedurer.
Denne tilgang gør det meget enkelt at ændre databehandlingsalgoritmer, som straks bliver tilgængelige for alle netværksbrugere, og giver mulighed for at udvide systemet uden at foretage ændringer i selve applikationen: bare skift gemt procedure på databaseserveren. Udvikleren behøver ikke at omkompilere applikationen, oprette kopier af den eller instruere brugere i at arbejde med den nye version. Brugere er måske ikke engang klar over, at der er foretaget ændringer i systemet.
Lagrede procedurer eksistere uafhængigt af tabeller eller andre databaseobjekter. De kaldes af klientprogrammet, et andet gemt procedure eller trigger. Udvikleren kan administrere adgangsrettigheder til gemt procedure, tillader eller forbyder dens udførelse. Skift kode gemt procedure kun tilladt af dens ejer eller et medlem af en fast databaserolle. Hvis det er nødvendigt, kan du overføre ejerskabet af det fra én bruger til en anden.
Lagrede procedurer i MS SQL Server-miljø
Når du arbejder med SQL Server, kan brugere oprette deres egne procedurer, der implementerer bestemte handlinger. Lagrede procedurer er fuldgyldige databaseobjekter, og derfor er hver af dem gemt i en bestemt database. Direkte opkald gemt procedure er kun muligt, hvis det udføres i sammenhæng med den database, hvor proceduren er placeret.
Typer af lagrede procedurer
SQL Server har flere typer lagrede procedurer.
- System lagrede procedurer designet til at udføre forskellige administrative handlinger. Næsten alle udføres med deres hjælp. Det kan vi sige systemisk lagrede procedurer er en grænseflade, der giver arbejde med systemtabeller, som i sidste ende handler om at ændre, tilføje, slette og hente data fra systemtabeller i både bruger- og systemdatabaser. System lagrede procedurer har sp_-præfikset, er gemt i systemdatabasen og kan kaldes i sammenhæng med enhver anden database.
- Brugerdefinerede lagrede procedurer gennemføre visse handlinger. Lagrede procedurer– et fuldgyldigt databaseobjekt. Som et resultat, hver gemt procedure er placeret i en bestemt database, hvor den udføres.
- Midlertidig lagrede procedurer eksisterer kun i et stykke tid, hvorefter de automatisk ødelægges af serveren. De er opdelt i lokale og globale. Lokalt midlertidigt lagrede procedurer kan kun kaldes fra den forbindelse, hvor de blev oprettet. Når du opretter en sådan procedure, skal du give den et navn, der begynder med et enkelt #-tegn. Som alle midlertidige genstande, lagrede procedurer af denne type slettes automatisk, når brugeren afbryder forbindelsen, eller serveren genstartes eller stoppes. Global midlertidig lagrede procedurer er tilgængelige for alle forbindelser fra en server, der har samme procedure. For at definere det, skal du blot give det et navn, der starter med tegnene ## . Disse procedurer slettes, når serveren genstartes eller stoppes, eller når forbindelsen i den kontekst, de blev oprettet i, lukkes.
Opret, rediger og slet lagrede procedurer
Skabelse gemt procedure indebærer løsning af følgende problemer:
- bestemme typen af oprettet gemt procedure: midlertidig eller brugerdefineret. Derudover kan du oprette dit eget system gemt procedure, giver det et navn foran med sp_ og placerer det i systemdatabasen. Denne procedure vil være tilgængelig i forbindelse med enhver lokal serverdatabase;
- planlægning af adgangsrettigheder. Mens du skaber gemt procedure det skal tages i betragtning, at det vil have samme adgangsrettigheder til databaseobjekter som brugeren, der oprettede det;
- definition gemte procedureparametre. I lighed med de procedurer, der er inkluderet i de fleste programmeringssprog, lagrede procedurer kan have input- og outputparametre;
- kode udvikling gemt procedure. Procedurekoden kan indeholde en sekvens af alle SQL-kommandoer, inklusive kald til andre lagrede procedurer.
Oprettelse af en ny og ændring af en eksisterende gemt procedure gjort med følgende kommando:
<определение_процедуры>::= (CREATE | ALTER ) PROC procedurenavn [;nummer] [(@parameternavn datatype ) [=standard] ][,...n] AS sql_operator [...n]
Lad os se på parametrene for denne kommando.
Ved at bruge præfikserne sp_, # , ## kan den oprettede procedure defineres som et system eller midlertidigt. Som du kan se af kommandosyntaksen, er det ikke tilladt at angive navnet på ejeren, der skal eje den oprettede procedure, samt navnet på den database, hvor den skal placeres. Således for at placere den skabte gemt procedure i en specifik database skal du udstede kommandoen CREATE PROCEDURE i konteksten af den database. Når man vender sig fra kroppen gemt procedure forkortede navne kan bruges til objekter i samme database, dvs. uden at angive databasenavnet. Når du skal have adgang til objekter i andre databaser, er det obligatorisk at angive databasenavnet.
Nummeret i navnet er et identifikationsnummer gemt procedure, som entydigt identificerer det i en gruppe af procedurer. For at lette administrationen er procedurerne logisk set af samme type lagrede procedurer kan grupperes ved at tildele dem samme navne, men forskellige identifikationsnumre.
At overføre input og output data i den oprettede gemt procedure parametre kan bruges, hvis navne, ligesom navnene på lokale variabler, skal begynde med @-symbolet. En gemt procedure Du kan angive flere parametre adskilt af kommaer. Brødteksten i en procedure bør ikke bruge lokale variabler, hvis navne falder sammen med navnene på parametrene for denne procedure.
For at bestemme den datatype, som den tilsvarende gemt procedure parameter, alle SQL-datatyper er egnede, inklusive brugerdefinerede. CURSOR datatypen kan dog kun bruges som output parameter gemt procedure, dvs. angivelse af nøgleordet OUTPUT.
Tilstedeværelsen af nøgleordet OUTPUT betyder, at den tilsvarende parameter er beregnet til at returnere data fra gemt procedure. Dette betyder dog ikke, at parameteren ikke er egnet til at overføre værdier til gemt procedure. Angivelse af nøgleordet OUTPUT instruerer serveren om at afslutte gemt procedure tildele den aktuelle værdi af parameteren til den lokale variabel, der blev angivet, da proceduren blev kaldt som værdien af parameteren. Bemærk, at når du angiver nøgleordet OUTPUT, kan værdien af den tilsvarende parameter, når proceduren kaldes, kun indstilles ved hjælp af en lokal variabel. Udtryk eller konstanter, der er tilladt for regulære parametre, er ikke tilladt.
Nøgleordet VARYING bruges sammen med parameteren OUTPUT, som er af typen CURSOR. Det afgør det output parameter der vil være et resultatsæt.
DEFAULT nøgleordet repræsenterer den værdi, som den tilsvarende standard parameter. Når du kalder en procedure, behøver du således ikke udtrykkeligt at angive værdien af den tilsvarende parameter.
Da serveren cacher forespørgselsudførelsesplanen og den kompilerede kode, næste gang proceduren kaldes, vil de færdiglavede værdier blive brugt. I nogle tilfælde er det dog stadig nødvendigt at omkompilere procedurekoden. Angivelse af nøgleordet RECOMPILE instruerer systemet i at oprette en eksekveringsplan gemt procedure hver gang hun ringer.
FOR REPLIKATION-parameteren er påkrævet, når du replikerer data og aktiverer den oprettede gemt procedure som en artikel til udgivelse.
Nøgleordet ENCRYPTION instruerer serveren om at kryptere koden gemt procedure, som kan give beskyttelse mod brug af proprietære algoritmer, der implementerer arbejdet gemt procedure.
AS nøgleordet er placeret i begyndelsen af selve kroppen gemt procedure, dvs. et sæt SQL-kommandoer, ved hjælp af hvilke denne eller hin handling vil blive implementeret. I selve proceduren kan næsten alle SQL-kommandoer bruges, transaktioner kan erklæres, låse kan indstilles, og andre kan kaldes. lagrede procedurer. Afslut fra gemt procedure kan gøres ved hjælp af RETURN-kommandoen.
Fjernelse af en lagret procedure udføres af kommandoen:
SLIPPROCEDURE (procedurenavn) [,...n]
Udførelse af en lagret procedure
Til udføre en lagret procedure Den anvendte kommando er:
[[ EXEC [ UTE] procedurenavn [;nummer] [[@parameter_navn=](værdi | @variabelnavn) |][,...n]
Hvis opkaldet gemt procedure er ikke den eneste kommando i pakken, tilstedeværelsen af kommandoen EXECUTE er påkrævet. Desuden er denne kommando påkrævet for at kalde en procedure fra kroppen af en anden procedure eller trigger.
Brugen af nøgleordet OUTPUT, når en procedure kaldes, er kun tilladt for parametre, der blev erklæret hvornår oprettelse af en procedure med nøgleordet OUTPUT.
Når nøgleordet DEFAULT er angivet for en parameter, når en procedure kaldes, vil det blive brugt standard værdi. Det angivne ord DEFAULT er naturligvis kun tilladt for de parametre, som det er defineret for standard værdi.
Syntaksen for kommandoen EXECUTE viser, at parameternavne kan udelades, når en procedure kaldes. Men i dette tilfælde skal brugeren angive værdierne for parametrene i samme rækkefølge, som de blev anført, når oprettelse af en procedure. Tildel til parameter standard værdi, du kan ikke bare springe det over, når du noterer. Hvis du vil udelade de parametre, som den er defineret for standard værdi, er det nok eksplicit at angive parameternavnene, når du kalder gemt procedure. Desuden kan du på denne måde liste parametre og deres værdier i enhver rækkefølge.
Bemærk, at når du kalder en procedure, angives enten parameternavne med værdier eller kun værdier uden parameternavn. Det er ikke tilladt at kombinere dem.
Eksempel 12.1. Fremgangsmåde uden parametre. Udvikle en procedure for at få navne og omkostninger på varer købt af Ivanov.
CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer. Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Kunde .Efternavn='Ivanov' Eksempel 12.1. Procedure for at få navne og værdier af varer købt af Ivanov.
Til adgang til proceduren du kan bruge kommandoerne:
EXEC my_proc1 eller my_proc1
Proceduren returnerer et datasæt.
Eksempel 12.2. Fremgangsmåde uden parametre. Opret en procedure for at reducere prisen på førsteklasses varer med 10%.
Til adgang til proceduren du kan bruge kommandoerne:
EXEC my_proc2 eller my_proc2
Proceduren returnerer ingen data.
Eksempel 12.3. Fremgangsmåde med input parameter . Opret en procedure for at få navne og priser på varer købt af en given kunde.
CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.East Name FROM Customer INNER JOIN (Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode) ON Customer. CustomerCode =Transaktion.ClientCode WHERE Client.LastName=@k Eksempel 12.3. En procedure til at indhente navne og priser på varer købt af en given kunde.
Til adgang til proceduren du kan bruge kommandoerne:
EXEC my_proc3 "Ivanov" eller my_proc3 @k="Ivanov"
Eksempel 12.4.. Opret en procedure til at reducere prisen på et produkt af en given type i overensstemmelse med den angivne %.
Til adgang til proceduren du kan bruge kommandoerne:
EXEC my_proc4 "Vafler",0.05 eller EXEC my_proc4 @t="Vafler", @p=0.05
Eksempel 12.5. Fremgangsmåde med inputparametre og standardværdier. Opret en procedure til at reducere prisen på et produkt af en given type i overensstemmelse med den angivne %.
OPRET PROC my_proc5 @t VARCHAR(20)='Candy', @p FLOAT=0.1 SOM OPDATERING Produktsæt Pris=Pris*(1-@p) WHERE Type=@t Eksempel 12.5. Procedure med inputparametre og standardværdier. Opret en procedure til at reducere prisen på et produkt af en given type i overensstemmelse med den angivne %.
Til adgang til proceduren du kan bruge kommandoerne:
EXEC my_proc5 "Waffles",0.05 eller EXEC my_proc5 @t="Waffles", @p=0.05 eller EXEC my_proc5 @p=0.05
I dette tilfælde reduceres prisen på slik (typeværdien er ikke angivet, når proceduren kaldes og tages som standard).
I sidstnævnte tilfælde er begge parametre (både type og procent) ikke angivet, når proceduren kaldes; deres værdier tages som standard.
Eksempel 12.6. Procedure med input- og outputparametre. Opret en procedure til at bestemme de samlede omkostninger for solgte varer i en bestemt måned.
OPRET PROC my_proc6 @m INT, @s FLOAT OUTPUT SOM SELECT @s=Sum(Produkt.Pris*Transaktion.Mængde) FRA Produkt INNER JOIN Transaktion PÅ Product.ProductCode=Transaction.ProductCode GRUPPER EFTER Måned(Transaction.Dato) HAVING Month( Transaktionsdato)=@m Eksempel 12.6. Procedure med input- og outputparametre. Opret en procedure til at bestemme de samlede omkostninger for solgte varer i en bestemt måned.
Til adgang til proceduren du kan bruge kommandoerne:
DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st
Denne blok af kommandoer giver dig mulighed for at bestemme prisen på varer solgt i januar ( input parameter måned er angivet som 1).
Opret en procedure til at bestemme den samlede mængde varer købt af den virksomhed, hvor en given medarbejder arbejder.
Først vil vi udvikle en procedure til at bestemme den virksomhed, hvor medarbejderen arbejder.
Eksempel 12.7. Brug indlejrede procedurer. Opret en procedure til at bestemme den samlede mængde varer købt af den virksomhed, hvor en given medarbejder arbejder.
Så laver vi en procedure, der beregner den samlede mængde varer købt af den virksomhed, vi er interesseret i.
OPRET PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Transaction.Quantity) FRA Client INNER JOIN Transaktion PÅ Client.ClientCode= Transaction.ClientCode GROUP BY Client.Firm HAVING Client.Company=@firm Eksempel 12.7. Opret en procedure til at bestemme den samlede mængde varer købt af den virksomhed, hvor en given medarbejder arbejder.
Proceduren kaldes ved hjælp af kommandoen:
DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k
Vi overvejer en situation, hvor lagrede procedurer kan forringe forespørgselsydeevnen.
Når du kompilerer lagrede procedurer i MS SQL Server 2000, placeres de lagrede procedurer i procedurecachen, hvilket kan forbedre ydeevnen, når de udføres, ved at eliminere behovet for at parse, optimere og kompilere lagret procedurekode.
På den anden side er der faldgruber ved at gemme den kompilerede kode for en lagret procedure, som kan have den modsatte effekt.
Faktum er, at når der kompileres en lagret procedure, kompileres eksekveringsplanen for de sætninger, der udgør procedurekoden; følgelig, hvis den kompilerede lagrede procedure cachelagres, cachelagres dens eksekveringsplan, og derfor vil den lagrede procedure ikke blive gemt. optimeret til en specifik situation og forespørgselsparametre.
Lad os lave et lille eksperiment for at demonstrere dette.
TRIN 1. Oprettelse af en database.
Til eksperimentet, lad os skabe separat database data.
OPRET DATABASE test_sp_perf
TIL (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG PÅ (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
TRIN 2. Oprettelse af et bord.
OPRET TABEL sp_perf_test(kolonne1 int, kolonne2 char(5000))
TRIN 3. Udfyldning af tabellen med testrækker. Duplikerede rækker tilføjes med vilje til en tabel. 10.000 linjer med tal fra 1 til 10.000 og 10.000 linjer med numre 50.000.
DECLARE @i int
SET @i=1
Mens jeg<10000)
BEGYNDE
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)))
INDSTIL @i= @i+1
ENDE
VÆLG ANTAL(*) FRA sp_perf_test
GÅ
TRIN 4. Oprettelse af et ikke-klynget indeks. Fordi udførelsesplanen er cachelagret med proceduren, vil indekset blive brugt på samme måde på tværs af alle opkald.
OPRET IKKE-KLUNGERET INDEX CL_perf_test ON sp_perf_test(kolonne1)
GÅ
TRIN 5. Oprettelse af en lagret procedure. Proceduren udfører simpelthen en SELECT-sætning med en betingelse.
OPRET PROC proc1 (@param int)
SOM
VÆLG kolonne1, kolonne2 FRA sp_perf_test WHERE kolonne1=@param
GÅ
TRIN 6. Kører en lagret procedure. Når du kører en sårbar procedure, bruges en selektiv parameter specifikt. Som et resultat af proceduren får vi 1 linje. Udførelsesplanen angiver brugen af et ikke-klynget indeks, fordi Forespørgslen er selektiv, og dette er den bedste måde at hente rækken på. En procedure, der er optimeret til at hente en enkelt række, er gemt i den proceduremæssige cache.
EXEC proc1 1234
GÅ
TRIN 7. Kør en lagret procedure med en ikke-selektiv parameter. Værdien, der bruges som en parameter, er 50.000. Der er omkring 10.000 rækker med denne værdi i den første kolonne; derfor er brug af et ikke-klynget indeks og bogmærkeopslagsoperationen ineffektiv, men da den kompilerede kode med udførelsesplanen er gemt i den proceduremæssige cache, det er det, der vil blive brugt. Udførelsesplanen viser dette, samt det faktum, at der blev udført en bogmærkeopslagsoperation for 9999 rækker.
EXEC proc1 50000
GÅ
TRIN 8. Udførelse af et udvalg af rækker med det første felt lig med 50000. Når du kører en separat forespørgsel, optimeres forespørgslen og kompileres med en specifik værdi for den første kolonne. Som et resultat heraf bestemmer forespørgselsoptimeringsværktøjet, at feltet duplikeres mange gange og beslutter sig for at bruge tabelscanningsoperationen, som i dette tilfælde er meget mere effektiv end at bruge et ikke-klynget indeks.
VÆLG kolonne1, kolonne2 FRA sp_perf_test WHERE kolonne1=50000
GÅ
Derfor kan vi konkludere, at brug af lagrede procedurer ikke altid forbedrer forespørgselsydeevnen. Du bør være meget forsigtig med lagrede procedurer, der opererer på resultater med et variabelt antal rækker, og som bruger forskellige eksekveringsplaner.
Du kan bruge scriptet til at gentage eksperimentet på din MS SQL-server.
Lagrede procedurer
Emnet for dette kapitel er et af de mest kraftfulde værktøjer, der tilbydes udviklere af InterBase-databaseapplikationer til implementering af forretningslogik. Lagrede procedurer (engelsk, stoied procedurer) giver dig mulighed for at implementere en væsentlig del af applikationslogikken på databaseniveau og dermed øge udførelsen af hele applikationen, centralisere databehandling og reducere mængden af kode, der kræves for at fuldføre tildelte opgaver Næsten enhver ret kompleks databaseapplikation kan ikke undvære brugen af lagrede procedurer.
Ud over disse velkendte fordele ved at bruge lagrede procedurer, fælles for de fleste relationelle DBMS'er, kan InterBase lagrede procedurer fungere som næsten komplette datasæt, hvilket gør det muligt at bruge de resultater, de returnerer, i almindelige SQL-forespørgsler.
Ofte forestiller nybegyndere sig lagrede procedurer som et sæt specifikke SQL-forespørgsler, der gør noget inde i databasen, og der er en opfattelse af, at det er meget vanskeligere at arbejde med lagrede procedurer end at implementere den samme funktionalitet i en klientapplikation, i en høj- niveau sprog
Så hvad er lagrede procedurer i InterBase?
En lagret procedure (SP) er en del af databasens metadata, som er en underrutine, der er kompileret i den interne repræsentation af InterBase, skrevet på et særligt sprog, hvis compiler er indbygget i kernen af InteiBase-serveren
En lagret procedure kan kaldes fra klientapplikationer, fra triggere og fra andre lagrede procedurer. Den lagrede procedure kører inde i serverprocessen og kan manipulere data i databasen, samt returnere resultaterne af dens eksekvering til den klient, der kaldte den (dvs. trigger, HP, applikation)
Grundlaget for de kraftfulde egenskaber, der er iboende i HP, er et proceduremæssigt programmeringssprog, som omfatter både modificerede udsagn af almindelig SQL, såsom INSERT, UPDATE og SELECT, samt værktøjer til at organisere grene og loops (IF, WHILE), samt fejlhåndteringsværktøjer og ekstraordinære situationer Sproget i lagrede procedurer giver dig mulighed for at implementere komplekse algoritmer til at arbejde med data, og på grund af fokus på at arbejde med relationelle data er HP meget mere kompakt end tilsvarende procedurer på traditionelle sprog.
Det skal bemærkes, at det samme programmeringssprog bruges til triggere, med undtagelse af en række funktioner og begrænsninger. Forskellene mellem delmængden af sproget, der bruges i udløsere, og HP-sproget diskuteres detaljeret i kapitlet "Triggere" (del 1).
Eksempel på en simpel lagret procedure
Det er tid til at oprette din første lagrede procedure og bruge den som et eksempel for at lære processen med at oprette lagrede procedurer. Men først skal vi sige et par ord om, hvordan man arbejder med lagrede procedurer Faktum er, at HP skylder sit ry som et obskurt og ubelejligt værktøj til ekstremt dårlige standardværktøjer til udvikling og fejlretning af lagrede procedurer. InterBase-dokumentationen anbefaler at oprette procedurer ved hjælp af SQL-scriptfiler, der indeholder HP-tekst, som leveres som input til isql-fortolkeren, og dermed oprette og ændre HP If i dette SQL-script, på tidspunktet for kompilering af procedureteksten i BLR (ca. BLR, se kapitel "InterBase Database Structure" (Del 4)), hvis der opstår en fejl, vil isql vise en meddelelse om, hvilken linje i SQL-scriptfilen denne fejl opstod på. Ret fejlen og gør det hele igen. Der tales overhovedet ikke om debugging i ordets moderne forstand, altså om eksekveringssporing, med evnen til at se mellemværdier af variabler. Naturligvis bidrager denne tilgang ikke til væksten af attraktiviteten af lagrede procedurer i udviklerens øjne
Dog ud over den standard minimalistiske tilgang til HP-udvikling<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Syntaksen for lagrede procedurer er beskrevet som følger:
OPRET PROCEDURE navn
[ (param datatype [, param datatype ...]) ]
)]
SOM
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var datatype;
BEGYNDE
< compound_statement>
[< compound_statement> ...]
ENDE
< compound_statement> =
(
Det ser ret omfangsrigt ud og kan endda være besværligt, men faktisk er alt meget simpelt.For gradvist at mestre syntaksen, lad os se på gradvist mere komplekse eksempler.
Så her er et eksempel på en meget simpel lagret procedure, der tager to tal som input, tilføjer dem og returnerer resultatet:
CREATE PROCEDURE SP_Add(first_arg DOBBELT PRÆCISION,
second_arg DOBBELT PRÆCISION)
RETUR (Resultat DOBBELT PRÆCISION)
SOM
BEGYNDE
Resultat=første_arg+anden_arg;
SUSPENDERE;
ENDE
Som du kan se, er alt enkelt: efter CREATE PROCEDURE-kommandoen angives navnet på den nyoprettede procedure (som skal være unik i databasen) - i dette tilfælde SP_Add, så er HP-inputparametrene - first_arg og second_arg - anført i parentes, adskilt af kommaer, der angiver deres typer.
Listen over inputparametre er en valgfri del af CREATE PROCEDURE-sætningen - der er tilfælde, hvor en procedure modtager alle data for sit arbejde gennem forespørgsler til tabeller inde i procedurens brødtekst.
Lagrede procedurer bruger alle skalære datatyper InteiBase Det tillader ikke brug af arrays og brugerdefinerede typer - domæner
Dernæst kommer nøgleordet RETURNS, hvorefter de returnerede parametre er angivet i parentes, der angiver deres typer - i dette tilfælde kun én - Resultat.
Hvis proceduren ikke skulle returnere parametre, mangler ordet RETURNS og listen over returnerede parametre.
Efter RETURNSQ angives nøgleordet AS. Før søgeordet AS går titel, og efter det - techo procedurer.
Brødteksten i en lagret procedure er en liste over beskrivelser af dens interne (lokale) variabler (hvis de findes, vil vi se på dem mere detaljeret nedenfor), adskilt af et semikolon (;) og en blok af udsagn indesluttet i operatørbeslag BEGYND SLUT. I dette tilfælde er hoveddelen af HP'en meget enkel - vi beder om at tilføje to input-argumenter og tildele deres resultat til output-en, og kalder derefter SUSPEND-kommandoen. Lidt senere vil vi forklare essensen af handlingen af denne kommando, men for nu vil vi kun bemærke, at det er nødvendigt at overføre returparametrene til hvorfra den lagrede procedure blev kaldt.
Afgrænsninger i lagrede procedurer
Bemærk, at en sætning i en procedure ender med et semikolon (;). Semikolonet er som bekendt en standard kommandoseparator i SQL - det er et signal til SQL-fortolkeren om, at kommandoteksten er indtastet fuldt ud og skal begynde at behandle den. Ville det ikke vise sig, at hvis SQL-fortolkeren finder et semikolon i midten af HP'en, vil den antage, at kommandoen er indtastet fuldt ud og vil forsøge at udføre en del af den lagrede procedure? Denne antagelse er ikke uden berettigelse. Faktisk, hvis du opretter en fil, hvor du kan skrive ovenstående eksempel, tilføje en forbindelseskommando fra databasen og forsøge at udføre dette SQL-script ved hjælp af isql-fortolkeren, vil der blive returneret en fejl på grund af den uventede, efter fortolkerens mening, slutning af kommandoen til oprettelse af lagret procedure. Hvis du opretter lagrede procedurer ved hjælp af SQL-scriptfiler uden at bruge specialiserede InterBase-udviklerværktøjer, skal du før hver HP-oprettelseskommando (det samme gælder for triggere) ændre scriptkommando-separatoren til et andet tegn end semikolon og efter teksten HP for at gendanne den tilbage. Isql-kommandoen, der ændrer SQL-sætningsseparatoren, ser sådan ud:
INDSTIL LID
Til typisk tilfælde oprettelse af en lagret procedure ser det sådan ud:
SÆT TERM^;
CREATE PROCEDURE some_procedure
... . .
ENDE
^
SÆT TERM ;^
Opkald til en lagret procedure
Men lad os vende tilbage til vores lagrede procedure. Nu hvor det er blevet oprettet, skal du kalde det på en eller anden måde, sende parametre til det og få resultaterne returneret. Dette er meget nemt at gøre - bare skriv en SQL-forespørgsel som denne:
VÆLG *
FRA Sp_add(181.35, 23.09)
Denne forespørgsel returnerer os én linje, der kun indeholder ét resultatfelt, som vil indeholde summen af tallene 181.35 og 23.09, dvs. 204.44.
Således kan vores procedure bruges i alm SQL-forespørgsler udfører som i klientprogrammer, såvel som i andre HP eller triggere. Denne brug af vores procedure er muliggjort ved at bruge kommandoen SUSPEND i slutningen af den lagrede procedure.
Faktum er, at der i InterBase (og i alle dets kloner) er to typer lagrede procedurer: valgbare procedurer og eksekverbare procedurer. Forskellen i driften af disse to typer HP er, at samplingsprocedurer normalt returnerer mange sæt outputparametre, grupperet linje for linje, som ligner et sæt data, og eksekverbare procedurer kunne enten slet ikke returnere parametre eller kun returnere et sæt outputparametre , angivet i Returns, hvor en linje med parametre. Select-procedurer kaldes i SELECT-forespørgsler, og eksekverbare procedurer kaldes ved hjælp af kommandoen EXECUTE PROCEDURE.
Begge typer af lagrede procedurer har den samme oprettelsessyntaks og er formelt set ikke forskellige, så enhver eksekverbar procedure kan kaldes i en SELECT-forespørgsel, og enhver valgprocedure kan kaldes ved hjælp af EXECUTE PROCEDURE. Spørgsmålet er, hvordan HP vil opføre sig hvornår forskellige typer opkald. Med andre ord ligger forskellen i at designe proceduren for en bestemt type opkald. Det vil sige, at valgproceduren er specifikt oprettet til at blive kaldt fra en SELECT-forespørgsel, og den eksekverbare procedure er specifikt oprettet til at blive kaldt ved hjælp af EXECUTE PROCEDURE. Lad os se på, hvad forskellene er i designet af disse to typer HP.
For at forstå, hvordan prøveudtagningsproceduren fungerer, bliver du nødt til at dykke lidt dybere ned i teorien. Lad os forestille os en almindelig SQL-forespørgsel som SELECT ID, NAME FROM Table_example. Som et resultat af dens udførelse får vi en tabel bestående af to kolonner (ID og NAME) og et vist antal rækker (svarende til antallet af rækker i tabellen Table_example). Tabellen, der returneres som et resultat af denne forespørgsel, kaldes også et SQL-datasæt. Lad os tænke på, hvordan datasættet dannes under udførelsen af denne forespørgsel. Serveren, der har modtaget forespørgslen, bestemmer, hvilke tabeller den refererer til, og finder derefter ud, hvilket undersæt af poster fra disse tabeller, der skal inkluderes i forespørgselsresultatet. Derefter læser serveren hver post, der opfylder forespørgselsresultaterne, vælger de påkrævede felter fra den (i vores tilfælde ID og NAVN) og sender dem til klienten. Derefter gentages processen igen - og så videre for hver valgt post.
Al denne digression er nødvendig, så den kære læser forstår, at alle SQL-datasæt genereres række for række, også i lagrede procedurer! Og den største forskel mellem henteprocedurer og eksekverbare procedurer er, at førstnævnte er designet til at returnere mange rækker, mens sidstnævnte er designet til kun at returnere én. Det er derfor, de bruges forskelligt: Vælg-proceduren kaldes ved at bruge SELECT-kommandoen, som "kræver" at proceduren opgiver alle de poster, den kan returnere. Den eksekverbare procedure kaldes ved at bruge EXECUTE PROCEDURE, som "tager ud" kun én linje fra HP'en og ignorerer resten (selvom de eksisterer!).
Lad os se på et eksempel på en prøveudtagningsprocedure for at gøre det klarere. For > tilgivelse, lad os oprette en lagret procedure, der fungerer nøjagtigt som et SELECT ID, NAME FROM Table_Example-forespørgsel, det vil sige, at den blot vælger ID- og NAME-felterne fra hele tabellen. Her er dette eksempel:
OPRET PROCEDURE Simple_Select_SP
VENDER TILBAGE (
procID INTEGER,
procNAME VARCHAR(80))
SOM
BEGYNDE
TIL
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
GØR
BEGYNDE
SUSPENDERE;
ENDE
ENDE
Lad os se på trinene i denne procedure, kaldet Simple_Select_SP. Som du kan se, har den ingen inputparametre og to outputparametre - ID og NAME. Det mest interessante ligger selvfølgelig i procedurens krop. FOR SELECT-konstruktionen bruges her:
TIL
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
GØR
BEGYNDE
/*gør noget med variablerne procID og procName*/
ENDE
Dette stykke kode betyder følgende: For hver række valgt fra tabellen Table_example skal du sætte de valgte værdier i procID og procName variablerne, og derefter gøre noget med disse variable.
Du kan lave et overrasket ansigt og spørge: "Variabler? Hvilke andre variabler? 9" Det er lidt overraskende i dette kapitel, at vi kan bruge variabler i lagrede procedurer. På HP-sproget kan du erklære både dine egne lokale variabler i en procedure og bruge input- og outputparametre som variable.
For at erklære en lokal variabel i en lagret procedure, skal du placere dens beskrivelse efter AS nøgleordet og før det første ord BEGIN. Beskrivelsen af en lokal variabel ser således ud:
ERKLÆR VARIABEL
For f.eks. at erklære en heltals lokal variabel Mylnt, skal du indsætte følgende erklæring mellem AS og BEGIN
ERKLÆR VARIABEL Mylnt HELTAL;
Variablerne i vores eksempel begynder med et kolon. Dette gøres, fordi de tilgås inden for FOR SELECT SQL-kommandoen, så for at skelne mellem felter i tabeller, der bruges i SELECT, og variabler, skal sidstnævnte have et kolon foran. Variabler kan jo have nøjagtig det samme navn som felter i tabeller!
Men kolon før et variabelnavn bør kun bruges i SQL-forespørgsler. Uden for tekster henvises til en variabel uden kolon, for eksempel:
procName="Noget navn";
Men lad os vende tilbage til hoveddelen af vores procedure. FOR SELECT-sætningen returnerer data ikke i form af en tabel - et sæt data, men en række ad gangen. Hvert returneret felt skal placeres i sin egen variabel: ID => procID, NAME => procName. I DO-delen sendes disse variabler til den klient, der kaldte proceduren ved hjælp af kommandoen SUSPEND
Således går FOR SELECT...DO-kommandoen gennem de poster, der er valgt i SELECT-delen af kommandoen. I hoveddelen af løkken, der dannes af DO-delen, overføres den næste genererede post til klienten ved hjælp af kommandoen SUSPEND.
Så udvælgelsesproceduren er designet til at returnere en eller flere rækker, for hvilke der er organiseret en løkke inde i HP-kroppen, der udfylder de resulterende variable parametre. Og i slutningen af brødteksten af denne løkke er der altid en SUSPEND-kommando, som returnerer den næste række data til klienten.
Sløjfer og grenudsagn
Ud over kommandoen FOR SELECT...DO, som organiserer en loop gennem registreringerne af en markering, er der en anden type loop - WHILE...DO, som giver dig mulighed for at organisere en loop baseret på kontrol af eventuelle betingelser. Her er et eksempel på HP, der bruger WHILE..DO-løkken. Denne procedure returnerer kvadraterne af heltal fra 0 til 99:
OPRET PROCEDJRE QUAD
RETUR (KVADRAT HELTAL)
SOM
ERKLÆR VARIABEL I HELTAL;
BEGYNDE
I = 1;
Mens jeg<100) DO
BEGYNDE
KVADRAT= I*I;
I=I+1;
SUSPENDERE;
ENDE
ENDE
Som et resultat af at udføre SELECT FROM QUAD-forespørgslen, vil vi modtage en tabel, der indeholder en QUADRAT-kolonne, som vil indeholde kvadraterne af heltal fra 1 til 99
Ud over at gentage resultaterne af en SQL-eksempel og en klassisk loop, bruger det lagrede proceduresprog IF...THEN..ELSE-operatoren, som giver dig mulighed for at organisere forgrening afhængigt af udførelsen af eventuelle betingelser. Dens syntaks er ens. til de fleste brancheoperatører i programmeringssprog på højt niveau, som Pascal og C.
Lad os se på et mere komplekst eksempel på en lagret procedure, der gør følgende.
- Beregner gennemsnitsprisen i tabellen Tabel_eksempel (se kapitlet "Tabeller primære nøgler og generatorer")
- Dernæst foretager den følgende kontrol for hver post i tabellen: hvis den eksisterende pris (PRIS) er større end gennemsnitsprisen, sætter den en pris lig med gennemsnitsprisen plus en specificeret fast procentdel
- Hvis den eksisterende pris er mindre end eller lig med gennemsnitsprisen, sættes en pris svarende til den tidligere pris plus halvdelen af forskellen mellem den tidligere og gennemsnitsprisen.
- Returnerer alle ændrede rækker i tabellen.
Lad os først definere navnet på HP'en samt input- og outputparametre Alt dette er skrevet i overskriften på den lagrede procedure.
OPRET PROCEDURE Forøg priserne (
Procent2lnøgning DOBBELT PRÆCISION)
RETURER (ID HELTAL, NAVN VARCHAR(SO), ny_pris DOBBELT
PRÆCISION SOM
Proceduren vil blive kaldt IncreasePrices, den har én inputparameter Peiceni21nciease af typen DOBBELT PRECISION og 3 outputparametre - ID, NAME og new_pnce. Bemærk, at de to første output-parametre har samme navne som felterne i tabellen Tabel_eksempel, som vi skal arbejde med. Dette er tilladt i henhold til reglerne for det lagrede proceduresprog.
Nu skal vi deklarere en lokal variabel, der skal bruges til at gemme gennemsnitsværdien. Deklarationen vil se således ud:
ERKLÆR VARIABEL avg_price DOBBELT PRÆCISION;
Lad os nu gå videre til hoveddelen af den lagrede procedure Åbn hoveddelen af HP
søgeord BEGIN.
Først skal vi udføre det første trin i vores algoritme - beregne gennemsnitsprisen. For at gøre dette vil vi bruge følgende type forespørgsel:
VÆLG AVG(Pris_l)
FRA Tabel_Eksempel
INTO:avg_price,-
Denne forespørgsel bruger AVG-aggregatfunktionen, som returnerer gennemsnittet af PRICE_1-feltet blandt de valgte forespørgselsrækker - i vores tilfælde gennemsnittet af PRICE_1 på tværs af hele Table_example-tabellen. Den værdi, der returneres af anmodningen, placeres i variablen avg_price. Bemærk, at variablen avg_pnce er indledt af et kolon for at skelne den fra de felter, der bruges i anmodningen.
Feature af denne anmodning er, at den altid returnerer præcis én enkelt post. Sådanne forespørgsler kaldes singleton-forespørgsler, og kun sådanne valg kan bruges i lagrede procedurer. Hvis en forespørgsel returnerer mere end én række, skal den formateres som en FOR SELECT...DO-konstruktion, som organiserer en løkke for at behandle hver returneret række
Så vi fik gennemsnitsprisen. Nu skal du gennemgå hele tabellen, sammenligne prisværdien i hver post med gennemsnitsprisen og tage passende handlinger
Fra begyndelsen organiserer vi søgningen efter hver post fra tabellen Table_example
TIL
VÆLG ID, NAVN, PRICE_1
FRA Tabel_Eksempel
INTO:ID, :NAVN, :nypris
GØR
BEGYNDE
/*_her beskriver vi hver post*/
ENDE
Når denne konstruktion udføres, vil data blive ekstraheret fra tabellen Table_example række for række, og feltværdierne i hver række vil blive tildelt variablerne ID, NAME og new_pnce. Du husker selvfølgelig, at disse variable er erklæret som outputparametre, men der er ingen grund til at bekymre sig om, at de valgte data vil blive returneret som resultater: det faktum, at outputparametrene er tildelt noget, betyder ikke, at klienten kalder HP vil straks modtage disse værdier! Parametre sendes kun når kommandoen SUSPEND udføres, og før det kan vi bruge outputparametrene som almindelige variable - i vores eksempel gør vi netop det med new_price parameteren.
Så inde i kroppen af BEGIN... END-løkken kan vi behandle værdierne for hver række. Som du husker, er vi nødt til at finde ud af, hvordan den eksisterende pris sammenlignes med gennemsnittet og træffe passende foranstaltninger. Vi implementerer denne sammenligningsprocedure ved hjælp af IF-sætningen:
HVIS (ny_pris > gennemsnitspris) SÅ /*hvis den eksisterende pris er større end gennemsnitsprisen*/
BEGYNDE
/*installer derefter ny pris, lig med gennemsnitsprisen plus en fast procentdel */
ny_pris = (gennemsnitspris + gennemsnitspris*(Procent2stigning/100));
OPDATERING Tabel_eksempel
SÆT PRIS_1 = :ny_pris
WHERE ID = :ID;
ENDE
ANDET
BEGYNDE
/* Hvis den eksisterende pris er mindre end eller lig med gennemsnitsprisen, så sæt en pris svarende til den tidligere pris plus halvdelen af forskellen mellem den tidligere og gennemsnitsprisen */
new_price = (new_pnce + ((avg_pnce new_price)/2));
OPDATERING Tabel_eksempel
SÆT PRIS_1 = :ny_pris
WHERE ID = .ID;
ENDE
Som du kan se, er resultatet en ret stor IF-konstruktion, som ville være svær at forstå, hvis ikke for kommentarerne indesluttet i /**/-symbolerne.
For at ændre prisen i henhold til den beregnede forskel, vil vi bruge UPDATE-erklæringen, som giver os mulighed for at ændre eksisterende optegnelser- en eller flere. For entydigt at angive, i hvilken post prisen skal ændres, bruger vi primærnøglefeltet i WHERE-tilstanden, og sammenligner det med værdien af den variabel, der gemmer ID-værdien for den aktuelle post: ID=:ID. Bemærk, at ID-variablen indledes med et kolon.
Efter udførelse af IF...THEN...ELSE-konstruktionen, indeholder variablerne ID, NAME og new_price data, som vi skal returnere til den klient, der kaldte proceduren. For at gøre dette skal du efter IF indsætte kommandoen SUSPEND, som vil sende dataene til det sted, hvor HP blev ringet op fra. Under overførslen vil proceduren blive suspenderet, og når der kræves en ny post fra HP, fortsættes igen - og dette vil fortsætte indtil FOR SELECT...DO ikke gentager alle posterne i sin forespørgsel.
Det skal bemærkes, at ud over kommandoen SUSPEND, som kun suspenderer den lagrede procedure, er der en EXIT-kommando, der afslutter den lagrede procedure efter at have passeret strengen. EXIT-kommandoen bruges dog ret sjældent, da den primært er nødvendig for at afbryde sløjfen, når en betingelse er nået
Desuden i sagen, hvor proceduren blev indkaldt SELECT erklæring og afsluttet ved EXIT, vil den sidst hentede række ikke blive returneret. Det vil sige, hvis du skal afbryde proceduren og stadig >få denne streng, skal du bruge sekvensen
SUSPENDERE;
AFSLUT;
Hovedformålet med EXIT er at modtage singleton datasæt, returnerede parametre ved at kalde EXECUTE PROCEDURE. I dette tilfælde er værdierne af outputparametrene indstillet, men SQL-datasættet genereres ikke fra dem, og udførelsen af proceduren slutter.
Lad os skrive hele teksten i vores lagrede procedure ned, så vi kan fange dens logik med et blik:
OPRET PROCEDURE Forøg priserne (
Procent2Forøg DOBBELT PRÆCISION)
RETURER (ID HELTAL, NAVN VARCHAR(80),
new_price DOBBELT PRÆCISION) SOM
ERKLÆR VARIABEL avg_price DOBBELT PRÆCISION;
BEGYNDE
VÆLG AVG(Pris_l)
FRA Tabel_Eksempel
INTO:avg_pris;
TIL
VÆLG ID, NAVN, PRICE_1
FRA Tabel_Eksempel
INTO:ID, :NAVN, :nypris
GØR
BEGYNDE
/*behandle hver post her*/
HVIS (new_pnce > avg_price) THEN /*hvis den eksisterende pris er større end gennemsnitsprisen*/
BEGYNDE
/*sæt en ny pris svarende til gennemsnitsprisen plus en fast procentdel */
ny_pris = (gennemsnitspris + gennemsnitspris*(Procent2lstigning/100));
OPDATERING Tabel_eksempel
SÆT PRIS_1 = :ny_pris
WHERE ID = :ID;
ENDE
ANDET
BEGYNDE
/* Hvis den eksisterende pris er mindre end eller lig med gennemsnitsprisen, så sætter en pris lig med den tidligere pris plus halvdelen af forskellen mellem den forrige og gennemsnitsprisen */
ny_pris = (ny_pris + ((gennemsnitspris - ny_pris)/2));
OPDATERING Tabel_eksempel
SÆT PRIS_1 = :ny_pris
WHERE ID = :ID;
ENDE
SUSPENDERE;
ENDE
ENDE
Dette eksempel på lagrede procedurer illustrerer brugen af grundlæggende sprogkonstruktioner og triggere for lagrede procedurer. Dernæst vil vi se på måder at bruge lagrede procedurer til at løse nogle almindelige problemer.
Rekursive lagrede procedurer
InterBase-lagrede procedurer kan være rekursive. Det betyder, at en lagret procedure kan kalde sig selv. Op til 1000 niveauer af indlejring af lagrede procedurer er tilladt, men vi skal huske, at gratis ressourcer på serveren kan løbe tør, før den maksimale indlejring af HP er nået.
En almindelig anvendelse af lagrede procedurer er at behandle træstrukturer gemt i en database. Træer bruges ofte i produktsammensætning, lager, personale og andre almindelige anvendelser.
Lad os se på et eksempel på en lagret procedure, der udvælger alle produkter af en bestemt type, startende fra et bestemt indlejringsniveau.
Lad os have følgende problemformulering: vi har et vareregister med hierarkisk struktur denne type:
Gods
- Hårde hvidevarer
- Køleskabe
- Tre-kammeret
- Dobbeltkammer
- Enkeltkammer
- Vaskemaskine
- Lodret
- Frontal
- Klassisk
- Smal
- Computerteknologi
....
Denne struktur af produktkategorikataloget kan have grene af varierende dybde. og stiger også over tid. Vores opgave er at sikre udvælgelsen af alle endelige elementer fra mappen med "udvidelse af det fulde navn", startende fra enhver node. For eksempel, hvis vi vælger noden "Vaskemaskiner", skal vi have følgende kategorier:
Vaskemaskiner - Lodret
Vaskemaskiner - Front Classic
Vaskemaskiner - Front Smal
Lad os definere tabelstrukturen til lagring af produktkatalogoplysninger. Vi bruger et forenklet skema til at organisere træet i én tabel:
OPRET TABEL GoodsTree
(ID_GOOD HELTAL IKKE NULL,
ID_PARENT_GOOD HELTAL,
GOOD_NAME VARCHAR(80),
begrænsning pkGooci primær nøgle (ID_GOOD));
Vi opretter én tabel GoodsTree, hvori der kun er 3 felter: ID_GOOD - den smarte identifikator for kategorien, ID_PARENT_GOOD - identifikatoren for moderselskabet for denne kategori og GOOD_NAME - navnet på kategorien. For at sikre integriteten af dataene i denne tabel vil vi pålægge denne tabel en fremmednøglebegrænsning:
ÆNDRING TABEL GoodsTree
TILFØJ BEGRÆNSNING FK_goodstree
UDENLANDSKE NØGLE (ID_PARENT_GOOD)
REFERENCER GOODSTPEE (ID__GOOD)
Tabellen refererer til sig selv, og denne fremmednøgle holder styr på det. således at tabellen ikke indeholder henvisninger til ikke-eksisterende forældre, og også forhindrer forsøg på at slette produktkategorier, der har børn.
Lad os indtaste følgende data i vores tabel:
| ID_GODT 1
|
ID_PARENT_GODT 0
|
GODT NAVN GODS |
Nu hvor vi har et sted at gemme dataene, kan vi begynde at oprette en lagret procedure, der vil udskrive alle de "endelige" produktkategorier i en "udvidet" form - for eksempel for kategorien "Tre-kammer", den fulde kategori navnet ville være "Husholdningsapparater Køleskabe" Tre-kammers".
Lagrede procedurer, der behandler træstrukturer, har deres egen terminologi. Hvert element i træet kaldes en node; og forholdet mellem noder, der refererer til hinanden, kaldes et forældre-barn-forhold. Noder, der er helt for enden af træet og ikke har børn, kaldes "blade".
For denne lagrede procedure vil inputparameteren være kategoriidentifikatoren, hvorfra vi skal starte drilldown. Den lagrede procedure vil se sådan ud:
OPRET PROCEDURE GETFULLNAME (ID_GOOD2SHOW HELTAL)
RETURER (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD HELTAL)
SOM
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGYNDE
/*0organiser den ydre FOR SELECT-løkke efter de umiddelbare efterkommere af produktet med ID_GOOD=ID_GOOD2SHOW */
FOR VÆLG gtl.id_good, gtl.good_name
FRA GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
GØR
BEGYNDE
/"Tjek ved hjælp af EXISTS-funktionen, som returnerer TRUE, hvis forespørgslen i parentes returnerer mindst én række. Hvis den fundne node med ID_PARENT_GOOD = ID_CHILD_GOOD ikke har nogen børn, så er den et "blad" af træet og er inkluderet i resultaterne */
HVIS (FINDES IKKE(
VÆLG * FRA GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
DEREFTER
BEGYNDE
/* Send træets "blad" til resultaterne */
SUSPENDERE;
ENDE
ANDET
/* For noder, der har børn*/
BEGYNDE
/*gem navnet på den overordnede node i en midlertidig variabel */
CURR_CHILD_NAME=fuld_varenavn;
/* kør denne procedure rekursivt */
TIL
VÆLG ID_CHILD_GOOD, fuld_varenavn
FRA GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
BEGYND
/*tilføj navnet på den overordnede node til det fundne underordnede navn ved hjælp af strengsammenkædningsoperationen || */
full_goods_name=CURR_CHILD_NAME| " " | fulde_varenavn,-
SUSPENDERE; /* returner det fulde navn på produktet*/
ENDE
ENDE
ENDE
ENDE
Hvis vi opfylder denne procedure med inputparameter ID_GOOD2SHOW= 1 får vi følgende:
Som du kan se, gennemgik vi hele kategoritræet ved hjælp af en rekursiv lagret procedure og viste det fulde navn på "blade"-kategorierne, der er placeret i spidsen af grenene.
Konklusion
Dette afslutter vores overvejelse af hovedfunktionerne i det lagrede proceduresprog. Det er klart, at det er umuligt fuldt ud at mestre udviklingen af lagrede procedurer i blot ét kapitel, men her har vi forsøgt at introducere og forklare de grundlæggende begreber forbundet med lagrede procedurer. De beskrevne designs og teknikker til design af HP kan anvendes i de fleste databaseapplikationer
Nogle af de vigtige spørgsmål relateret til udviklingen af lagrede procedurer vil blive dækket i næste kapitel - "Avancerede funktioner i InterBase-sproget for lagrede procedurer", som er dedikeret til håndtering af undtagelser, løsning af fejlsituationer i lagrede procedurer og arbejde med arrays.