数据库对象.ppt

《数据库对象.ppt》由会员分享，可在线阅读，更多相关《数据库对象.ppt（90页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库对象管理数据库对象管理3.1索引的概念3.1.1索引的概念3.1.2创建索引的优点与缺点3.1.3考虑建索引的列和不考虑建索引的列23.1.1索引的概念索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对一个表而建立的，它是由数据页面以外的索引页面组成的。数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。33.1.2索引的优、缺点1.创建索引的优点创建索引的优点可以大大加快数据检索速度。通过创建唯一索引，可以保证数据记录的唯一性。在使用ORDERBY和GROUPBY子句进行检索数据时，可以显著减少

2、查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。42.创建索引的缺点创建索引要花费时间和占用存储空间。创建索引需要占用存储空间，如创建聚簇索引需要占用的存储空间是数据库表占用空间的1.2倍。在建立索引时，数据被复制以便建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除。创建索引也需要花费时间。建立索引加快了数据检索速度，却减慢了数据修改速度。因为每当执行一次数据的插入、删除和更新操作，就要维护索引。修改的数据越多，涉及维护索引的开销也就越大。如果将一些数据行插入到一个已经放满行的数据

3、页面上，还必须将这个数据页面中最后一些数据移到下一个页面中去，这样，还必须改变索引页中的内容，以保持数据顺序的正确性。这就是对索引的维护。由于修改数据时要动态维护其索引，所以，对建立了索引的表执行修改操作要比未建立索引的表执行修改操作所花的时间要长。因此，创建索引虽然可以加快数据查询的速度，但是却会减慢数据修改的速度。53.1.3考虑建索引的列和不考虑建索引的列考虑建索引的列和不考虑建索引的列1.考虑建索引的列考虑建索引的列如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。考虑建索引的列有如下这些：主键通常，检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。连接

4、中频繁使用的列用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。在某一范围内频繁搜索的列和按排序顺序频繁检索的列。62.不考虑建索引的列不考虑建索引的列建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间，因此，没有必要对表中的所有列都建立索引。创建索引与否以及在哪些列上建立索引，要看建立索引和维护索引的代价与因建立索引所节省的时间相比哪个更合算。一般来说，如下一些列不考虑建立索引：很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。只有两个或很少几个值的列（如性别，只有两个值

5、“男”或“女”），以这样的列创建索引并不能得到建立索引的好处。以bit、text、image数据类型定义的列。数据行数很少的小表一般也没有必要创建索引。73.2聚簇索引与非聚簇索引3.2.1索引的分类索引的分类根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引与非聚簇索引。和表及视图一样，索引也是数据库对象。聚簇索引聚簇索引（ClusteredIndex）数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录。非聚簇索引非聚簇索引（NonclusteredIndex）数据表的物理顺序和索引表的顺序不相同,索引表仅仅包含指向数据表的指针，这些指针本身是

6、有序的，用于在表中快速定位数据。8SQLServer2005中，创建索引有两种方法：使用T-SQL语句创建索引使用SQLServerManagementStudio创建索引在一个表的一个或多个列上创建索引时，应考虑以下几点：当在一个表上创建PRIMARYKEY约束或UNIQUE约束时，SQLServer自动创建唯一性索引。不能在已经创建PRIMARYKEY约束或UNIQUE约束的列上创建索引。定义PRIMARYKEY约束或UNIQUE约束与创建标准索引相比应是首选的方法。必须是表的拥有者才能创建索引。在一个列上创建索引之前，确定该列是否已经存在索引。也可以在视图上创建索引，但创建视图时必须带参

7、数SCHEMABINDING。在视图上创建索引的创建方法参见SQLServer2005随机帮助。3.3索引的创建与管理93.3.1使用T-SQL语句创建索引3.3.2使用T-SQL语句管理索引3.3.3使用SQLServerManagementStudio管理索引3.3索引的创建与管理101.创建索引的创建索引的SQL语句语法语句语法创建索引使用的是CREATEINDEX语句。CREATEINDEX语句的语法形式如下：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable_name（column_name ASC|DESC,.n）WIT

8、HPAD_INDEX，FILLFACTOR=fillfactor，DROP_EXISTING3.3.1使用T-SQL语句创建索引11在以上语法形式中：UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。CLUSTERED|NONCLUSTERED：指定被创建索引的类型。使用CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的是非聚簇索引。这两个关键字中只能选其中的一个。index_name：为新创建的索引指定的名字。table_name：创建索引的基表的名字。column_name：索引中包含的列的名字。ASC|DESC：确定某个具体的索引

9、列是升序还是降序排序。默认设置为ASC升序。PAD_INDEX和FILLFACTOR：填充因子，它指定SQLServer创建索引的过程中，各索引页的填满程度。DROP_EXISTING：删除先前存在的、与创建索引同名的聚簇索引或非聚簇索引。122.2.创建唯一索引创建唯一索引索引按照结构可分为聚簇索引和非聚簇索引两种不同的类型。按照实现的功能分，有一类索引被称作“唯一索引”。它既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构。唯一索引的特征：唯一索引的特征：不允许两行具有相同的索引值。可用于实施实体完整性。在创建主键约束和唯一约束时自动创建唯一索引。在已有数据的表上创建唯一索引时，如果在该

10、列数据存在重复值，那么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。因为在一个表中，可能会有很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。13【例例3-1】在JWGL数据库的BOOK表上创建一个名为book_id_index的唯一性聚簇索引，索引关键字为book_id，升序，填充因子50%USEjwglGOCREATEUNIQUECLUSTEREDINDEXbook_id_indexONbook(book_idASC)WITHFILLFACTOR=50143.创建复合索引创建复合索引有些索引列只有一列，而有些索引列由两列或更多列组成。我们把

11、由两列或更多列组成的索引称作“复合索引”。复合索引的特征复合索引的特征把两列或更多列指定为索引列。将复合列作为一个整体进行搜索。创建复合索引中的列序不一定与表定义列序相同。【例例-2】在JWGL数据库的student_course表上创建一个名为student_course_index的非聚簇复合索引，索引关键字为student_id,course_id，升序，填充因子50%USEjwglGOCREATENONCLUSTEREDINDEXstudent_course_indexONstudent_course(student_idASC,course_idASC)WITHFILLFACTOR=

12、5015创建复合索引应注意的几点创建复合索引应注意的几点查询的WHERE子句必须引用复合索引中的第一列，以便让查询优化程序使用该复合索引。被查询表中需要频繁访问的列应考虑建复合索引以提高查询性能。在一个复合索引中索引列最多可组合16列。列的顺序很重要，应首先定义最具唯一性的列，（column1，column2）上的索引不同于（column2，column1）上的索引。使用复合索引能增加查询性能，并减少表上创建索引的数量。161.使用使用T-SQL语句查看索引语句查看索引在创建索引之前或在创建索引之后，可以用sp_helpindex或sp_help系统存储过程查看表的索引。【例例3-3】用系统存

13、储过程sp_helpindex查看JWGL数据库中表book的索引信息。USEjwglGOEXECsp_helpindexbook3.3.2使用T-SQL语句管理索引172.使用使用T-SQL语句对索引更名语句对索引更名在创建索引之后，可以用sp_rename系统存储过程重新命名表的索引。【例例3-4】用系统存储过程sp_rename将表book的索引book_id_index重新命名为book_id_index1。USEjwglGOsp_renamebook.book_id_index,book_id_index1注意：要重命名的索引要以“表名.索引名”的形式给出。但新索引名不能给出表名。但

14、新索引名不能给出表名。183.使用使用T-SQL语句删除索引语句删除索引在创建索引之后，如果该索引不再需要，可以用DROP语句将其删除。DROP语句的语法如下：DROPINDEXtable.index,.n【例例3-5】用DROP语句将表book的索引“book_id_index1”删除。USEjwglGODROPINDEXbook.book_id_index1注意：注意：被删除的索引要以“表名.索引名”的形式给出。删除索引时要注意，如果索引是在CREATETABLE语句中创建的，只能用ALTERTABLE语句删除索引。如果索引是用CREATEINDEX创建的，可用DROPINDEX删除。19

15、使用SQLServerManagementStudio可以创建索引。3.3.3使用SQLServerManagementStudio管理索引20在SQLServerManagementStudio的“对象资源管理器”面板中，使用与创建索引同样的方法即可看到该索引对应的信息。使用系统存储过程sp_helpindex查看指定表的索引信息。【例6.5】使用系统存储过程sp_helpindex查看book数据库中book1表的索引信息。在SQLServerManagementStudio查询窗口中运行如下命令：USEbookGOEXECsp_helpindexbook1GO显示索引信息21使用SQLS

16、erverManagementStudio删除索引。3.3.3使用SQLServerManagementStudio管理索引223.2视图视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中的数据。当对通过视图看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。视图可以是一个数据表的一部分，也可以是多个基表的联合；视图也可以由一个或多个其他视图产生。一旦视图定义后,就可以用select语句象对真实表一样查询。233.2

17、.1创建视图使用Transact-SQL语句创建视图1.创建视图的SQL语句的语法形式CREATEVIEW.view_name(column_name,.n)WITHENCRYPTIONASselect_statementFROMtable_nameWHEREsearch_conditionWITHCHECKOPTION其中：view_name：为新创建的视图指定的名字，视图名称必须符合标识符规则。column_name：在视图中包含的列名,也可以在SELECT语句中指定列名。24table_name：视图基表的名字。select_statement：选择哪些列进入视图的SELECT语句。WH

18、EREsearch_condition：基表数据进入视图所应满足的条件WITHCHECKOPTION：迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。WITHENCRYPTION：对视图的定义进行加密。2.用SQL语句创建视图的步骤在创建视图时，应首先测试SELECT语句以确保能返回正确的结果。创建视图的步骤如下：编写用于创建视图的SELECT语句。对SELECT语句进行测试。检查测试结果是否正确，是否和预期的一样。创建视图。253.在创建视图的时候，应该考虑以下因素在CREATEVIEW语句中，不能包括ORDERBY、GROUPBY子句，也不能出现INTO关键字。创建视图所参

19、考基表的列数最多为1024列。创建视图不能参考临时表。在一个批处理语句中，CREATEVIEW语句不能和其他Transact-SQL语句混合使用。尽量避免使用外连接创建视图。26使用SQLServerManagementStudio创建视图假设要从student表中建立一个性别为“男”、包含student_id、student_name、class_id、sex四列信息的视图。使用SQLServerManagementStudio创建视图的具体步骤如下：1）首先进入SQLServerManagementStudio。2）按顺序展开“数据库”、要创建视图所属的数据库、再展开“视图”子节点。3）右

20、边“摘要”窗口显示的是数据库中已经存在的视图，右击窗口的空白处，在弹出的快捷菜单上选择“新建视图”项，系统弹出如图9-1的“添加表”窗口，这个窗口用于为新创建的视图提供基础数据。该窗口有三个选项卡，表、视图及函数，这意味着可以以表、视图及表值函数为基础数据创建新的视图。274）点击“添加”，选择表student，再点击“关闭”。系统呈现如图9-2的视图建立窗口。图9-1 创建视图的窗口28图9-2视图建立窗口29在SQL查询条件窗格中输入查询条件语句：SELECTstudent_id,student_name,class_id,sexFROMstudentWHEREsex=15）确认结果正确后

21、，点击工具栏上的“”按钮，保存当前创建的视图，输入视图的名称，点击“确定”按钮，一个视图也就创建完成了。303.2.2使用视图的优点和缺点1.视图的优点视图可以屏蔽数据的复杂性，简化用户对数据库的操作,还可以使用视图重新组织数据。视图可以让不同的用户以不同的方式看到不同或者相同的数据集。安全保护:视图可以定制不同用户对数据的访问权限。2.视图的缺点性能降低:修改的限制:313.2.3创建水平视图视图的常见用法是限制用户只能够存取表中的某些数据行，用这种方法产生的视图称为水平视图，即表中行的子集。【例9-1】在数据库JWGL的表student上创建一个视图student_view1，视图的数据包

22、括班级号为g99402或g99403所有学生的资料。USEjwglGOCREATEVIEWstudent_view1ASSELECT*FROMstudentWHERE（class_id=g99402ORclass_id=g99403）323.2.4创建投影视图如果限制用户只能存取表中的部分列的数据，那么，使用这种方法创建的视图就称为投影视图，即表中列的子集。【例9-2】创建一个名为“studdent_view2”的视图，它从数据库JWGL的student表中查询出性别为“男”的所有学生的姓名、性别、家庭住址资料。USEjwglGOCREATEVIEWstudent_view2ASSELECTs

23、tudent_idAS学号,student_nameAS姓名,sexAS性别,class_idAS班级,home_addrAS家庭住址,entrance_dateAS入学时间,birthAS出生年月FROMstudentWHEREsex=1WITHCHECKOPTION333.2.5创建联合视图使用视图的一个原因是简化多表查询,可以生成从多个表中提取数据的联合视图(joinedView)把查询结果表示为一个单独的“可见表”。【例9-3】创建一个名为“student_view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用

24、教材的教材名、出版社及作者的视图。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)

25、343.2.6创建包含集合函数的视图在视图定义中可以包含GROUPBY和集合函数，从而将这些汇总数据放到一个“可见”的表中，允许用户对它们做进一步的查询。要注意，出现在SELECT子句中的列名，要么包含在集合函数中，要么包含在GROUPBY子句中。【例9-4】使用集合函数SUM和GROUPBY子句以student_course表为基表，创建一个名为“student_sum_view4”、能显示所有学生学号和总成绩的视图。USEjwglGOCREATEVIEWstudent_sum_view4(学号,总成绩)ASSELECTstudent_id,sum(grade)FROMstudent_cou

26、rseGROUPBYstudent_id注意：与水平视图和投影视图不同，本例产生的视图中的行与基本表中的行不是一一对应的，它是一些行数据的汇总，因此，不能通过视图来修改数据。353.2.7创建视图的视图前面创建的视图都是在表的基础上创建的，在视图的基础上还可以创建视图。【例9-5】从视图student_view1创建一个名为“student_view5”，能查询出班级名为“g99402”的所有学生资料的视图。USEjwglGOCREATEVIEWstudent_view5ASSELECT*FROMstudent_view1WHEREclass_id=g99402GO363.2.8 查看视图信息

27、 使用系统存储过程查看视图信息 sp_help 数据库对象名称sp_helptext 视图（触发器、存储过程）sp_depends 数据库对象名称 1.使用系统存储过程查看视图的名称、拥有者及创建日期等信息sp_helpstudent_view1查看视图的定义脚本sp_helptextstudent_view1查看数据的来源sp_dependsstudent_view12.使用SQLServerManagementStudio查看视图373.2.9视图的修改1使用SQLServerManagementStudio修改视图(略)2使用Transact-SQL语句修改视图ALTERVIEWview

28、_name（column,.n）WITHENCRYPTIONASselect_statementWITHCHECKOPTION 在以上语句的语法中：view_name：被修改的视图的名字。column_name：在视图中包含的列名。WITHCHECKOPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。38table_name：视图基表的名字。WITHENCRYPTION：对包含创建视图的SQL脚本进行加密。【例9-6】修改视图student_view1的定义，使其从student表中查询出性别为“女”的所有学生的资料。USEjwglGOALTERVIEWstudent

29、_view1ASSELECT*FROMstudentWHEREsex=0393.2.10视图的删除删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的数据并没有被删除。删除视图参考表的时候，视图不会被删除，视图必须被单独删除。删除视图有两种方法，一是使用SQLServerManagementStudio删除，二是用DROPVIEW语句删除。1.用DROPVIEW语句删除视图DROPVIEW语句的语法形式如下：DROPVIEWview_namel,view_name2,使用DROPVIEW语句可以一次删除多个视图。下面的例子是将student_view5视图删除。DROPVI

30、EWstudent_view5402.使用SQLServerManagementStudio删除视图进入SQLServerManagementStudio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。在右侧的视图资料显示窗口上，右击视图student_view1，在弹出的快捷菜单中，单击“删除”菜单命令，再单击“全部除去”按钮即可将选定的视图删除。413.2.12视图数据查询、插入、修改与删除用T-SQL语句进行视图数据的查询、插入、修改与删除使用T-SQL语句可以进行视图数据的查询、插入、修改与删除。其语法形式和对表中数据的查询、

31、插入、修改与删除操作几乎一样。1.从视图中查询数据【例9-7】从视图student_view2中查询出学生姓名为“钱利”的学生资料。USEjwglGOSELECT*FROMstudent_view2WHERE姓名=钱利想一想，为什么WHERE子句不能用student_name=钱利?422.向视图插入数据【例9-8】向视图student_view2中插入一行数据。学号、姓名、性别、班级、家庭住址入学时间,出生年月分别是“g9940210”，“赵青”,“男”,“g99402”，“南京中山北路10号”，1999-09-01,1985-01-09。USEjwglGOINSERTINTOstudent

32、_view2(学号,姓名,性别,班级,家庭住址,入学时间,出生年月)VALUES(g9940210,赵青,1,g99402,南京中山北路10号,1999-09-01,1985-01-09)433.修改视图中的数据【例9-9】将视图student_view2中“钱利”同学的家庭住址改为“扬州市南京路8号”USEjwglGOUPDATEstudent_view2SET家庭住址=扬州市南京路8号WHERE姓名=钱利4.删除视图中的数据【例9-10】将视图student_view2中“钱利”同学的资料删除USEjwglGODELETEFROMstudent_view2WHERE姓名=钱利44用SQLS

33、erverManagementStudio进行视图数据的插入、修改与删除使用SQLServerManagementStudio对视图插入、修改与删除数据进入SQLServerManagementStudio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。右击要操作的视图名称，分别单击“打开视图”、“返回所有行”，便会出现图9-3的视图数据显示窗口。插入数据：直接在视图的最后一行进行数据的插入。修改数据：直接点击要修改的数据进行修改。删除数据：右击图9-3的窗口的要删除数据行的最左列，在弹出的快捷菜单上点击“删除”操作完成后，点击工具栏

34、上的“！”，确认视图数据的修改。453.3存储过程存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQLServer服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。系统存储过程系统存储过程系统存储过程是指安装SQLServer时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。扩展存储过程扩展存储过程扩展存

35、储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQLServer，用户可以使用编程语言（诸如C或C+等）创建自己的扩展过程。用户定义的存储过程用户定义的存储过程由用户为完成某一特定功能而编写的存储过程。46存储过程的优点存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：存储过程提供了处理复杂任务的能力存储过程提供了许多标准SQL语言所

36、没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。增强代码的重用性和共享性每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率。47减少网络数据流量减少网络数据流量存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以

37、，使用存储过程可以减少网络中数据流量。加快系统运行速度加快系统运行速度第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。加强系统安全性加强系统安全性SQLServer可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。483.3.1使用使用T-SQL语句创建存储过程语句创

38、建存储过程1.创建存储过程的创建存储过程的SQL语句语法语句语法创建一个存储过程的语法如下：CREATEPROCEDUREOWNER.procedure_name(parameterdata_typeVARYING=defaultOUTPUT),.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement.n只有两个必需的参数必须传递给CREATEPROCEDURE语句：创建存储过程所需的procedure_name和和sql_statements。49在以上语句的语法中：procedure_name：为新创建的存储过程所指定的名

39、字，它必须遵循标准SQLServer命名约定，且必须在同一个数据库中是唯一的。parameter：存储过程的输入或输出参数。default：参数缺省值。WITHRECOMPILE：重编译选项。sql_statements：存储过程中实现功能的SQL语句。2.创建步骤创建步骤一般来说，创建一个存储过程应按照以下步骤进行：编写SQL语句。测试SQL语句是否正确，并能实现功能要求。若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。执行该存储过程，验证其正确性。503.存储过程创建示例存储过程创建示例【例11-1】使用使用Transact-SQL语句在语句在books数据库中创建一

40、个数据库中创建一个名为名为p_books的存储过程。该存储过程返回的存储过程。该存储过程返回books表中所有表中所有的图书记录。的图书记录。createprocp_booksasselect*frombooks执行以上脚本，便可创建存储过程p_books。如要执行该存储过程，可在查询分析器中执行如下语句：EXECp_books514.创建存储过程的注意事项创建存储过程的注意事项在创建存储过程的时候，需要注意如下几点：每个存储过程应该完成一项单独的工作。为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITHENCRYPTION。一般存储过程都是在服务器上创建和测试，在

41、客户机上使用时，还应该进行测试。523.3.2创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，其参数值在执行该存储过程时由调用该存储过程的语句给出。具体语法如下:parameter_namedataype=default其中：parameter_name：存储过程的输入参数名，必须以符号为前缀。执行该存储过程时，应该向输入参数提供相应的值。datatype：该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。default：如果执行存储过程时未提供该参数值，则使用DEFAULT值。53【例例11-2】继续上一节的例子

42、。建立一个存储过程，选择某一个指定出版社的名称。createprocp_books_publisherpublishervarchar(50)asselect*frombookswherepublisher=publisher54用户可以运行下面的语句来执行它，以查询清华大学出版社出版的所有图书：execp_books_publisher清华大学出版社这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。如将上例中第二行：publi

43、shervarchar(50)替换为：publishervarchar(50)=%，重新创建存储过程p_books_publisher，如果执行该存储过程时不提供任何参数，则执行返回的结果集将是空集，而不会产生错误。55【例例11-3】为bookShop数据库建立一个存储过程，通过执行存储过程将用户信息添加到Users表。CREATEPROCEDUREspAddUserUseridvarchar(50)=NULL,Pwdvarchar(50)=NULL,Usernamevarchar(50)=NULL,sexchar(2)=NULL,addressvarchar(500)=NULL,email

44、varchar(50)=NULL,telphonevarchar(50)=NULL,mobilevarchar(50)=NULLASIFUseridISNULLORPwdISNULLORUsernameISNULLORsexISNULL56BEGINPRINT请重新输入该用户信息！请重新输入该用户信息！PRINT你必须提供用户的用户名、用户密码、你必须提供用户的用户名、用户密码、用户姓名及性别。用户姓名及性别。PRINT（用户地址、邮箱、固定电话和移动手（用户地址、邮箱、固定电话和移动手机可以为空）机可以为空）RETURNENDelseINSERTUsers(userid,pwd,userna

45、me,Sex,address,email,telphone,mobile)VALUES(userid,pwd,username,Sex,address,email,telphone,mobile)PRINT用用户户+userid+的信息成功添加到表的信息成功添加到表Users中。中。573.3.3创建带输出参数的存储过程具体语法如下：parameter_namedataype=defaultOUTPUT其中：parameter_name：存储过程的输出参数名，必须以符号为前缀。datatype：输出参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。OUTPUT：指明该

46、参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时在调用该过程时SQL调用脚本必须使用调用脚本必须使用OUTPUT关关键字。键字。58【例例11-4】创建一个实现加法计算并将运算结果作为输出参数的存储过程。CREATEPROCEDUREspAddValue1INT,Value2INT,ResultValueINTOUTPUTASSELECTResultValue=Value1+Value2GO创建了上面的存储过程之后，下面我们来看看怎样使用它。为了使用spAdd，接

47、受其输出参数的返回值，调用它的程序中也必须定义一个变量，并使用OUTPUT关键字指定它为调用输出参数。59【例11-5】执行spAdd存储过程，输入参数由value1和value2提供，输出参数valueTotal。DECLAREvalue1INTDECLAREvalue2INTDECLAREvalueTotalINTSETvalue1=125SETvalue2=3SETvalueTotal=34EXECspAddvalue1,value2,valueTotalOUTPUTPRINTCONVERT(CHAR(5),value1)+与+CONVERT(CHAR(5),value2)+的和等于:+

48、CONVERT(CHAR(5),valueTotal)GO60结果消息显示：125与3的和等于:128在上述例子中，若调用中省略OUTPUT，调用仍能执行，但valueTotal值仍为34。若在存储过程的定义中省略OUTPUT，调用时会出错。实际上，每个存储过程的执行，都将自动返回一个返回状态（可以通过return_status获得），用于告诉调用程序“执行该存储过程的状况”。调用程序可根据返回状态作相应的处理。一般而言，系统使用0表示该存储过程执行成功。用户也可以在存储过程中使用RETURN来返回指定的值。613.3.4查看、修改和删除存储过程查看存储过程查看存储过程存储过程被创建以后，它的

49、名字存放在当前数据库的系统表sysobjects中，创建它的源代码存放在syscomments系统表中。可以通过SQLServer2000提供的几个系统存储过程来查看用户存储过程的有关信息。1.用用T-SQL语句查看存储过程语句查看存储过程查看存储过程的定义查看存储过程的定义系统存储过程sp_helptext可查看未加密未加密的存储过程的定义脚本，也可用于查看规则、默认值、用户定义函数、触发器或视图的定义脚本。使用其语法如下：sp_helptextobjname=name其中：objname=name是对象的名称，要查看的对象必须在当前数据库中。这里为存储过程名。例如，执行sp_helptex

50、tspAdd，可显示存储过程spAdd的定义脚本。62查看有关存储过程的信息查看有关存储过程的信息使用系统存储过程sp_help可查看有关存储过程的信息。具体语法形式如下：sp_helpproc_name其中：proc_name：要查看的存储过程名。执行上面的语句后，系统将返回指定存储过程的名称、拥有者、类型和创建时间，并且返回这个存储过程所有参数的名称、类型、宽度、精度和默认值等信息。2.使用使用SQLServerManagementStudio查看存储过程查看存储过程1）展开服务器。2）分别展开“数据库”、“JWGL”、“可编程性”、“存储过程”。3）右击需要查看的存储过程，然后单击“属性