sql 版本
sql servre 帮助文档中对存储过程的解释
创建存储过程。存储过程是已保存的 Transact-SQL 语句集合,或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用,
可接收并返回用户提供的参数。可以创建过程供永久使用,或在一个会话(局部临时过程)中临时使用,或在所有会话(全局临时过程)中临时使用
创建存储过程可以用 proc或者procedure关键字 proc是简写
目前有个简单的库(TestInfo),库里面有个简单的表(Show) 表里面有些简单的数据
编写个简单的存储过程,没有任何参数和返回值的存储过程
USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL --存储过程是否存在
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
AS
SELECT * FROM Show
执行sql语句成功后,在sql中可以看到存储过程已经创建
OBJECT_ID(object_name,'object_type);函数用于判断对象是否存在
object_type对应的类型如下
AF = Aggregate function (CLR)
C = CHECK constraint --检查约束
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint --外键约束
FN = SQL scalar function --函数
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure --存储过程
PC = Assembly (CLR) stored-procedure --CLR存储过程
PG = Plan guide
PK = PRIMARY KEY constraint --主键约束
R = Rule (old-style, stand-alone) --规则
RF = Replication-filter-procedure
S = System base table --数据库
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger --CLR触发器
TF = SQL table-valued-function
TR = SQL DML trigger --DML触发器
U = Table (user-defined) --数据表
UQ = UNIQUE constraint --唯一约束
V = View --视图
X = Extended stored procedure
比如上面的判断存储过程是否存在 OBJECT_ID('proc_show01','P') 获取存储过程名为:proc_show01
判断表是否存在 OBJECT_ID('info','U') 获取表名为:info
或者:
select * from sysobjects where name='info' and type='u'
SQL Server支持五种类型的完整性约束
NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
CHECK (检查)--检查在约束中指定的条件是否得到了满足.
UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.
在.Net转编写测试代码。调用刚创建的存储过程
/// 执行存储过程
/// </summary>
/// <param name="cmdText">存储过程的名称</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public static DataTable GetPro2(string cmdText, params SqlParameter[] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
// 指定执行语句为存储过程
cmd.CommandType = CommandType.StoredProcedure;
if (param != null && param.Length != 0)
{
cmd.Parameters.AddRange(param);
}
SqlDataAdapter dp = new SqlDataAdapter(cmd);
dp.Fill(dt);
}
return dt;
}
成功后的界面
编写一个有输入参数的存储过程
USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
@name nvarchar(20)
)
AS
SELECT * FROM Show where @name=Name
测试看结果,传入 name="张三"
编写存储过程的可空参数,当存储过程参数有默认值的时候,
那么.net在调用存储过程的时候。可以不用传参数。否则如果不传这会报错
所以:
除非定义了参数的默认值或者将参数设置为等于另一个参数,否则用户必须在调用过程时为每个声明的参数提供值
USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL --存储过程是否存在
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
@name nvarchar(20) = null --默认值为空
)
AS
IF @name is null
begin
set @name='刘德华' --当没有传值的时候。设置默认值
end
select * from Show where @name = name
测试。当不传值的时候,默认查询的是 “刘德华”
创建有输入参数和输出参数的存储过程
USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
@id int output, --输出参数,输出id
@name nvarchar(20) = null --默认值为空
)
AS
IF @name is null
begin
set @name='刘德华' --当没有传值的时候。设置默认值
set @id=0
select * from Show where name = @name
--return
end
else
begin
set @id=(select ID from Show where name = @name)
select * from Show where name = @name
end
既然有输出参数。那么得修改上面的.Net代码。如下
/// <summary>
/// 调用存储过程
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="v1">输出参数(这里是ID)</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static DataTable GetPro1(string cmdText, out int v1, params SqlParameter[] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
// 指定执行语句为存储过程
cmd.CommandType = CommandType.StoredProcedure;
if (param != null && param.Length != 0)
{
cmd.Parameters.AddRange(param);
}
SqlDataAdapter dp = new SqlDataAdapter(cmd);
//dp.SelectCommand.Parameters.AddRange(param);
dp.Fill(dt);
/*
获取输出参数。
* 这里在组装参数的时候。指定了数组的第一个数是输出参数
*/
v1 = Convert.ToInt32(param[0].Value);
//也可以通过SqlCommand根据参数名称获取存储过程的返回值,跟param[0].Value是同样的效果
object id = cmd.Parameters["@id"].Value;
}
return dt;
}
在组装参数的时候。要指定哪些参数是输入参数。哪些是输出参数,默认是输入参数(Input)。
如果是输入(或者输入输出)参数。则必须要赋值。在存储过程中没有给默认值的情况下
在下图可以看出。有输入参数有两个 Input 和 InputOutput
.Net 有个ParameterDirection 枚举类
sql 中有out 输出参数,output 输入输出参数
CREATE PROC proc_show01
(
@id int output, --输入输出参数,输出id
@ck int out,
@name nvarchar(20) = null --默认值为空 ,默认是输入参数
)
但在.Net都是output。因为output就是输入输入参数的总称
param[3].Direction = ParameterDirection.Output
测试代码
static void Main(string[] args)
{
//SqlParameter p = new SqlParameter("@name", "张三");
//拼装参数
SqlParameter[] param = {
new SqlParameter("@id",SqlDbType.Int),
new SqlParameter("@name",SqlDbType.NVarChar)
};
//设置参数是输出参数
param[0].Direction = ParameterDirection.Output;
param[1].Value = "王五";
int id;
DataTable dt = SQLHelper.GetPro1("proc_show01", out id, param); //没有参数
}
运行看结果
上面用 param[0].Direction = ParameterDirection.Output; 指定第一个参数是输出参数
然后通过 v1 = Convert.ToInt32(param[0].Value); 获取存储过程返回的值
从ParameterDirection枚举可以看出输入输出参数都测试过了。还有一个操作的返回值没有测试
那么来编写一个有操作类返回值的存储过程
USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
@id int output, --输出参数,输出id
@name nvarchar(20) = null --默认值为空
)
AS
/*
定义一个变量,返回一个值
也可以不定义变量,直接用return 返回
*/
declare @returnValue int
IF @name is null
begin
set @name='刘德华' --当没有传值的时候。设置默认值
set @id=0
select * from Show where name = @name
set @returnValue=10
--return 0 --这里同样可以
end
else
begin
set @id=(select ID from Show where name = @name)
select * from Show where name = @name
set @returnValue=11
end
return @returnValue --返回值
用 ParameterDirection.ReturnValue 指定是返回值
GetPro1则要加一个out 参数
public static DataTable GetPro1(string cmdText, out int v1,out int v2, params SqlParameter[] param)
则: v2 = Convert.ToInt32(param[1].Value);
测试看效果
存储过程里面也可以执行一个新增操作。然后返回刚新增的ID,比如:
begin
set @name='刘德华' --当没有传值的时候。设置默认值
--set @id=0
select * from Show where name = @name
set @returnValue=10
--return 0 --这里同样可以
insert Show(Name,Remark)VALUES('新增','地球');
set @id = @@IDENTITY --返回新增的ID
end
上面都是通过.net代码访问存储过程,那么通过Transact-SQL语句怎么执行呢?
Transact-SQL 语句用exec(简写)关键字 ,全称是execute关键字
因为存储过程的参数顺序是
所以可以这样直接传参数,但顺序必须根据存储过程定义参数顺序一样
上面只是获取结果集。那么怎获取输出参数(outPut)和返回值呢(ReturnValue)
因为返回值是方法的返回值。所以可以变量名=存储过程名称 是不是跟.Net中很相似?
declare @m int --定义变量接收值
declare @result int
exec @result=[proc_show01] @name='赵六',@id=@m output --一定要注名是output
select @m as '输出参数',@result as '返回值'
执行结果
其实就是参数名= 值 @name='赵六',@id=@m 的方式。因为
一旦使用了 '@name = value' 形式之后,所有后续的参数就必须以 '@name = value' 的形式传递。
用这种方式。顺序可以不用跟存储过程中定义参数的顺序相同
如果不用@name = value'的方式同样可以,
但这样直接传参数,但顺序必须根据存储过程定义参数顺序一样,返回值放在最前面
DECLARE @name varchar
--set @name='张三'
DECLARE @id int
DECLARE @result int
EXEC @result = proc_show01 @id output,'张三' --这里传参数的顺序一定要对应
--EXEC @result = proc_show01 @id output,@name
select @id as '输出参数',@result as '返回值'
结果一样,其实不一样。因为传的参数不一样,哈哈哈
存储过程与SQL语句对比 (知识来源于网络)
优势:
1、提高性能
SQL语句在创建过程时进行分析和编译。 存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、
优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。
2、降低网络开销
存储过程调用时只需用提供存储过程名和必要的参数信息,从而可降低网络的流量。
3、便于进行代码移植
数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
4、更强的安全性
1)系统管理员可以对执行的某一个存储过程进行权限限制,避免非授权用户对数据的访问
2)在通过网络调用过程时,只有对执行过程的调用是可见的。 因此,恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
3)使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码,所以,攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
4)可以对过程进行加密,这有助于对源代码进行模糊处理。
劣势:
1、存储过程需要专门的数据库开发人员进行维护,但实际情况是,往往由程序开发员人员兼职
2、设计逻辑变更,修改存储过程没有SQL灵活
为什么在实际应用中,存储过程用到相对较少呢?
在通常的项目研发中,用存储过程却相对较少,这是为什么呢?
分析原因如下:
1)没有特定的数据库开发人员,普通程序员兼职进行数据库操作
2)程序员往往只需操作程序,即可完成数据访问,无需再在数据库上进行开发
3)项目需求变动比较频繁,修改SQL语句比较方便,特别是涉及逻辑变更
存储过程与SQL语句如何抉择?
基于实际应用的经验,给予如下建议:
1、在一些高效率或者规范性要求比较高的项目,建议采用存储过程
2、对于一般项目建议采用参数化命令方式,是存储过程与SQL语句一种折中的方式
3、对于一些算法要求比较高,涉及多条数据逻辑,建议采用存储过程
--新增字段
ALTER TABLE dbo.info ADD test bit NULL
--添加描述
EXECUTE sp_addextendedproperty N'MS_Description', '这里是描述', N'user', N'dbo', N'table', N'info', N'column', N'test'
--更新说明
EXEC sp_updateextendedproperty 'MS_Description','字段1','user',dbo,'table','info','column',test
--删除属性
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','info','column',test