Gespeicherte SQL-Zusammenfassungsprozedur

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
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [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

--查询存储过程，根据城市查询总数
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、查询姓氏为李的学生信息，含通配符
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)='李%' --默认值
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

--根据姓名查询的学生信息，返回学生的城市及年龄
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),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

Ausführung:

--执行
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、存储过程：新增学生信息
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、存储过程：删除学生信息
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 >

Ausführung:

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

1. Gespeicherte Prozedur, wird bei jeder Ausführung neu kompiliert

2. Verschlüsseln Sie die gespeicherte Prozedur

--1、存储过程，重复编译
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 --重复编译
AS 
    SELECT * FROM Students
GO

Nach der Verschlüsselung kann das Quellskript nicht angezeigt und geändert werden

Ausführung:

Auswirkung: Das Skript kann nicht angezeigt werden. Oder exportieren Sie das Erstellungsskript

--2、查询存储过程，进行加密
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