初识SQL存储过程

痛定思痛。 2022-05-27 09:45 126阅读 0赞

/\*SQLserver   
存储过程 实现特定功能的SQL语句集合,经编译后存储在数据库中 可以通过指定存储过程的名称并给其参数来执行 \*/  
  
  
/\*\*  
 \* 优点  
 \*    模块化编程:可以写一个存储过程一次,然后一次又一次的调用,从应用程序的不同部分(甚至多个应用程序)  
 \*    性能:  
 \*  1. 更快的执行:存储过程进行解析,并为他创造尽快优化和存储过程是存储在内存中.这意味着它会执行比从应用程序到SQL Server发送SQL多行代码快了很多。这样做需要SQL Server来编译和每次运行时优化改进你的SQL代码。  
 \*  2.减少网络流量：如果通过网络发送的SQL多行代码到你的SQL Server中，这会影响网络性能。如果你有数以百计的SQL代码行和/或你有很多活动在应用程序，这是尤其如此。运行SQL Server上的代码（作为存储过程中）消除了需要发送此代码在网络上。唯一的网络通信将提供的参数和任何查询的结果。  
 \*    安全:  
 \*  用户无需执行任何直接的语句可以执行存储过程。因此，存储过程可以谁也不能正常访问这些任务的用户提供先进的数据库功能，但这种功能是在严格控制的方式提供。  
 \*/  
  
  
      
  //创建存储过程(假设创建一个从数据表user中返回指定行数的存储过程)  
  $sql = "CREATE PROCEDURE test @count int AS  
          SET ROWCOUNT @count  
          SELECT username FROM  AS name,passwdord AS pwd from user ORDER BY user\_id DESC ";  
  
  
   //执行该存储过程  
   $sql = "EXEC test @count = 10 ";//结果就返回10条数据  
  
  
   $sql = "EXEC test @count = 30 ";//结果就返回30条数据  
    
 //修改存储过程  
   
  //如果需要修改现有的存储过程，只需更换掉 CREATE ，使用 ALTER。   
  
  
//我们在 “test” 之前添加一个下划线（即“\_test”），并添加描述字段，如下：  
  
  
$SQL = " ALTER PROCEDURE test @Count int AS  
SET ROWCOUNT @Count  
SELECT username AS test\_name, password AS pwd FROM user ORDER BY id DESC";  
  
  
  
  
      \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#table "student"\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
      \#| S\#   |  Sname |  Sage        |  Ssex  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_   
      \#| 01   |   赵雷 |  1990-01-01  | 男  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 02   |   钱店 |  1992-01-02  |  男  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 03   |   孙峰 |  1992-03-08  | 女  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 04   |   周梅  |  1992-05-20 | 男  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 05   |   乌兰 |  1995-02-15  | 男  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 06   |   郑主 |   1989-02-14 | 女  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
      \#| 07   |   王菊 |   2005-12-12 | 男  
      \#\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
    
     //创建五参数存储过程  
     $sql = "create proc stuProc   
     as    
     begin  
     select S\#,Sname,Sage,Ssex from student  
     end  
      ";  
  
  
      //有参数存储过程 (外部赋值)  
      $sql = "create proc stuProc  
         @sname varchar(100)  
         as   
         begin  
           select S\#,Sname,Sage,Ssex from student where sname = @sname  
         end  
      ";  
    //执行该存储过程  
    $sql = "exec stuProc '赵雷'";  
  
  
  //有参数存储过程 (内部赋值)  
     
 $sql = "create proc StuProc  
@sname varchar(100)='赵雷'  
as   
begin  
select S\#,Sname,Sage,Ssex from student where sname=@sname  
end  
" ;  
//执行该存储过程  
$sql = "exec StuProc";  
  
  
  
  
  //也可以把变量的内容输出，使用output  
 $sql = "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  
 ";  
  
  
 $sql = "declare @isRight int  
         exec stuProc '赵雷',@isRight output  
         select @isRight  
 ";  
  
  
 //在SQL Server查询编辑器窗口中用CREATE PROCEDURE语句创建存储过程PROC\_InsertEmployee，用于实现向员工信息表（tb\_Employee）中添加信息，同时生成自动编号。其SQL语句如下：  
   
  $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 ";  
  
  
//创建验证登录用户身份的存储过程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   
";  
//在"登录"按钮的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('您是合法用户，登录成功！')</script>");   
                return;   
            \}   
            else   
            \{   
                Response.Write("<script>alert('您输入的用户名和密码不正确，请重新输入！')</script>");   
                return;   
            \}   
        \}   
        catch(Exception ex)   
        \{   
            Response.Write(ex.Message.ToString());   
        \}   
        finally   
        \{   
            myConn.Close();   
            myConn.Dispose();   
            myCmd.Dispose();   
        \}\}