搜索
第12章课程回顾-1创建索引时能提升创建性能的方法有那些？什么时全文索引和全文目录？本章节授课目标理解视图的概念掌握创建、修改、删除视图了解使用视图修改数据理解游标的概念掌握游标的操作什么是视图视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图什么是视图视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库如何创建视图演示使用企业管理器创建视图如何创建视图使用T-SQL语句创建视图的语法CREATEVIEWview_nameASselect语句如何创建视图创建方便管理人员查看的订单信息IFOBJECT_ID('dbo.v_TMe_OrderInfoTest','V')ISNOTNULLDROPVIEWdbo.v_TMe_OrderInfoTest;GOCREATEVIEWdbo.v_TMe_OrderInfoTestASSELECTA.BillCodeAS订单编号,A.UserNameAS会员名称,ISNULL(B.OperName,'')AS操作员,ISNULL(C.DeliveryName,'')AS配送商,A.HandlerAS经手人,A.OutBillCodeAS出库单,A.PostBillCodeAS快递单号,A.BillStatusAS订单状态,A.OrderTimeAS订购时间,A.RecManAS收货人,A.LinkTelAS联系电话,A.RecAddressAS收货地址,A.PostCodeAS邮政编码,A.TotalMoneyAS总额FROMdbo.TMe_OrderInfoASALEFTOUTERJOINdbo.TAu_OperInfoASBONA.OperID=B.OperIDLEFTOUTERJOINdbo.TBa_DeliveryInfoASCONA.DeliveryID=C.DeliveryIDGOSELECT*FROMdbo.v_TMe_OrderInfoTest;检测是否存在创建视图使用视图删除视图管理人员需要查询的信息来源于不同的数据表，需要进行数据表连接，属于有一定复杂度的查询动作该查询属于日常行为，会频繁地进行不允许创建新表查看视图语句使用系统存储过程sp_helptext查看--查看视图语句EXECsp_helptext'dbo.v_TMe_OrderInfoTest';修改视图管理员希望修改视图，除去部分不常用的信息，并希望对视图进行加密修改视图的语法ALTERVIEW[schema_name.]view_name[(column[,...])][WITHview_option[,...]]ASselect_statement[WITHCHECKOPTION]ALTERVIEWdbo.v_TMe_OrderInfoTestWITHENCRYPTIONASSELECTA.BillCodeAS订单编号,A.UserNameAS会员名称,ISNULL(C.DeliveryName,'')AS配送商,A.HandlerAS经手人,A.OutBillCodeAS出库单,A.PostBillCodeAS快递单号,A.BillStatusAS订单状态,A.OrderTimeAS订购时间,A.RecManAS收货人,A.LinkTelAS联系电话,A.RecAddressAS收货地址,A.PostCodeAS邮政编码,A.TotalMoneyAS总额FROMdbo.TMe_OrderInfoASALEFTOUTERJOINdbo.TAu_OperInfoASBONA.OperID=B.OperIDLEFTOUTERJOINdbo.TBa_DeliveryInfoASCONA.DeliveryID=C.DeliveryIDGO加密后的视图无法查看创建的语句可更新视图通过视图更新数据需要满足以下要求：在定义视图的SELECT列表中不能包含聚合函数。在定义视图的SELECT语句中不能使用GROUPBY或HAVING子句。单个INSERT、UPDATE或DELETE语句更新视图时，发生变化的数据只能来自一个源表。该视图必须包含源表的必填列小结12什么是游标游标提供了以结果集中单条记录为单位进行数据操作的机制，也就是说，是一个可控的指向单条记录的指针使用游标步骤使用游标包括以下五个步骤声明游标打开游标提取数据关闭游标释放游标游标声明声明游标的基本语法DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FORREADONLY|UPDATE[OFcolumn_name[,...]]]参数说明INSENSITIVE当使用游标时，对源表数据的更新不敏感同时，也不允许通过游标修改源表数据SCROLL设置FETCH语句中的参数FIRST、LAST、PRIOR、NEXT、RELATIVE和ABSOLUTE均可用READONLY设定游标为只读的UPDATE[OFcolumn_name[,...]]定义可通过游标更新的列打开、关闭、释放游标打开游标的基本语法关闭游标释放游标OPENcursor_nameOPENcursor_nameDEALLOCATEcursor_name案例8-1在线销售平台的订单经审核成功后，系统需要根据订单实现商品的自动出库功能每张订单的订单明细信息都不同，一单中可能有多种商品需要同时出库商品出库时，系统需要同时进行以下操作根据订单，自动生成出库单记录生成出库明细记录修改商品的库存修改订单信息案例8-2参考实现代码：--定义变量，保存订单的测试数据DECLARE@BillCodevarchar(30)='2010080600007',--订单号@UserNamevarchar(20)='zizhu1226',--用户名@DeliveryIDvarchar(3)='100',--配送商编码@OperIDvarchar(6)='100',--操作员编码@Handlervarchar(20)='张三',--经手人@OutBillCodevarchar(30)='2010100200001',--出库单号@PostBillCodevarchar(20)='063000',--快递单号@TotalMoneymoney,--金额@Remarkvarchar(150)='';--备注案例8-3--使用事务完成根据订单自动出库功能BEGINTRAN;--定义变量，用于保存最新错误整数值DECLARE@Errorint;--定义变量，保存游标中的数据DECLARE@MerchandiseIDvarchar(20),@Numint,@Pricemoney;--声明游标DECLAREOrderDetails_CursorCURSORFORSELECTMerchandiseID,Num,PriceFromTMe_OrderDetailsInfoWHEREBillCode=@BillCode;--打开游标OPENOrderDetails_Cursor;--获取游标中的数据给变量赋值FETCHOrderDetails_CursorINTO@MerchandiseID,@Num,@Price;案例8-4--循环遍历游标中的数据WHILE@@Fetch_Status=0BEGIN--定义变量，保存商品的库存数量DECLARE@NumB1int;--获取商品的库存数量SELECT@NumB1=NumFROMTMe_StockInfoWITH(xlock)WHEREMerchandiseID=@MerchandiseID;--更新商品的库存数量UPDATETMe_StockInfoSETNum=(@NumB1-@Num)WHEREMerchandiseID=@MerchandiseID;SET@Error=@@ERROR;IF(@Error0)GOTOErrorHandler;案例8-5--插入出库单明细信息INSERTINTOdbo.TMe_OutStockDetailsInfo([MerchandiseID],[OutBillCode],[Num],[Price])VALUES(@MerchandiseID,@OutBillCode,@Num,@Price);SET@Error=@@ERROR;IF(@Error0)GOTOErrorHandler;FETCHOrderDetails_CursorINTO@MerchandiseID,@Num,@Price;ENDCLOSEOrderDetails_Cursor;DEALLOCATEOrderDetails_Cursor;案例8-6--插入出库单信息DECALRE@OutTimedatetime=GETDATE();INSERTINTOdbo.TMe_OutStockInfo([OutBillCode],[OperID],[OutTime],[Handler],[OutType],[TotalMoney],[Remark])VALUES(@OutBillCode,@OperID,@OutTime,@Handler,1,@TotalMoney,@Remark);SET@Error=@@ERROR;IF(@Error0)GOTOErrorHandler;案例8-7--更新订单信息UPDATETMe_OrderInfoSET[UserName]=@UserName,[DeliveryID]=@DeliveryID,[OperID]=@OperID,[Handler]=@Handler,[OutBillCode]=@OutBillCode,[PostBillCode]=@PostBillCode,[BillStatus]=2WhereBillCode=@BillCodeSET@Error=@@ERROR;IF(@Error0)GOTOErrorHandler;PRINT'自动出库成功，事务提交！';COMMITTRAN;案例8-8--事务异常处理ErrorHandler:IF@Error0BEGINPRINT'入库失败，事务回滚！';ROLLBACKTRAN;ENDGO小结26总结-1如何创建视图？利用游标操作数据的步骤？上机作业作业创建库存统计的视图利用游标实现入库单信息的修改28课余作业作业总结本堂课的知识点完善上机练习，并将练习结果保存为.sql脚本提交。29课后预习预习内容存储过程的概念创建存储过程的语法创建函数的语法30