什么是存储过程？
用某百科的话来说就是一堆为了完成某一功能或者某些功能的SQL语句的集合，而数据库则会将这些存储过程的方法存储到数据库中去。
优点：
1.可重用并且效率高：存储过程经过一次编译后不需要再次进行编译，这就提高了效率，再过一次编译的存储过程可重复使用，无需在进行第二次编译。
2.减少网络流量：以前传输的往往是SQL语句，当使用存储过程后使用的是参数，只需要传递参数即可。
3.安全性：在SqlServer中，可以使用Grant进行权限的分配不同的权限，也可以使用Revoke进行收回，还可以进行Deny拒绝。
如何使用存储过程？
关于建立存储，当然是在已经有的数据库中建立。下面的是我建立的数据库和表。
打开“可编程性”可以看到“存储过程”，右键创建新的存储过程即可：
--调用数据库
use 数据库名称
go
create proc 存储过程名称
--字段
as
--关联
go
具体代码使用过程如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// 引入数据库管理控件
using System.Data;
using System.Data.SqlClient; namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
// SQL语句插入操作
/*
* // 多个参数操作
SqlConnection conn = new SqlConnection(); // 初始化一个SqlConnection()类
conn.ConnectionString = "server=服务器名称;database=pc_test;uid=账号;pwd=密码"; // 连接字符串,格式(注意间隔要使用分号（";"）来做分割)："server=数据库名称(据说可以使用"."来表示本地数据库，没试过);database=数据库;uid=账号;pwd=密码"。注：如果使用的是"."进行登陆的话，可以将uid和pwd换成"integrated security=SSPI"即："server=.;database=数据库名;integrated security=SSPI";
conn.Open(); // 打开数据库
string sql = "insert into student(name,age,sex) values(@tname,@tage,@tsex);"; // SQL命令
SqlCommand comm = new SqlCommand(sql, conn); // 初始化SqlCommand()类
SqlParameter[] pars =
{
new SqlParameter("@tname",SqlDbType.NChar,10),
new SqlParameter("@tage",SqlDbType.Int,10),
new SqlParameter("@tsex",SqlDbType.TinyInt,2)
};
pars[0].Value = "namejr1";
pars[1].Value = 12;
pars[2].Value = 1;
comm.CommandType = CommandType.Text; // 这个表明是SQL命令
comm.Parameters.AddRange(pars);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds, "student"); // 对这个表进行显示，具体显示方法下一个会给出来
conn.Close();*/
//
/*
* 单条SQL操作
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=NAMEJR-PC;database=pc_test;uid=sa;pwd=123456"; // 连接字符串,格式(注意间隔要使用分号（";"）来做分割)："server=数据库名称(据说可以使用"."来表示本地数据库，没试过);database=数据库;uid=账号;pwd=密码"。注：如果使用的是"."进行登陆的话，可以将uid和pwd换成"integrated security=SSPI"即："server=.;database=数据库名;integrated security=SSPI";
conn.Open(); // 打开数据库
string sql = "select name,age,sex from student where id=@Id;";
SqlCommand comm = new SqlCommand(sql, conn);
SqlParameter par = comm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int, 10));
par.Value = 1;
comm.CommandType = CommandType.Text; // 这个表明是SQL命令
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds, "student");
foreach(DataTable dt in ds.Tables)
{
foreach(DataRow dr in dt.Rows)
{
foreach(DataColumn dc in dt.Columns)
{
// dt.TableName:就是使用Fill()指定的表名。dc.ColumnName字段名，dr[dc]字段值
Console.WriteLine("{0}=>{1}=>{2}", dt.TableName, dc.ColumnName, dr[dc]);
}
}
}
conn.Close();
*/
//
/*
* 快捷显示
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=NAMEJR-PC;database=pc_test;uid=sa;pwd=123456";
conn.Open();
string sql = "execute TSelect 1";
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds, "student");
// 显示同上
*/
}
}
}
Sqlserver中对存储过程的一些操作：
exec sp_databases -- 查看数据库
exec sp_tables -- 查看表
exec sp_columns 表名 -- 查看所填表名的列
exec sp_helpIndex 表名-- 查看对应表的索引
exec sp_helpConstraint 表名 -- 查看对应表的约束
exec sp_stored_procedures -- 查看全部的存储过程
exec sp_helptext 存储进程名 -- 查看对应的存储进程结构
此外还有sp_rename(修改表/列/索引的名称)、sp_renamedb(修改数据库名称)、sp_defaultdb(设置默认登录数据库)、sp_helpdb(获取帮助信息)
带输入输出函数的存储过程：
sqlserver查询记录命令：
use 数据库名
declare @CourtName nvarchar()
declare @totalRoomNum int
declare @NumHousesComple int
declare @NumHousesNotComple int
declare @HCTO int
declare @HCTON int
exec 存储过程名 ,,'2019-03-28',@CourtName output,@totalRoomNum output,@NumHousesComple output,@NumHousesNotComple output,@HCTO output,@HCTON output
print @CourtName
print @totalRoomNum
print @NumHousesComple
print @NumHousesNotComple
print @HCTO
print @HCTON
#region 执行带输入输出的存储过程
public static void IOSql(string proName, string Courtid, long _costtype, string _time, out string CourtName, out int totalRoomNum, out int NumHousesComple, out int NumHousesNotComple, out int HCTO, out int HCTON)
{
/*
SqlConnection conn = new SqlConnection("server=服务器地址;uid=账号;pwd=密码;database=数据库");
*/
// 连接数据库
using (SqlConnection conn = GetConnection())
{
conn.Open(); // 打开数据库
using(SqlCommand commd = conn.CreateCommand()){
commd.CommandType = CommandType.StoredProcedure; // 存储过程方式
commd.CommandText = proName; // 存储过程名称(可以是表名什么的)
// 构建数据类型
SqlParameter[] para=new SqlParameter[]{
new SqlParameter("@CourtId",SqlDbType.Int),
new SqlParameter("@CostType",SqlDbType.Int),
new SqlParameter("@time",SqlDbType.Date),
new SqlParameter("@CourtName",SqlDbType.NVarChar,), // 注意，使用nvarchar的时候记得写长度
new SqlParameter("@totalRoomNum",SqlDbType.Int),
new SqlParameter("@NumHousesComple",SqlDbType.Int),
new SqlParameter("@NumHousesNotComple",SqlDbType.Int),
new SqlParameter("@HCTO",SqlDbType.Int),
new SqlParameter("@HCTON",SqlDbType.Int)
};
para[].Value = Courtid;
para[].Value = _costtype;
para[].Value = _time.ToString();
para[].Direction = ParameterDirection.Output;
para[].Direction = ParameterDirection.Output;
para[].Direction = ParameterDirection.Output;
para[].Direction = ParameterDirection.Output;
para[].Direction = ParameterDirection.Output;
para[].Direction = ParameterDirection.Output;
commd.Parameters.AddRange(para);
commd.ExecuteNonQuery(); // 执行语句
// 单个数据查看
CourtName = Convert.ToString(commd.Parameters[].Value);
totalRoomNum = Convert.ToInt32(commd.Parameters[].Value);
NumHousesComple = Convert.ToInt32(commd.Parameters[].Value);
NumHousesNotComple = Convert.ToInt32(commd.Parameters[].Value);
HCTO = Convert.ToInt32(commd.Parameters[].Value);
HCTON = Convert.ToInt32(commd.Parameters[].Value);
}
conn.Close();
}
}
#endregion
AddBy 2020-2-24
存储函数的使用：
create function ControlLessHundred(@Num int)
returns int
as begin
declare @ReNumber int;
if(@Num>) begin
set @ReNumber=
end
else begin
set @ReNumber=@Num
end
return @ReNumber
end
查询方式：
对于无参数的存户函数在sqlserver中查询只需要：
exec ControlLessHundred() 对于有参数的查询
select ControlLessHundred() 关于在存储过程中的引用
ControlLessHundred() 如果存在'ControlLessHundred' 不是可以识别的 内置函数名称 的报错，那么请在前面加上dbo. 即：
select dbo.ControlLessHundred()
EndBy 2020-2-24