9-1杨梅-计算机等级考试-三级数据库技术-精-第8章 数据库后台编程技术

计算机等级考试三级数据库技术精讲班主讲老师：杨梅,第八章数据库后台编程技术,内容提要：1、掌握存储过程的定义与使用2、掌握用户定义函数的创建与使用3、掌握触发器的定义与使用4、掌握游标的定义与使用,第八章数据库后台编程技术,1、基本概念使用T-SQL语言编写代码时，有两种方式存储和执行代码：（1）在客户端存储代码，通过客户端程序或SQL命令向DBMS发出操作请求，由DBMS将结果返回给用户程序。（2）以子程序的形式将程序模块存储在数据库中，供有权限的用户通过调用反复执行。存储过程：即存储在数据库中供所有用户程序调用的子程序。,第一节存储过程,存储过程分为三类：系统存储过程用户自定义存储过程扩展存储过程,第一节存储过程,用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。本节将详细介绍用户自定义的存储过程。以下三个存储过程即为用户自定义存储过程：,第一节存储过程,扩展存储过程是SQLServer可以动态装载并执行的动态链接库(DLL)。扩展存储过程使您得以使用象C这样的编程语言创建自己的外部例程。对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。,第一节存储过程,存储过程的优点：极高的执行效率。增强代码的重用性和共享性。使用存储过程可以减少网络流量。使用存储过程保证安全性。在大型数据库中，应用程序访问数据库的最主要方式就是存储过程。存储过程可以在系统启动时自动执行。,第一节存储过程,2、创建、执行和删除存储过程存储过程定义包含两个主要组成部分：过程名称及其参数的说明；过程的主体（其中包含执行过程操作的Transact-SQL语句）。创建存储过程的语法格式如下：,第一节存储过程,CREATEPROCEDUREprocedure_name;number/*定义过程名parameterdata_type/*定义参数的类型VARYING=defaultOUTPUT/*定义参数的属性,n1WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION/*执行的操作ASsql_statement,n2,创建存储过程：,第一节存储过程,EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,nWITHRECOMPILE,执行存储过程：,第一节存储过程,【实例1】建立查询某个指定地区购买了单价高于指定价格商品的顾客购买信息，列出顾客姓名，购买商品名，单价，购买日期，会员积分，其中默认地区是“长沙岳麓区”CREATEPROCEDUREp_custbuyareavarchar(20)=长沙岳麓区，PricemoneyASSELECTFROMJOINWHEREAddress=areaANDSaleUnitPricePrice执行：EXECp_custbuyPrice=1000,第一节存储过程,【实例2】建立统计某个指定地区和指定性别的顾客人数和平均年龄的存储过程，并将统计结果作为输出参数返回。CREATEPROCEDUREp_custcoutareavarchar(20)，sexcharcountintoutputavg_ageintoutputASSELECTcount=COUNT(*),ave_age=AVG(YEAR(GETDATE()-YEAR(BIRTHDATE),第一节存储过程,FROMTable_CustomerWHEREAddress=areaANDSex=sex执行：DECLARExint,yintEXECp_custcount长沙岳麓区，F,xoutput，youtputSELECTxAS人数，yAS平均年龄,第一节存储过程,删除存储过程：DROPPROCEDURE实例：DROPPROCEDUREp_custbuyDROPPROCEDUREp_custcoutDROPPROCEDUREp_update,第一节存储过程,用户定义函数：类似于编程语言中的函数，其结构与存储过程类似，但函数必须有一个RETURN子句，用于返回函数值。两类用户定义函数：标量函数和表值函数。前者返回单个数据值，表值函数返回一个表。,第二节用户定义函数,1、创建和调用标量函数定义标量函数：CREATEFUCTIONRETURNSreturn_data_typeASBEGIN【函数体】RETURNscalar_expressionEND,第二节用户定义函数,【实例】创建查询指定商品类别的商品种类数的标量函数。,第二节用户定义函数,CREATEFUCTIONdbo.f_GoodsCount(classvarchar(10)RETURNintASBEGINDECLARExintSELECTx=count(*)FROMTable_GoodsClassaJOINTable_GoodsbONa.GoodsClassID=b.GoodsClassIDWHEREGoodsClassName=classRETURNxEND,第二节用户定义函数,调用标量函数：注意：调用时需要提供函数拥有者名和函数名；可以在任何出现表达式的SQL语句中调用类型一致的标量函数。,第二节用户定义函数,【实例】查询“服装”类商品的名称和种类数量SELECTGoodsNameAS商品名，dbo.f_GoodsCount(服装)AS种类数FROMWHERE,第二节用户定义函数,2、创建和调用内嵌表值函数创建内联表值函数：CREATEFUCTIONRETURNSTABLEASRETURN(select_stmt)参数说明：select_stmt是定义内联表值函数返回值的单个SELECT语句；表值函数没有返回变量，没有函数体，只返回一个查询结果。,第二节用户定义函数,调用内联表值函数：使用内联表值函数与视图类似，其作用相当于带参数的视图。,第二节用户定义函数,【实例】创建查询指定类别的商品名和单价的内联表值函数。CREATEFUCTIONf_GoodsInfo(classchar(10)RETURNSTABLEASRETURN(SELECTGoodName,SaleUnitPriceFROMTable_GoodClassaJOINTable_GoodsbONa.GoodsClassID=B.GoodsClassIDWHEREGoodClassName=class)调用：SELECT*FROMdbo.f_GoodsInfo(服装),第二节用户定义函数,3、创建和调用多语句表值函数CREATEFUCTIONRETURNSreturn_variableTABLEASBEGIN【函数体:SQL语句】RETURNEND调用建多语句表值函数:在SELECT的FROM子句中使用。,第二节用户定义函数,【实例】创建查询指定类别的商品名、单价、生产日期和商品种类的多语句表值函数。CREATEFUCTIONf_GoodsDatails(classvarchar(20)RETURNSf_GoodsDatailsTABLE（商品名varchar（50），单价money，生产日期datetime，种类数int）AS,第二节用户定义函数,BEGININSERTINTOf_GoodsDatailsSELECTGoodName,SaleUnitPrice，ProductionDate，dbo.f_GoodsDatails(class)FROMTable_GoodClassaJOINTable_GoodsbONa.GoodsClassID=B.GoodsClassIDWHEREGoodClassName=class