Konzept

Gespeicherte Prozedur: Eine oder mehrere SQL-Anweisungen, die in eine ausführbare Prozedur vorkompiliert wurden.

Syntax für gespeicherte Prozeduren erstellen

CREATE proc | procedure procedure_name
    [{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
     {@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
     ....
    ]
as
    SQL_statements
go

Vergleich von gespeicherten Prozeduren und SQL-Anweisungen

Vorteile:

1. Leistung verbessern
SQL-Anweisungen sind Wird erstellt, wenn w?hrend des Prozesses Analyse und Kompilierung durchgeführt werden. Gespeicherte Prozeduren werden vorkompiliert. Wenn eine gespeicherte Prozedur zum ersten Mal ausgeführt wird, analysiert und optimiert sie ein Speicherplan, der schlie?lich in der Systemtabelle gespeichert wird. Auf diese Weise kann dieser Overhead beim Ausführen der Prozedur eingespart werden .
2. Netzwerk-Overhead reduzieren
Beim Aufruf einer gespeicherten Prozedur müssen Sie nur den Namen der gespeicherten Prozedur und die erforderlichen Parameterinformationen angeben, was den Netzwerkverkehr reduzieren kann.
3. Code-Transplantation erleichtern
Datenbankexperten k?nnen die gespeicherten Prozeduren jederzeit ?ndern, dies hat jedoch keine Auswirkungen auf den Quellcode der Anwendung, wodurch die Portabilit?t des Programms erheblich verbessert wird.
4. St?rkere Sicherheit
1) Systemadministratoren k?nnen die Berechtigungen für einen ausgeführten gespeicherten Prozess einschr?nken, um zu verhindern, dass nicht autorisierte Benutzer auf Daten zugreifen.
2) Beim Aufrufen eines Prozesses über das Netzwerk sind nur Aufrufe des Ausführungsprozesses m?glich sichtbar. Daher k?nnen b?swillige Benutzer die Namen von Tabellen- und Datenbankobjekten nicht sehen, ihre eigenen Transact-SQL-Anweisungen einbetten oder nach kritischen Daten suchen.
3) Die Verwendung von Prozedurparametern hilft, SQL-Injection-Angriffe zu vermeiden. Da Parametereingaben als Literalwerte und nicht als ausführbarer Code behandelt werden, ist es für einen Angreifer schwieriger, Befehle in Transact-SQL-Anweisungen innerhalb der Prozedur einzufügen und die Sicherheit zu gef?hrden.
4) Der Prozess kann verschlüsselt werden, was hilft, den Quellcode zu verschleiern.

Nachteile:

1. Gespeicherte Prozeduren erfordern die Wartung dedizierter Datenbankentwickler, aber die tats?chliche Situation ist, dass Programmentwickler oft Teilzeit arbeiten

2. Das ?ndern gespeicherter Prozeduren ist nicht so flexibel wie SQL

Warum werden gespeicherte Prozeduren in tats?chlichen Anwendungen relativ selten verwendet?

In der normalen Projektentwicklung werden gespeicherte Prozeduren relativ selten verwendet. Warum ist das so?
Die Analysegründe sind wie folgt:
1) Es gibt keine spezifischen Datenbankentwickler, normale Programmierer führen Datenbankoperationen in Teilzeit durch
2) Programmierer müssen das Programm oft nur bedienen, um den Datenzugriff abzuschlie?en, ohne dies zu tun an der Datenbank arbeiten Entwickeln
3) Projektanforderungen ?ndern sich h?ufig und es ist bequemer, SQL-Anweisungen zu ?ndern, insbesondere solche, die logische ?nderungen beinhalten

Wie w?hlt man zwischen gespeicherten Prozeduren und SQL-Anweisungen?

Basierend auf praktischen Anwendungserfahrungen werden folgende Vorschl?ge gegeben:

1. Bei einigen Projekten mit hoher Effizienz oder hohen Standardisierungsanforderungen wird empfohlen, gespeicherte Prozeduren zu verwenden
2 für allgemeine Projekte Die Verwendung parametrisierter Befehle ist ein Kompromiss zwischen gespeicherten Prozeduren und SQL-Anweisungen
3. Für einige Algorithmen mit relativ hohen Anforderungen und mehreren Teilen der Datenlogik wird die Verwendung gespeicherter Prozeduren empfohlen

Spezifische Anwendungen gespeicherter Prozeduren

1. Einfache Abfrage

1. Erstellen Sie eine gespeicherte Prozedur ohne Parameter

Beispiel: Fragen Sie die Gesamtzahl der Schüler ab

--查詢存儲過程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

Ausführung:

EXEC PROC_SELECT_STUDENTS_COUNT

2. Gespeicherte Prozedur mit Parametern

--查詢存儲過程，根據(jù)城市查詢總數(shù)
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

Ausführungsanweisung:

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

3 Zeichen

Platzhalter, fügen Sie beim Zuweisen von Parameterwerten den entsprechenden Platzhalter

--3、查詢姓氏為李的學(xué)生信息，含通配符
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默認(rèn)值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

hinzu, um Folgendes auszuführen:

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

4. Mit Ausgabeparametern

--根據(jù)姓名查詢的學(xué)生信息，返回學(xué)生的城市及年齡
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --輸入?yún)?shù)
    @city nvarchar(20) out, --輸出參數(shù)
    @age  int output        --輸入輸出參數(shù)
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

Ausführung:

--執(zhí)行
declare @name nvarchar(50),
        @city nvarchar(20),
        @age int;
set @name = N'李明';
set @age = 20;
exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;
select @city, @age;

2. Verwenden Sie gespeicherte Prozeduren zum Hinzufügen, L?schen und ?ndern

1. Hinzufügen

Schülerinformationen hinzufügen

--1、存儲過程：新增學(xué)生信息
IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE procedure PROC_INSERT_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)
GO

Ausführung:

EXEC PROC_INSERT_STUDENT 1001,N'張三',19,'ShangHai'

2. ?ndern Sie

, um die Studenteninformationen basierend auf dem Studentenausweis zu aktualisieren

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE procedure PROC_UPDATE_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@id
GO

Ausführung:

EXEC PROC_UPDATE_STUDENT 1001,N'張思',20,'ShangHai'

3. L?schen

L?schen eines Schülerdatensatzes basierend auf der ID

--3、存儲過程：刪除學(xué)生信息
IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

Ausführung:

EXEC PROC_DELETE_STUDENT_BY_ID 1001

3 . Gespeicherte Prozedur zum Implementieren der Paging-Abfrage

1. Verwenden Sie die Paging-Funktion ?row_number“

--分頁查詢
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp 
    WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndex
GO

2. Verwenden Sie das herk?mmliche Top-Paging >

EXEC PROC_SELECT_BY_PAGE 1,10

--使用TOP分頁
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    SELECT TOP(@pageSize) * FROM Students 
    WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    
GO

4. Andere Funktionen:

EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2

2. Verschlüsseln Sie die gespeicherte Prozedur

--1、存儲過程，重復(fù)編譯
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE
with recompile --重復(fù)編譯
AS 
    SELECT * FROM Students
GO

Ausführung:

--2、查詢存儲過程，進(jìn)行加密
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION
with encryption --加密
AS 
    SELECT * FROM Students
GO

EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION