SQL Server-Grundlagen: Gespeicherte Prozeduren

Einfach ausgedrückt ist eine gespeicherte Prozedur eine Sammlung einer oder mehrerer SQL-Anweisungen, die als Batchdatei betrachtet werden kann, ihre Rolle ist jedoch nicht auf die Batchverarbeitung beschränkt. In diesem Artikel werden hauptsächlich die Verwendung von Variablen sowie die Vorgänge zum Erstellen, Aufrufen, Anzeigen, Ändern und Löschen gespeicherter Prozeduren und gespeicherter Funktionen vorgestellt.

1: Übersicht über gespeicherte Prozeduren

Die gespeicherten Prozeduren in SQL Server sind Codesegmente, die mit T_SQL geschrieben wurden. Ihr Zweck besteht darin, auf einfache Weise Informationen aus Systemtabellen abzufragen oder Verwaltungsaufgaben im Zusammenhang mit der Aktualisierung von Datenbanktabellen und anderen Systemverwaltungsaufgaben auszuführen. Die T_SQL-Anweisung ist die Programmierschnittstelle zwischen der SQL Server-Datenbank und dem Anwendungsprogramm. In vielen Fällen werden einige Codes von Entwicklern wiederholt geschrieben. Wenn der Code jedes Mal mit derselben Funktion geschrieben wird, ist dies nicht nur umständlich und fehleranfällig, sondern verringert auch die Betriebseffizienz des Systems, da SQL Server Anweisungen ausführt eins nach dem anderen.

Kurz gesagt handelt es sich bei einer gespeicherten Prozedur darum, dass SQL Server einige feste Operationsanweisungen schreibt, die mehrmals in Programmsegmente aufgerufen werden müssen, um bestimmte Aufgaben zu erfüllen. Diese Programmsegmente werden auf dem Server und in der Datenbank gespeichert Der Server übergibt das aufzurufende Programm.

Vorteile gespeicherter Prozeduren:

Gespeicherte Prozeduren beschleunigen den Systembetrieb. Sie werden nur dann kompiliert, wenn sie erstellt werden, und müssen nicht bei jeder Ausführung neu kompiliert werden.

Gespeicherte Prozeduren können komplexe Datenbankvorgänge kapseln und den Vorgangsprozess vereinfachen, z. B. das Aktualisieren und Löschen mehrerer Tabellen.

Es kann eine modulare Programmierung realisieren und die gespeicherte Prozedur kann mehrfach aufgerufen werden, wodurch eine einheitliche Datenbankzugriffsschnittstelle bereitgestellt und die Wartbarkeit der Anwendung verbessert wird.

Gespeicherte Prozeduren können die Sicherheit des Codes erhöhen. Für Benutzer, die die in der gespeicherten Prozedur referenzierten Objekte nicht direkt bedienen können, kann SQL Server die Ausführungsberechtigungen des Benutzers für die angegebene gespeicherte Prozedur festlegen.

Gespeicherte Prozeduren können den Netzwerkverkehr reduzieren. Der gespeicherte Prozedurcode wird direkt in der Datenbank gespeichert. Während des Kommunikationsprozesses zwischen dem Client und dem Server wird kein großer T_SQL-Codeverkehr generiert.

Nachteile gespeicherter Prozeduren:

Die Datenbanktransplantation ist unpraktisch. Der in gespeicherten SQL Server-Prozeduren gekapselte Operationscode kann nicht direkt auf andere Datenbankverwaltungssysteme übertragen werden.

Unterstützt kein objektorientiertes Design, kann logisches Geschäft nicht objektorientiert kapseln oder sogar ein allgemeines Geschäftslogik-Framework bilden, das Dienste unterstützen kann.

Der Code ist schlecht lesbar und schwer zu pflegen. Clustering wird nicht unterstützt.

2: Klassifizierung gespeicherter Prozeduren

1. Gespeicherte Systemprozedur

Eine gespeicherte Systemprozedur ist eine vom SQL Server-System selbst bereitgestellte gespeicherte Prozedur, die als Befehl verwendet werden kann um verschiedene Operationen durchzuführen.

Systemgespeicherte Prozeduren werden hauptsächlich verwendet, um Informationen aus Systemtabellen abzurufen. Verwenden Sie gespeicherte Systemprozeduren, um die Verwaltung des Datenbankservers abzuschließen, Systemadministratoren zu helfen und Benutzern das Anzeigen von Datenbankobjekten zu erleichtern befindet sich in der Datenbank. Auf dem Server beginnend mit sp_ werden die gespeicherten Systemprozeduren in den systemdefinierten und benutzerdefinierten Datenbanken definiert, und es ist nicht erforderlich, vor der gespeicherten Prozedur beim Aufruf einen datenbankqualifizierten Namen hinzuzufügen. Beispiel: Die gespeicherte Systemprozedur sp_rename kann den Namen eines vom Benutzer erstellten Objekts in der aktuellen Datenbank ändern. Die gespeicherte Prozedur sp_helptext kann Textinformationen zu Regeln, Standardwerten oder -ansichten sowie viele Verwaltungsaufgaben auf dem SQL SERVER-Server anzeigen werden durch die Ausführung gespeicherter Systemprozeduren vervollständigt, viele Systeminformationen können auch durch die Ausführung gespeicherter Systemprozeduren abgerufen werden.

Gespeicherte Systemprozeduren werden im Systemdatenbank-Master erstellt und gespeichert. Einige gespeicherte Systemprozeduren können nur von Systemadministratoren verwendet werden, während andere gespeicherte Systemprozeduren von anderen Benutzern durch Autorisierung verwendet werden können.

2. Vom Benutzer gespeicherte Prozeduren (benutzerdefinierte gespeicherte Prozeduren)

Benutzerdefinierte gespeicherte Prozeduren sind T_SQL, die von Benutzern mithilfe von T_SQL-Anweisungen geschrieben und in die Benutzerdatenbank geschrieben werden, um eine bestimmte Geschäftsanforderung zu erfüllen Benutzerdefinierte gespeicherte Prozeduren können Eingabeparameter akzeptieren, Ergebnisse und Informationen an den Client zurückgeben, Ausgabeparameter zurückgeben usw. Beim Erstellen einer benutzerdefinierten gespeicherten Prozedur bedeutet das Hinzufügen von „##“ vor dem Namen der gespeicherten Prozedur, dass eine globale temporäre gespeicherte Prozedur erstellt wird; das Hinzufügen von „#“ vor der gespeicherten Prozedur zeigt an, dass eine lokale temporäre gespeicherte Prozedur erstellt wird. Eine lokal temporär gespeicherte Prozedur kann nur innerhalb der Sitzung verwendet werden, in der sie erstellt wurde, und wird gelöscht, wenn die Sitzung endet. Beide gespeicherten Prozeduren werden in der Datenbank tempdb gespeichert.

Benutzerdefinierte gespeicherte Prozeduren sind in zwei Kategorien unterteilt: T_SQL und CLR

T_SQL: Gespeicherte Prozeduren sind eine Sammlung wertsparender T_SQL-Anweisungen, die vom Benutzer bereitgestellte Parameter akzeptieren und zurückgeben können Prozeduren können auch Daten von der Datenbank an die Clientanwendung zurückgeben.

Gespeicherte CLR-Prozeduren beziehen sich auf gespeicherte Methodenprozeduren, die auf die gemeinsame Sprache des Microsoft.NET Frameworks verweisen. Sie können vom Benutzer bereitgestellte Parameter akzeptieren und zurückgeben. Sie werden als öffentliche statische Methoden von Klassen in .NET implementiert Rahmenmontage.

3. Erweiterte gespeicherte Prozeduren

Erweiterte gespeicherte Prozeduren werden als dynamische Verbindungen (DLL-Dateien) implementiert, die außerhalb der SQL SERVER-Umgebung ausgeführt werden und in den Adressraum geladen werden können, in dem die SQL SERVER-Instanz ausgeführt wird Zur Ausführung können erweiterte gespeicherte Prozeduren mithilfe der erweiterten gespeicherten Prozeduren von SQL SERVER programmiert werden. Für Benutzer sind erweiterte gespeicherte Prozeduren mit den gespeicherten Mandarin-Prozeduren identisch gleiche Methode.

3: Erstellen Sie eine gespeicherte Prozedur

Wenn Sie Ihre Arbeit gut machen möchten, müssen Sie zunächst Ihre Werkzeuge wie folgt vorbereiten:

use sample_db;
--创建测试books表
create table books (
 book_id int identity(1,1) primary key,
 book_name varchar(20),
 book_price float,
 book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
 values
 ('论语',25.6,'孔子'),
 ('天龙八部',25.6,'金庸'),
 ('雪山飞狐',32.7,'金庸'),
 ('平凡的世界',35.8,'路遥'),
 ('史记',54.8,'司马迁');

Erstellen Sie eine gespeicherte Prozedur ohne Parameter.

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
 drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

Ändern Sie die Gespeicherte Prozedur

alter procedure dbo.getAllBooks 
as
select book_auth from books;

Gespeicherte Prozedur löschen

drop procedure getAllBooks;

Gespeicherte Prozedur umbenennen

sp_rename getAllBooks,proc_get_allBooks;

Gespeicherte Prozedur mit Parametern erstellen
Es gibt zwei Arten von Parametern für Gespeicherte Prozeduren: Eingabeparameter und Ausgabeparameter

Eingabeparameter: werden zum Übergeben von Werten an gespeicherte Prozeduren verwendet, ähnlich der Werteübergabe in der Java-Sprache oder C.

Ausgabeparameter: werden für die Ergebnisse der Besprechungsteilnahme nach dem Aufruf der gespeicherten Prozedur verwendet, ähnlich der Referenzübergabe in der Java-Sprache.

Der Unterschied zwischen Wertübertragung und Referenzübertragung:

Die Zuweisung zu Basisdatentypen erfolgt durch Wertübertragung; die Zuweisung zwischen Referenztypen erfolgt durch Referenzübertragung.
Wertübergabe übergibt den tatsächlichen Variablenwert; Referenzübergabe übergibt die Referenzadresse des Objekts.
Nach der Übergabe des Werts ändern die beiden Variablen ihre jeweiligen Werte; nach der Übergabe der Referenz ändern die beiden Referenzen den Status desselben Objekts

(1) Gespeicherte Prozedur mit einem Parameter

if (exists (select * from sys.objects where name = 'searchBooks'))
 drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
 --要求book_id列与输入参数相等
 select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2) Gespeicherte Prozedur mit 2 Parametern

if (exists (select * from sys.objects where name = 'searchBooks1'))
 drop proc searchBooks1
go
create proc searchBooks1(
 @bookID int,
 @bookAuth varchar(20)
)
as
 --要求book_id和book_Auth列与输入参数相等
 select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3) Gespeicherte Prozedur mit Rückgabewert erstellen

MySQL

if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字
if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4) Speicher mit Platzhalterprozedur erstellen

if (exists (select * from sys.objects where name = 'charBooks'))
 drop proc charBooks
go
create proc charBooks(
 @bookAuth varchar(20)='金%',
 @bookName varchar(20)='%'
)
as 
 select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec charBooks '孔%','论%';

(5) Die gespeicherte Verschlüsselungsprozedur

mit der Verschlüsselungsklausel verbirgt den Text der gespeicherten Prozedur vor dem Benutzer. Das folgende Beispiel erstellt einen Verschlüsselungsprozess und verwendet zum Abrufen die gespeicherte Prozedur sp_helptext Informationen zum Verschlüsselungsprozess. Versuchen Sie dann, Informationen über den Prozess direkt aus der Systemkommentartabelle abzurufen.

if (object_id('books_encryption', 'P') is not null)
 drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
 select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6) (7). Parameter mit Cursors erstellen. Gespeicherte Prozedur

--with recompile不缓存
if (object_id('book_temp', 'P') is not null)
 drop proc book_temp
go
create proc book_temp
with recompile
as
 select * from books;
go
exec book_temp;
exec sp_helptext 'book_temp';

(8). Erstellen Sie eine gespeicherte Paging-Prozedur

if (object_id('book_cursor', 'P') is not null)
 drop proc book_cursor
go
create proc book_cursor
 @bookCursor cursor varying output
as
 set @bookCursor=cursor forward_only static for
 select book_id,book_name,book_auth from books
 open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
 @bookID int,
 @bookName varchar(20),
 @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
 fetch next from @cur into @bookID,@bookName,@bookAuth;
 print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
 +' ,bookAuth: '+@bookAuth;
end
close @cur --关闭游标
DEALLOCATE @cur; --释放游标

if (object_id('book_page', 'P') is not null)
 drop proc book_page
go
create proc book_page(
 @TableName varchar(50), --表名
 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*)
 @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(500) =N'', --条件语句(不用加where)
 @PageSize int, --每页多少条记录
 @PageIndex int = 1 , --指定当前为第几页
 @TotalRecord int output --返回总记录数
)
as
begin
 --处理开始点和结束点
 Declare @StartRecord int;
 Declare @EndRecord int; 
 Declare @TotalCountSql nvarchar(500); 
 Declare @SqlString nvarchar(2000); 
 set @StartRecord = (@PageIndex-1)*@PageSize + 1
 set @EndRecord = @StartRecord + @PageSize - 1 
 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
 --
 IF (@WhereString! = '' or @WhereString!=null)
 BEGIN
 SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;
 SET @SqlString =@SqlString+ ' where '+ @WhereString; 
 END
 --第一次执行得到
 --IF(@TotalRecord is null)
 -- BEGIN
 EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
 -- END
 ----执行主语句
 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
 Exec(@SqlString) 
END
--调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;
--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。