SqlServer专题二：数据库主要对象

SqlServer专题⼆：数据库主要对象存储过程(1)减少⽹络通信量。调⽤⼀个⾏数不多的存储过程与直接调⽤sql语句的⽹络通信量可能不会有很⼤的差别，可是如果存储过程包含上百⾏sql语句，那么其性能绝对⽐⼀条⼀条的调⽤sql语句要⾼得多。(2)执⾏速度更快。有两个原因：⾸先，在存储过程创建的时候，数据库已经对其进⾏了⼀次解析和优化。其次，存储过程⼀旦执⾏，在内存中就会保留⼀份这个存储过程，这样下次再执⾏同样的存储过程时，可以从内存中直接调⽤。(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进⾏的，因此数据库开发⼈员可以在不改动存储过程接⼝的情况下对数据库进⾏任何改动，⽽这些改动不会对应⽤程序造成影响。(4)布⼯作：应⽤程序和数据库的⼯作可以别⽴进⾏，⽽不会相互压制。存储过程可以⽤临时表,函数不能⽤临时表存储过程可以使⽤Update,函数不能使⽤Update存储过程可以⽤Getdate()等函数,函数不能使⽤Getdate()这些函数⼀、使⽤存储过程要注意⼀些问题1、在as⾯⽴即跟上⼀个setnocounton⼦句，这样会减少很⼤开销(相对于过程中存在DML操作的时候)2、创建和引⽤过程的时候加上架构名3、避免在返回许多⾏数据的SELECT语句中使⽤标量函数(getdate())。因为标量函数必须应⽤于每⼀⾏。4、避免使⽤SELECT*。⽽是应指定所需的列名称。5、避免处理或返回”过多”的数据，⼀些没必要的数据就不要了6、通过使⽤BEGIN/ENDTRANSACTION来使⽤显式事务并且保留尽可能短的事务。更长的事务意味着更长的记录锁定和更⾼的死锁风险7、在LIKE⼦句中避免使⽤通配符作为前导字符，例如LIKE‘%a%。’因为第⼀个字符是不确定的，所以，查询处理器⽆法使⽤可⽤的索引。应改⽤LIKE‘a%。’8、使⽤Transact-SQLTRY…CATCH功能进⾏过程内的错误处理9、创建/修改Table时给列⼀个Default值10、使⽤IFEXISTS(SELECTTOP1FROMtable_name)来代替IFEXISTS(SELECT*FROMtable_name)。11、使⽤UNIONALL运算符来代替UNION或OR运算符12、创建存储过程时在其定义中指定WITHRECOMPILE选项，表明SQLServer将不对该存储过程计划进⾏⾼速缓存；该存储过程将在每次执⾏时都重新编译。当存储过程的参数值在各次执⾏间都有较⼤差异(个⼈觉得应该是动态创建存储过程时，使得很可能有时候参数不同)，导致每次均需创建不同的执⾏计划时，可使⽤WITHRECOMPILE选项。此选项并不常⽤，因为每次执⾏存储过程时都必须对其进⾏重新编译，这样会使存储过程的执⾏变慢，⼆、知识点：1、SQLServer启动时可以⾃动执⾏⼀个或多个过程。这些过程必须由系统管理员在master数据库中创建，并以sysadmin固定服务器⾓⾊作为后台进程执⾏。这些过程不能有任何输⼊或输出参数。2、过程可以嵌套，并且最多可以嵌套32级。3、过程可以引⽤尚不存在的表。在创建时，只进⾏语法检查。直到第⼀次执⾏该过程时才对其进⾏编译。只有在编译过程中才解析过程中引⽤的所有对象。加密存储过程CreateprocedureFirstPWITHENCRYPTIONassetnocounton

select*fromCgoEXECsp_helptext'dbo.FirstP'存储过程实现递归select*fromtborderbycasewhenISNUMERIC(col)=1then''elseleft(col,1)end,cast(casewhenISNUMERIC(col)=0thenstuff(col,1,1,'')elsecolendasint)droptabledbo.ExamplecreatetableExample(IDint,ParentIDint)insertExampleselect0,nullunionselect1,0unionselect2,0unionselect3,1unionselect4,6unionselect5,2--取其某个ID为⽗节点的树结构的SQL如下：createproceduredisplay(@IDint)asbeginWITHExample_Table(ID,ParentID,lv)AS(取根--节点放⼊临时表SELECTID,ParentID,0FROMExampleWHEREID=@ID根据--已取到的数据递归取其字节点的数据UNIONALLSELECTA.ID,A.ParentID,B.lv+1FROMExampleAINNERJOINExample_TableBONA.ParentID=B.ID)SELECT*FROMExample_Tableenddroptabletbgo⾃定义函数⽤户定义⾃定义函数像内置函数⼀样返回标量值，也可以将结果集⽤表格变量返回⽤户⾃定义函数的类型:标量函数:返回⼀个标量值

表格值函数{内联表格值函数、多表格值函数}:返回⾏集（即返回多个值）1、标量函数Createfunction函数名（参数）Returns返回值数据类型[with{Encryption|Schemabinding}][as]beginSQL语句(必须有return变量或值)EndSchemabinding:将函数绑定到它引⽤的对象上（注：函数⼀旦绑定，则不能删除、修改，除⾮删除绑定）CreatefunctionAvgResult(@scodevarchar(10))ReturnsrealAsBeginDeclare@avgrealDeclare@codevarchar(11)Set@code=@scode+‘%’Select@avg=avg(result)fromLearnResult_baijialiWherescodelike@codeReturn@avgEnd执⾏⽤户⾃定义函数select⽤户名。函数名as字段别名selectdbo.AvgResult(‘s0002’)asresult⽤户⾃定义函数返回值可放到局部变量中，⽤set,select,exec赋值declare@avg1real,@avg2real,@avg3realselect@avg1=dbo.AvgResult(‘s0002’)set@avg2=dbo.AvgResult(‘s0002’)exec@avg3=dbo.AvgResult‘s0002’select@avg1asavg1,@avg2asavg2,@avg3asavg3函数引⽤createfunctioncode(@scodevarchar(10))returnsvarchar(10)asbegindeclare@ccodevarchar(10)set@scode=@scode+‘%’select@ccode=ccodefromcmessage

whereccodelike@scodereturn@ccodeendselectnamefromclasswhereccode=dbo.code(‘c001’)2、表格值函数a、内联表格值函数格式：createfunction函数名（参数）returnstable[with{Encryption|Schemabinding}]asreturn(⼀条SQL语句)createfunctiontabcmess(@codevarchar(10))returnstableasreturn(selectccode,scodefromcmessagewhereccodelike@ccode)b、多句表格值函数createfunction函数名（参数）returns表格变量名table表(格变量定义)[with{Encryption|Schemabinding}]asbeginSQL语句end多句表格值函数包含多条SQL语句，⾄少有⼀条在表格变量中填上数据值表格变量格式returns@变量名table(column定义|约束定义[,…])对表格变量中的⾏可执⾏select,insert,update,delete，但selectinto和insert语句的结果集是从存储过程插⼊。Createfunctiontabcmessalot(@codevarchar(10))Returns@ctabletable(codevarchar(10)null,cnamevarchar(100)null)AsBeginInsert@ctableSelectccode,explainfromcmessageWherescodelike@codereturnEnd

Select*fromtabcmessalot(‘s0003’)触发器⼀:触发器是⼀种特殊的存储过程﹐它不能被显式地调⽤﹐⽽是在往表中插⼊记录﹑更新记录或者删除记录时被⾃动地激活。所以触发器可以⽤来实现对表实施复杂的完整性约束。⼆:SQLServer为每个触发器都创建了两个专⽤表:Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中⽽不是在数据库中。这两个表的结构总是与被该触发器作⽤的表的结构相同。触发器执⾏完成后﹐与该触发器相关的这两个表也被删除。Deleted表存放由于执⾏Delete或Update语句⽽要从表中删除的所有⾏。Inserted表存放由于执⾏Insert或Update语句⽽要向表中插⼊的所有⾏。三:Insteadof和After触发器SQLServer2000提供了两种触发器:Insteadof和After触发器。这两种触发器的差别在于他们被激活的同:Insteadof触发器⽤于替代引起触发器执⾏的T-SQL语句。除表之外﹐Insteadof触发器也可以⽤于视图﹐⽤来扩展视图可以⽀持的更新操作。After触发器在⼀个Insert,Update或Deleted语句之后执⾏﹐进⾏约束检查等动作都在After触发器被激活之发⽣。After触发器只能⽤于表。⼀个表或视图的每⼀个修改动作(insert,update和delete)都可以有⼀个insteadof触发器﹐⼀个表的每个修改动作都可以有多个After触发器。四:触发器的执⾏过程如果⼀个Insert﹑update或者delete语句违反了约束﹐⼳After触发器不会执⾏﹐因为对约束的检查是在After触发器被激动之发⽣的。所以After触发器不能超越约束。Insteadof触发器可以取代激发它的操作来执⾏。它在Inserted表和Deleted表建⽴﹐其它任何操作还有发⽣时被执⾏。因为Insteadof触发器在约束之前执⾏﹐所以它可以对约束进⾏⼀些预处理。五:使⽤T-SQL语句来创建触发器基本语句如下:createtriggertrigger_nameon{table_name|view_name}{for|After|Insteadof}[insert,update,delete]assql_statement六:删除触发器：基本语句如下:droptriggertrigger_name七：查看数据库中已有触发器：--查看数据库已有触发器usejxcSoftwaregoselect*fromsysobjectswherextype='TR'--查看单个触发器execsp_helptext触'发器名'⼋:修改触发器：基本语句如下:altertriggertrigger_nameon{table_name|view_name}{for|After|Insteadof}[insert,update,delete]assql_statement九:相关⽰例:1:在Orders表中建⽴触发器﹐当向Orders表中插⼊⼀条订单记录时﹐检查goods表的货品状态status是否为1(正在整理)﹐是﹐则不能往Orders表加⼊该订单。createtriggerorderinsertonordersafterinsertasif(selectstatusfromgoods,=inserted.goodsname)=1beginprint'thegoodsisbeingprocessed'print'theordercannotbecommitted'rollbacktransaction回滚--﹐避免加⼊end2:在Orders表建⽴⼀个插⼊触发器﹐在添加⼀条订单时﹐减少Goods表相应的货品记录中的库存。createtriggerorderinsert1onordersafterinsertasupdategoodssetstorage=storage-inserted.quantity

fromgoods,=inserted.goodsname3:在Goods表建⽴删除触发器﹐实现Goods表和Orders表的级联删除。createtriggergoodsdeleteongoodsafterdeleteasdeletefromorderswheregoodsnamein(selectnamefromdeleted)4:在Orders表建⽴⼀个更新触发器﹐监视Orders表的订单⽇期(OrderDate)列﹐使其不能⼿⼯修改.createtriggerorderdateupdateonordersafterupdateasifupdate(orderdate)beginraiserror('orderdatecannotbemodified',10,1)rollbacktransactionend5:在Orders表建⽴⼀个插⼊触发器﹐保证向Orders表插⼊的货品名必须要在Goods表中⼀定存在。createtriggerorderinsert3onordersafterinsertasif(selectcount(*)fromgoods,=inserted.goodsname)=0beginprint'noentryingoodsforthisorder'rollbacktransactionend6：Orders表建⽴⼀个插⼊触发器，保证向Orders表插⼊的货品信息要在Order表中添加altertriggeraddOrderonOrdersforinsertasinsertintoOrderselectinserted.Id,inserted.goodName,inserted.Numberfrominserted游标SQlServer的游标在你需要对记录集进⾏单条处理时很有⽤处。select返回⼀个记录集，但是你想根据每条记录的不同情况进⾏单条处理，这时游标的⽤处就显出来了。⼀个游标(cursor)可以被看作指向结果集(asetofrows)中⼀⾏的