Transact-SQL中的存储过程,非常类似于C#语言中的方法,可以重复调用。当存储过程执行一次后,可以将语句存储到缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。
一、存储过程的概念
存储过程Procedure是一组为了完成特定功能的SQL语句集合,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。
1、 存储过程的优点
A、 存储过程允许标准组件式编程
存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
B、 存储过程能够实现较快的执行速度
如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。
C、 存储过程减轻网络流量
对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。
D、 存储过程可被作为一种安全机制来充分利用
系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。
二、存储过程的分类
1、系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。
--创建测试表MyStudentInfo
CREATE table MyStudentInfo( Id int not null primary key, Name varchar(16), Age int, Gender varchar(2), Phone varchar(16), Address varchar(50), GradeId int, Score int) --联合插入多条数据INSERT INTO MyStudentInfo SELECT 1,'张三',20,'1','15801258912','上海',1,90 UNIONSELECT 2,'李四',22,'1','12345678901','北京',1,84 UNIONSELECT 3,'王五',16,'1','13976891234','天津',2,35 UNIONSELECT 4,'赵六',19,'1','18676891234','重庆',3,56 UNIONSELECT 5,'小红',21,'2','17776891234','广州',4,82 UNIONSELECT 6,'小王',25,'2','13176891234','深圳',5,54 UNIONSELECT 7,'小刘',18,'2','13374591234','南京',6,69 UNION SELECT 8,'小张',16,'1','13974596734','长沙',6,58 UNION SELECT 9,'小罗',27,'1','13175122786','武汉',7,40 UNION SELECT 10,'小袁',21,'2','17715872346','石家庄',8,34CREATE table GradeInfo
( Id int not null primary key, GradeName varchar(16))--联合插入多条数据
INSERT INTO GradeInfoSELECT 1,'.NET' UNIONSELECT 2,'Android' UNIONSELECT 3,'PHP' UNIONSELECT 4,'UI' UNIONSELECT 5,'HTML5' UNIONSELECT 6,'JAVA' UNIONSELECT 9,'HADOOP' UNIONSELECT 10,'大数据'
--1、不带参数的存储过程,获取MyStudentInfo表的所有记录
CREATE proc Proc_GetInfo as select * from MyStudentInfo--执行存储过程
exec Proc_GetInfo go
--2、创建带输入参数的存储过程
CREATE proc Proc_InsertData @Id int, @Name varchar(16), @Age int, @Gender varchar(2), @Phone varchar(16), @Address varchar(50), @GradeId int, @Score int as begin insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score) end--执行存储过程
exec Proc_InsertData @Id=11,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=62 GO
--3、创建带默认值的输入参数的存储过程
CREATE proc Proc_InsertDefault @Id int, @Name varchar(16), @Age int, @Gender varchar(2)='1', @Phone varchar(16), @Address varchar(50)='沈阳', @GradeId int, @Score int as begin insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score) end--执行存储过程
exec Proc_InsertDefault @Id=12,@Name='小阮',@Age=24,@Phone='13832757512',@GradeId=4,@Score=70 GO--4、创建带输出参数的存储过程(根据输入的ID返回年级ID)
--在创建存储过程时,可以用关键字OUTPUT来创建一个输出参数,另外,调用时也必须给出OUTPUT关键字 create proc Proc_Out @Id int, @gradeId int out as begin select @gradeId=GradeId from MyStudentInfo where id=@id end--执行存储过程
declare @GradeIdOut int exec Proc_Out 10,@GradeIdOut out print @GradeIdOut GO--执行存储过程
DECLARE @Sid intEXEC Proc_Out @gradeId=@Sid OUT,@Id=10 PRINT @Sid
--5、创建带返回值的存储过程
--print语句可以将用户定义的消息返回给客户端 --编写一个存储过程,在插入学生数据前,先判断学号是否存在,如果存在, --输出“要插入的学生的学号已经存在”;否则,插入学生数据,返回“恭喜,数据插入成功” create proc Proc_Return @Id int, @Name varchar(16), @Age int, @Gender varchar(2), @Phone varchar(16), @Address varchar(50), @GradeId int, @Score int as begin IF exists (SELECT * FROM MyStudentInfo WHERE Id=@Id) begin print '要插入的学生的学号已经存在' ROLLBACK end else begin insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@GradeId,@Score) print '恭喜,数据插入成功' end end GO--执行存储过程
exec Proc_Return @Id=10,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75--执行存储过程
exec Proc_Return @Id=12,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75--()return 语句,return语句可以从过程、批处理或语句块中退出,不执行其后继语句
CREATE proc Proc_Delete @name varchar(16) as begin delete FROM MyStudentInfo where Name=@name return @@rowcount end--调用存储过程
declare @Del_Row int exec @Del_Row= Proc_Delete @name='小刘' select @Del_Row 删除的行 GO
--6、创建带变量的存储过程
/*在存储过程可以定义变量,包括全局变量(@@变量名)和局部变量(@变量名)。 用于保存存储过程中的临时结果。 编写存储过程Proc_Var,根据输入的学生学号,计算该学生的平均成绩。 根据该生平局成绩与全体学生平均成绩的关系,返回相应信息*/ create proc Proc_Var @id int, @ResStr varchar(16) out as begin --声明变量 declare @curAvg decimal(18,2) declare @totalAvg decimal(18,2) select @totalAvg=AVG(Score) from MyStudentInfo select @curAvg=AVG(Score) from MyStudentInfo where Id=@id IF @curAvg>@totalAvg set @ResStr='高于平均分' else set @ResStr='低于平均分' print '总平均分为:'+convert(varchar(18),@totalAvg) print '该生平均分为:'+convert(varchar(18),@curAvg) print @ResStr end--调用存储过程
declare @resstring varchar(20) exec Proc_Var 6,@resstring out--7、创建带输入输出参数的存储过程
create proc Proc_OutPut @Id int,--默认输入参数 @Score int out,--输出参数 @Age int output --输入输出参数 as begin select @Score=Score,@Age=Age from MyStudentInfo where Id=@Id and Age=@Age end
--执行存储过程
declare @Sid int,@SScore int,@SAge intset @Sid=8set @SAge=16exec Proc_OutPut @Sid,@SScore out,@SAge outputprint '成绩:'+convert(varchar(8),@SScore)+',年龄:'+convert(varchar(8),@SAge)
--8、创建分页存储过程
create proc Proc_Page@StartIndex int,@EndIndex intas begin select COUNT(*) from MyStudentInfo select * from ( SELECT ROW_NUMBER() OVER(ORDER BY Id) AS rowid,* FROM MyStudentInfo ) temp where temp.rowid between @StartIndex AND @EndIndex end--执行存储过程
exec Proc_Page 1,4--分页存储过程2
create proc Proc_P @PageIndex int, @PageSize int as begin 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 rowid ,* FROM MyStudentInfo ) temp where temp.rowid between @StartRow AND @EndRow end--执行存储过程
exec Proc_P 2,4