第07章：存储过程、事务、视图、索引

第7章存储过程、事务、视图、索引本章内容1索引2视图3事物3存储过程讲解时间：15分钟实践时间：30分钟索引：内容预览理论索引实践练习创建索引索引SQLServer数据访问方法表扫描法：从表的起始处逐行查找，直至找到符合查询条件的记录为止。索引法：系统会通过遍历索引结构来查找行的存储位置，相比表扫描法，效率大为提高。索引的作用增强数据记录的唯一性。可以大大加快数据检索的速度。加速表与表之间的连接，这在实现数据参照完整性方面有特殊的意义。使用ORDERBY和GROUPBY子句进行检索数据时，可以显著减少在查询中排序和分组所占用的时间。索引索引使用的代价维护索引，在对数据进行插入、更新和删除操作时会消耗系统时间。在建立索引时，由于需要复制数据，也会消耗系统的时间和空间。可在以下场合创立索引主键、外键。需要在指定范围快速或频繁查询的列。需要排序的列。在聚合函数中使用的列。索引的使用场合以存储结构区别，有“聚集索引〞〔ClusteredIndex，也称聚类索引、簇集索引〕和“非聚集索引〞〔NonClusteredIndex，也称非聚类索引、非簇集索引〕。以数据的唯一性区别，有“唯一索引〞〔UniqueIndex〕和“非唯一索引〞〔NonUniqueIndex〕。以索引列的个数区分，那么有“单列索引〞与“多列索引〞。索引分类索引聚集索引非聚集索引索引唯一索引非唯一索引索引单列索引多列索引聚集索引特点：将数据行的值在表内排序，并存储对应的数据记录，使数据表物理顺序与索引顺序相一致。当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序依据。一个表仅能建立一个聚集索引，但该索引可以包含多个列〔组合索引〕主键默认为聚集索引。聚集索引非聚集索引特点：完全独立于数据行的结构。非聚集索引中的数据排列顺序并非表中数据的排列顺序。一个表可以拥有多个非聚集索引，每个非聚集索引提供访问数据的不同排序顺序。在默认情况下，SQLServer所建立的索引是非聚集索引。非聚集索引聚集索引VS非聚集索引

插入数据速度查询数据速度索引的数量所需空间聚集索引慢快一表一个少非聚集索引快慢一表可以多个多系统自动创立索引系统在创立表中的其他对象时，可以附带创立新索引。通常情况下，在创立UNIQUE约束或PRIMARYKEY约束时，SQLServer会自动为这些约束列创立聚集索引。用户创立索引使用SQLServerManagementStudio的对象资源管理器。利用T-SQL语句CREATEINDEX。创立索引现使用对象资源管理器在商品表的“团购价〞字段上建立非聚集索引使用SSMS创立索引使用SSMS创立索引使用SSMS创立索引使用SSMS创立索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEXindex_name

ONtable_name(column_name…)使用T-SQL创立索引在商品表的“商品编号〞和“标题〞字段上建立非聚集索引CREATENONCLUSTEREDINDEXidx_Product_productID_titleONProduct(productID,title)练习稳固上述语法客户在预订线路时，经常需要根据线路名进行查询，并按价格排序显示。如何使用索引技术提高检索。通常在主外键、频繁查询的列、排序列和聚合函数列的字段上建立索引。依据需求，可在线路表的线路名和价格上建立索引。系统需要经常获取每个客户预订线路的数量，如何使用索引技术提升查询效率?实践练习实践时间：30分钟讲解时间：20分钟实践时间：40分钟视图：内容预览理论视图实践练习使用视图视图(View)是一种数据库对象，是一个从一张表、多张表或视图中导出的虚表。视图的结构和数据是对数据表进行查询的结果。视图仅存放视图的定义，不存放视图所对应的数据。视图如果基表中的数据发生变化，那么从视图中查询出的数据也随之改变。视图的特点：关注点聚焦简化操作定制数据合并分割数据提供平安机制视图使用SSMS管理平台，创立团购价高于400元的商品信息视图，用于查看商品类型名、商品标题和团购价。视图创立完成后，点击“保存〞并命名为“v_Product_Category〞。通常约定视图名以“v〞作为前缀，并用下划线连接基表名。SELECT*FROMv_Product_Category。使用SQLServer平台创立视图使用SQLServer平台创立视图使用SQLServer平台创立视图使用SQLServer平台创立视图创立视图的语法CREATEVIEW[schema_name.]view_name

[(column[,...n])]

ASSELECT_statement使用T-SQL语句创立视图使用T-SQL语句创立团购价高于400元的商品信息视图，用于查看商品类型名、商品标题、团购价、地区名和商店名。使用T-SQL平台创立视图CREATEVIEWv_Product_Category_Area_Shop

ASSELECTp.title商品标题,c.categoryName类型,p.currentPrice团购价,a.areaName地区,s.shopName商店

FROMProductp,Categoryc,Areaa,ShopsWHEREp.categoryID=c.categoryIDANDp.areaID=a.areaIDANDp.shopID=s.shopIDANDp.currentPrice>400视图中的列不仅可以是基表的数据列，还可以是计算列或聚合函数列。例：创立一个视图，用于生成每个订单的金额，并利用该视图更新订单表中相应订单的金额创立复杂查询视图生成每个订单的金额，需联接订单表、订单详细表和商品表，并根据订单分组、汇总每个订购明细的金额。在子查询中使用视图用于更新订单表金额。CREATEVIEWv_OrdersAmount

ASSELECTod.ordersID,SUM(p.currentPrice*od.quantity)calAmountFROMOrderso,OrdersDetailod,ProductpWHEREo.ordersID=od.ordersIDANDductID=ductIDGROUPBYod.ordersIDUPDATEOrdersSETamount=A.calAmountFROM(SELECTordersID,calAmountFROMv_OrdersAmount)AWHEREOrders.ordersID=A.ordersID创立复杂查询视图练习稳固上述语法创立一个视图，用于获取所有线路的预订次数。线路表Line提供了线路根本信息，订单线路表OL_Detail提供了线路预订情况。使用这两张表创立一个视图，获取所有线路的预订次数。创立一个视图，用于获取所有顾客的线路预订信息。为获取每个客户预订线路的详细信息，需联接客户表Customer、订单客户表OC_Detail、订单线路表OL_Detail和线路表Line。使用这四张表创立一个视图，即可查询所有客户的预订信息。创立一个视图，用于获取每一种线路类型的预订次数。创立一个视图，用于获取所有预订“国内短线游〞客户的根本信息。实践练习实践时间：40分钟讲解时间：20分钟实践时间：40分钟事物：内容预览理论事务实践练习使用事务为什么需要事物事务是一个由用户所定义的完整的工作单元，一个事务内的所有语句作为一个整体来执行，即或者全部执行，或者全部不执行。当遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。转账交易过程中的转出和转入可设计为一个数据库事务。什么是事物原子性〔atomic、atomicity)：事务必须是一个原子工作单元。对于其数据修改，或者全都执行，或者全都不执行。一致性〔consistent、consistency)：事务在完成时，必须使所有的数据都保持一致的状态，即事务执行的结果必须是使数据库从一个一致性状态转换为另一个一致性状态。隔离性〔isolation)：由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。持久性〔duration、durability〕：事务完成后，其对于系统的影响是永久性的。事务的特性显式事务：显式地定义事务的开始和事务的结束。BEGINTRANSACTION：标识一个事务的开始，即启动事务。COMMITTRANSACTION：提交事务。标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION：回滚事务，即事务内所修改的数据被回滚到事务执行前的状态。显式事务隐式事务在当前事务提交或回滚后，SQLServer自动开始下一个事务。隐式事务不需要使用BEGINTRANSACTION语句启动事务，仅需要用户使用COMMITTRANSACTION或ROLLBACKTRANSACTION语句提交或回滚事务。在提交或回滚后，SQLServer自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONSON语句可使SQLServer进入隐式事务模式。隐式事物自动提交事务将每个T-SQL语句都视为一个事务，如果成功执行，那么自动提交；如果出现错误，那么自动回滚。它是SQLServer默认的事务模式。自动提交事务在数据库实际开发过程中，显式事务用到得最多。因为自动提交事务无法将多条语句作为一个独立的逻辑单元来处理，而隐式事务无法明确控制事务的开始位置。例如：客户“雷亚波〞在美淘网购置了两份“宝岛牛排〞和一双“花花公子登山鞋〞，请使用事务技术维护相应的数据表以反映该购置业务。事务的使用客户购置商品，除在订单表〔Orders〕上生成一条记录外，还应在订单明细表〔OrdersDetail〕上生成两条记录。因而需要在上述这两张表上分别进行“INSERT〞操作。两个操作是一个业务整体，仅允许全部成功，如果其中有一个失败，那么撤销所有的操作。使用数据库事务可以很好地实现该业务。事务·参考代码·(1)变量声明DECLARE@ordersIDint,@productIDintDECLARE@customerIDint=999DECLARE@ordersTimevarchar(10)='2023-01-01'DECLARE@deliveryTimevarchar(10)='2023-01-03'事务·参考代码·(2)主体代码BEGINTRANSACTION--启动事务INSERTINTOOrders(customerID,ordersDate,deliveryDate)--添加订单表记录VALUES(@customerID,CONVERT(date,@ordersTime),CONVERT(date,@deliveryTime))IF@@ERROR=0--添加订单表记录成功BEGINSELECT@ordersID=MAX(ordersID)FROMOrders--获取新增订单的订单编号SELECT@productID=productIDFROMProductWHEREtitle='宝岛牛排'INSERTINTOOrdersDetail(ordersID,productID,quantity)VALUES(@ordersID,@productID,2)--添加2份“宝岛牛排〞到订单明细表SELECT@productID=productIDFROMProductWHEREtitle='花花公子登山鞋'INSERTINTOOrdersDetail(ordersID,productID,quantity)VALUES(@ordersID,@productID,1)--添加1双“花花公子登山鞋〞到订单明细表IF@@ERROR=0--添加订单表和订单明细表记录成功BEGINPRINT‘添加订单明细成功'COMMITTRANSACTION--提交事务ENDELSEBEGINPRINT'添加订单明细失败'ROLLBACKTRANSACTION--回滚事务ENDENDELSEBEGINPRINT'添加订单失败'ROLLBACKTRANSACTION--回滚事务END练习稳固上述语法在不违背外键约束的前提下删除“出境游〞线路类型信息。线路类型表LineType与线路表Line是一对主从表，线路类型表是主表，线路表是从表，两者之间存在外键约束。为删除“出境游〞线路类型信息，只能先将线路表中类型为“出境游〞的类型编号赋值为NULL，然后再删除线路类型表的“出境游〞记录。这两个操作是一个整体，或者全部成功，或者全部撤销。可使用数据库事务技术实现该业务。实践练习实践时间：30分钟讲解时间：15分钟实践时间：30分钟存储过程:内容预览理论存储过程介绍系统存储过程实践练习使用系统存储过程SQLServer中的T-SQL语言为了实现特定的任务，而将一些需要屡次调用的固定操作编写为子程序，并集中以一个存储单元的形式存储在效劳器上，由SQLServer数据库效劳器通过子程序名来调用，这些子程序即存储过程。存储过程是一种数据库对象，存储于数据库内，可由应用程序通过一个调用执行，具有较强的编程功能。存储过程可以使用EXECUTE语句来运行。存储过程的概念存储在SQLServer客户机中的T-SQL的工作机制客户机上的大量T-SQL语句需逐条向SQLServer发送，将降低系统运行效率。许多批处理代码被重复使用屡次，每次都输入相同的代码。存储在SQLServer效劳器中的存储过程的工作机制将固定逻辑的操作以T-SQL批处理块的形式存储在数据库效劳器上，并取名，该名称即为存储过程名。仅需调用存储过程名，即可执行该存储过程。存储过程的优点加快系统运行速度。存储过程仅在创立时进行编译，以后每次执行存储过程均无须再重新编译。封装复杂操作。当对数据库进行复杂操作时〔如对多个表进行更新、删除时〕，可用存储过程将此复杂操作进行封装。实现模块化程序设计和代码重用。存储过程一旦创立，以后即可在程序中任意调用屡次。增强平安性，参数化存储过程有助于保护应用程序不受SQL的注入式攻击。减少网络流量。存储过程的特点存储过程VS函数存储过程分类系统存储过程自定义存储过程存储过程函数EXEC单独执行随处调用可以改数据（限制少）不能改数据有输出参数（多个）有返回值（一个）效率更高（有争议）效率较高SQLServer提供系统存储过程，它们是一组预编译的T-SQL语句。系统存储过程主要用于从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供便利。所有系统存储过程均以“sp_〞开始，系统存储过程均存放于系统数据库master中。系统管理员拥有这些存储过程的使用权限，在任何数据库中均可运行系统存储过程，执行结果反映的是当前数据库的信息。系统存储过程系统存储过程系统存储名称说

明sp_helpdb报告有关指定数据库或所有数据库的信息【EXECsp_helpdb】sp_renamedb更改数据库的名称，如：【sp_renamedb'Meitao',‘Taobao‘】用于将数据库Meitao更名为Taobaosp_tables返回当前环境下可查询的对象列表【EXECsp_tables】【sp_tablesRank】sp_columns查看某个表的所有列信息，如：【sp_columnsEmployee】用于查看表Employee所有列的信息sp_help查看某个表的所有信息【sp_help】【sp_helpRank】sp_helpconstraint查看某个表的约束【sp_helpconstraintRank】sp_helpindex查看某个表的索引【sp_helpindexRank】练习使用系统存储过程实践练习实践时间：30分钟讲解时间：30分钟实践时间：60分钟存储过程:内容预览理论自定义存储过程实践练习使用自定义存储过程用户自定义存储过程是指用户根据自身需要，为完成某一特定功能，在用户数据库中创立的存储过程。CREATEPROC[EDURE]proc_name[{@parameter_namedata_type}=[默认值]][OUTPUT],…,n]AS procedure_body存储过程关键字PROCDURE可以简称为PROC。proc_name为存储过程名称。“@parameter_namedata_type〞为参数列表，其中，@parameter_name为参数名，data_type为参数类型。每个参数均可指定默认值，“n〞表示可以有多个参数。参数默认为INPUT类型，即输入类型；参数之后的“OUTPUT〞说明该参数为输出类型。AS之后的procedure_body为存储过程的主体，是存储过程的核心。自定义存储过程创立一个名为proc_Product_Info的存储过程，其将获取所有商品的标题、类型名、团购价、地区名和商店名，按照类型和团购价升序显示。IFEXISTS(SELECT*FROMsysobjectsWHEREname='proc_Product_Info') DROPPROCEDUREproc_Product_InfoGOCREATEPROCEDUREproc_Product_InfoASSELECTtitle,categoryName,currentPrice,areaName,shopNameFROMProductp,Categoryc,Areaa,ShopsWHEREp.categoryID=c.categoryIDANDp.areaID=a.areaIDANDp.shopID=s.shopIDORDERBYcategoryName,currentPrice自定义存储过程存储过程proc_Product_Info创立成功后，可在SQLServerManagementStudio的对象资源管理器的数据库“Meitao〞→“可编程性〞→“存储过程〞下看到新创立的存储过程。使用“EXECUTE|EXEC存储过程名〞命令执行存储过程。EXECproc_Product_Info自定义存储过程创立一个名为proc_ProductStatistics的存储过程，其将获取不同类型的商品个数和平均团购价。自定义存储过程CREATEPROCEDUREproc_ProductStatisticsASSELECTcategoryName商品类型名,COUNT(ductID)商品数量,AVG(currentPrice)平均团购价FROMProductp,CategorycWHEREp.categoryID=c.categoryIDGROUPBYcategoryName--根据categoryName分组ORDERBY平均团购价存储过程的参数个数可以是多个，且与参数的顺序无关。参数定义为：“参数名数据类型〞。存储过程中的参数分为输入类型和输出类型，默认为输入类型。自定义存储过程创立一个名为proc_ProdStatisGivenCategory的存储过程，其将获取指定类型的商品个数和平均团购价。自定义存储过程所谓“指定类型〞，即商品类型不是固定的，而是由用户或系统指定。定义一个带输入参数的存储过程，该参数用于接收外界输入的需要进行统计的商品类型名称。自定义存储过程CREATEPROCEDUREproc_ProdStatisGivenCategory(

--输入参数，用于接收商品类型名称

@categoryNamenvarchar(20))ASSELECTcategoryName商品类型名,COUNT(ductID)

商品数量,AVG(currentPrice)

平均团购价FROMProductp,CategorycWHEREp.categoryID=c.categoryIDANDcategoryName=@categoryName--输入的@categoryName赋给Category表的categoryName列GROUPBYcategoryName--根据categoryName分组ORDERBY平均团购价执行带参数的存储过程，需要将实际参数值赋给存储过程中的参数。EXECproc_ProdStatisGivenCategory‘数码电器’自定义存储过程执行存储过程proc_ProdStatisGivenCategory，传入“数码电器〞，将获取数码电器类商品的统计信息。注意：传入值的类型、个数和顺序都需要与存储过程中定义的参数逐一对应。创立一个名为proc_MaxPriceGivenCategory的存储过程，其将获取指定商品类型的最高团购价，要求输出指定商品类型的最高团购价及其商品名。自定义存储过程存储过程中需定义三个参数，一个为输入参数，用于接收商品类型信息〔此题为类型名〕；另两个为输出参数，其中一个用于输出指定类型商品的最高团购价，另一个用于输出具有最高团购价的商品名。自定义存储过程CREATEPROCEDUREproc_MaxPriceGivenCategory(

@categoryNamenvarchar(20),--输入参数，用于接收商品类型名 @maxPricemoneyOUTPUT,--输出参数，用于输出指定类型商品的最高团购价 @productNamenvarchar(20)OUTPUT--输出参数，用于输出具有指定类型最高团购价的商品名)AS--获取@categoryName商品类型的最高团购价，赋给输出参数@maxPriceSELECT@maxPrice=MAX(currentPrice)FROMProductp,CategorycWHEREp.categoryID=c.categoryIDANDcategoryName=@categoryName--获取最高团购价的商品标题，赋给输出参数@productNameSELECT@productName=titleFROMProductWHEREcurrentPrice=@maxPrice执行带输出参数的存储过程，需要在批处理块中定义输出参数，且在将实际参数传给存储过程的输出参数时，需要在实际参数之后添加关键字“OUTPUT〞，才可在成功执行存储过程后，通过实际参数变量得到存储过程传出的参数值。自定义存储过程DECLARE@categoryNamenvarchar(20)DECLARE@maxPricemoney--定义最高团购价变量DECLARE@productNamenvarchar(20)--定义商品名变量SET@categoryName='服装'--赋值商品类型EXECproc_MaxPriceGivenCategory@categoryName,@maxPriceOUTPUT,@productNameOUTPUTPRINT'团购价最贵的'+@categoryName+'类商品是：'+@productName+'，价格是：'+CONVERT(nvarchar(20),@maxPri