第21课：数据库常用操作(4)——存储过程

数据库系统中，除了以上几课讨论的操作，还有众多功能强大的特性，在某些应用场景下，合理使用它们可以达到事半功倍的效果。

存储过程（SP，Stored Procedure）是数据库中重要的编程特性之一，可以向应用开发一样处理各种逻辑，是数据库管理员（DBA）需要掌握的基本技能。项目开发中，当数据库应用的逻辑比较复杂时，可以由数据库管理员创建存储过程，应用开发人员只需要调用这些存储过程即可执行相应的任务并返回执行结果。

存储过程的定义包括名称、输入参数和输出参数，下面的代码会在SQL Server数据库创建usp_login存储过程，其功能是通过用户名和密码参数查询对应的用户ID，没有返回数据时表示没有匹配的记录，可以通过SSMS执行此代码。

SQL

use cdb_demo;
go

create procedure usp_login
	@username as nvarchar(30),
	@userpwd as nvarchar(64)
as
begin
select userid from user_main
where username=@username and userpwd=@userpwd and locked=0;
end

成功创建usp_login存储过程后，可以使用如下代码调用。

SQL

use cdb_demo;
go

declare @username as nvarchar(30)='user01';
declare @userpwd as nvarchar(64)=
'8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92';
exec usp_login @username,@userpwd;

代码中设置的密码是123456的SHA-256编码，可以在C#中通过tStr.Sha256("123456")获取。当用户名为user01、密码为123456，并且没有锁定（locked=0）时，执行usp_login存储事务会显示此用户记录的userid数据。

下面的代码，我们在/Test.aspx.cs文件中调用usp_login存储过程。

using System;
using System.Data;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection cnn =
            new SqlConnection(tApp.Db.GetJet().CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "usp_login";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@username", "user01");
            cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
            long userid = tLng.GetValue(cmd.ExecuteScalar());
            if (userid > 0) tWeb.WriteLine(userid);
            else tWeb.WriteLine("没有匹配的记录");
        }
    }
}

当用户名和密码正确，并且locked等于0时，页面会显示用户记录的userid数据，否则显示“没有匹配的记录”，可以修改@username或@userpwd参数值观察执行结果。

在存储过程，也可以使用输出参数返回查询结果，如下面的代码就是在SQL Server数据库创建一个包含了输出参数的存储过程，名称为usp_login1。

SQL

use cdb_demo;
go

create procedure usp_login1
	@username as nvarchar(30),
	@userpwd as nvarchar(64),
	@userid as bigint output
as
begin
select @userid=userid from user_main
where username=@username and userpwd=@userpwd and locked=0;
end

代码中，存储过程的第3个参数后使用了output关键字，此参数就定义为输出参数。下面的代码演示了如何执行此存储过程。

SQL

use cdb_demo;
go

declare @username as nvarchar(30)='user01';
declare @userpwd as nvarchar(64)=
'8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92';
declare @userid as bigint;
exec usp_login1 @username,@userpwd,@userid output;
select @userid;

当存在满足条件的记录时返回userid字段值，否则返回NULL值。下面的代码演示了如何在C#代码中读取存储过程输出参数的数据。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection cnn =
            new SqlConnection(tApp.Db.GetJet().CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "usp_login1";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@username", "user01");
            cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
            cmd.Parameters.Add("@userid", SqlDbType.BigInt).Direction =
                ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            //
            long userid = tLng.GetValue(cmd.Parameters["@userid"].Value);
            if (userid > 0) tWeb.WriteLine(userid);
            else tWeb.WriteLine("没有匹配的记录");
        }
    }
}

代码中，读取存储过程的输出参数值时，同样使用SqlCommand对象中的Parameters参数集合，并使用其中的Value属性读取。设置输出参数时，在使用Parameters.Add()方法添加参数时需要指定参数名和数据类型，而Direction属性则指定参数的方法为输出，使用ParameterDirection.Output值设置。

本课程源代码中定义了tSp接口和tOutputParam类等资源实现存储过程的操作，相关代码如下（/app_code/data/base/tSp.cs）。