这篇文章来说说sql中的存储过程 。
前些天看了几道笔试题 基本上只要考sql 就会必考存储过程
这不得不使我要把存储过程学好 课后 我将存储过程做了一个简单的总结 希望能对大家有帮助
有不对的地方请您指出 万分感谢！！！
什么是存储过程
Sql中的存储过程就像是c#中的方法
* 存储过程---就像数据库中运行方法(函数)
* 和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
* 前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用
* 优点：
执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 – 类似方法的复用
提高系统安全性 – 防止SQL注入
减少网络流通量 – 只要传输 存储过程的名称
* 系统存储过程
* 由系统定义，存放在master数据库中
* 名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。
* 自定义存储过程
* 由用户在自己的数据库中创建的存储过程
使用存储过程的好处
1. 试代码结构化 层次清晰
2. 在连接数据库时不能直接修改其参数，避免了sql注入漏洞
定义存储过程
create proc ups_getnum // create 定义的关键字 ups_getnum 存储名字
修改存储过程
alter proc ups_getnum // alter 修改的关键字
创建一个无参数 无返回值的存储过程（输出1~100的累加和）
create proc ups_getnum alter
as
declare @i int // 声明变量
declare @s int
set @i=0 // 变量赋值
set @s=0
while @i<=100
begin
set @s=@s+@i
set @i=@i+1
end
select @s
exec ups_getnum // 执行存储过程
创建一个有参数 无返回值的存储过程（输出1~100的累加和）
create proc usp_OneParsSum
@max int // 参数
as
declare @s int
declare @i int
set @s=0
set @i=0
while @i<=@max
begin
set @s=@s+@i
set @i=@i+1
end
select @s
exec usp_OneParsSum 100 // 执行存储过程 将参数传进去
exec usp_OneParsSum @max = 100 // 执行存储过程 将参数传进去
创建一个有参数 有返回值的存储过程（输出1~100的累加和）
alter proc usp_OneParsSumReturn
@min int, // 参数
@max int, // 参数
@ss int output // 返回值 用 output 修饰
as
declare @s int
declare @i int
set @s=0
set @i=@min
while @i<=@max
begin
set @s=@s+@i
set @i=@i+1
end
set @ss=@s
-- 调用
declare @ss int // 声明返回值变量
exec usp_OneParsSumReturn 1,100,@ss output // 将参数传进去 返回返回值
select @ss
使用Ado.Net执行存储过程 无参数 无返回值的
Sql中的存储过程
create proc usp_showTalbe
as
select * from T_Persons
vs中的ado
class Program
{
static void Main(string[] args)
{
// 存储类的集合
List<person> lis = new List<person>();
string str_con = @"server=.\sqlexpress;database=newStu;integrated security=true";
using (SqlConnection conn =new SqlConnection(str_con))
{
// 这里不是sql语句 是存储过程
string sql = @"usp_showTalbe"; // sql 语句是存储过程
using (SqlCommand cmd=new SqlCommand(sql,conn))
{
// 将command声明为存储过程
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State==System.Data.ConnectionState.Closed)
{
conn.Open();
}
using (SqlDataReader rader=cmd.ExecuteReader())
{
if (rader.HasRows)
{
while (rader.Read())
{
person p = new person();
p.Id = Convert.ToInt32(rader["FId"]);
p.Name = Convert.ToString(rader["FName"]);
p.Gender = Convert.ToString(rader["FGender"]);
p.Age = Convert.ToInt32(rader["FAge"]);
lis.Add(p);
}
}
}
}
}
foreach (person p in lis)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", p.Id, p.Name, p.Gender, p.Age);
}
Console.ReadKey();
}
}
使用Ado.Net执行存储过程 有参数 无返回值的
Sql中的存储过程
create proc usp_getStu
@min int,
@max int
as
-- 调用
select * from T_Persons where FAge between @min and @max
vs中的ado
class Program
{
static void Main(string[] args)
{
List<person> lis = new List<person>();
string str_con = @"server=.\sqlexpress;database=newStu;integrated security=true";
using (SqlConnection conn=new SqlConnection(str_con))
{
// 这里不是sql语句 是存储过程
string sql = @"usp_getStu";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// 将command声明为存储过程
cmd.CommandType = CommandType.StoredProcedure;
// 赋值方法一
SqlParameter min = new SqlParameter("@min", 12);
SqlParameter max = new SqlParameter("@max", 20);
cmd.Parameters.Add(min);
cmd.Parameters.Add(max);
// 赋值方法二
//cmd.Parameters.AddWithValue("@min", (object)1);
//cmd.Parameters.AddWithValue("@max", (object)2);
if (conn.State==System.Data.ConnectionState.Closed)
{
conn.Open();
}
using (SqlDataReader rader = cmd.ExecuteReader())
{
if (rader.HasRows)
{
while (rader.Read())
{
person p = new person();
p.Id = Convert.ToInt32(rader["FId"]);
p.Name = Convert.ToString(rader["FName"]);
p.Gender = Convert.ToString(rader["FGender"]);
p.Age = Convert.ToInt32(rader["FAge"]);
lis.Add(p);
}
}
}
}
}
foreach (person p in lis)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", p.Id, p.Name, p.Gender, p.Age);
}
Console.ReadKey();
}
}
使用Ado.Net执行存储过程 有参数 有返回值的
Sql中的存储过程
alter proc usp_Return
@min int,
@max int,
@number int output
as
-- 调用
select @number= COUNT(*) from T_Persons where FAge between @min and @max
select * from T_Persons where FAge between @min and @max
vs中的ado
class Program
{
static void Main(string[] args)
{
List<person> lis = new List<person>();
string str_con = @"server=.\sqlexpress;database=newStu;integrated security=true";
using (SqlConnection conn = new SqlConnection(str_con))
{
string sql = @"usp_Return";
// string sql = @"usp_getStu";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// 将command声明为存储过程
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@min", 9);
cmd.Parameters.AddWithValue("@max", 90);
// 设定返回的参数
// 1.创建一个Sqlparameter 对象 存放返回的那个数
SqlParameter par = new SqlParameter("@number", System.Data.DbType.Int32);
// 2.设定他是一个输出的参数
par.Direction = ParameterDirection.Output;
// 3.将这个参数添加到cmd中
cmd.Parameters.Add(par);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
using (SqlDataReader rader = cmd.ExecuteReader())
{
if (rader.HasRows)
{
while (rader.Read())
{
person p = new person();
p.Id = Convert.ToInt32(rader["FId"]);
p.Name = Convert.ToString(rader["FName"]);
p.Gender = Convert.ToString(rader["FGender"]);
p.Age = Convert.ToInt32(rader["FAge"]);
lis.Add(p);
}
}
}
foreach (person p in lis)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", p.Id, p.Name, p.Gender, p.Age);
}
object o = par.Value;
if (o != null)
{
Console.WriteLine("{0} 条数据", Convert.ToInt32(par.Value));
}
}
}
Console.ReadKey();
}
}