Detaillierte Erläuterung der Beispiele für gespeicherte SQL-Prozeduren

Detaillierte Erläuterung der Instanz einer gespeicherten SQL-Prozedur

Eine gespeicherte Prozedur ist eine Reihe von Prozeduren, die zur Ausführung bestimmter SQL-Funktionen entwickelt wurden Anweisungen umfassen, ähnlich einer Programmiersprache, auch Datentypen, Flusskontrolle, Eingabe und Ausgabe sowie eine eigene Funktionsbibliothek.

Eine gespeicherte Prozedur kann als Datensatzsatz bezeichnet werden. Es handelt sich um einen Codeblock, der aus einigen T-SQL-Anweisungen besteht, die einige Funktionen wie eine Methode implementieren (Hinzufügen zu einer einzelnen Tabelle). (mehrere Tabellen). Löschen, ändern und prüfen. Geben Sie diesem Codeblock dann einen Namen und rufen Sie ihn einfach auf, wenn diese Funktion verwendet wird. Allerdings sind gespeicherte SQL-Prozeduren immer noch relativ abstrakt und für einige Anfänger schwer zu verstehen. In diesem Artikel werden gespeicherte SQL-Prozeduren daher von oberflächlich bis tiefgehend analysiert, um Ihnen das Erlernen zu erleichtern.

Empfohlen: „SQL-Video-Tutorial“

Vorteile gespeicherter Prozeduren

1. Gespeicherte Prozeduren werden nur beim Erstellen ausgeführt Nach dem Kompilieren ist keine erneute Kompilierung bei jeder weiteren Ausführung der gespeicherten Prozedur erforderlich. Daher kann die Verwendung gespeicherter Prozeduren die Ausführungsgeschwindigkeit der Datenbank verbessern und ist effizienter als T-SQL-Anweisungen.

2. Bei der Ausführung komplexer Vorgänge in der Datenbank kann dieser komplexe Vorgang in einer gespeicherten Prozedur gekapselt und in Verbindung mit der von der Datenbank bereitgestellten Transaktionsverarbeitung verwendet werden.

3. Eine gespeicherte Prozedur kann eine große Anzahl von T-SQL-Anweisungen ersetzen, wenn das Programm im Netzwerk interagiert, sodass sie auch das Kommunikationsvolumen des Netzwerks reduzieren und die Kommunikationsrate erhöhen kann.

4. Gespeicherte Prozeduren können wiederverwendet werden, was die Arbeitsbelastung von Datenbankentwicklern reduzieren kann.

Hohe Sicherheit, Sie können festlegen, dass nur bestimmte Benutzer das Recht haben, bestimmte gespeicherte Prozeduren zu verwenden

Grundlegende Syntax gespeicherter Prozeduren

--------------创建存储过程-----------------
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
--------------调用存储过程-----------------
EXECUTE Procedure_name '' --存储过程如果有参数，后面加参数格式为：@参数名=value，也可直接为参数值value
--------------删除存储过程-----------------
drop procedure procedure_name    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

Parameter zum Erstellen einer gespeicherten Prozedur

● procedure_name: Der Name der gespeicherten Prozedur. Fügen Sie # vor, um eine lokal temporär gespeicherte Prozedur anzugeben, und fügen Sie ## hinzu, um eine globale temporär gespeicherte Prozedur anzugeben Verfahren.

● Zahl: ist eine optionale Ganzzahl, die zum Gruppieren von Prozeduren mit demselben Namen verwendet wird, sodass dieselbe Gruppe von Prozeduren zusammen mit einer DROP PROCEDURE-Anweisung entfernt werden kann. Beispielsweise verwendet eine Anwendung mit dem Namen „orders“ Prozeduren mit den Namen „orderproc;1“, „orderproc;2“ usw. Die DROP PROCEDURE orderproc-Anweisung löscht die gesamte Gruppe. Wenn der Name einen durch Trennzeichen getrennten Bezeichner enthält, sollte die Nummer nicht im Bezeichner enthalten sein und entsprechende Trennzeichen sollten nur vor und nach procedure_name verwendet werden.

● @parameter: Parameter der gespeicherten Prozedur. Es kann einen oder mehrere geben. Der Benutzer muss beim Ausführen der Prozedur für jeden deklarierten Parameter einen Wert angeben (es sei denn, für diesen Parameter ist ein Standardwert definiert). Gespeicherte Prozeduren können bis zu 2100 Parameter haben.

● Verwenden Sie das @-Symbol als erstes Zeichen, um den Parameternamen anzugeben. Parameternamen müssen den Regeln für Bezeichner entsprechen. Die Parameter jeder Prozedur werden nur in der Prozedur selbst verwendet; die gleichen Parameternamen können in anderen Prozeduren verwendet werden. Standardmäßig können Parameter nur Konstanten ersetzen und nicht zum Ersetzen von Tabellennamen, Spaltennamen oder Namen anderer Datenbankobjekte verwendet werden. Weitere Informationen finden Sie unter EXECUTE.

● data_type: Der Datentyp des Parameters. Alle Datentypen, einschließlich Text, Ntext und Bild, können als Parameter für gespeicherte Prozeduren verwendet werden. Der Cursor-Datentyp kann jedoch nur mit OUTPUT-Parametern verwendet werden. Wenn der angegebene Datentyp ein Cursor ist, müssen auch die Schlüsselwörter VARYING und OUTPUT angegeben werden. Weitere Informationen zu den von SQL Server bereitgestellten Datentypen und ihrer Syntax finden Sie unter Datentypen.

Erklärt, dass es keine maximale Begrenzung für die Anzahl der Ausgabeparameter gibt, die vom Cursor-Datentyp sein können.

● VARYING: Geben Sie den als Ausgabeparameter unterstützten Ergebnissatz an (dynamisch erstellt durch die gespeicherte Prozedur, der Inhalt kann sich ändern). Gilt nur für Cursorparameter.

● Standard: Der Standardwert des Parameters. Wenn ein Standardwert definiert ist, müssen Sie für diesen Parameter keinen Wert angeben, um die Prozedur auszuführen. Der Standardwert muss eine Konstante oder NULL sein. Wenn die Prozedur das Schlüsselwort LIKE für diesen Parameter verwendet, können Platzhalterzeichen (%, _, [] und [^]) in den Standardwert einbezogen werden.

● OUTPUT: Gibt an, dass der Parameter ein Rückgabeparameter ist. Der Wert dieser Option kann an EXEC[UTE] zurückgegeben werden. Verwenden Sie den OUTPUT-Parameter, um Informationen an die aufrufende Prozedur zurückzugeben. Als OUTPUT-Parameter stehen Text-, Ntext- und Bildparameter zur Verfügung. Ausgabeparameter, die das Schlüsselwort OUTPUT verwenden, können Cursor-Platzhalter sein.

● NEU KOMPILE: Zeigt an, dass SQL Server den Plan für die Prozedur nicht zwischenspeichert und die Prozedur zur Laufzeit neu kompiliert wird. Verwenden Sie die Option RECOMPILE, wenn Sie mit atypischen oder temporären Werten arbeiten und den im Speicher zwischengespeicherten Ausführungsplan nicht überschreiben möchten.

● ENCRYPTION: Stellt den Eintrag in der verschlüsselten Systemkommentartabelle von SQL Server dar, der den Text der CREATE PROCEDURE-Anweisung enthält. Verwenden Sie ENCRYPTION, um zu verhindern, dass die Prozedur als Teil der SQL Server-Replikation veröffentlicht wird. Beschreibung Während des Upgradevorgangs verwendet SQL Server die in Systemkommentaren gespeicherten Verschlüsselungskommentare, um den Verschlüsselungsprozess neu zu erstellen.

● FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。  

● AS:指定过程要执行的操作。 

● sql_statement:过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 

实例操作学习

下面通过表Student来具体了解一下存储过程，因为是要了解存储过程的简单用法，所以例子很简单。 

无参数存储过程

选出Student表中的所有信息 

create proc StuProc
as      //此处 as 不可以省略不写
begin   //begin 和 end 是一对，不可以只写其中一个，但可以都不写
select S#,Sname,Sage,Ssex from student
end
go

有参数存储过程

全局变量

全局变量也称为外部变量，是在函数的外部定义的，它的作用域为从变量定义处开始，到本程序文件的末尾。

选出指定姓名的学生信息: 

create proc StuProc
@sname varchar(100)   
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go
exec StuProc '赵雷'   //执行语句

上面是在外部给变量赋值，也可以在内部直接给变量设置默认值 

create proc StuProc
@sname varchar(100)='赵雷'
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go
exec StuProc

也可以把变量的内容输出，使用output 

create proc StuProc
@sname varchar(100),
@IsRight int  output //传出参数
as 
if exists (select S#,Sname,Sage,Ssex from student where sname=@sname)
set @IsRight =1
else
set @IsRight=0
go
declare @IsRight int 
exec StuProc '赵雷' , @IsRight output
select @IsRight

以上是全局变量，下面来了解局部变量 

局部变量

局部变量也称为内部变量。局部变量是在函数内作定义说明的。其作用域仅限于函数内部，离开该函数后再使用这种变量是非法的。

局部变量的定义

必须先用Declare命令定以后才可以使用，declare{@变量名 数据类型}

局部变量的赋值方法

set{@变量名=表达式}或者select{@变量名=表达式}

局部变量的显示

create proc StuProc
as 
declare @sname varchar(100)
set @sname='赵雷'
select S#,Sname,Sage,Ssex from student where sname=@sname
go
exec StuProc

那如果是要把局部变量的数据显示出来怎么办呢？ 

create proc StuProc
as 
declare @sname varchar(100)
set @sname=(select Sname from student where S#=01)
select @sname
go
exec StuProc

更详细的实例操作学习

比如，在SQL Server查询编辑器窗口中用CREATE PROCEDURE语句创建存储过程PROC_InsertEmployee，用于实现向员工信息表（tb_Employee）中添加信息，同时生成自动编号。其SQL语句如下： 

IF EXISTS (SELECT name  
   FROM   sysobjects  
   WHERE  name = 'Proc_InsertEmployee'  
   AND          type = 'P') 
DROP PROCEDURE Proc_InsertEmployee 
GO 
CREATE PROCEDURE Proc_InsertEmployee 
@PName nvarchar(50), 
@PSex nvarchar(4), 
@PAge int, 
@PWage money 
AS 
begin 
   declare @PID nvarchar(50) 
   select @PID=Max(员工编号) from tb_Employee 
   if(@PID is null) 
       set @PID='P1001' 
   else 
       set @PID='P'+cast(cast(substring(@PID,2,4) as int)+1 as nvarchar(50)) 
   begin 
       insert into tb_Employee values(@PID,@PName,@PSex,@PAge,@PWage) 
   end 
end 
go

存储过程的修改

创建完存储过程之后，如果需要重新修改存储过程的功能及参数，可以在SQL Server 2005中通过以下两种方法进行修改：一种是用Microsoft SQL Server Mangement修改存储过程；另外一种是用T-SQL语句修改存储过程。 

使用Microsoft SQL Server Mangement修改存储过程，步骤如下： 

（1）在SQL Server Management Studio的“对象资源管理器”中，选择要修改存储过程所在的数据库（如：db_18），然后在该数据库下，选择“可编程性”。 

（2）打开“存储过程”文件夹，右键单击要修改的存储过程（如：PROC_SEINFO），在弹出的快捷菜单中选择“修改”命令，将会出现查询编辑器窗口。用户可以在此窗口中编辑T-SQL代码，完成编辑后，单击工具栏中的“执行（X）”按钮，执行修改代码。用户可以在查询编辑器下方的Message窗口中看到执行结果信息。 

使用Transact-SQL修改存储过程： 

使用ALTER PROCEDURE语句修改存储过程，它不会影响存储过程的权限设定，也不会更改存储过程的名称。 

语法：

ALTER PROC [ EDURE ] procedure_name [ ; number ] 
    [ { @parameter data_type }  
         [ VARYING ] [ = default ] [ OUTPUT ] 
    ] [ ,...n ]  
[ WITH 
    { RECOMPILE | ENCRYPTION 
        | RECOMPILE , ENCRYPTION   }  
] 
[ FOR REPLICATION ]  
AS 
    sql_statement [ ...n ]

参数说明

procedure_name：是要更改的存储过程的名称。

交叉链接：关于ALTER PROCEDURE语句的其他参数与CREATE PROCEDURE语句相同，可参见上面的“创建存储过程的参数”。

例如，修改存储过程PROC_SEINFO，用于查询年龄大于35的员工信息。SQL语句如下：

ALTER PROCEDURE [dbo].[PROC_SEINFO] 
AS 
BEGIN 
SELECT * FROM tb_Employee where 员工年龄>35 
END

存储过程的删除

使用Microsoft SQL Server Mangement删除存储过程，步骤如下：

（1）在SQL Server Management Studio的“对象资源管理器”中，选择要删除存储过程所在的数据库（如：db_student），然后在该数据库下选择“可编程性”。  

（2）打开“存储过程”文件夹，右键单击要删除的存储过程（如：PROC_SEINFO），在弹出的快捷菜单中选择“删除”命令。 

（3）单击“确定”按钮，即可删除所选定的存储过程。  

注意：删除数据表后，并不会删除相关联的存储过程，只是其存储过程无法执行。

使用T-SQL删除存储过程： 

DROP PROCEDURE语句用于从当前数据库中删除一个或多个存储过程或过程组。 

语法：

DROP PROCEDURE { procedure } [ ,...n ]

参数说明：   

Procedure：是要删除的存储过程或存储过程组的名称。过程名称必须符合标识符规则。可以选择是否指定过程所有者名称，但不能指定服务器名称和数据库名称。    

n：是表示可以指定多个过程的占位符。 

    

例如删除PROC_SEINFO存储过程的SQL语句如下。 

DROP PROCEDURE PROC_SEINFO

例如，删除多个存储过程proc10、proc20和proc30。 

DROP PROCEDURE proc10, proc20, proc30

例如，删除存储过程组procs（其中包含存储过程proc1、proc2、proc3）。 

DROP PROCEDURE procs

注意：

SQL语句DROP不能删除存储过程组中的单个存储过程。

应用存储过程验证用户登录身份：

目前，验证用户登录身份的方法有多种，而通过调用存储过程来实现用户身份验证是目前最好的解决方案之一。因为存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句要快得多。 

本例是通过调用存储过程来验证用户登录的用户名和密码是否正确。运行本实例，在“用户名”和“密码”文本框中输入相应的用户名和密码，单击“登录”按钮即可。 

程序开发步骤： 

（1）新建一个网站，将其命名为"index"，默认主页名为Default.aspx。    

（2）Default.aspx页面涉及到的控件如表1所示。 

（3）主要程序代码如下。 

打开SQL Server Management Studio，并连接到SQL Server2005中的数据库。单击工具栏中“ ”按钮，新建查询编辑器。

在该查询编辑器中，创建验证登录用户身份的存储过程PROC_EXISTS，具体的SQL语句如下： 

CREATE PROC PROC_EXISTS 
( 
@UserName NVARCHAR(20), 
@PassWord NVARCHAR(20), 
@ReturnValue int OUTPUT 
) 
AS 
IF EXISTS(select * from tb_member where userName=@UserName AND passWord=@PassWord) 
       set @ReturnValue= 100 
ELSE 
       set @ReturnValue= -100 
GO

在"登录"按钮的Click事件下，执行验证登录用户身份的存储过程，如果输入的用户名和密码正确，则弹出对话框提示用户登录成功，代码如下： 

protected void btnLogin_Click(object sender, EventArgs e) 
    { 
        //连接数据库 
        myConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); 
        myCmd = new SqlCommand("PROC_EXISTS", myConn);   //调用存储过程，判断用户是否存在
        myCmd.CommandType = CommandType.StoredProcedure; 
        //为存储过程的参数赋值 
        SqlParameter userName=new SqlParameter("@UserName", SqlDbType.NVarChar, 20); 
        userName.Value=this.txtName.Text.Trim(); 
        myCmd.Parameters.Add(userName); 
        SqlParameter passWord=new SqlParameter("@PassWord", SqlDbType.NVarChar, 20); 
        passWord.Value = this.txtPassword.Text.Trim(); 
        myCmd.Parameters.Add(passWord); 
        //指出该参数是存储过程的OUTPUT参数 
        SqlParameter ReturnValue = new SqlParameter("@ReturnValue",SqlDbType.Int ,4); 
        ReturnValue.Direction = ParameterDirection.Output; 
        myCmd.Parameters.Add(ReturnValue); 
        try 
        { 
            myConn.Open(); 
            myCmd.ExecuteNonQuery(); 
            if (int.Parse(ReturnValue.Value.ToString()) == 100) 
            { 
                Response.Write("<script>alert(&#39;您是合法用户，登录成功！&#39;)</script>"); 
                return; 
            } 
            else 
            { 
                Response.Write("<script>alert(&#39;您输入的用户名和密码不正确，请重新输入！&#39;)</script>"); 
                return; 
            } 
        } 
        catch(Exception ex) 
        { 
            Response.Write(ex.Message.ToString()); 
        } 
        finally 
        { 
            myConn.Close(); 
            myConn.Dispose(); 
            myCmd.Dispose(); 
        }}

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Beispiele für gespeicherte SQL-Prozeduren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!