子项目7存储过程和函数的.

子项目7存储过程和函数的使用7.1Transact-SQL编程知识Transact-SQL直接来源于SQL，是SQLServer2005在SQL的基础上添加了流程控制语句的扩展。Transact-SQL是SQLServer2005功能的核心。不管应用程序的用户界面是什么形式，只要与数据库服务器交互使用，最终都必然表现为Transact-SQL。7.1.1批处理批处理是将一组Transact-SQL语句作为一个整体进行编译和执行。一组语句可以是一条语句，也可以是多条语句，最终以GO作为结束标志。当编译器读到GO时，就会把它之前，直到上一个GO之间的语句当做一组批处理语句，发给服务器执行。GO是一个标志性语句，本身本不被执行。7.1.2程序注释语句（1）单行注释（--）。语法格式为：--注释文本，例如上面的代码“--查询经费本信息”。（2）块注释。注释符号与C语言中的注释相同，即“/*…*/”，其中，“/*”为注释的开头，“*/”为注释的结尾。7.1.3局部变量与全局变量1.局部变量局部变量是用户自定义的变量，它的作用范围仅在程序内部。局部变量的名称是用户自定义的，局部变量的命名必须符合SQLServer标识符命名规则。局部变量的声明必须使用DECLARE语句，具体的语法格式如下：DECLARE{@varaible_name,datatype[,…n]}2.全局变量全局变量是SQLServer2005系统内部事先定义好的变量，不需要用户参与定义，对用户而言是只读的。其作用的范围并不局限于某一程序，而是程序随时可以调用。全局变量通常用于存储一些SQLServer2005的配置设定值和效能统计数据。7.1.4流程控制语句1.IF…ELSE语句2.WHILE语句7.2存储过程存储过程是一组为了完成特定功能的Transact-SQL语句集，经编译后存储在数据库中。存储过程是数据库的一个重要对象，任何一个设计良好的为数据库应用程序都应该用到存储过程。可以使用Transact-SQLEXECUTE语句来运行存储过程。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。7.2.2创建和执行简单的存储过程创建存储过程的基本语法如下：CREATEPROCEDURE存储过程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL语句其中，WITHENCRYPTION为对存储过程加密；WITHRECOMPILE为对存储过程重新编译。7.2.3创建复杂的存储过程声明带有参数的存储过程的语法格式如下：CREATEPROCEDURE存储过程名@参数名数据类型[=默认值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL语句其中，“@参数名”是局部变量，前面一定要加“@”符号，在声明局部变量一样，后面一定要有数据类型。同时，多个参数之间用逗号隔开。7.2.4存储过程的管理和维护（1）查看存储过程查看存储过程的定义有两种方法，一种是使用Transact-SQL查看存储过程的定义，另一种是使用SQLServerManagementStudio查看存储过程的定义。（2）修改存储过程修改已创建的存储过程已有图形界面方式和使用Transact_SQL语句，在7.2.2节中，创建和执行简单的存储过程里，已经提到如何使用图形界面工具修改存储过程。在这里，主要说明如何使用Transact_SQL语句修改存储过程。（3）删除存储过程当不再需要某个存储过程时，可将其删除。删除存储过程已有两种方法：一种是使用Transact-SQL语句删除存储过程，另一种是使用SQLServerManagementStudio删除存储过程。7.3用户自定义函数函数是具有内部运算功能的语法元素，在数据查询和程序设计过程中，常常需要调用函数实现一些复杂功能。在SQLServer2005里内置了许多函数，用以简化大量的复杂的数据和修改操作，巧妙地利用系统函数或自定义函数，可以实现复杂的数据查询功能，简化程序设计复杂性，减少代码长度，提高数据查询的效率。7.3.1字符串函数例7.1在用户表“tbuserinfo”中查询用户编号为6的用户名字的长度。在查询分析器中运行如下命令：SELECTLEN(username)FROMtbuserinfoWHEREuserid=6GO例7.2当用户登录财务管理系统时，如图7-27所示，需要验证用户的用户名和密码，验证成功后才允许登录，编写存储过程实现该功能。USE[FM]GOCREATEPROCEDURE[dbo].[pc_Login1](@usernameVARCHAR(1024),@userpwdVARCHAR(1024))ASSET@username=ltrim(@username)--清除左边的空格SET@username=rtrim(@username)--清除右边的空格SET@userpwd=ltrim(@userpwd)--清除左边的空格SET@userpwd=rtrim(@userpwd)--清除右边的空格BEGINSELECT*FROMtbuserinfoWHEREusername=@usernameanduserpwd=@userpwdanduserflag='1'END7.3.2日期和时间函数日期和时间函数主要用来显示有关日期和时间的信息。日期和时间函数执行算术运算与其它函数一样，也可以在SQL语句的SELECT和WHERE子句以及表达式中使用。例7.3实验室财务管理系统的新增财务处事件模块如图7-29所示。将新增的各项和表“tbfevent”中的部分字段相对应，编写存储过程，实现新增的功能。CREATEPROCEDURE[dbo].[pc_fevent1](@f_idVARCHAR(1024),@useridVARCHAR(1024),@fe_abstractVARCHAR(1024),@fe_incomefloat,@fe_outputfloat,@fe_memoVARCHAR(1024),@fe_flagvarchar(1))ASdeclare@table_nameVARCHAR(20),@PKcolumn_nameVARCHAR(20),@idvarchar(20),@fe_datedatetimeset@table_name='tbfevent'set@PKcolumn_name='fe_id'execpc_GetID@table_name,@PKcolumn_name,@max_id=@idoutput--pc_GetID的定义在7.2.3set@fe_id=@idset@fe_date=getdate()begininsertintotbfevent(f_id,userid,fe_id,fe_abstract,fe_income,fe_output,fe_date,fe_memo,fe_flag)values(@f_id,@userid,@fe_id,@fe_abstract,@fe_income,@fe_output,@fe_date,@fe_memo,@fe_flag)end7.3.3数据类型转换函数一般情况下，SQLServer2005会自动完成数据类型转换，这种转换叫做隐式转换。但是，有些类型就不能直接转换，比如INT和CHAR类型，则需要显式转换。显式转换函数共有两个，分别为CAST函数和CONVERT函数7.3.4用户自定义函数1.标量函数创建标量函数的语法格式如下：CREATEFUNCTION[所有者名称.]函数名称[（{@参数名称[AS]标量数据类型=[默认值]}[…n]）]RETURNS标量数据类型[AS]BEGIN函数体RETURN标量表达式END2.内嵌表值函数内嵌表值函数没有由BEGIN…END语句括起来的函数体，其返回的表从一个位于RETURN子句中的SELECT命令段从数据库筛选出来。内嵌表值函数功能相当于一个参数化的视图。创建内嵌表值函数的语法格式如下：CREATEFUNCTION[所有者名称.]函数名称[（{@参数名称[AS]标量数据类型=[默认值]}[…n]）]RETURNSTABLE[AS]RETURN[(SELECT语句)]3.多语句表值函数多语句表值函数可以看作标量函数和内嵌表值函数的结合体。它的返回值是一个表，但它与标量函数一样有一个用BEGIN…END语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选、合并、弥补了内嵌表值函数的不足。创建多语句表值函数的语法格式如下：CREATEFUNCTION[所有者名称.]函数名称[（{@参数名称[AS]标量数据类型=[默认值]}[…n]）]RETURNS@表名变量TABLE表的定义[AS]BEGIN函数体RETURNEND7.4项目拓展(1)SQL系统函数该篇文章主要讲解了常用的SQLServer系统函数，主要包括字符转换函数、去空格函数、取子串函数、字符串比较函数、数据类型转换函数、日期函数、数学函数、系统函数。（2）SQL语句大全(经典珍藏版).pdf该篇文章主要讲解了常用的SQL语句、功能及其使用方法。