《数据库对象管理》PPT课件

数据库对象管理,3.1索引3.2视图3.3存储过程3.4触发器,2,3.1索引的概念,3.1.1索引的概念3.1.2创建索引的优点与缺点3.1.3考虑建索引的列和不考虑建索引的列,3,3.1.1索引的概念,索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对一个表而建立的，它是由数据页面以外的索引页面组成的。数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。,4,3.1.2索引的优、缺点,1.创建索引的优点可以大大加快数据检索速度。通过创建唯一索引，可以保证数据记录的唯一性。在使用ORDERBY和GROUPBY子句进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。,5,2.创建索引的缺点创建索引要花费时间和占用存储空间。创建索引需要占用存储空间，如创建聚簇索引需要占用的存储空间是数据库表占用空间的1.2倍。在建立索引时，数据被复制以便建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除。创建索引也需要花费时间。建立索引加快了数据检索速度，却减慢了数据修改速度。因为每当执行一次数据的插入、删除和更新操作，就要维护索引。修改的数据越多，涉及维护索引的开销也就越大。如果将一些数据行插入到一个已经放满行的数据页面上，还必须将这个数据页面中最后一些数据移到下一个页面中去，这样，还必须改变索引页中的内容，以保持数据顺序的正确性。这就是对索引的维护。由于修改数据时要动态维护其索引，所以，对建立了索引的表执行修改操作要比未建立索引的表执行修改操作所花的时间要长。因此，创建索引虽然可以加快数据查询的速度，但是却会减慢数据修改的速度。,6,3.1.3考虑建索引的列和不考虑建索引的列,1.考虑建索引的列如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。考虑建索引的列有如下这些：主键通常，检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。连接中频繁使用的列用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。在某一范围内频繁搜索的列和按排序顺序频繁检索的列。,7,2.不考虑建索引的列建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间，因此，没有必要对表中的所有列都建立索引。创建索引与否以及在哪些列上建立索引，要看建立索引和维护索引的代价与因建立索引所节省的时间相比哪个更合算。一般来说，如下一些列不考虑建立索引：很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），以这样的列创建索引并不能得到建立索引的好处。以bit、text、image数据类型定义的列。数据行数很少的小表一般也没有必要创建索引。,8,3.2聚簇索引与非聚簇索引,3.2.1索引的分类根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引与非聚簇索引。和表及视图一样，索引也是数据库对象。聚簇索引（ClusteredIndex）数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录。非聚簇索引（NonclusteredIndex）数据表的物理顺序和索引表的顺序不相同,索引表仅仅包含指向数据表的指针，这些指针本身是有序的，用于在表中快速定位数据。,9,SQLServer2005中，创建索引有两种方法：使用T-SQL语句创建索引使用SQLServerManagementStudio创建索引在一个表的一个或多个列上创建索引时，应考虑以下几点：当在一个表上创建PRIMARYKEY约束或UNIQUE约束时，SQLServer自动创建唯一性索引。不能在已经创建PRIMARYKEY约束或UNIQUE约束的列上创建索引。定义PRIMARYKEY约束或UNIQUE约束与创建标准索引相比应是首选的方法。必须是表的拥有者才能创建索引。在一个列上创建索引之前，确定该列是否已经存在索引。也可以在视图上创建索引，但创建视图时必须带参数SCHEMABINDING。在视图上创建索引的创建方法参见SQLServer2005随机帮助。,3.3索引的创建与管理,10,3.3.1使用T-SQL语句创建索引3.3.2使用T-SQL语句管理索引3.3.3使用SQLServerManagementStudio管理索引,3.3索引的创建与管理,11,1.创建索引的SQL语句语法创建索引使用的是CREATEINDEX语句。CREATEINDEX语句的语法形式如下：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable_name（column_nameASC|DESC,.n）WITHPAD_INDEX，FILLFACTOR=fillfactor，DROP_EXISTING,3.3.1使用T-SQL语句创建索引,12,在以上语法形式中：UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。CLUSTERED|NONCLUSTERED：指定被创建索引的类型。使用CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的是非聚簇索引。这两个关键字中只能选其中的一个。index_name：为新创建的索引指定的名字。table_name：创建索引的基表的名字。column_name：索引中包含的列的名字。ASC|DESC：确定某个具体的索引列是升序还是降序排序。默认设置为ASC升序。PAD_INDEX和FILLFACTOR：填充因子，它指定SQLServer创建索引的过程中，各索引页的填满程度。DROP_EXISTING：删除先前存在的、与创建索引同名的聚簇索引或非聚簇索引。,13,2.创建唯一索引索引按照结构可分为聚簇索引和非聚簇索引两种不同的类型。按照实现的功能分，有一类索引被称作“唯一索引”。它既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构。唯一索引的特征：不允许两行具有相同的索引值。可用于实施实体完整性。在创建主键约束和唯一约束时自动创建唯一索引。在已有数据的表上创建唯一索引时，如果在该列数据存在重复值，那么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。因为在一个表中，可能会有很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。,14,【例3-1】在JWGL数据库的BOOK表上创建一个名为book_id_index的唯一性聚簇索引，索引关键字为book_id，升序，填充因子50%USEjwglGOCREATEUNIQUECLUSTEREDINDEXbook_id_indexONbook(book_idASC)WITHFILLFACTOR=50,15,3.创建复合索引有些索引列只有一列，而有些索引列由两列或更多列组成。我们把由两列或更多列组成的索引称作“复合索引”。复合索引的特征把两列或更多列指定为索引列。将复合列作为一个整体进行搜索。创建复合索引中的列序不一定与表定义列序相同。【例-2】在JWGL数据库的student_course表上创建一个名为student_course_index的非聚簇复合索引，索引关键字为student_id,course_id，升序，填充因子50%USEjwglGOCREATENONCLUSTEREDINDEXstudent_course_indexONstudent_course(student_idASC,course_idASC)WITHFILLFACTOR=50,16,创建复合索引应注意的几点查询的WHERE子句必须引用复合索引中的第一列，以便让查询优化程序使用该复合索引。被查询表中需要频繁访问的列应考虑建复合索引以提高查询性能。在一个复合索引中索引列最多可组合16列。列的顺序很重要，应首先定义最具唯一性的列，（column1，column2）上的索引不同于（column2，column1）上的索引。使用复合索引能增加查询性能，并减少表上创建索引的数量。,17,1.使用T-SQL语句查看索引在创建索引之前或在创建索引之后，可以用sp_helpindex或sp_help系统存储过程查看表的索引。【例3-3】用系统存储过程sp_helpindex查看JWGL数据库中表book的索引信息。USEjwglGOEXECsp_helpindexbook,3.3.2使用T-SQL语句管理索引,18,2.使用T-SQL语句对索引更名在创建索引之后，可以用sp_rename系统存储过程重新命名表的索引。【例3-4】用系统存储过程sp_rename将表book的索引book_id_index重新命名为book_id_index1。USEjwglGOsp_renamebook.book_id_index,book_id_index1注意：要重命名的索引要以“表名.索引名”的形式给出。但新索引名不能给出表名。,19,3.使用T-SQL语句删除索引在创建索引之后，如果该索引不再需要，可以用DROP语句将其删除。DROP语句的语法如下：DROPINDEXtable.index,.n【例3-5】用DROP语句将表book的索引“book_id_index1”删除。USEjwglGODROPINDEXbook.book_id_index1注意：被删除的索引要以“表名.索引名”的形式给出。删除索引时要注意，如果索引是在CREATETABLE语句中创建的，只能用ALTERTABLE语句删除索引。如果索引是用CREATEINDEX创建的，可用DROPINDEX删除。,20,使用SQLServerManagementStudio可以创建索引。,3.3.3使用SQLServerManagementStudio管理索引,21,在SQLServerManagementStudio的“对象资源管理器”面板中，使用与创建索引同样的方法即可看到该索引对应的信息。使用系统存储过程sp_helpindex查看指定表的索引信息。【例6.5】使用系统存储过程sp_helpindex查看book数据库中book1表的索引信息。在SQLServerManagementStudio查询窗口中运行如下命令：USEbookGOEXECsp_helpindexbook1GO,显示索引信息,22,使用SQLServerManagementStudio删除索引。,3.3.3使用SQLServerManagementStudio管理索引,23,3.2视图,视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中的数据。当对通过视图看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。视图可以是一个数据表的一部分，也可以是多个基表的联合；视图也可以由一个或多个其他视图产生。一旦视图定义后,就可以用select语句象对真实表一样查询。,24,3.2.1创建视图,使用Transact-SQL语句创建视图1.创建视图的SQL语句的语法形式CREATEVIEW.view_name(column_name,.n)WITHENCRYPTIONASselect_statementFROMtable_nameWHEREsearch_conditionWITHCHECKOPTION其中：view_name：为新创建的视图指定的名字，视图名称必须符合标识符规则。column_name：在视图中包含的列名,也可以在SELECT语句中指定列名。,25,table_name：视图基表的名字。select_statement：选择哪些列进入视图的SELECT语句。WHEREsearch_condition：基表数据进入视图所应满足的条件WITHCHECKOPTION：迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。WITHENCRYPTION：对视图的定义进行加密。2.用SQL语句创建视图的步骤在创建视图时，应首先测试SELECT语句以确保能返回正确的结果。创建视图的步骤如下：编写用于创建视图的SELECT语句。对SELECT语句进行测试。检查测试结果是否正确，是否和预期的一样。创建视图。,26,3.在创建视图的时候，应该考虑以下因素在CREATEVIEW语句中，不能包括ORDERBY、GROUPBY子句，也不能出现INTO关键字。创建视图所参考基表的列数最多为1024列。创建视图不能参考临时表。在一个批处理语句中，CREATEVIEW语句不能和其他Transact-SQL语句混合使用。尽量避免使用外连接创建视图。,27,使用SQLServerManagementStudio创建视图假设要从student表中建立一个性别为“男”、包含student_id、student_name、class_id、sex四列信息的视图。使用SQLServerManagementStudio创建视图的具体步骤如下：1）首先进入SQLServerManagementStudio。2）按顺序展开“数据库”、要创建视图所属的数据库、再展开“视图”子节点。3）右边“摘要”窗口显示的是数据库中已经存在的视图，右击窗口的空白处，在弹出的快捷菜单上选择“新建视图”项，系统弹出如图9-1的“添加表”窗口，这个窗口用于为新创建的视图提供基础数据。该窗口有三个选项卡，表、视图及函数，这意味着可以以表、视图及表值函数为基础数据创建新的视图。,28,4）点击“添加”，选择表student，再点击“关闭”。系统呈现如图9-2的视图建立窗口。,图9-1创建视图的窗口,29,图9-2视图建立窗口,30,在SQL查询条件窗格中输入查询条件语句：SELECTstudent_id,student_name,class_id,sexFROMstudentWHEREsex=15）确认结果正确后，点击工具栏上的“”按钮，保存当前创建的视图，输入视图的名称，点击“确定”按钮，一个视图也就创建完成了。,31,3.2.2使用视图的优点和缺点,1.视图的优点视图可以屏蔽数据的复杂性，简化用户对数据库的操作,还可以使用视图重新组织数据。视图可以让不同的用户以不同的方式看到不同或者相同的数据集。安全保护:视图可以定制不同用户对数据的访问权限。2.视图的缺点性能降低:修改的限制:,32,3.2.3创建水平视图,视图的常见用法是限制用户只能够存取表中的某些数据行，用这种方法产生的视图称为水平视图，即表中行的子集。【例9-1】在数据库JWGL的表student上创建一个视图student_view1，视图的数据包括班级号为g99402或g99403所有学生的资料。USEjwglGOCREATEVIEWstudent_view1ASSELECT*FROMstudentWHERE（class_id=g99402ORclass_id=g99403）,33,3.2.4创建投影视图,如果限制用户只能存取表中的部分列的数据，那么，使用这种方法创建的视图就称为投影视图，即表中列的子集。【例9-2】创建一个名为“studdent_view2”的视图，它从数据库JWGL的student表中查询出性别为“男”的所有学生的姓名、性别、家庭住址资料。USEjwglGOCREATEVIEWstudent_view2ASSELECTstudent_idAS学号,student_nameAS姓名,sexAS性别,class_idAS班级,home_addrAS家庭住址,entrance_dateAS入学时间,birthAS出生年月FROMstudentWHEREsex=1WITHCHECKOPTION,34,3.2.5创建联合视图,使用视图的一个原因是简化多表查询,可以生成从多个表中提取数据的联合视图(joinedView)把查询结果表示为一个单独的“可见表”。【例9-3】创建一个名为“student_view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及作者的视图。USEjwglGOCREATEVIEWstudent_view3WITHENCRYPTION/*加密视图*/ASSELECTcourse.course_nameAS课程名,book.book_nameAS书名,book.publish_companyAS出版社,book.authorAS作者FROMcourse,book,class_courseWHERE(course.book_id=book.book_idANDclass_course.course_id=course.course_id)AND(class_course.class_id=g99402),35,3.2.6创建包含集合函数的视图,在视图定义中可以包含GROUPBY和集合函数，从而将这些汇总数据放到一个“可见”的表中，允许用户对它们做进一步的查询。要注意，出现在SELECT子句中的列名，要么包含在集合函数中，要么包含在GROUPBY子句中。【例9-4】使用集合函数SUM和GROUPBY子句以student_course表为基表，创建一个名为“student_sum_view4”、能显示所有学生学号和总成绩的视图。USEjwglGOCREATEVIEWstudent_sum_view4(学号,总成绩)ASSELECTstudent_id,sum(grade)FROMstudent_courseGROUPBYstudent_id注意：与水平视图和投影视图不同，本例产生的视图中的行与基本表中的行不是一一对应的，它是一些行数据的汇总，因此，不能通过视图来修改数据。,36,3.2.7创建视图的视图,前面创建的视图都是在表的基础上创建的，在视图的基础上还可以创建视图。【例9-5】从视图student_view1创建一个名为“student_view5”，能查询出班级名为“g99402”的所有学生资料的视图。USEjwglGOCREATEVIEWstudent_view5ASSELECT*FROMstudent_view1WHEREclass_id=g99402GO,37,3.2.8查看视图信息,使用系统存储过程查看视图信息sp_help数据库对象名称sp_helptext视图（触发器、存储过程）sp_depends数据库对象名称1.使用系统存储过程查看视图的名称、拥有者及创建日期等信息sp_helpstudent_view1查看视图的定义脚本sp_helptextstudent_view1查看数据的来源sp_dependsstudent_view12.使用SQLServerManagementStudio查看视图,38,3.2.9视图的修改,1使用SQLServerManagementStudio修改视图(略)2使用Transact-SQL语句修改视图ALTERVIEWview_name（column,.n）WITHENCRYPTIONASselect_statementWITHCHECKOPTION在以上语句的语法中：view_name：被修改的视图的名字。column_name：在视图中包含的列名。WITHCHECKOPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。,39,table_name：视图基表的名字。WITHENCRYPTION：对包含创建视图的SQL脚本进行加密。【例9-6】修改视图student_view1的定义，使其从student表中查询出性别为“女”的所有学生的资料。USEjwglGOALTERVIEWstudent_view1ASSELECT*FROMstudentWHEREsex=0,40,3.2.10视图的删除,删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的数据并没有被删除。删除视图参考表的时候，视图不会被删除，视图必须被单独删除。删除视图有两种方法，一是使用SQLServerManagementStudio删除，二是用DROPVIEW语句删除。1.用DROPVIEW语句删除视图DROPVIEW语句的语法形式如下：DROPVIEWview_namel,view_name2,使用DROPVIEW语句可以一次删除多个视图。下面的例子是将student_view5视图删除。DROPVIEWstudent_view5,41,2.使用SQLServerManagementStudio删除视图进入SQLServerManagementStudio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。在右侧的视图资料显示窗口上，右击视图student_view1，在弹出的快捷菜单中，单击“删除”菜单命令，再单击“全部除去”按钮即可将选定的视图删除。,42,3.2.12视图数据查询、插入、修改与删除,用T-SQL语句进行视图数据的查询、插入、修改与删除使用T-SQL语句可以进行视图数据的查询、插入、修改与删除。其语法形式和对表中数据的查询、插入、修改与删除操作几乎一样。1.从视图中查询数据【例9-7】从视图student_view2中查询出学生姓名为“钱利”的学生资料。USEjwglGOSELECT*FROMstudent_view2WHERE姓名=钱利想一想，为什么WHERE子句不能用student_name=钱利?,43,2.向视图插入数据【例9-8】向视图student_view2中插入一行数据。学号、姓名、性别、班级、家庭住址入学时间,出生年月分别是“g9940210”，“赵青”,“男”,“g99402”，“南京中山北路10号”，1999-09-01,1985-01-09。USEjwglGOINSERTINTOstudent_view2(学号,姓名,性别,班级,家庭住址,入学时间,出生年月)VALUES(g9940210,赵青,1,g99402,南京中山北路10号,1999-09-01,1985-01-09),44,3.修改视图中的数据【例9-9】将视图student_view2中“钱利”同学的家庭住址改为“扬州市南京路8号”USEjwglGOUPDATEstudent_view2SET家庭住址=扬州市南京路8号WHERE姓名=钱利4.删除视图中的数据【例9-10】将视图student_view2中“钱利”同学的资料删除USEjwglGODELETEFROMstudent_view2WHERE姓名=钱利,45,用SQLServerManagementStudio进行视图数据的插入、修改与删除使用SQLServerManagementStudio对视图插入、修改与删除数据进入SQLServerManagementStudio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。右击要操作的视图名称，分别单击“打开视图”、“返回所有行”，便会出现图9-3的视图数据显示窗口。插入数据：直接在视图的最后一行进行数据的插入。修改数据：直接点击要修改的数据进行修改。删除数据：右击图9-3的窗口的要删除数据行的最左列，在弹出的快捷菜单上点击“删除”操作完成后，点击工具栏上的“！”，确认视图数据的修改。,46,3.3存储过程,存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。系统存储过程系统存储过程是指安装SQLServer时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。扩展存储过程扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQLServer，用户可以使用编程语言（诸如C或C+等）创建自己的扩展过程。用户定义的存储过程由用户为完成某一特定功能而编写的存储过程。,47,存储过程的优点,存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：存储过程提供了处理复杂任务的能力存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。增强代码的重用性和共享性每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率。,48,减少网络数据流量存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。加快系统运行速度第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。加强系统安全性SQLServer可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。,49,3.3.1使用T-SQL语句创建存储过程,1.创建存储过程的SQL语句语法创建一个存储过程的语法如下：CREATEPROCEDUREOWNER.procedure_name(parameterdata_typeVARYING=defaultOUTPUT),.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement.n只有两个必需的参数必须传递给CREATEPROCEDURE语句：创建存储过程所需的procedure_name和sql_statements。,50,在以上语句的语法中：procedure_name：为新创建的存储过程所指定的名字，它必须遵循标准SQLServer命名约定，且必须在同一个数据库中是唯一的。parameter：存储过程的输入或输出参数。default：参数缺省值。WITHRECOMPILE：重编译选项。sql_statements：存储过程中实现功能的SQL语句。2.创建步骤一般来说，创建一个存储过程应按照以下步骤进行：编写SQL语句。测试SQL语句是否正确，并能实现功能要求。若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。执行该存储过程，验证其正确性。,51,3.存储过程创建示例【例11-1】使用Transact-SQL语句在books数据库中创建一个名为p_books的存储过程。该存储过程返回books表中所有的图书记录。createprocp_booksasselect*frombooks执行以上脚本，便可创建存储过程p_books。如要执行该存储过程，可在查询分析器中执行如下语句：EXECp_books,52,4.创建存储过程的注意事项在创建存储过程的时候，需要注意如下几点：每个存储过程应该完成一项单独的工作。为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITHENCRYPTION。一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。,53,3.3.2创建带输入参数的存储过程,输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，其参数值在执行该存储过程时由调用该存储过程的语句给出。具体语法如下:parameter_namedataype=default其中：parameter_name：存储过程的输入参数名，必须以符号为前缀。执行该存储过程时，应该向输入参数提供相应的值。datatype：该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。default：如果执行存储过程时未提供该参数值，则使用DEFAULT值。,54,【例11-2】继续上一节的例子。建立一个存储过程，选择某一个指定出版社的名称。createprocp_books_publisherpublishervarchar(50)asselect*frombookswherepublisher=publisher,55,用户可以运行下面的语句来执行它，以查询清华大学出版社出版的所有图书：execp_books_publisher清华大学出版社这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。如将上例中第二行：publishervarchar(50)替换为：publishervarchar(50)=%，重新创建存储过程p_books_publisher，如果执行该存储过程时不提供任何参数，则执行返回的结果集将是空集，而不会产生错误。,56,【例11-3】为bookShop数据库建立一个存储过程，通过执行存储过程将用户信息添加到Users表。CREATEPROCEDUREspAddUserUseridvarchar(50)=NULL,Pwdvarchar(50)=NULL,Usernamevarchar(50)=NULL,sexchar(2)=NULL,addressvarchar(500)=NULL,emailvarchar(50)=NULL,telphonevarchar(50)=NULL,mobilevarchar(50)=NULLASIFUseridISNULLORPwdISNULLORUsernameISNULLORsexISNULL,57,BEGINPRINT请重新输入该用户信息！PRINT你必须提供用户的用户名、用户密码、用户姓名及性别。PRINT（用户地址、邮箱、固定电话和移动手机可以为空）RETURNENDelseINSERTUsers(userid,pwd,username,Sex,address,email,telphone,mobile)VALUES(userid,pwd,username,Sex,address,email,telphone,mobile)PRINT用户+userid+的信息成功添加到表Users中。,58,3.3.3创建带输出参数的存储过程,具体语法如下：parameter_namedataype=defaultOUTPUT其中：parameter_name：存储过程的输出参数名，必须以符号为前缀。datatype：输出参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时SQL调用脚本必须使用OUTPUT关键字。,59,【例11-4】创建一个实现加法计算并将运算结果作为输出参数的存储过程。CREATEPROCEDUREspAddValue1INT,Value2INT,ResultValueINTOUTPUTASSELECTResultValue=Value1+Value2GO创建了上面的存储过程之后，下面我们来看看怎样使用它。为了使用spAdd，接受其输出参数的返回值，调用它的程序中也必须定义一个变量，并使用OUTPUT关键字指定它为调用输出参数。,60,【例11-5】执行spAdd存储过程，输入参数由value1和value2提供，输出参数valueTotal。DECLAREvalue1INTDECLAREvalue2INTDECLAREvalueTotalINTSETvalue1=125SETvalue2=3SETvalueTotal=34EXECspAddvalue1,value2,valueTotalOUTPUTPRINTCONVERT(CHAR(5),value1)+与+CONVERT(CHAR(5),value2)+的和等于:+CONVERT(CHAR(5),valueTotal)GO,61,结果消息显示：125与3的和等于:128在上述例子中，若调用中省略OUTPUT，调用仍能执行，但valueTotal值仍为34。若在存储过程的定义中省略OUTPUT，调用时会出错。实际上，每个存储过程的执行，都将自动返回一个返回状态（可以通过return_status获得），用于告诉调用程序“执行该存储过程的状况”。调用程序可根据返回状态作相应的处理。一般而言，系统使用0表示该存储过程执行成功。用户也可以在存储过程中使用RETURN来返回指定的值。,62,3.3.4查看、修改和删除存储过程,查看存储过程存储过程被创建以后，它的名字存放在当前数据库的系统表sysobjects中，创建它的源代码存放在syscomments系统表中。可以通过SQLServer2000提供的几个系统存储过程来查看用户存储过程的有关信息。1.用T-SQL语句查看存储过程查看存储过程的定义系统存储过程sp_helptext可查看未加密的存储过程的定义脚本，也可用于查看规则、默认值、用户定义函数、触发器或视图的定义脚本。使用其语法如下：sp_helptextobjname=name其中：objname=name是对象的名称，要查看的对象必须在当前数据库中。这里为存储过程名。例如，执行sp_helptextspAdd，可显示存储过程spAdd的定义脚本。,63,查看有关存储过程的信息使用系统存储过程sp_help可查看有关存储过程的信息。具体语法形式如下：sp_helpproc_name其中：proc_name：要查看的存储过程名。执行上面的语句后，系统将返回指定存储过程的名称、拥有者、类型和创建时间，并且返回这个存储过程所有参数的名称、类型、宽度、精度和默认值等信息。2.使用SQLServerManagementStudio查看存储过程1）展开服务器。2）分别展开“数据库”、“JWGL”、“可编程性”、“存储过程”。3）右击需要查看的存储过程，然后单击“属性”命令，系统将弹出如下图的“存储过程属性”窗口。4）点击“常规”，可以查看到该存储过程属于哪个数据库，创建日期，属于哪个数据库用户等信息。5）点击“权限”，可以为该存储过程添加用户并授予其权限。,64,修改存储过程1.使用T-SQL语句修改存储过程Transact-SQL中提供了ALTERPROCEDURE语句来更改已经创建的存储过程，它不会更改权限，也不影响相关的存储过程或触发器。它的语法如下所示：ALTERPROCEDUREprocedure_nameparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,65,2.使用SQLServerManagementStudio修改存储过程在SQLServerManagementStudio中修改存储过程的步骤如下：1）展开服务器。2）分别展开“数据库”、“JWGL”、“可编程性”、“存储过程”。3）右击需要修改的存储过程，然后单击“修改”命令。4）进行存储过程的修改。,66,删除存储过程对于不再需要的存储过程，可将其删除。可以使用SQL语句，也可以使用SQLServerManagementStudio。1.使用SQL语句删除存储过程从当前数据库中删除一个或多个存储过程的T-SQL语句是DROPPROCEDURE。具体语法如下：DROPPROCEDUREprocedure,n其中参数：procedure：是要删除的存储过程的名称。n：表示可以指定多个存储过程。例如，要将spAdd存储过程删除，则可以执行DROPPROCspAdd语句。,67,2.使用SQLServerManagementStudio删除存储过程使用SQLServerManagementStudio中删除存储过程的步骤如下：1）展开服务器。2）分别展开“数据库”、“JWGL”、“可编程性”、“存储过程”。3）右击需要删除的存储过程，然后单击“删除”命令。4）在系统弹出的“删除对象”对话框中点击“确定”即可删除该存储过程。,68,3.4触发器,触发器的概念及分类触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。,69,触发器（Trigger）是一种实施复杂数据完整性的特殊存储过程.在对表或视图执行UPDATE、INSERT或DELETE语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。触发器是与表紧密联系在一起的，是在特定表上进行定义的，这个特定表也被称为触发器表。触发器和一般的存储过程又有一些不同，它不可以像调用存储过程一样由用户直接调用执行。触发器与表是密不可分的，触发器是不能离开表而独立存在的，触发器主要用于保护表中的数据，实现数据的完整性。对表中数据的操作有三种基本类型，数据插入、修改、删除，因此，触发器也有三种类型：INSERT、UPDATE、DELETE。当向触发器表中插入数据时，如果该触发器表有INSERT类型的触发器，INSERT触发器就被触发执行。同样的道理，UPDATE触发器会被数据更新触发执行、DELETE触发器会被数据删除触发执行。,70,触发器的工作原理SQLServer为执行的触发器创建一个或两个专用的临时表：inserted表或者deleted表。inserted表和deleted表的结构总是与被该触发器作用的表的结构相同，而且只能由创建它们的触发器引用。它们是临时的逻辑表，由系统来维护，不允许用户直接对它们进行修改。它们存放于内存中，并不存放在数据库中。触发器工作完成后，与该触发器相关的这两个表也会被删除。1.INSERT触发器的工作原理当一个记录插入到表中时，INSERT触发器自动触发执行，相应的插入触发器创建一个inserted表，新的记录被增加到该触发器表和inserted表中。它允许用户参考初始的INSERT语句中的数据，触发器可以检查inserted表，以确定该触发器里的操作是否应该执行和如何执行。,71,2.DELETE触发器的工作原理当从表中删除一条记录时，DELETE触发器自动触发执行，相应的删除触发器创建一个deleted表，deleted表是个逻辑表，用于保存已经从表中删除的记录，该deleted表允许用户参考原来的DELETE语句删除的已经记录在日志中的数据。应该注意：当被删除的记录放在deleted表中的时候，该记录就不会存在于数据库的表中了。因此，deleted表和数据库表之间没有共同的记录。3.UPDATE触发器的工作原理修改一条记录就等于插入一条新记录，删除一条旧记录。进行数据更新也可以看成由删除一条旧记录的DELETE语句和插入一条新记录的INSERT语句组成。当在某一个触发器表的上面修改一条记录时，UPDATE触发器自动触发执行，相应的更新触发器创建一个deleted表和inserted表，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。,72,3.4.1触发器的创建,使用T-SQL语句创建触发器1.创建触发器的SQL语句语法：创建触发器的一般语法如下。CREATETRIGGERtrigger_nameONtable_nameWITHENCRYPTIONFORDELETE,INSERT,UPDATEASsql_statementtrigger_name：要创建的触发器名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。table_name：指定所创建的触发器与之相关联的表名。必须是一个现存的表。,73,WITHENCRYPTION：加密创建触发器的文本。FORINSERT,DELETE,UPDATE：指定所创建的触发器将在发生哪些事件时被触发，也即指定创建触发器的类型。“INSERT”，表示创建插入触发器；“DELETE”，表示创建删除触发器；“UPDATE”表示创建更新触发器。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，以逗号分隔这些选项。sql_statement：指定触发器执行的SQL语句。触发器只能在当前数据库中创建，并且一个触发器只能作用在一个表上。在同一条CREATETRIGGER语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。值得注意的是，在SQLServer中也可以对视图建立触发器，只要将视图名称作为table_name用在创建语法中就可以了。但是，对视图建立触发器有一些限制，详细信息请参考SQLServer联机丛书。,74,2.创建INSERT触发器下面以为表Basket建