SQL Server 课件-第7章 SQL Server2000 Transact-SQL 编程和应用.ppt

2019/2/5,7.1 sql server 2000 transact-sql编程 7.2 存储过程 7.3 触发器 7.4 案例5：建立活期储蓄管理系统 数据库的触发器和存储过程,7.1 sql server 2000 transact-sql编程,transact-sql语言的主要特点如下： 是一种交互式查询语言，功能强大，简单易学； 既可以直接查询数据库，也可以嵌入到其它高级语言中执行； 非过程化程度高，语句的操作执行由系统自动完成； 所有的transact-sql命令都可以在查询分析器中完成。 transact-sql不仅支持所有的sql语句，而且还提供了丰富的编程功能，允许使用变量、运算符、函数、流程控制语句等。,2019/2/5,7.1.1 用户定义的数据类型 sql server提供了用户自定义数据类型的功能，用户定义的数据类型可以实现以下功能： 可以让不同表中重复出现的各列具有相同的特性，使相似的数据种类标准化。 可以将规则和默认值捆绑到用户定义的数据类型上，以约束使用此数据类型的每个列。 用户定义的数据类型与系统数据类型一样，都是用来限制操作者输入数据的种类和长度，它可以用于使用系统数据类型的任何地方。 如果在model数据库中创建了用户自定义的数据类型，则它将出现在所有以后新建的数据库中。但是定义在用户数据库中的用户自定义数据类型，则只会出现在定义它的数据库中。 用户定义的数据类型是与表、视图等并列的数据库对象，可以对它执行创建、修改、删除等操作。,2019/2/5,创建用户自定义数据类型 使用企业管理器创建用户自定义数据类型步骤如下： (1)在企业管理器中展开服务器组、服务器。 (2)展开【数据库】，选择要创建用户自定义数据类型的数据库。右击【用户定义的数据类型】目录，在弹出的快捷菜单中选择【新建用户定义数据类型】命令。 (3)打开的新建用户自定义数据类型对话框如图7-1所示。在对话框的【名称】栏中输入用户自定义数据类型的名称，如tel_no。 (4)在对话框的【数据类型】下拉列表框中，选择该用户自定义数据类型所基于的系统数据类型（此例选择varchar）。 (5)如果选择的基类型是可以设定长度的(如，varchar、char等)，则还须要在【长度】栏中设定数据类型的长度。 (6)如果允许为空值，则选中【允许null值】复选框。 (7)如果希望该数据类型与规则或默认值捆绑，则分别在【规则】和【默认值】下拉列表框中选择要捆绑的规则和默认值，否则选择“无”。 (8)单击【确定】按钮，完成。,2019/2/5,图7-1 新建用户自定义数据类型对话框,2019/2/5,使用t-sql语句创建用户自定义数据类型 通过调用系统存储过程sp_addtype实现，具体语法为： sp_addtype type，system data_type，null_type 其中： type：为用户定义的数据类型名，这个名称在数据库中必须是惟一的。 system data_type：为用户定义的数据类型所基于的系统数据类型，可以包括数据的长度、精度等。当系统数据类型中包括标点符号(例如括号、逗号)时，应用引号括起来。 null_type：指定该数据类型能否接受空值。其值可以为null、not null或no null。 例如，创建一个如图7-1所示的电话号码数据类型，可使用如下命令： exec sp_addtype tel_no,varchar(8),null,2019/2/5,查看用户自定义数据类型 使用查询分析器查看用户自定义数据类型tel_no，如图7-2所示。,图7-2 查看用户自定义数据类型,2019/2/5,删除用户自定义数据类型 可以使用t-sql语句或企业管理器来删除用户自定义数据类型。 使用t-sql语句删除 使用系统存储过程sp_droptype来删除用户自定义数据类型。sp_droptype type_name 注意：正被表或数据库使用的用户自定义数据类型不能被删除。 使用企业管理器删除 使用企业管理器删除用户定义的数据类型的步骤如下： (1)在企业管理器中展开服务器组、服务器。 (2)展开【数据库】，选中【用户定义的数据类型】，右边的窗口中将显示数据库中所有用户自定义数据类型对象，右击要删除的用户自定义数据类型，在快捷菜单中选择【删除】命令。 (3)接着打开如图73所示的删除对话框。 (4)单击图7-3所示中的【全部除去】按钮。,2019/2/5,图7-3 删除用户定义数据类型对话框,2019/2/5,7.1.2 变量 transact-sql中的变量分为局部变量与全局变量。 批和脚本 批 一个批是由一条或多条t-sql语句组成的语句集，这些语句一起提交并作为一个组来执行。sql server将批中的语句作为一个整体编译为一个执行计划。因为批中的语句是一起提交给服务器的，所以可以节省系统开销。 在查询分析器中，可以用go命令标志一个批的结束。go不是一个执行语句，是通知查询分析器有多少语句要包含在当前的批中。查询分析器将两个go之间的语句组成一个字符串交给服务器去执行。,2019/2/5,如，以下代码包含三个批： use mydb go /*第1批未*/ create view v_students as select 编号,姓名 from readers where 读者类型=学生 go /*第2批未*/ select * from v_students go /*第3批未*/,2019/2/5,因为sql server为一个批生成一个单独的执行计划，所以一个批本身应该是完整的，不能在一个批中引用其它批定义的变量，也不能将注释从一个批开始，在另一个批中结束。 如果批中的语句出现编译错误，那么将不能生成执行计划，批中的任何一个语句都不会执行。 批有如下限制： create default、create procedure、create rule、create trigger、create view语句不能与其它语句位于同一个批中。 不能在一个批中修改一个表的结构，然后在同一个批中引用刚修改的新列。 如果批的第一条语句是execute,则execute关键字可以省略。否则，不能省略。,2019/2/5,脚本 脚本是一系列顺序提交的批。脚本可以直接在查询分析器等工具中输入并执行，也可以保存在文件中，再由查询分析器等工具执行。 一个脚本可以包含一个或多个批，脚本中的go命令标志一个批的结束，如果一个脚本没有包含任何go命令，那么它被视为一个批。 脚本可用于： 将服务器上创建一个数据库的步骤永久地记录在脚本文件中。 将语句保存为脚本文件，从一台计算机传递到另一台计算机，这样可以方便地使两台计算机执行同样的操作。,2019/2/5,局部变量 局部变量是用户自定义的变量。使用范围是定义它的批、存储过程或触发器。局部变量前面通常加上标记。 declare 定义局部变量，并指明此变量的数据类型 set或select命令对其赋值。局部变量的数据类型可以是用户自定义的数据类型，也可以是系统数据类型，但不能将其定义为text或image数据类型。 定义局部变量的语法如下： declare local_variable data_type , local_variable data_type declare命令可以定义多个局部变量，之间用逗号分隔。,2019/2/5,用select为局部变量赋值的语法如下： select variable_name=expression select statement ，variable_name=expression select statement from list of tables where expression group by having order by 说明： (1)select命令可以将一个表达式的值赋给一个局部变量，也可以将一个select查询的结果赋给一个局部变量。 (2)select命令通常返回一个值给局部变量。当返回多个值，则变量的值为最后一个返回值。如果select命令没有返回值，则局部变量保持原值不变，如expression为个分级查询且没有返回值时，变量被置为null。,2019/2/5,【例7-1】 数据库mydb中readers表中的编号及姓名两列内容如下，执行下面脚本。 use mydb declare varl varchar(8) -声明局部变量 select var1=读者姓名 -为局部变量赋初值 select varl=姓名 from readers where 编号=2004160426 select varl as 读者姓名 -显示局部变量结果 执行结果为： 读者姓名 - 刘超,2019/2/5,【例7-2】 多个返回值的赋值。 use mydb declare varl varchar(8) select varl=读者姓名 select varl = 姓名 from readers select varl as 读者姓名 执行结果为： 读者姓名 - 返回最后一名读者的姓名,2019/2/5,用set为局部变量赋值 用set为局部变量赋值的常用语法格式为： set local_variable= expression 【例7-3】使用set命令赋值的变量。 use mydb go declare no varchar(10) set no=2004060003 select 编号,姓名 from readers where 编号= no go 执行结果为： 编号 姓名 - - 2004060003 李亚茜,2019/2/5,全局变量 全局变量是由sql server系统提供并赋值的变量。用户不能建立全局变量，也不能修改全局变量的值。 在使用全局变量时请注意以下规则： 全局变量是在服务器级定义的，不是由用户例程定义的。 用户只能使用系统预定义的全局变量。 引用全局变量时，前面一定加上标记。 用户不能定义与系统全局变量同名的局部变量，否则将产生不可预测的结果。,2019/2/5,例如，rowcount，是返回最近一次数据库操作所涉及到的行数。 【例7-4】使用全局变量rowcount，查询命令影响的行数。 update readers set 已借数量=2 select rowcount as 行数 go 执行结果为 行数 5,2019/2/5,7.1.3 运算符 transact-sql提供了如下几种类型的运算符：算术运算符、比较运算符、字符连接运算符和逻辑运算符。 算术运算符 算术运算符用于数字之间的运算，其种类与含义如表7-1所示。,2019/2/5,比较运算符 比较运算符用于在多个数据或表达式之间进行比较，结果为布尔值。其种类与含义如表7-2所示。,2019/2/5,字符连接运算符 连接运算符只有一种：+，其用于将两个字符数据连接起来。 逻辑运算符 逻辑运算符一般用于条件判断，其运算符与含义如表7-3所示。设a和b是两个逻辑量，各逻辑运算情况如表7-4所示。,2019/2/5,2019/2/5,7.1.4函数 sql server 2000提供了非常丰富的内置函数，而且也允许用户自定义函数。利用这些函数可以方便地实现各种运算和操作。一般函数的返回值返回给select请求。 内置函数 内置函数的分类 sql server 2000提供的函数按功能分为若干类。函数分类情况见表7-5所示。,2019/2/5,基本sql函数 通常情况下基本的sql函数，在selete 、order by和where等查询子句中使用。这些函数及其功能如表7-6所示。,2019/2/5,系统函数 系统函数使用户能够获取计算机系统、数据库及对象信息。系统函数在存储过程等对象中发挥了重要的作用，可以让用户根据不同的系统反馈信息采取不同的动作。 系统函数可以用于select目标列及where子句中。主要系统函数见表7-7。,2019/2/5,2019/2/5,【例7-6】 系统函数举例。 select 已借数量，isnull(已借数量，0) as 空值置换 from readers where isnull(已借数量，0)=0 执行结果为： 已借数量 空值置换 - null .0000 null .0000,2019/2/5,用户自定义的函数 除了系统提供的内置函数以外，用户可以根据需要自己定义函数，即通过编程实现特定的功能。 sql server2000支持三种用户自定义函数： 返回数值的用户自定义函数 内联（单语句）的返回表的用户自定义函数 多语句返回表的用户自定义函数 用户自定义函数可以接受零个或多个输入参数，返回值可以是数值或表，不支持输出参数。 创建用户自定义函数用create function语句实现。,2019/2/5,返回数值的用户自定义函数语句格式： create function function_name ( parameter_name scalar_parameter_data_type = default ,.n ) returns scalar_return_data_type as begin function_body return scalar_expression end 其中： parameter_name：为输入参数名。 scalar_parameter_data_type：为输入参数的类型。 returns scalar_return_data_type：函数返回值的类型。 begin 与end之间定义了函数体，该函数体中包含一条return语句， 用于返回一个值。,2019/2/5,内联（单语句）的返回表的用户自定义函数语句格式： create function function_name ( parameter_name scalar_parameter_data_type = default ,.n ) returns table as return (select-statement) 注意： returns table：函数的返回值是一个表。,2019/2/5,【例7-7】 创建一个返回数值的用户自定义函数，返回某读者借书的数量。 create function books_num (bh char(10) returns int begin declare num int select num=已借数量 from readers where 编号=bh return num end 该函数可以用以下语句来调用： select dbo.books_num(2004060003),2019/2/5,【例7-8】 创建一个返回表的用户自定义函数，返回到某日到期的读者及所借的书的列表。 create function books_due (days datetime) returns table return ( select * from borrowinf where 应还日期days ) 该函数可以用以下语句来调用： select * from books_due(2004-01-01),2019/2/5,7.1.5 流程控制语句 在transact-sql程序设计中，流程控制语句用于改变或优化程序的执行顺序，提高执行效率。 if.else 其语法结构为： if boolean_expression sql_statement | statement_block else sql_statement | statement_block 程序执行到if.else命令时，测试if后面的表达式，若为真，则执行if下的程序体。否则，执行else下面的程序体或直接执行接下来的程序体（当没有else分支时）。if.else允许嵌套使用。,2019/2/5,【例7-9】查询books表中英语类图书的数量，如果查不到，则显示提示。 if exists(select * from books where 书名=英语) select count(*) as 英语图书数量 from books where书名=英语 else print数据库中没有英语书,2019/2/5,【例7-10】利用嵌套ifelse编程。 use mydb go if exists(select * from books where 书名=英语) select count(*) as 英语书数量 from books where 书名=英语 else if exists(select * from books where 书名=计算机文化基础) select count(*) as 计算机文化基础数量 from books where 书名=计算机文化基础 else print 英语及计算机文化基础两种书都没有!,2019/2/5,beginend beginend结构使一组transact-sql命令作为一个整体或一个单元来执行。 begin定义了一个单元的起始位置,end 作为一个单元的结束。 beginend多用于if.else 结构和while结构中。 其语法结构为： begin sql-statement statemen-block end,2019/2/5,【例7-11】 查询books表中英语类和计算机类图书的数量 declare ebook int, cbook int if exists(select * from books where 书名=英语) begin select ebook=count(*) from books where 书名=英语 print 英语书数量：+rtrim(ebook) +册 end else print 英语书没有库存 ！ if exists(select * from books where 书名=计算机文化基础) begin select cbook=count(*) from books where 书名=计算机文化基础 print 计算机数量为：+ rtrim(cbook)+册 end else print 计算机文化基础没有库存!,2019/2/5,while while命令用于执行一个循环体。其语法结构为: while boolean_expression sql-statement | statement_block break sql-statement | statement_block continue 当程序执行到while语句时,先判断while后面的条件（称为循环条件）是否为真,若是,则执行循环体,否则不执行while循环体内的程序,直接向下执行。 break用于终止循环的执行, continue用于将循环返回到while开始处,重新判断条件,以决定是否重新执行新的一次循环。,2019/2/5,【例7-12】 一个小循环程序。 declare x int set x=0 while x3 begin set x=x+1 print x=+rtrim(x) end go,2019/2/5,goto 标签 goto命令与其它使用goto命令的高级语言一样，将程序的执行跳到相关的标签处。 goto命令的语法结构如下： goto label 说明：label表示程序要跳到的相应标签处。程序中定义标签的语法结构如下： label：程序行,2019/2/5,waitfor 命令 waitfor命令产生一个延时，使存储过程或程序等候或直到一个特定时间片后继续执行。其语法结构如下： waitfor delay time | time time 说明： delay指明sql server等候的时间长度，最长为24小时。 time指明sql server需要等到的时刻。delay与time使用的时间格式为 hh:mm:ss。,2019/2/5,【例7-13】延迟30秒钟执行查询命令。 waitfor delay 00:00:30 select * from readers 【例7-14】在时刻21:20:00时执行查询命令。 waitfor time 21:20:00 select * from readers,2019/2/5,reture 命令 return命令使一个存储过程或程序退出并返回到调用它的程序中。其语法结构如下： return integer_expression 说明： 此命令中的可选项integer-expression为整型表达式。使用return命令只可以返回一个整型值给其调用程序，如果想返回其他类型的数据，必须使用输出参数。,2019/2/5,调用存储过程时，sql server用数值0表示返回成功，用负数表示返回出现错误。0-99由sql server系统保留。,2019/2/5,注释语句 transact-sql的注释语句共有两种：行注释、块注释。在transact-sql程序中，由多条命令语句组成的一组程序称为块。使用注释语句主要是为了增加程序的可读性，为日后编辑、修改程序提供方便。程序执行时，注释部分不参与执行。 行注释语句为：以两个减号（-）开始的若干字符，一般位于程序行之后。 块注释语句为：以/*开始到*/结束的若干字符。,2019/2/5,7.1.6 case表达式 简单case表达式 语法结构如下： case lnput_expression when when_expression then result_expression n else else_result expression end 说明： 语法结构中.n 表示有n个类似when when_expression then result expression的子句。 首先在所有when子句中查找与input_expression匹配的第一个表达式，并计算相应的then子句。如果没有匹配的表达式，执行else子句。,2019/2/5,【例7-15】 显示readers表中读者的借书量。 select 姓名,rt.类型名称 as 类型,借阅量= case 读者类型 when 3 then 可以借8本书！ when 2 then 可以借20本书！ when 1 then 可以借50本书！ else 无规定！ end from readers r,readertype rt where r.读者类型=rt.类型编号,2019/2/5,搜索式case表达式 搜索式case的语法结构如下： case when boolean_expression then result_expression .n else else_result_expression end 说明： 语法结构中.n 表示有n个类似when 的子句。 有搜索式case表达式的t-sql语句首先查找值为真的表达式。如果没有一个when子句的条件计算值为真，则返回else表达式的值。,2019/2/5,【例7-16】显示readers表中读者的借书量。 select 姓名,rt.类型名称 as 类型,借阅量= case when 读者类型=3 then 可以借8本书！ when 读者类型=2 then 可以借20本书！ when 读者类型=1 then 可以借50本书！ else 无规定！ end from readers r,readertype rt where r.读者类型=rt.类型编号,2019/2/5,7.1.7 显示和输出语句 print命令 print 命令用于在指定设备上显示信息，可以输出的数据类型只有：char、nchar、varchar、nvarchar以及全局变量version等。 print 命令的语句如下： print any ascii text |local_variable |function | string_expr 说明： any ascii text：文本或字符串。 local_variable：字符类型的局部变量。 function：返回字符串结果的函数。 string_expr：字符串表达式，最长为8000个字符。,2019/2/5,【例7-17】查询c语言程序设计的数量。 declare count int if exists(select 书名 from books where 书名=c语言程序设计) begin select count =count(书名) from books where 书名=c语言程序设计 print c语言程序设计数量为： +rtrim(count)+册 end,2019/2/5,错误消息处理 在存储过程中可以使用系统存储过程sp_addmessage、全局变量error或raiserror命令返回出错信息。 sp_addmessage 该存储过程可以用于创建定制的错误消息。其使用方法如下: sp_addmessage msgnum=msg_id， severity= severity， msgtext= msg ,lang= language ,with_log= with_log ,replace= replace error 此全局变量返回最近一次系统的错误信息。,2019/2/5,7.2 存储过程,7.2.1 存储过程基本知识 什么是存储过程 应用程序与sql server数据库交互执行某些操作有两种方法： 一种是存储在本地的应用程序记录操作命令，应用程序向sql server发送每一个命令，并对返回的数据进行处理； 另一种是在sql server中定义某个过程，其中记录了一系列的操作，每次应用程序只需调用该过程就可完成该操作。 这种在sql server中定义的过程被称为存储过程。,2019/2/5,存储过程的功能 sql server中的存储过程类似于编程语言中的过程和函数，它具有以下功能： 接受输入参数并返回多个输出值。 包含t-sql语句用以完成特定的sql server操作。 返回一个指示成功与否及失败原因的状态代码给调用它的过程。 存储过程是一组预编译的transact-sql语句，主体构成是标准sql命令，同时包括sql的扩展：语句块、结构控制命令、变量、常量、运算符、表达式、流程控制等，所有这些组合在一起用于构造存储过程。,2019/2/5,存储过程的优点 存储过程有以下几个优点： 允许模块化编程，增强代码的重用性和共享性 使用存储过程可以加快运行速度 使用存储过程可以减少网络流量。 存储过程可以作为安全性机制。 存储过程的分类 存储过程有以下几种类型：系统存储过程、用户存储过程、临时存储过程、扩展存储过程、远程存储过程。,2019/2/5,系统存储过程是由系统提供的过程，可以作为命令直接执行。系统存储过程存储在master数据库中，其前缀为sp_。系统存储过程可以在任意一个数据库中执行。 用户存储过程是创建在用户数据库中的存储过程。其名称前面一般不加sp_前缀。主要在应用程序中使用，以完成特定的任务。 临时存储过程属于用户存储过程。如果用户存储过程前面加上符号“#”，则该存储过程称为局部临时存储过程，只能在一个用户会话中使用；如果用户存储过程前面加上符号“#”，则该过程称为全局存储过程，可以在所有用户会话中使用。 扩展存储过程是在sql server环境之外执行的动态链接库dll，其前缀为xp_。尽管这些动态链接库在sql server环境之外，但它们可以被加载到sql server系统中，并且按照存储过程的方式执行。 远程存储过程是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器上的客户机上调用的存储过程。,2019/2/5,7.2.2 创建存储过程 在sql server中创建存储过程主要有以下3种方法：使用向导、使用企业管理器、 使用transact-sql命令。 使用向导创建存储过程 使用向导创建存储过程的步骤为： (1)启动企业管理器，展开数据库目录。 (2)在企业管理器的【工具】菜单中选择【向导】项。 (3)在【选择向导】对话框中展开【数据库】，单击【创建储存过程向导】项。 (4)单击【确定】按钮，打开创建存储过程的欢迎对话框(见图7-6)，这个对话框向用户介绍了利用创建向导创建存储过程的主要步骤。,2019/2/5,(5)单击【下一步】按钮，打开数据库选择对话框(存储过程必须创建在一个数据库中)，在数据库下拉列表中选择所需要的数据库mydb。 (6)单击【下一步】按钮，打开选择储存过程对话框，对话框中共4个列表框，第1列为选定的数据库中的所有表，第2列为插入复选框，第3列为删除复选框，第4列为更新复选框。在这个对话框中选择将要包含在存储过程中的表【books】和对该表的操作【插入】，如图7-7所示。,2019/2/5,图7-6 创建存储过程的欢迎对话框图 7-7 选择储存过程对话框,2019/2/5,(7)单击【下一步】按钮，打开完成创建储存过程对话框，如图7-8所示，对话框中显示了系统赋予存储过程的名称及其描述信息。单击【编辑】按钮可以进入编辑储存过程对话框，使用这个对话框可以修改存储过程的名称，列表框中设有 4列信息，依次为列名、数据类型、长度和是否选择复选框。默认情况下选择所有列，单击复选框来选择需要的列，如图7-9所示。 在编辑储存过程对话框中单击【编辑sql】按钮，打开编辑储存过程sql对话框，进行transact-sql的编辑，如图7-10所示。编辑完成后，单击【确定】按钮，返回上一个对话框。 (8)在图7-8所示对话框中，单击【完成】按钮，则存储过程创建完毕。在选定的数据库中，就会形成一个新的存储过程。 注意利用创建向导的存储过程功能有限，只适用于对表的简单操作，若须创建功能复杂的存储过程，则要用create procedure命令完成。,2019/2/5,图7-8 完成创建储存过程对话框图 7-9 编辑储存过程对话框,2019/2/5,图7-10 编辑储存过程sql 对话框,2019/2/5,使用企业管理器创建存储过程 利用企业管理器创建存储过程步骤如下： (1)展开要在其中创建存储过程的数据库。 (2)右击【存储过程】图标，在快捷菜单中选择【新建存储过程】命令，打开新建存储过程对话框，如图7-11所示。 (3)在新建存储过程对话框的文本框中书写存储过程定义。【检查语法】按钮用于检查存储过程语法的正确性。定义完存储过程后，单击【确定】按钮保存存储过程。 这种方法与下面讲的利用transact-sql命令创建存储过程几乎完全相同。,2019/2/5,图7-11 新建存储过程对话框,2019/2/5,使用transact-sql命令创建存储过程 利用transact-sql命令创建存储过程的语法结构如下： create procedure procedure_name parameter data_type = default output，n with recompile | encryption | recompile，encryption for replication as sql_statement n 说明： (1) procedure_name 存储过程名，其命名规则遵守 sql server 标识符的命名规则，最长为 128个字符。 (2) parameter 创建存储过程时可以声明一个或多个参数，最多为1024个。 (3) default 参数的默认值，可以为null，也可以包含通配符(或_)。 (4) output 表明参数为一个输出参数，当使用execute执行时作为返回值，不能是text类型。,2019/2/5,(5) with recompile 每次执行存储过程时重新编译，产生新的执行计划，不能与 for replication同时使用。 with encryption 将syscomments表中的存储过程文本进行加密，使用户不能利用sp_helptext查看存储过程内容。 (6) for replication 表示该存储过程不能在订阅器上执行，只能在复制期间执行。 (7) sql_statement 作为存储过程主体部分的transact-sql内容。 存储过程只能在当前数据库中创建，且只有具有system administrators、db_owner或 db_ddladmin角色的成员才可以创建。 在create procedure语句中，可以包括任意数量的transact-sql语句，但是不能使用create default,create procedure,create rule,create trigger和create view这些创建对象的语句。,2019/2/5,【例7-18】 创建一个简单的存储过程。 use mydb go create procedure borrowed_num as select 姓名,已借数量 from readers where 姓名=刘超 go,2019/2/5,【例7-19】 通过多表连接查询，创建较复杂的存储过程。 use mydb go create procedure borrowed_books1 as select r.编号,r.姓名,b.图书编号,k.书名,b.借期 from readers r inner join borrowinf b on r.编号=b.读者编号 inner join books k on b.图书编号=k.编号 where 姓名=刘超,2019/2/5,7.2.3 存储过程中的参数 sql server中存储过程的参数包括输入参数和输出参数。参数扩展了sql server的功能，通过储存过程每次执行时不同的参数值，实现其灵活性。 输入参数 输入参数用于把值传入存储过程。,2019/2/5,【例7-20】 使用输入参数，使得能够显示某人借阅书籍的情况。 use mydb go create procedure borrowed_books2 name varchar(10) as select r.编号,r.姓名,b.图书编号,k.书名,b.借期 from readers r inner join borrowinf b on r.编号=b.读者编号 inner join books k on b.图书编号=k.编号 where 姓名=name,2019/2/5,将值传入存储过程有以下几种方法： 直接将值传入 如 exec borrowed_books2 张刚。 利用与声明时相同类型的变量来传递 如 exec borrowed_books2 templ (此处，templ为已声明的字符类型变量，且已赋值)。 使用参数名进行传递 这种形式对于有多个输入参数时，可以以任意的顺序进行参数传递。但如果对一个参数使用了名字，则必须对随后的参数都使用名字。,2019/2/5,【例7-21】使用默认参数，使得能够显示某人借阅书籍的情况。 create procedure borrowed_books3 name varchar(10)=null as if name is null select r.编号,r.姓名,b.图书编号,k.书名,b.借期 from readers r inner join borrowinf b on r.编号=b.读者编号 inner join books k on b.图书编号=k.编号 else select r.编号,r.姓名,b.图书编号,k.书名,b.借期 from readers r inner join borrowinf b on r.编号=b.读者编号 inner join books k on b.图书编号=k.编号 where 姓名=name,2019/2/5,输出参数 输出参数用于把返回值赋予变量并传给调用它的存储过程或应用程序。声明输出参数时需在声明参数的后面加上output，以表明此参数为输出参数。 【例7-22】 利用输出参数计算阶乘。 use mydb if exists ( select name from sysobjects where name=factorial and type=p) drop procedure factorial go,2019/2/5,create procedure factorial in float, out float output as declare i int,s float set i=1,s=1 while i=in begin set s=s*i set i=i+1 end set out=s 在查询分析器中，用下面一段代码执行此存储过程： declare ou float exec factorial 5,ou out print 其阶乘为：+rtrim(ou),2019/2/5,7.2.4 修改存储过程 使用企业管理器进行修改 修改存储过程只需在相应数据库的存储过程对象项中找到要修改的存储过程，双击存储过程打开存储过程属性窗口，在窗口中直接进行修改，完成后单击【确定】按钮。,2019/2/5,使用t-sql命令进行修改 使用alter procedure可以修改存储过程，其语法结构如下： alter procedure procedure_name ;number parameter data_type =default output,n with recompile|encryption|recompile,encryption for replication as sql-statement n 说明：alter procedure与create procedure类似。,2019/2/5,使用查询分析器进行修改 打开查询分析器，按f8键（或单击工具栏上的对象浏览器按钮），在对象浏览器窗口中，打开存储过程所在的数据库，打开存储过程文件夹，右击要修改的存储过程名（如test）,在弹出的菜单中选择【编辑】（如图7-13所示），则存储过程出现在查询分析器的编辑窗口中，修改完毕保存即可。,2019/2/5,图7-13 在查询分析器中编辑存储过程,2019/2/5,7.2.5 运行存储过程 存储过程的编译 首次运行存储过程时，sql server编译并检验其中的程序，如果发现错误，系统将拒绝运行此存储过程。 即使只执行一条transact-sql语句，也要创建一份执行计划，执行计划包括存储过程所需的表行的索引。执行计划保留在缓存中，用于后续执行时完成存储过程的查询任务，提高执行速度。当出现下列几种情况时，存储过程被重新编译： (1)当sql server重新启动，或存储过程第一次被执行时。 (2)存储过程修改后或其引用的表索引被删除后，执行计划被重新创建。 (3)当一个用户在使用缓冲区中的执行计划，重新编译为第二个用户创建第二个执行计划。 (4)存储过程删除或重建后，缓冲区中所有的执行计划备件都被删除，执行时自动进行重新编译，形成一份新的执行计划。,2019/2/5,存储过程的执行 具有存储过程执行许可execute的用户，才可以执行存储过程。在查询分析器中，可以直接输入存储过程名，指定相应的输入参数和输出参数后执行。或者利用execute命令执行存储过程与函数不同，它不能直接用过程名返回值，也不能直接在表达式中使用。execute的语法结构如下： execute return status = procedure_name；number| procedure_name_var) parameter= value| variable output | default ,n with recompile 说明： (1)利用with recompile可选项可以在执行存储过程时强制重新编译。 (2)return status：为一整型变量，用于保存存储过程的返回状态。,2019/2/5,7.2.6 删除存储过程 使用企业管理器删除存储过程 展开数据库后，单击存储过程图标，在右边窗口中出现的存储过程中选择要删除的过程，单击右键，在弹出的快捷菜单中单击【删除】选项，即将存储过程删除。 使用t-sql命令删除存储过程 利用drop procedure命令删除存储过程,其语法结构如下： drop procedureprocedure ，n 【例7-23】 删除上例中创建的存储过程borrowed_num。 use mydb go drop procedure borrowed_num,2019/2/5,7.2.7 查看存储过程的有关信息 使用sp helptext系统存储过程，可以查看定义存储过程的t-sql语句。在查询分析器中查看存储过程borrowed_books1的定义。 例如： sp_helptext borrowed_books1 使用企业管理器，也可以查看存储过程的定义。 如果存储过程的定义是被加密的，即在定义或修改存储过程的语句中使用了with encryption子句，则存储过程的定义以不可读的形式保存在syscomments表中。这时，将不能查看存储过程的定义。,2019/2/5,7.3 触发器,7.3.1 触发器基本知识 触发器及其作用 触发器是一种特殊的存储过程，是sql server为保证数据完整性、确保系统正常工作而设置的一种高级技术。触发器在特定的表上定义，该表也称为触发器表。当触发器所保护的数据发生变化时，触发器就会自动运行，以保证数据的完整性与正确性。 触发器有如下作用： 可以对数据库进行级联修改。 可以完成比check更复杂的约束。与check约束不同，在触发器中可以引用其他