Wat is een “opgeslagen procedure”en hoe werken ze?
Wat is de samenstelling van een opgeslagen procedure (dingen die elk moeteneen opgeslagen procedure zijn)?
Antwoord 1, autoriteit 100%
Opgeslagen procedures zijn een reeks SQL-instructies die op een aantal manieren kunnen worden uitgevoerd. De meeste grote DBM’s ondersteunen opgeslagen procedures; echter niet allemaal. U moet de details van uw specifieke DBMS-helpdocumentatie verifiëren. Omdat ik het meest bekend ben met SQL Server, zal ik dat als mijn voorbeelden gebruiken.
Om een opgeslagen procedure te maken is de syntaxis vrij eenvoudig:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
Dus bijvoorbeeld:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Een voordeel van opgeslagen procedures is dat u de logica voor gegevenstoegang op één plek kunt centraliseren, die vervolgens gemakkelijk door DBA’s kan worden geoptimaliseerd. Opgeslagen procedures hebben ook een beveiligingsvoordeel doordat u uitvoeringsrechten kunt verlenen aan een opgeslagen procedure, maar de gebruiker hoeft geen lees-/schrijfrechten te hebben voor de onderliggende tabellen. Dit is een goede eerste stap tegen SQL-injectie.
Opgeslagen procedures hebben nadelen, in feite het onderhoud dat hoort bij uw basis CRUDoperatie. Laten we zeggen dat u voor elke tabel een Insert, Update, Delete en ten minste één selectie hebt op basis van de primaire sleutel, dat betekent dat elke tabel 4 procedures zal hebben. Neem nu een behoorlijke database van 400 tabellen en je hebt 1600 procedures! En dat is ervan uitgaande dat je geen duplicaten hebt, wat waarschijnlijk wel het geval zal zijn.
Hier gebruik je een ORMof een andere methode om automatisch uw basis CRUD-bewerkingen te genereren, heeft veel verdienste.
Antwoord 2, autoriteit 61%
Een opgeslagen procedure is een set vooraf gecompileerde SQL-instructies die worden gebruikt om een speciale taak uit te voeren.
Voorbeeld: als ik een Employee-tabel heb
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Eerst haal ik de tabel Employeeop:
Create Procedure Employee details
As
Begin
Select * from Employee
End
De procedure uitvoeren op SQL Server:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Ten tweede plaats ik de waarde in de werknemerstabel
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Om de geparametriseerde procedure op SQL Server uit te voeren:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Voorbeeld: @Name Varchar(30)
In de tabel Employeemoet de grootte van de kolom Namevarchar(30)zijn.
Antwoord 3, autoriteit 32%
Een opgeslagen procedure is een groep SQL-instructies die is gemaakt en opgeslagen in de database. Een opgeslagen procedure accepteert invoerparameters zodat een enkele procedure via het netwerk kan worden gebruikt door meerdere clients die verschillende invoergegevens gebruiken. Een opgeslagen procedure zal het netwerkverkeer verminderen en de prestaties verhogen. Als we een opgeslagen procedure wijzigen, krijgen alle klanten de bijgewerkte opgeslagen procedure.
Voorbeeld van het maken van een opgeslagen procedure
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
Voordelen van het gebruik van opgeslagen procedures
-
Een opgeslagen procedure maakt modulair programmeren mogelijk.
U kunt de procedure één keer maken, in de database opslaan en een willekeurig aantal keren in uw programma oproepen.
-
Een opgeslagen procedure zorgt voor snellere uitvoering.
Als de bewerking een grote hoeveelheid SQL-code vereist die herhaaldelijk wordt uitgevoerd, kunnen opgeslagen procedures sneller zijn. Ze worden geparseerd en geoptimaliseerd wanneer ze voor het eerst worden uitgevoerd, en een gecompileerde versie van de opgeslagen procedure blijft in een geheugencache voor later gebruik. Dit betekent dat de opgeslagen procedure niet bij elk gebruik opnieuw hoeft te worden geparseerd en geoptimaliseerd, wat resulteert in veel snellere uitvoeringstijden.
-
Een opgeslagen procedure kan het netwerkverkeer verminderen.
Een bewerking die honderden regels Transact-SQL-code vereist, kan worden uitgevoerd via een enkele instructie die de code in een procedure uitvoert, in plaats van door honderden regels code over het netwerk te verzenden.
-
Opgeslagen procedures bieden een betere beveiliging van uw gegevens
Gebruikers kunnen toestemming krijgen om een opgeslagen procedure uit te voeren, zelfs als ze geen toestemming hebben om de instructies van de procedure rechtstreeks uit te voeren.
In SQL Server hebben we verschillende soorten opgeslagen procedures:
- Opgeslagen systeemprocedures
- Door de gebruiker gedefinieerde opgeslagen procedures
- Uitgebreide opgeslagen procedures
-
Systeem-opgeslagen procedures worden opgeslagen in de hoofddatabase en deze beginnen met een
sp_prefix. Deze procedures kunnen worden gebruikt om een verscheidenheid aan taken uit te voeren ter ondersteuning van SQL Server-functies voor externe applicatie-aanroepen in de systeemtabellenVoorbeeld: sp_helptext [StoredProcedure_Name]
-
Door de gebruiker gedefinieerdeopgeslagen procedures worden meestal opgeslagen in een gebruikersdatabase en zijn meestal ontworpen om de taken in de gebruikersdatabase te voltooien. Tijdens het coderen van deze procedures gebruikniet het voorvoegsel
sp_, want als we eerst het voorvoegselsp_gebruiken, wordt de hoofddatabase gecontroleerd en vervolgens het gaat om een door de gebruiker gedefinieerde database. -
Uitgebreideopgeslagen procedures zijn de procedures die functies aanroepen vanuit DLL-bestanden. Tegenwoordig worden uitgebreide opgeslagen procedures niet meer gebruikt, omdat het beter zou zijn om geen uitgebreide opgeslagen procedures te gebruiken.
Antwoord 4, autoriteit 15%
Over het algemeen is een opgeslagen procedure een ‘SQL-functie’. Ze hebben:
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
Dit is een op T-SQL gericht voorbeeld. Opgeslagen procedures kunnen de meeste SQL-instructies uitvoeren, scalaire en op tabellen gebaseerde waarden retourneren en worden als veiliger beschouwd omdat ze SQL-injectie-aanvallen voorkomen.
Antwoord 5, autoriteit 6%
Bedenk een situatie als deze,
- Je hebt een database met gegevens.
- Er zijn een aantal verschillende applicaties nodig om toegang te krijgen tot die centrale database, en in de toekomst ook enkele nieuwe applicaties.
- Als je de inline databasequery’s gaat invoegen om toegang te krijgen tot de centrale database, binnen de code van elke applicatie afzonderlijk, dan moet je waarschijnlijk dezelfde query steeds opnieuw dupliceren in de code van verschillende applicaties.
- In dat soort situaties kun je opgeslagen procedures (SP’s) gebruiken. Met opgeslagen procedures schrijft u een aantal veelvoorkomende query’s (procedures) en slaat u deze op in de centrale database.
- Het dupliceren van werk zal nu nooit meer gebeuren zoals voorheen en de toegang tot gegevens en het onderhoud zal centraal worden gedaan.
OPMERKING:
- In de bovenstaande situatie kun je je afvragen: “Waarom kunnen we geen centrale gegevenstoegangsserver introduceren om met alle applicaties te communiceren? Ja. Dat is een mogelijk alternatief. Maar,
- Het belangrijkste voordeel van SP’s ten opzichte van die aanpak is dat SP’s, in tegenstelling tot uw datatoegangscode met inline-query’s, vooraf gecompileerde instructies zijn, zodat ze sneller worden uitgevoerd. En de communicatiekosten (via netwerken) zijn minimaal.
- In tegenstelling daarmee zullen SP’s wat meer belasting toevoegen aan de databaseserver. Als dat een probleem zou zijn, afhankelijk van de situatie, is een gecentraliseerde server voor gegevenstoegang met inline-query’s een betere keuze.
Antwoord 6, autoriteit 4%
Een opgeslagen procedure wordt voornamelijk gebruikt om bepaalde taken op een database uit te voeren. Bijvoorbeeld
- Ontvang databaseresultatensets van bepaalde bedrijfslogica op gegevens.
- Voer meerdere databasebewerkingen uit in één aanroep.
- Gebruikt om gegevens van de ene tabel naar de andere te migreren.
- Kan worden aangeroepen voor andere programmeertalen, zoals Java.
Antwoord 7, autoriteit 3%
Een opgeslagen procedure is niets anders dan een groep SQL-instructies die zijn gecompileerd in een enkel uitvoeringsplan.
- Maak één keer en noem het een aantal keer
- Het vermindert het netwerkverkeer
Voorbeeld: een opgeslagen procedure maken
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
Een opgeslagen procedure wijzigen of wijzigen:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
Een opgeslagen procedure verwijderen of verwijderen:
DROP PROCEDURE GetEmployee
Antwoord 8, autoriteit 2%
Een opgeslagen procedure wordt gebruikt om gegevens op te halen, gegevens te wijzigen en gegevens in de databasetabel te verwijderen. U hoeft niet elke keer een hele SQL-opdracht te schrijven als u gegevens in een SQL-database wilt invoegen, bijwerken of verwijderen.
Antwoord 9, autoriteit 2%
-
Een opgeslagen procedure is een vooraf gecompileerde set van een of meer SQL-instructies die een specifieke taak uitvoeren.
-
Een opgeslagen procedure moet stand-alone worden uitgevoerd met behulp van
EXEC -
Een opgeslagen procedure kan meerdere parameters retourneren
-
Een opgeslagen procedure kan worden gebruikt om transacties te implementeren
Antwoord 10, autoriteit 2%
“Wat is een opgeslagen procedure” is al beantwoord in andere berichten hier. Wat ik zal posten is een minder bekende manier om de opgeslagen procedure te gebruiken. Het is grouping stored proceduresof numbering stored procedures.
Syntaxisreferentie
; numbervolgens deze
Een optioneel geheel getal dat wordt gebruikt om procedures met dezelfde naam te groeperen. Deze gegroepeerde procedures kunnen samen worden verwijderd met behulp van één DROP PROCEDURE-instructie
Voorbeeld
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
Gebruik
exec FirstTest 10
exec FirstTest;2 20,30
Resultaat
Nog een poging
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
Resultaat
Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3]
Kan procedure ‘SecondTest’ niet aanmaken met groepsnummer 2 omdat er momenteel geen procedure met dezelfde naam en groepsnummer 1 bestaat in de database.
Moet CREATE PROCEDURE ‘SecondTest’;1 eerst uitvoeren.
Referenties:
- MAAK PROCEDURE met de syntaxis voor nummer
- Genummerde opgeslagen procedures in SQL Server – techneut.blogspot.com
- Opgeslagen procedures groeperen – sqlmag
VOORZICHTIG
- Nadat je de procedures hebt gegroepeerd, kun je ze niet meer afzonderlijk laten vallen.
- Deze functie kan in een toekomstige versie van Microsoft SQL Server worden verwijderd.
Antwoord 11
voor eenvoudig,
Opgeslagen procedureszijn Opgeslagen programma’s, een programma/functie opgeslagen in de database.
Elk opgeslagen programma bevat een hoofdtekst die bestaat uit een SQL-instructie. Deze verklaring kan een samengestelde verklaring zijn die is samengesteld uit verschillende verklaringen, gescheiden door puntkomma’s (;).
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
Antwoord 12
Een opgeslagen procedure is een benoemde verzameling van SQL-instructies en procedurele logica, d.w.z. gecompileerd, geverifieerd en opgeslagen in de serverdatabase. Een opgeslagen procedure wordt doorgaans behandeld als andere database-objecten en beheerd via het serverbeveiligingsmechanisme.
Antwoord 13
In een DBMS is een opgeslagen procedure een set SQL-instructies met een toegewezen naam die in gecompileerde vorm in de database wordt opgeslagen, zodat deze door een aantal programma’s kan worden gedeeld.
Het gebruik van een opgeslagen procedure kan nuttig zijn in
-
Gecontroleerde toegang tot gegevens bieden (eindgebruikers kunnen alleen gegevens invoeren of wijzigen, maar kunnen geen procedures schrijven)
-
Zorgen voor de integriteit van gegevens (gegevens worden op een consistente manier ingevoerd) en
-
Verbetert de productiviteit (de statements van een opgeslagen procedure hoeven maar één keer te worden geschreven)
Antwoord 14
Opgeslagen procedures in SQL Server kunnen invoerparameters accepteren en meerdere waarden van uitvoerparameters retourneren; in SQL Server programmeren opgeslagen procedures instructies om bewerkingen in de database uit te voeren en een statuswaarde terug te sturen naar een aanroepende procedure of batch.
De voordelen van het gebruik van opgeslagen procedures in SQL Server
Ze maken modulair programmeren mogelijk.
Ze maken een snellere uitvoering mogelijk.
Ze kunnen het netwerkverkeer verminderen.
Ze kunnen worden gebruikt als beveiligingsmechanisme.
Hier is een voorbeeld van een opgeslagen procedure die een parameter neemt, een query uitvoert en een resultaat retourneert. De opgeslagen procedure accepteert de BusinessEntityID als parameter en gebruikt deze om de primaire sleutel van de HumanResources.Employee-tabel te matchen om de gevraagde werknemer te retourneren.
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end
Ik heb dit geleerd van essential.com…het is erg handig.
Antwoord 15
Opgeslagen procedure helpt u bij het maken van code op de server. U kunt parameters doorgeven en uitvoer vinden.
create procedure_name (para1 int,para2 decimal)
as
select * from TableName
Antwoord 16
In Stored Procedures-statements worden slechts één keer geschreven en verminderen het netwerkverkeer tussen clients en servers.
We kunnen ook SQL-injectie-aanvallen vermijden.
- In het geval dat u een programma van derden gebruikt in uw aanvraag voor:
het verwerken van betalingen, hier mag de database alleen de
informatie die het nodig had en activiteit die deze derde partij is geweest
geautoriseerd, hierdoor kunnen we de vertrouwelijkheid van gegevens bereiken door in te stellen:
machtigingen met behulp van Opgeslagen procedures. - Het bijwerken van de tabel mag alleen worden gedaan aan de tabel waarop deze is gericht
maar het zou geen enkele andere tabel moeten bijwerken, waarmee we kunnen bereiken
gegevensintegriteit met behulp van transactieverwerking en foutafhandeling. - Als u een of meer items met een gegevenstype wilt retourneren, dan is dat:
het is beter om een uitvoerparameter te gebruiken. - In Opgeslagen procedures gebruiken we een uitvoerparameter voor alles dat
moet worden teruggestuurd. Als je slechts één artikel wilt retourneren met slechts
een integer gegevenstype, gebruik dan beter een retourwaarde. eigenlijk de
retourwaarde is alleen om het succes of falen van de Opgeslagen . te informeren
Procedure.
Antwoord 17
Voorwoord: In 1992 werd de SQL92-standaard gemaakt en gepopulariseerd door de Firebase DB. Deze standaard introduceerde de ‘Opgeslagen procedure’.
**
Passthrough-query: een tekenreeks (normaal gesproken programmatisch aaneengeschakeld) die evalueert tot een syntactisch correcte SQL-instructie, normaal gegenereerd op de serverlaag (in talen zoals PHP, Python, PERL enz.). Deze verklaringen worden vervolgens doorgegeven aan de database.
**
**
Trigger: een stukje code dat is ontworpen om te worden geactiveerd als reactie op een databasegebeurtenis (meestal een DML-gebeurtenis) die vaak wordt gebruikt om de gegevensintegriteit af te dwingen.
**
De beste manier om uit te leggen wat een opgeslagen procedure is, is door de legacy-manier van het uitvoeren van DB-logica uit te leggen (dwz zonder een opgeslagen procedure te gebruiken).
De oude manier om systemen te maken was om een ’Passthrough Query’ te gebruiken en mogelijk triggers in de DB te hebben.
Vrijwel iedereen die geen Stored Procedures gebruikt, gebruikt een ding dat een ‘Passthrough Query’ wordt genoemd
Met de moderne conventie van Opgeslagen Procedures werden triggers legacy samen met ‘Passthrough Queries’.
De voordelen van opgeslagen procedures zijn:
- Ze kunnen in de cache worden opgeslagen als de fysieke tekst van de Opgeslagen Procedure
verandert nooit. - Ze hebben ingebouwde mechanismen tegen kwaadaardige SQL
injectie. - Alleen de parameters hoeven te worden gecontroleerd op kwaadaardige SQL
injectie bespaart veel processoroverhead. - Meest moderne database
engines compileren eigenlijk opgeslagen procedures. - Ze verhogen de
mate van abstractie tussen lagen. - Ze komen in dezelfde voor
proces als de database die zorgt voor meer optimalisatie en
doorvoer. - De gehele workflow van de backend kan worden getest
zonder client-side code. (bijvoorbeeld de opdracht Uitvoeren in
Transact SQL of de opdracht CALL in MySQL). - Ze kunnen worden gebruikt om
de beveiliging verbeteren omdat ze kunnen worden gebruikt om de
database te benaderen op een manier die niet strookt met de manier waarop de
systeem is ontworpen om te werken. Dit gebeurt via de databasegebruiker
toestemming mechanisme. U kunt gebruikers bijvoorbeeld alleen privileges geven
om opgeslagen procedures uit te voeren in plaats van SELECTEREN, UPDATE enz.
privileges. - De DML-laag die aan triggers is gekoppeld, is niet nodig. **
Door slechts één trigger te gebruiken, wordt een DML-laag geopend die zeer
processorintensief **
Samengevat is er bij het maken van een nieuw SQL-databasesysteem geen goed excuus om Passthrough-query’s te gebruiken.
Het is ook opmerkelijk om te vermelden dat het volkomen veilig is om Opgeslagen Procedures te gebruiken in oudere systemen die al triggers of Passthrough-query’s gebruiken; wat betekent dat migratie van legacy naar Stored Procedures heel eenvoudig is en dat een dergelijke migratie een systeem niet lang of helemaal niet hoeft te doen.