前言
虽说现在orm，nosql对数据的操作会减少很多t-sql的编程，但是t-sql这应该是每个程序员要掌握的，接下来的两周准备把这块知识点进行系统总结。
1.什么是存储过程
Transact-SQL中的存储过程，非常类似于net语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样
就可以提高存储过程的性能。
1. 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
2. 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
3. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。
2.存储过程的优点
2.1 存储过程允许标准组件式编程
存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。
2.2存储过程能够实现较快的执行速度
如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程 时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。
2.3 存储过程减轻网络流量
对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。
2.4存储过程可被作为一种安全机制来充分利用
系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。
3.存储过程的缺点
3.1. 运行速度
所以对于很简单的sql，存储过程没有什么优势。
3.2. 开发调试
存储过程的开发调试要比一般程序困难（老版本DB2还只能用C写存储过程，更是一个灾难）。
3.3. 移植性：
这是我不选择存储过程的主要原因。反正一般的应用总是绑定某个数据库的，不然就无法靠优化数据库访问来提高性能了。
4.存储过程
系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存 储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过 程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。
View Code
4.1.系统存储过程示例
--表重命名
exec sp_rename 'stu', 'stud';--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
4.2.自定义存储过程
4.2.1. 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
4.2.2.创建不带参数存储过程
--创建存储过程
if (object_id('proc_get_student', 'P') is not null)--判断存储过程是否存在 另外一种 if (exists (select * from sys.objects where name = 'proc_get_student'))//
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--调用、执行存储过程
exec proc_get_student;
4.2.3. 修改存储过程
--修改存储过程
alter proc proc_get_student
as
select * from student;
4.2.4. 带参存储过程
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)--判断存储过程是否存在
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
4.2.5.带通配符参数存储过程
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
4.2.6. 带输出参数存储过程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
4.2.7.不缓存存储过程
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;
4.2.8. 加密存储过程
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
4.2.9. 带游标参数存储过程
View Code
4.2.9.分页存储过程
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
5.Raiserror
Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。
语法如下：
Raiserror({msg_id | msg_str | @local_variable}
{, severity, state}
[,argument[,…n]]
[with option[,…n]]
)
# msg_id:在sysmessages系统表中指定的用户定义错误信息
# msg_str:用户定义的信息，信息最大长度在2047个字符。
# severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。
任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。
# state：介于1至127直接的任何整数。State默认值是1。
raiserror('is error', 16, 1);
select * from sys.messages;
--使用sysmessages中定义的消息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);
6.SqlServer中的存储过程有两种返回参数的方式
SqlServer中的存储过程有两种返回参数的方式
1. Return
2. OutPut
第一种只能返回INT类型的数据，而第二种则可以是任意的（即Object类型）
今天用的方法是第二种方式取返回值，首先看一个简单的存储过程
CREATE Procedure [dbo].[pro_ty_getFlowByFbillID]
@FID INT OUTPUT--定义output变量，输出变量
as
SET NOCOUNT ON
select @FID = Fite--进行赋值
from [dbo].[t_bm_exp]
where FbillID = 1
SET NOCOUNT OFF
存储过程中已经定义了输出变量了，下面之下net调用取值即可。
SqlParameter sp =
{--定义参数
new SqlParameter("@fid", SqlDbType.INT, 4)
};
--获得参数
sp[0].Direction = ParameterDirection.Output;
/*net调用存储过程*/
--略
return Convert.ToInt32 （sp[0] .value.ToString());
完成。
7.以前工作中写的存储过程【请轻喷，很久前写的啦】
7.1工作流待办事项分页
-- =============================================
-- Author: wangyanling
-- Create date: 2014-06-03
-- Description: 工作流待办事项分页
-- =============================================
ALTER PROCEDURE [dbo].[PageWorkFlowToDo]
@pageindex int,
@pagesize int,
@uid varchar(200),
@strWhere varchar(500)
AS
BEGIN
declare @sql varchar(4000)
Set @sql=' select top '+CAST(@pagesize as varchar)+' db_userwork.uwid,db_userwork.uid,db_userwork.Title,db_userwork.IniUid,u_user.UName as IniName,
convert(varchar(100),db_userwork.IniStartTime,20) as IniStartTime,convert(varchar(100),
db_userwork.endTime,20) as endTime,db_userwork.State,
S_FInfo.ID as FormID,db_userwork.ImpLevel,db_userwork.DueTime,db_userwork.wsuid,
db_userwork.NotifyType,db_userwork.IfTrans,db_userwork.ID,
case S_FInfo.FrmType when ''1'' then ''WorkFlowForm.aspx''
when ''2'' then ''DeWorkFlowForm.aspx''
when ''3'' then ''AddTeamWork.aspx''
else S_FInfo.FrmHtml end as FrmHtml,db_Work_Case.ExeStep,db_userwork.IfUpdate,db_userwork.FID,
db_userwork.NodeID,ExtParam from db_userwork
left join S_FInfo on db_userwork.wsuid=S_FInfo.WID
left join db_Work_Case on db_userwork.ID=db_Work_Case.ID
left join u_user on db_userwork.IniUid=u_user.UId
where db_userwork.uid='+@uid+' and db_userwork.State=1 and db_userwork.uwid not in (
select top '+CAST(((@pageindex-1)*@pagesize) as varchar)+'db_userwork.uwid from db_userwork
left join S_FInfo on db_userwork.wsuid=S_FInfo.WID
left join db_Work_Case on db_userwork.ID=db_Work_Case.ID
left join u_user on db_userwork.IniUid=u_user.UId
where db_userwork.uid='+@uid+' and db_userwork.State=1 and 1=1 '+@strWhere+' order by IniStartTime desc
) and 1=1 '
if(@strWhere<>'')
begin
Set @sql=@sql+@strWhere+' order by IniStartTime desc';
end
else
begin
Set @sql=@sql+' order by IniStartTime desc';
end
exec(@sql)
END
7.2.通过部门名称获取对应组号
</pre>游标：<pre code_snippet_id="570631" snippet_file_name="blog_20150103_3_3319497" name="code" class="sql">-- =============================================
-- Author: wangyanling
-- Create date: 2013-05-23
-- Description: 通过部门名称获取对应组号
-- =============================================
ALTER PROCEDURE [dbo].[GetGroupIDByOnLine]
-- Add the parameters for the stored procedure here
@GName varchar(200)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Create table #temp
(
GroupID int
)
Create table #tempTwoTable
(
GroupID int
)
insert into #temp select GId from g_group where PId =
(
select GId from g_group where GName=@GName
)
declare @count int
select @count=COUNT(*) from #temp
if(@count>0)
begin
declare @GroupID int
declare cur cursor for select GroupID from #temp
open cur
fetch next from cur into @GroupID
while @@FETCH_STATUS=0 --判断是否成功获取数据
begin
insert into #tempTwoTable select GId from g_group where PId =@GroupID
insert into #tempTwoTable select @GroupID
fetch next from cur into @GroupID
end
close cur --关闭游标
deallocate cur
insert into #tempTwoTable select GId from g_group where GName=@GName
end
else
begin
insert into #tempTwoTable select GId from g_group where GName=@GName
end
----
Declare @GroupIDs varchar(100)
Declare @DouHao nvarchar(max)
Set @DouHao=''
Declare curDouHao cursor for
select GroupID from #tempTwoTable
open curDouHao
fetch next from curDouHao into @GroupIDs
while @@FETCH_STATUS=0 --判断是否成功获取数据
begin
Set @DouHao=@DouHao+Convert(varchar(20),@GroupIDs)+','
fetch next from curDouHao into @GroupIDs
end
close curDouHao --关闭游标
deallocate curDouHao
select SUBSTRING(@DouHao,1,LEN(@DouHao)-1)
drop table #tempTwoTable
drop table #temp
END
7.3.存储过程作业操作
ALTER PROCEDURE [dbo].[add_job] @portal_dbName varchar(200)
AS
BEGIN
DECLARE @jobid uniqueidentifier, @jobname sysname,@sysdatetime varchar(500)
SET select @sysdatetime = convert(varchar(23),Datename(yyyy,getdate()))+convert(varchar(23),Datename(mm,getdate()))+convert(varchar(23),Datename(dd,getdate()))+Datename(hh,getdate())+Datename(n,getdate())+Datename(ss,getdate())
SET @jobname = N'portal_safe_job'+@sysdatetime
IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname)
EXEC msdb.dbo.sp_delete_job @job_name=@jobname
EXEC msdb.dbo.sp_add_job
@job_name = @jobname,
@job_id = @jobid OUTPUT
--定义作业步骤
DECLARE @sql nvarchar(4000),@dbname sysname
select @dbname=@portal_dbName, --作业步骤在当前数据库中执行 重要(根据不同的表来替换表名)
@sql=N'delete from db_userOnline where hartTime< dateadd(minute ,-2,getdate()) or logined=1' --一般定义的是使用TSQL处理的作业,这里定义要执行的Transact-SQL语句
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_name = N'step1',
@subsystem = 'TSQL', --步骤的类型,一般为TSQL
@database_name=@dbname,
@command = @sql
--创建调度(使用后面专门定义的几种作业调度模板)
EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid,@name = N'调度名称',
@freq_type=4, --每天
@freq_interval=1, --指定每多少天发生一次,这里是1天.
@freq_subday_type=0x4, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次
@freq_subday_interval=1, --重复周期数,这里每小时执行一次
@active_start_date = NULL, --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD
@active_end_date = 99991231,
@active_start_time = 00000,@active_end_time = 235959
-- 添加目标服务器
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
END