Explication détaillée des exemples de procédures stockées SQL

Explication détaillée de l'instance de procédure stockée SQL

La procédure stockée est un ensemble de procédures conçues pour remplir des fonctions spécifiques SQL Les instructions, similaires à un langage de programmation, incluent également des types de données, un contrôle de flux, des entrées et des sorties et sa propre bibliothèque de fonctions.

Une procédure stockée peut être considérée comme un ensemble d'enregistrements. Il s'agit d'un bloc de code composé de certaines instructions T-SQL implémentant certaines fonctions comme une méthode (ajout à une seule table ou). plusieurs tables). Supprimer, modifier et vérifier), puis donner un nom à ce bloc de code et l'appeler simplement lorsque cette fonction est utilisée. Cependant, les procédures stockées SQL sont encore relativement abstraites et difficiles à comprendre pour certains débutants, c'est pourquoi cet article analysera les procédures stockées SQL de manière superficielle à approfondie pour vous aider à les apprendre.

Recommandé : "Tutoriel vidéo SQL"

Avantages des procédures stockées

1. Après la compilation, il n'est pas nécessaire de recompiler chaque fois que la procédure stockée est exécutée ultérieurement. Généralement, les instructions SQL sont compilées une fois à chaque exécution. Par conséquent, l'utilisation de procédures stockées peut améliorer la vitesse d'exécution de la base de données et est plus efficace. que les instructions T-SQL.

2. Lors de l'exécution d'opérations complexes sur la base de données, les opérations complexes peuvent être encapsulées dans des procédures stockées et utilisées conjointement avec le traitement des transactions fourni par la base de données.

3. Une procédure stockée peut remplacer un grand nombre d'instructions T-SQL lorsque le programme interagit sur le réseau, elle peut donc également réduire le volume de communication du réseau et augmenter le débit de communication.

4. Les procédures stockées peuvent être réutilisées, ce qui peut réduire la charge de travail des développeurs de bases de données.

5. Haute sécurité, vous pouvez définir que seuls certains utilisateurs ont le droit d'utiliser les procédures stockées spécifiées

Syntaxe de base des procédures stockées

--------------创建存储过程-----------------
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    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

Paramètres de création d'une procédure stockée

● nom_procédure : Le nom de la procédure stockée Ajoutez # devant pour indiquer une procédure stockée temporaire locale, et ajoutez ## pour indiquer une procédure stockée temporaire globale. procédure.

Quantity : est un entier facultatif, utilisé pour regrouper les procédures portant le même nom, afin que le même groupe de procédures puisse être supprimé avec une instruction DROP PROCEDURE. Par exemple, une application nommée commandes utilise des procédures nommées orderproc;1, orderproc;2, etc. L'instruction DROP PROCEDURE orderproc supprime le groupe entier. Si le nom contient un identifiant délimité, le numéro ne doit pas être inclus dans l'identifiant et les délimiteurs appropriés ne doivent être utilisés qu'avant et après nom_procédure.

● @parameter : Paramètres de la procédure stockée. Il peut y en avoir un ou plusieurs. L'utilisateur doit fournir une valeur pour chaque paramètre déclaré lors de l'exécution de la procédure (sauf si une valeur par défaut pour ce paramètre est définie). Les procédures stockées peuvent contenir jusqu'à 2 100 paramètres.

● Utilisez le symbole @ comme premier caractère pour spécifier le nom du paramètre. Les noms de paramètres doivent être conformes aux règles relatives aux identifiants. Les paramètres de chaque procédure sont utilisés uniquement dans la procédure elle-même ; les mêmes noms de paramètres peuvent être utilisés dans d'autres procédures. Par défaut, les paramètres peuvent uniquement remplacer des constantes et ne peuvent pas être utilisés pour remplacer les noms de tables, de colonnes ou les noms d'autres objets de base de données. Voir EXÉCUTER pour plus d'informations.

● data_type : Le type de données du paramètre. Tous les types de données, y compris texte, ntext et image, peuvent être utilisés comme paramètres pour les procédures stockées. Cependant, le type de données curseur ne peut être utilisé qu'avec les paramètres OUTPUT. Si le type de données spécifié est un curseur, les mots-clés VARYING et OUTPUT doivent également être spécifiés. Pour plus d'informations sur les types de données fournis par SQL Server et leur syntaxe, consultez Types de données.

Explique qu'il n'y a pas de limite maximale sur le nombre de paramètres de sortie pouvant être du type de données du curseur.

● VARYING : Spécifiez l'ensemble de résultats pris en charge comme paramètre de sortie (construit dynamiquement par la procédure stockée, le contenu peut changer). S'applique uniquement aux paramètres du curseur.

● default : La valeur par défaut du paramètre. Si une valeur par défaut est définie, vous n'avez pas besoin de spécifier une valeur pour ce paramètre afin d'exécuter la procédure. La valeur par défaut doit être une constante ou NULL. Si la procédure utilise le mot-clé LIKE pour ce paramètre, des caractères génériques (%, _, [] et [^]) peuvent être inclus dans la valeur par défaut.

● OUTPUT : Indique que le paramètre est un paramètre de retour. La valeur de cette option peut être renvoyée à EXEC[UTE]. Utilisez le paramètre OUTPUT pour renvoyer des informations à la procédure appelante. Les paramètres Text, ntext et image sont disponibles en tant que paramètres OUTPUT. Les paramètres de sortie utilisant le mot-clé OUTPUT peuvent être des espaces réservés au curseur.

● RECOMPILE : indique que SQL Server ne mettra pas en cache le plan de la procédure et que la procédure sera recompilée au moment de l'exécution. Utilisez l'option RECOMPILE lorsque vous travaillez avec des valeurs atypiques ou temporaires et que vous ne souhaitez pas écraser le plan d'exécution mis en cache en mémoire.

● ENCRYPTION : représente l'entrée dans la table syscomments chiffrée de SQL Server qui contient le texte de l'instruction CREATE PROCEDURE. Utilisez ENCRYPTION pour empêcher la publication de la procédure dans le cadre de la réplication SQL Server. Description Pendant le processus de mise à niveau, SQL Server utilise les commentaires de chiffrement stockés dans syscomments pour recréer le processus de chiffrement.

● 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(); 
        }}

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!