存储过程和函数.ppt

批处理：有助于减少网络堵塞（network traffic)多个命令一起被提交和执行 批处理(batch)是一组一起提交给SQL Server执行的SQL语句在结尾使用GO命令以发送SQL语句到一个SQL Server实例当编译器读取到GO语句时，会把GO前面的语句当成一个批处理，并把这些语句打包发送给服务器。特点：批一次性的发送到SQL SERVER服务器执行，服务器将批处理语句编译成一个可执行单元，这种单元称为执行计划（execution plan)举例,创建批处理,说明：如果批处理中出现任何编译错误(compilation error)，导致执行计划无法编译，从而批处理中的任何语句都无法执行。如果编译完成，在运行中出错，(run-time error)则执行停止，出错之前执行的语句不受影响。,变量,使用变量存储值输入数据，中间值，最后结果使用PRINT语句显示用户定义的消息和变量的值如果变量不是字符串类型，要通过convert函数进行转换变量类型：全局变量（Global variables）记录服务器的活动状态是事先定义好的变量，不能由用户参与定义，只读用标记(举例）,创建局部变量DECLARE variable_name data_typeDECLARE MyCounter int多个变量，用，隔开DECLARE LastName nvarchar(30),FirstName nvarchar(20),State nchar(2)变量的作用域从声明变量的地方开始到声明变量的批处理或存储过程的结尾。,创建局部变量,第一次声明变量时，其值设置为 NULL 赋值的方法：SET 语句 SET avgscore=60.0SELECT 语句的选择列表中当前所引用值为变量赋值,为局部变量赋值,多行注释/*.*/单行注释-,注释,以下规则适用于批处理：CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其他语句组合使用。批处理必须以 CREATE 语句开始。所有跟在该批处理后的其他语句将被解释为第一个 CREATE 语句定义的一部分。不能在同一个批处理中更改表，然后引用新列。如果 EXECUTE 语句是批处理中的第一句，则不需要 EXECUTE 关键字。如果 EXECUTE 语句不是批处理中的第一条语句，则需要 EXECUTE 关键字。,创建批处理的指导方针,课间思考,下面哪个语句可以被用在批处理中?1.CREATE FUNCTION 2.CREATE RULE 3.DECLARE,答案：3.DECLARE,允许你使用下面的结构来控制语句的流程：IFELSE 语句CASE 语句WHILE 语句,程序块语句,语法：BEGIN sql_statement|statement_block END 在处理时，整个语句块看作是一条SQL语句。,程序块语句Begin.end,IFELSE 语句：基于布尔表达式的结果执行特定的操作语法：IF boolean_expression sql_statement|statement_block ELSE boolean_expression sql_statement|statement_block让我们看看如何,使用结构（续）,DECLARE Rate moneySELECT Rate=Rate FROM HumanResources.EmployeePayHistoryWHERE EmployeeID=23IF Rate 15PRINT Review requiredELSEBEGINPRINT Review not required PRINT your rate=PRINT RateEND,CASE 语句：评估条件的一个列表并且返回可能结果中的一个语法：CASE WHEN boolean_expression THEN expressionWHEN boolean_expression THEN expression.ELSE expressionEND让我们看看如何,使用结构（续）,SELECT EmployeeID,Marital Status=CASE MaritalStatusWHEN M THEN MarriedWHEN S THEN SingleELSE Not specifiedENDFROM HumanResources.Employee,WHILE 语句：重复执行只要给定的条件为真使用BREAK 和 CONTINUE语句来控制WHILE循环中的语句语法：WHILE boolean_expression sql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUEBREAK 导致从WHILE 循环中退出。将执行出现在 END 关键字（循环结束的标记）后面的任何语句。CONTINUE 使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。,使用结构（续）,Example:(WHILE)WHILE(SELECT AVG(Rate)+1 from HumanResources.EmployeePayHistory)127BREAKELSECONTINUEEND,SQL Server 中的错误可以用以下两种方式处理：使用TRY-CATCH 结构 使用RAISERROR语句,处理错误和异常,在以下情形下使用TRY-CATCH 结构：Try 块包含一组T-SQL语句。如果TRY块的语句中发生任何错误，控制将传递给CATCH块。CATCH块包含另外一组语句，这些语句在错误发生时执行。BEGIN TRY sql_statement|statement_block END TRY BEGIN CATCH sql_statement|statement_block END CATCH;TRY-CATCH在以前的版本中是没有的。,处理错误和异常（续）,在CATCH块中，可以使用以下的系统函数来确定关于错误的信息：ERROR_NUMBER()返回错误号。ERROR_MESSAGE()返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名称或时间）提供的值。ERROR_SEVERITY()返回错误严重性。ERROR_STATE()返回错误状态号。ERROR_LINE()返回导致错误的例程中的行号。ERROR_PROCEDURE()返回出现错误的存储过程或触发器的名称。,处理错误和异常（续）,RAISERROR：被用于返回消息到商业程序使用与系统错误或数据库引擎产生的警告消息相同的格式也可能返回用户定义的错误消息让我们看看如何,处理错误和异常（续）,错误由表维护。每一个错误代码都有相应的级别和描述。错误定义的级别从0到25。20以上的错误代表重大错误，通常意味着该错误会导致存储进程立刻终止，并且所有的客户连接都要重新初始化。非关键性错误只是禁止掉当前运行的程序行，并继续执行。,处理错误和异常（续）,语法RAISERROR(msg_id msg_str,severity,state,argument,.n)WITH option,.n,课间思考,课间思考,哪个系统函数返回错误消息的文本当用在CATCH块中的时候?,答案：ERROR_MESSAGE(),课间思考,你如何在批处理中返回用户定义的错误消息?,答案：使用RAISERROR语句,批处理是一系列一起提交到服务器执行的SQL语句。你可以使用变量存储一个临时值。你可以使用PRINT语句来显示用户定义消息或屏幕上一个变量的内容。你可以在批处理中使用注释实体给代码写注释。你可以使用IFELSE语句以条件执行SQL语句。CASE语句求一系列条件的值并且返回各种可能结果中的一个。你可以在批处理中使用WHILE语句以允许一系列T SQL语句重复执行只要给定条件为真。BREAK语句导致从WHILE循环中退出。,小结（续）,小结（续）,CONTINUE语句导致WHILE循环重启，忽略循环中CONTINUE语句后的任何语句。在批处理中处理错误的两种方法是：TRY-CATCH 结构RAISERROR 语句,存储过程：（stored procedurre)是数据库中保存的预编译的数据库对象批处理是临时的存储过程可以极大的减少网络需求 系统存储过程：sp_ 不要以 sp_ 为前缀创建任何存储过程。sp_ 前缀是 SQL Server 用来指定系统存储过程的,存储过程,存储过程的优点：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以重复使用,可减少数据库开发人员的工作量 安全性高,可设定只有某此用户才具有对指定存储过程的使用权,存储过程,存储过程：使用CREATE PROCEDURE语句创建存储过程使用EXECUTE 语句实现存储过程语法：CREATE PROCEDURE proc_nameASBEGINsql_statement1sql_statement2END让我们看看如何,创建存储过程,CREATE PROCEDURE prcDeptASBEGINSELECT Name FROMHumanResources.DepartmentENDExecuting the procedureEXECUTE prcDept,存储过程编译的过程：解析：组件被解析分解：检查引用对象的存在性存储过程的名字存储在sysobjects中，代码存储在syscomments表中编译存储过程，创建执行计划，并保存在缓存中首次执行的时候，读取执行计划并优化，运行。以后执行直接从缓存中读取，提高了性能。,创建存储过程,存储过程的依赖关系：sp_dependssp_depends object_name过程代码：sp_helptext注意：不能在单个批处理中将create procedure与其它SQL语句一起使用执行存储过程：EXEC proc_name如果在批处理的第一行执行存储过程，可省略EXEC。,创建存储过程,存储过程：使用 ALTER PROCEDURE语句修改存储过程语法：ALTER PROCEDURE proc_name使用DROP PROCEDURE 语句删除存储过程语法：DROP PROCEDURE proc_name让我们看看如何,创建存储过程（续）,ALTER PROCEDURE prcDeptASBEGINSELECT DepartmentID,Name FROM HumanResources.DepartmentEND,课间思考,你将使用哪个命令修改过程?,答案：ALTER PROCEDURE,课间思考,哪个系统定义的表存储所有存储过程的名称?,答案：sysobjects,带参数的存储过程：被用于在运行时传递值到存储过程这些值通过标准变量被传递。传递值的参数被定义为输入参数参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据输入参数允许调用方将数据值传递到存储过程或函数。输出参数允许存储过程将数据值传递回调用方。每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。,创建带参数的存储过程,CREATE PROC prcListEmployee title char(50)AS BEGINPRINT List of EmployeesSELECT EmployeeID,LoginID,TitleFROM HumanResources.Employee WHERE Title=titleENDExecute by passing a parameterEXECUTE prcListEmployee Tool Designer,存储过程：也可以从过程返回作为输出的值为了指定一个参数为输出参数，你可以使用OUTPUT关键字可用return返回整形值，缺省情况下，0代表成功，非零代表失败。语法：CREATE PROCEDURE procedure_name parameter data_type OUTPUTAS sql_statement.n让我们看看如何,从存储过程返回值,CREATE PROCEDURE prcGetEmployeeDetail EmpId int,DepId int OUTPUT,DepName char(50)OUTPUT,ShiftId int OUTPUTASBEGINIF EXISTS(SELECT*FROM HumanResources.Employee WHERE EmployeeID=EmpId)BEGINSELECT DepId=d.DepartmentID,DepName=Name,ShiftId=ShiftIDFROM HumanResources.Department d JOIN HumanResources.EmployeeDepartmentHistory hON d.DepartmentID=h.DepartmentIDWHERE EmployeeID=EmpIdRETURN 0ENDELSERETURN 1END,存储过程：可能需要在另一个过程中使用一个过程返回的值调用或执行另一个过程的过程被称为调用过程被调用或被调用过程执行的过程被称为被调用过程说明：过程被在另一个过程被调用的时候你必须使用EXEC关键字 让我们看看如何补充：ROWCOUNT：返回语句影响的行数。,从另一个过程调用一个过程,演示：创建存储过程,问题描述：你是AdventureWorks,Inc的数据库开发人员。人力资源部门需要修改员工的支付详情。你需要创建一个过程，它得到百分比值，你需要通过这个值增加支付率。而且，你需要确保仅修改那些员工支付率在最近六个月没有修改的员工的薪水。,解决方案：为了解决上述问题，你需要完成下面的任务：1.创建一个存储过程。2.执行存储过程。3.验证结果。,演示：创建存储过程（续）,在 SQL Server 中使用用户定义函数有以下优点：允许模块化程序设计。只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。执行速度更快。与存储过程相似，Transact-SQL 用户定义函数通过缓存计划并在重复执行时重用它来降低 Transact-SQL 代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。减少网络流量。基于某种无法用单一标量的表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，此函数便可以在 WHERE 子句中调用，以减少发送至客户端的数字或行数。,函数,标量函数用户定义标量函数返回在 RETURNS 子句中定义的类型的单个数据值 表值函数用户定义表值函数返回 table 数据类型 注意：函数不能对具有函数外作用域（例如数据库表的修改）的资源状态的任何永久性更改,函数类型,标量函数包含以下成分：带可选模式/拥有者名称的函数名输入参数名和数据类型可应用与输入参数的选项返回参数数据类型和选项名应用与返回参数的选项一个或多个T-SQL语句使用CREATE FUNCTION 语句创建标量函数。,创建 UDFs,语法：CREATE FUNCTION schema_name.function_name(parameter_name AS type_schema_name.parameter_data_type=default,.n)RETURNS return_data_type WITH,.n AS BEGIN function_body RETURN scalar_expressionEND;让我们看看如何,创建 UDFs（续）,表值函数：返回一个表作为输出，它可以来自SELECT语句的一部分可以替代视图,视图受限于单个 SELECT 语句，而用户定义函数可包含更多语句 使用表数据类型存储一系列行有以下两种类型：内联表值函数对于内联表值函数，没有函数主体 多语句表值函数 对于多语句表值函数，在 BEGIN.END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中,创建 UDFs（续）,确定函数和非确定函数,根据函数返回的结果集，分为确定性函数如果对于一组特定的输入值和数据库状态，函数始终返回相同的结果，则该用户定义的函数就是确定的。非确定性函数使用同一组输入值重复调用非确定性函数，返回的结果可能会不同 Getdate(),课间思考,哪个函数类型返回单一值?,答案：标量函数,问题描述：作为AdventureWorks,Inc.的数据库开发人员，你需要创建一个函数，它接收员工的员工ID并且返回下面的详情：Employee ID员工的姓名员工的职务在员工下面工作的其他员工的数量你将如何创建函数?,演示：创建函数,解决方案：为了解决上述问题，你需要完成下面的任务：1.创建一个函数。2.执行函数以验证结果。,演示：创建函数（续）,一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。函数中有些系统函数不可以使用，如getdate()对于存储过程来说可以返回任意个输出参数，而函数只能返回值或者表对象,且只有一个。存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。函数是2k以后新加入的功能。,存储过程和函数的区别,在本章中，你已经学到：存储过程是各种T-SQL语句的集合，它被存储在一个名字下，并且作为一个单元执行。存储过程可以使用CREATE PROCEDURE语句被创建。存储过程允许你声明参数、变量和使用T-SQL语句并且编程逻辑。存储过程提供更好的性能、安全性和准确性并且减少网络拥塞。使用CREATE PROCEDURE语句创建存储过程。,小结,存储过程通过输入参数接收数据。存储过程通过输出参数或返回语句返回数据。存储过程可以使用EXECUTE语句执行。存储过程使用ALTER PROCEDURE语句进行修改。用户定义函数是一个数据库对象，它包含一系列T-SQL语句。用户定义函数可以返回一个单一标量值或结果集。,小结（续）,UDFs有两种类型：标量函数和表值函数。标量函数接收单一值和返回单个值。表值函数返回一个表作为输出，它可能来自SELECT语句的一部分。,小结（续）,