下载文档原格式
SQL Server 2005存储过程与触发器1、基本储存过程的创建Stduent (Sno char (10),Sname char(10),Ssex char(2), Sage int ,Sdept char(20),Shobby char(10))Course(Cno char(10),Cname char(20),Ccredit int)SC(Sno char(10),Cno char(10),Grade int)①创建一存储过程get_student_num，利用输出参数形式获取学生人数信息。
并利用CALL调用该存储过程查看结果。
create procedure get_student_num(@num int output)asselect@num=COUNT(sno)from Studentdeclare@student_num intexec get_student_num@student_num outputprint@student_num②创建一存储过程get_student_by_sno，通过输入学生编号作为参数，获得该学生的记录信息。
并利用CALL调用该存储过程查看结果。
create procedure get_student_by_sno(@sno int)asselect*from Student where Sno=@snoexec get_student_by_sno'11010'③创建一存储过程update_sage_by_sno，通过输入学生编号、年龄作为参数，将指定学生的年龄更改为指定的年龄。
并利用CALL调用该存储过程查看结果。
create procedure update_sage_by_sno(@sno int,@age int)asupdate Student set Sage=@age where Sno=@snoexec update_sage_by_sno'11010','23'④创建一存储过程delete_student_by_sno，通过输入学生编号作为参数，删除该学生记录。
SqlServer存储过程和函数浅谈今天给⼤家总结⼀下sql server中的存储过程和函数。
本⼈是⼩⽩，⾥⾯内容⽐较初级，⼤神不喜勿喷⾃⾏飘过就是。
⾸先给⼤家简单列出sql server中的流控制语句，后⾯会⽤到的^_^sql server常⽤控制语句1.begin..end语句:该语句⽤来定义⼀串由顺序执⾏的SQL语句构成的块。
beginstatement blockend2.if....else语句：该语句⽤来定义有条件执⾏的某些语句。
if boolen_expressionstatement[else [if boolean_expression] statement]⽰例：查询学号为9704学⽣的成绩状况if ( select min (mark) from student where sno='9704') >90print' 学⽣成绩全部优秀 'elseif ( select min (mark) from student where sno='9704') >60print' 学⽣成绩全部及格 'elseprint' 学⽣成绩全部及格 'View Code3.while、break和continue语句：写过程序的同学相⽐对这个并不陌⽣，直接上代码⽰例：学号为9705学⽣的平均成绩如果⼩于75，则将该学⽣的每门成绩以5%的⽐例提⾼，当平均成绩⼤于等于75或者所有课程都及格时，终⽌操作。
while(select avg( mark) from student) <75beginupdate studentset mark= mark*1.05if(select min( mark) from student) >=60breakendView Code4.declare语句：⽤来定义⼀个局部变量，可⽤select语句为该变量赋初值。
SQL Server 2005 自定义函数语汇小结由于工作的需要，了解下SQL Server 2005 函数的写法，现在总结一下：对于SQL Server 2005 数据库而言，函数与存储过程在语法方面是有很大的相同点，最大的不同就是函数有返回值，直接使用returns ,而存储过程则使用output来声明输出变量一、下面先说明下，如何创建函数1、创建没有返回值与没有参数的函数CREATE FUNCTION my_function()ASBEGINDECLARE @variable varchar(255) --声明字符型变量DECLARE @variable int --声明整形型变量...(do something)SET @variable = '12345' --对变量variable赋值END2、创建没有返回值有参数的函数CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))ASBEGINDECLARE @variable_1 varchar(255) --声明字符型变量...(do something)SET @variable_1 = @user_Name + convert(varchar(255),@password) --将变量@user_Name与@password连接赋给@variable_1，其中convert()函数是将int型转为varchar型END3、创建有返回值与有参数的函数CREATE FUNCTION my_function(@user_Name varchar(128),@password int(6))returns varchar(255)--设置返回值，记住是returns 而不是returnASBEGINDECLARE @result varchar(5)DECLARE @fagle varchar(5)SET @result = select er_Name from USERS as users where er_Name = @user_Name and users.password = @passwordIF @result = ''BEGINSET @fagle = 'NO'ENDELSEBEGINSET @falge = 'YES'ENDreturn @result --返回结果END二、删除一个函数语法DROP FUNCTION my_function三、执行一个函数语法select dbo.my_function(...) --根据有没参数来处理go--注：在SQL Server 2005 中，有内部函数与外部函数，数据库系统自带函数，如sum()，count()等等，这些称为内部函数，而我们自定义的函数称为外部函数。
什么是存储过程，在存储在服务器上的 T-SQL 语句的命名集合，是封装性任务的方法，支 持变量及条件的编程。
SQL Server 的存储过程与其他编程语言中的过程 （包括函数） 类似， 可以包含数据库操作 （调 用其他过程）的编程语句，可以接受参数，可以返回状态值以表明成功或失败，以输出参数 的形式将多个值返回至调用过程 SQL Server 支持五种类型的存储过程： 系统存储过程（sp_） ：存储在 master 数据库中。
本地存储过程：在单独的用户数据库中。
临时存储过程：局部的以#开头，全局的以##开头。
远程存储过程：分布式查询支持此功能。
扩展存储过程：在 SQL Server 环境外执行。
存储过程的优点 封装商务逻辑， 若规则或策略改变只需修改存储过程就可以直接使用， 屏蔽数据库的详细资 料，用户不需要访问底层数据库和数据库对象。
提供安全机制，只需要提供存储过程的权限 而不需要提供整个数据库中数据的一个权限。
另外， 存储过程能够通过预编译的语句来确定执行哪一部分而不是都执行。
在传输过程中传 输的存储过程而不是数据，减少了通信量，能够实现一个较快的执行速度。
create proc liuhaoran as select price from titles where price>15 select title from titles where price<=15 在存储过程里可以包含任何数目和类型的 T-SQL 语句，但不能包含 create proc 、create trigger、create view 执行创建存储过程的用户必须是 sysadmin、db_owner 或 db_ddladmin 角色的成员，或必须 拥有 CREATE PROCEDURE 权限 存储过程有大小的限制，最大为 128M 存储过程可以传递参数，创建存储过程，定义两个浮点型的参数，无返回值 CREATE PROCEDURE titlespro @Beginningprice float,@Endingprice float AS IF @Beginningprice IS NULL OR @Endingprice IS NULL BEGINprint 'no price is exits' RETURN END SELECT price FROM titles WHERE price BETWEEN @Beginningprice AND @Endingprice GO/*执行语句,输入两个价格值作为参数值*/ exec titlespro 10,19指定参数的依据和指导原则 所有的输入参数值都应该在存储过程开始的时候进行检查， 以尽早捕获缺失值和非法值 应该为参数提供合适的默认值，可以未指定参数值的基础上执行存储过程 一个存储过程最多可以有 1024 个参数 不同存储过程可以使用相同的参数名 使用参数的指导原则 可以使用@参数=值的格式来指定参数，此方法可以按任意顺序来传递参数 对于有默认值的参数在调用存储过程的过程中可以不指定参数值 存储过程可以使用输出参数 --创建存储过程输入两个输入参数，定义一个输出参数 CREATE PROCEDURE Mathadd @m1 int, @m2 int, @result int OUTPUT --定义输出参数 AS SET @result=@m1+@m2 GO 调用过程如下： declare @resultvalues int exec mathadd 12,16,@resultvalues output --输出参数的值赋给变量 print 'The result is: '+convert(char,@resultvalues) 存储过程通过输出参数向调用它的存储过程或客户端返回信息， 通过输出参数， 存储过程的 运行结果可以保留到程序运行结束。
/****** 对象: StoredProcedure [dbo].[up_Page2005V2] 脚本日期: 05/21/2008 11:27:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE[dbo].[up_Page2005V2]@TableName varchar(50), --表名@Fields varchar(5000) ='*', --字段名(全部字段为*)@OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) =Null,--条件语句(不用加where)@pageSize int, --每页多少条记录@pageIndex int=1 , --指定当前为第几页@totalRecord int=0,@TotalPage int output --返回总页数ASBEGINBegin Tran--开始事务Declare@sql nvarchar(4000);if@totalRecord<=0begin--计算总记录数if (@SqlWhere=''or@sqlWhere=NULL)set@sql='select @totalRecord = count(*) from '+ @TableNameelseset@sql='select @totalRecord = count(*) from '+ @TableName+' with(nolock) where '+@sqlWhereEXEC sp_executesql @sql,N'@totalRecord intOUTPUT',@totalRecord OUTPUT--计算总记录数end--计算总页数select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)if (@SqlWhere=''or@sqlWhere=NULL)set@sql='Select * FROM (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId,'+@Fields+' from '+@TableName elseset@sql='Select * FROM (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId,'+@Fields+' from '+@TableName +' with(nolock) where '+@SqlWhere--处理页数超出范围情况if@PageIndex<=0Set@pageIndex=1if@pageIndex>@TotalPageSet@pageIndex=@TotalPage--处理开始点和结束点Declare@StartRecord intDeclare@EndRecord intset@StartRecord= (@pageIndex-1)*@PageSize+1set@EndRecord=@StartRecord+@pageSize-1--继续合成sql语句set@Sql=@Sql+') as t where rowId between '+Convert(varchar(50),@StartRecord) +' and '+Convert(varchar(50),@EndRecord)print@sqlExec(@Sql)---------------------------------------------------If@@Error<>0BeginRollBack TranReturn-1EndElseBeginCommit TranReturn@totalRecord---返回记录总数EndENDGO2005/****** 对象: StoredProcedure [dbo].[up_Page2005V2_Join] 脚本日期: 05/21/2008 11:27:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE[dbo].[up_Page2005V2_Join]@TableName varchar(150), --表名@Fields varchar(5000) ='*', --字段名(全部字段为*)@OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) =Null,--条件语句(不用加where)@pageSize int, --每页多少条记录@pageIndex int=1 , --指定当前为第几页@totalRecord int=0,@TotalPage int output --返回总页数ASBEGINBegin Tran--开始事务Declare@sql nvarchar(4000);if@totalRecord<=0begin--计算总记录数if (@SqlWhere=''or@sqlWhere=NULL)set@sql='select @totalRecord = count(*) from '+ @TableNameelseset@sql='select @totalRecord = count(*) from '+ @TableName+' where '+@sqlWhereEXEC sp_executesql @sql,N'@totalRecord intOUTPUT',@totalRecord OUTPUT--计算总记录数end--计算总页数select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)if (@SqlWhere=''or@sqlWhere=NULL)set@sql='Select * FROM (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId,'+@Fields+' from '+@TableName elseset@sql='Select * FROM (select ROW_NUMBER() Over(order by '+@OrderField+') as rowId,'+@Fields+' from '+@TableName +' where '+@SqlWhere--处理页数超出范围情况if@PageIndex<=0Set@pageIndex=1if@pageIndex>@TotalPageSet@pageIndex=@TotalPage--处理开始点和结束点Declare@StartRecord intDeclare@EndRecord intset@StartRecord= (@pageIndex-1)*@PageSize+1set@EndRecord=@StartRecord+@pageSize-1--继续合成sql语句set@Sql=@Sql+') as t where rowId between '+ Convert(varchar(50),@StartRecord) +' and '+Convert(varchar(50),@EndRecord)print@sqlExec(@Sql)--------------------------------------------------- If@@Error<>0BeginRollBack TranReturn-1EndElseBeginCommit TranReturn@totalRecord---返回记录总数EndEND2000。
SQL Server 存储过程详解◆优点：执行速度更快。
存储过程只在创造时进行编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程执行速度更快。
存储过程用于处理复杂的操作时，程序的可读性更强、网络的负担更小。
使用存储过程封装事务性能更佳。
能有效的放注入，安全性更好。
可维护性高，在一些业务规则发生变化时，有时只需调整存储过程即可，而不用改动和重编辑程序。
更好的代码重用。
◆缺点：存储过程将给服务器带来额外的压力。
存储过程多多时维护比较困难。
移植性差，在升级到不同的数据库时比较困难。
调试麻烦，SQL语言的处理功能简单。
总之复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL 语句不建议使用存储过程。
存储过程定义存储过程是一组Transact-SQL 语句，它们只需编译一次，以后即可多次执行。
因为Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。
触发器是一种特殊的存储过程，不由用户直接调用。
创建触发器时，将其定义为在对特定表或列进行特定类型的数据修改时激发。
存储过程的设计规则CREATE PROCEDURE 定义自身可以包括任意数量和类型的SQL 语句，但以下语句除外。
不能在存储过程的任何位置使用这些语句。
CREATE AGGREGATE、CREATE RULE、CREATE DEFAULT、CREATE SCHEMA、CREATE 或ALTER FUNCTION、CREATE 或ALTER TRIGGER、CREATE 或ALTER PROCEDURE、CREATE 或ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML、USE database_name其他数据库对象均可在存储过程中创建。
可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
SQL Server 2005 导出包含(insert into)数据的SQL脚本(使用存储过程)通常情况下，SQL Server里面的生成SQL脚本，只会包含数据库及表的字段结构，而不会包含表的数据，也就是SQL脚本里面只有Create database，Create table 这样的语句，没有insert into。
因为SQL Server并不包含这个功能，只能靠第三方的代码了。
以下存储过程可以实现：CREATE PROCEDURE pOutputData@tablename sysnameASdeclare @column varchar(1000)declare @columndata varchar(1000)declare @sql varchar(4000)declare @xtype tinyintdeclare @name sysnamedeclare @objectId intdeclare @objectname sysnamedeclare @ident intset nocount onset @objectId=object_id(@tablename)if @objectId is null -- 判断对象是否存在beginprint 'The object not exists'returnset @objectname=rtrim(object_name(@objectId))if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密beginprint 'object not in current database'returnendif OBJECTPROPERTY(@objectId,'IsTable') <> 1 -- 判断对象是否是tablebeginprint 'The object is not table'returnendselect @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 if @ident is not nullprint 'SET IDENTITY_INSERT '+@T ableName+' ON'declare syscolumns_cursor cursorfor select ,c.xtype from syscolumns c where c.id=@objectid order by c.colid open syscolumns_cursorset @column=''set @columndata=''fetch next from syscolumns_cursor into @name,@xtypewhile @@fetch_status <>-1if @@fetch_status <>-2beginif @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理beginset @column=@column+case when len(@column)=0 then'' else ','end+@nameset @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','end+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,charwhen @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,ncharwhen @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetimewhen @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetimewhen @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifierelse @name endendendfetch next from syscolumns_cursor into @name,@xtypeendclose syscolumns_cursordeallocate syscolumns_cursorset @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablenameprint '--'+@sqlexec(@sql)if @ident is not nullprint 'SET IDENTITY_INSERT '+@T ableName+' OFF'GO使用方法：exec UspOutputData 你的表名选择【执行模式】为“以文本显示结果”，然后将运行后的结果存成.sql，加上用SQL Server 生成的数据库脚本就可以了。
SQL Server用户自定义函数用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。
在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为三种类型：(1) 标量函数标量函数返回一个确定类型的标量值，其返回值类型为除 TEXT 、 NTEXT 、IMAGE 、 CURSOR 、 TIMESTAMP 和 TABLE 类型外的其它数据类型。
函数体语句定义在 BEGIN-END 语句内。
在 RETURNS 子句中定义返回值的数据类型，并且函数的最后一条语句必须为 Return 语句。
创建标量函数的格式：Create Function 函数名（参数）Returns 返回值数据类型[With {Encryption|Schemabinding}][AS]BEGINSQL 语句 ( 必须有 Return 子句 )END举例：******************************************************************* CREATE FUNCTION dbo.Max(@a int,@b int)RETURNS int ASBEGINDECLARE @max intIF @a>@b SET @max=@aELSE SET @max=@bReturn @maxEND*******************************************************************调用标量函数可以在 T-SQL 语句中允许使用标量表达式的任何位置调用返回标量值（与标量表达式的数据类型相同）的任何函数。
必须使用至少由两部分组成名称的函数来调用标量值函数，即架构名 . 对象名，如 dbo.Max(12,34) 。
(2) 内联表值函数内联表值型函数以表的形式返回一个返回值，即它返回的是一个表。
