1、 数据库对象管理数据库对象管理3.1 索引索引 3.2 视图视图 3.3 存储过程存储过程 3.4 触发器触发器23.1 索引的概念 3.1.1 索引的概念 3.1.2 创建索引的优点与缺点 3.1.3 考虑建索引的列和不考虑建索引的列 33.1.1 索引的概念 索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对一个表而建立的，它是由数据页面以外的索引页面组成的。数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。43.1.2 索引的优、缺点 1.创建索引的优点创建索引的优点 可以大大加快数据检索速度。
2、通过创建唯一索引，可以保证数据记录的唯一性。在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。5 2.创建索引的缺点 创建索引要花费时间和占用存储空间。创建索引需要占用存储空间，如创建聚簇索引需要占用的存储空间是数据库表占用空间的1.2倍。在建立索引时，数据被复制以便建立聚簇索引，索引建立后，再将旧的未加索引的表数据删除。创建索引也需要花费时间。建立索引加快了数据检索速度，却减慢了数据修改速度。因为每当执行一次数据的插
3、入、删除和更新操作，就要维护索引。修改的数据越多，涉及维护索引的开销也就越大。如果将一些数据行插入到一个已经放满行的数据页面上，还必须将这个数据页面中最后一些数据移到下一个页面中去，这样，还必须改变索引页中的内容，以保持数据顺序的正确性。这就是对索引的维护。由于修改数据时要动态维护其索引，所以，对建立了索引的表执行修改操作要比未建立索引的表执行修改操作所花的时间要长。因此，创建索引虽然可以加快数据查询的速度，但是却会减慢数据修改的速度。63.1.3考虑建索引的列和不考虑建索引的列考虑建索引的列和不考虑建索引的列 1.考虑建索引的列考虑建索引的列如果在一个列上创建索引，该列就称为索引列。索引列中
4、的值称为关键字值。考虑建索引的列有如下这些：主键通常，检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。连接中频繁使用的列用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。在某一范围内频繁搜索的列和按排序顺序频繁检索的列。7 2.不考虑建索引的列不考虑建索引的列 建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间，因此，没有必要对表中的所有列都建立索引。创建索引与否以及在哪些列上建立索引，要看建立索引和维护索引的代价与因建立索引所节省的时间相比哪个更合算。一般来说，如下一些列不考虑建立索
5、引：很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），以这样的列创建索引并不能得到建立索引的好处。以bit、text、image数据类型定义的列。数据行数很少的小表一般也没有必要创建索引。83.2 聚簇索引与非聚簇索引3.2.1 索引的分类索引的分类 根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引与非聚簇索引。和表及视图一样，索引也是数据库对象。聚簇索引聚簇索引（Clustered Index）数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录。非聚簇索
6、引非聚簇索引（Nonclustered Index）数据表的物理顺序和索引表的顺序不相同,索引表仅仅包含指向数据表的指针，这些指针本身是有序的，用于在表中快速定位数据。9 SQL Server 2005中，创建索引有两种方法：使用T-SQL语句创建索引 使用SQL Server Management Studio创建索引 在一个表的一个或多个列上创建索引时，应考虑以下几点：当在一个表上创建PRIMARY KEY约束或UNIQUE约束时，SQL Server自动创建唯一性索引。不能在已经创建PRIMARY KEY约束或UNIQUE约束的列上创建索引。定义PRIMARY KEY 约束或UNIQUE
7、约束与创建标准索引相比应是首选的方法。必须是表的拥有者才能创建索引。在一个列上创建索引之前，确定该列是否已经存在索引。也可以在视图上创建索引，但创建视图时必须带参数SCHEMABINDING。在视图上创建索引的创建方法参见SQL Server 2005随机帮助。3.3 索引的创建与管理10 3.3.1 使用T-SQL语句创建索引 3.3.2 使用T-SQL语句管理索引 3.3.3 使用SQL Server Management Studio管理索引3.3 索引的创建与管理111.创建索引的创建索引的SQL语句语法语句语法 创建索引使用的是CREATE INDEX语句。CREATE INDEX语
8、句的语法形式如下：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_nameON table_name（column_name ASC|DESC ,.n ）WITHPAD_INDEX，FILLFACTOR=fillfactor，DROP_EXISTING3.3.1 使用T-SQL语句创建索引12 在以上语法形式中：UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。CLUSTERED|NONCLUSTERED：指定被创建索引的类型。使用CLUSTERED创建的是聚簇索引；使用NONCLUSTERED创建的是非聚
9、簇索引。这两个关键字中只能选其中的一个。index_name：为新创建的索引指定的名字。table_name：创建索引的基表的名字。column_name：索引中包含的列的名字。ASC|DESC：确定某个具体的索引列是升序还是降序排序。默认设置为ASC升序。PAD_INDEX和FILLFACTOR：填充因子，它指定 SQL Server 创建索引的过程中，各索引页的填满程度。DROP_EXISTING：删除先前存在的、与创建索引同名的聚簇索引或非聚簇索引。13 2.2.创建唯一索引创建唯一索引 索引按照结构可分为聚簇索引和非聚簇索引两种不同的类型。按照实现的功能分，有一类索引被称作“唯一索引”
10、。它既可以采用聚簇索引的结构，又可以采用非聚簇索引的结构。唯一索引的特征：唯一索引的特征：不允许两行具有相同的索引值。可用于实施实体完整性。在创建主键约束和唯一约束时自动创建唯一索引。在已有数据的表上创建唯一索引时，如果在该列数据存在重复值，那么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。因为在一个表中，可能会有很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。14【例例3-1】在JWGL数据库的BOOK表上创建一个名为book_id_index的唯一性聚簇索引，索引关键字为book_id，升序，填充因子50%USE jwglGOCREATE
11、 UNIQUE CLUSTERED INDEX book_id_indexON book(book_id ASC)WITHFILLFACTOR =5015 3.创建复合索引创建复合索引 有些索引列只有一列，而有些索引列由两列或更多列组成。我们把由两列或更多列组成的索引称作“复合索引”。复合索引的特征复合索引的特征 把两列或更多列指定为索引列。将复合列作为一个整体进行搜索。创建复合索引中的列序不一定与表定义列序相同。【例例-2】在JWGL数据库的student_course表上创建一个名为student_course_index的非聚簇复合索引，索引关键字为student_id,course_i
12、d，升序，填充因子50%USE jwglGOCREATE NONCLUSTERED INDEX student_course_indexON student_course(student_id ASC,course_id ASC)WITHFILLFACTOR=5016 创建复合索引应注意的几点创建复合索引应注意的几点 查询的WHERE子句必须引用复合索引中的第一列，以便让查询优化程序使用该复合索引。被查询表中需要频繁访问的列应考虑建复合索引以提高查询性能。在一个复合索引中索引列最多可组合16列。列的顺序很重要，应首先定义最具唯一性的列，（column1，column2）上的索引不同于（colu
13、mn2，column1）上的索引。使用复合索引能增加查询性能，并减少表上创建索引的数量。171.使用使用T-SQL语句查看索引语句查看索引 在创建索引之前或在创建索引之后，可以用sp_helpindex或sp_help系统存储过程查看表的索引。【例例3-3】用系统存储过程sp_helpindex查看JWGL数据库中表book的索引信息。USE jwglGOEXEC sp_helpindex book 3.3.2 使用T-SQL语句管理索引18 2.使用使用T-SQL语句对索引更名语句对索引更名 在创建索引之后，可以用sp_ rename系统存储过程重新命名表的索引。【例例3-4】用系统存储过程
14、sp_ rename将表book的索引book_id_index 重新命名为book_id_index1。USE jwglGOsp_rename book.book_id_index,book_id_index1 注意：要重命名的索引要以“表名.索引名”的形式给出。但新索引名不能给出表名。但新索引名不能给出表名。19 3.使用使用T-SQL语句删除索引语句删除索引 在创建索引之后，如果该索引不再需要，可以用DROP语句将其删除。DROP语句的语法如下：DROP INDEX table.index,.n【例例3-5】用DROP语句将表book的索引“book_id_index1”删除。USE j
15、wglGODROP INDEX book.book_id_index1 注意：注意：被删除的索引要以“表名.索引名”的形式给出。删除索引时要注意，如果索引是在CREATE TABLE语句中创建的，只能用ALTER TABLE语句删除索引。如果索引是用CREATE INDEX创建的，可用DROP INDEX删除。20 使用SQL Server Management Studio可以创建索引。3.3.3 使用SQL Server Management Studio管理索引21 在SQL Server Management Studio的“对象资源管理器”面板中，使用与创建索引同样的方法即可看到该索
16、引对应的信息。使用系统存储过程sp_helpindex查看指定表的索引信息。【例6.5】使用系统存储过程sp_helpindex查看book数据库中book1表的索引信息。在SQL Server Management Studio查询窗口中运行如下命令：USE book GO EXEC sp_helpindex book1 GO显示索引信息 22 使用SQL Server Management Studio删除索引。3.3.3 使用SQL Server Management Studio管理索引233.2 视图 视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是
17、对数据表进行查询的结果。视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中的数据。当对通过视图看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。视图可以是一个数据表的一部分，也可以是多个基表的联合；视图也可以由一个或多个其他视图产生。一旦视图定义后,就可以用select语句象对真实表一样查询。243.2.1 创建视图 使用Transact-SQL语句创建视图 1.创建视图的SQL语句的语法形式CREATE VIEW .view_name (column_name ,.n )WITH ENCRYPTION AS sele
18、ct_statementFROM table_name WHERE search_conditionWITH CHECK OPTION 其中：view_name：为新创建的视图指定的名字，视图名称必须符合标识符规则。column_name：在视图中包含的列名,也可以在SELECT 语句中指定列名。25 table_name：视图基表的名字。select_statement：选择哪些列进入视图的SELECT语句。WHERE search_condition：基表数据进入视图所应满足的条件 WITH CHECK OPTION：迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。WITH
19、 ENCRYPTION：对视图的定义进行加密。2.用SQL语句创建视图的步骤 在创建视图时，应首先测试SELECT语句以确保能返回正确的结果。创建视图的步骤如下：编写用于创建视图的SELECT语句。对SELECT语句进行测试。检查测试结果是否正确，是否和预期的一样。创建视图。26 3.在创建视图的时候，应该考虑以下因素 在CREATE VIEW语句中，不能包括ORDER BY、GROUP BY 子句，也不能出现INTO关键字。创建视图所参考基表的列数最多为1024列。创建视图不能参考临时表。在一个批处理语句中，CREATE VIEW语句不能和其他Transact-SQL语句混合使用。尽量避免使
20、用外连接创建视图。27 使用SQL Server Management Studio创建视图 假设要从student表中建立一个性别为“男”、包含student_id、student_name、class_id、sex四列信息的视图。使用SQL Server Management Studio创建视图的具体步骤如下：1）首先进入SQL Server Management Studio。2）按顺序展开“数据库”、要创建视图所属的数据库、再展开“视图”子节点。3）右边“摘要”窗口显示的是数据库中已经存在的视图，右击窗口的空白处，在弹出的快捷菜单上选择“新建视图”项，系统弹出如图9-1的“添加表”窗
21、口，这个窗口用于为新创建的视图提供基础数据。该窗口有三个选项卡，表、视图及函数，这意味着可以以表、视图及表值函数为基础数据创建新的视图。28 4）点击“添加”，选择表student，再点击“关闭”。系统呈现如图9-2的视图建立窗口。图9-1 创建视图的窗口29图9-2 视图建立窗口30 在SQL查询条件窗格中输入查询条件语句：SELECT student_id,student_name,class_id,sex FROM student WHERE sex=1 5）确认结果正确后，点击工具栏上的“”按钮，保存当前创建的视图，输入视图的名称，点击“确定”按钮，一个视图也就创建完成了。313.2.
22、2 使用视图的优点和缺点 1.视图的优点 视图可以屏蔽数据的复杂性，简化用户对数据库的操作,还可以使用视图重新组织数据。视图可以让不同的用户以不同的方式看到不同或者相同的数据集。安全保护:视图可以定制不同用户对数据的访问权限。2.视图的缺点 性能降低:修改的限制:323.2.3 创建水平视图 视图的常见用法是限制用户只能够存取表中的某些数据行，用这种方法产生的视图称为水平视图，即表中行的子集。【例9-1】在数据库JWGL的表student上创建一个视图student_view1，视图的数据包括班级号为g99402 或g99403所有学生的资料。USE jwglGOCREATE VIEW stu
23、dent_view1ASSELECT *FROM studentWHERE（class_id=g99402 OR class_id=g99403）333.2.4 创建投影视图 如果限制用户只能存取表中的部分列的数据，那么，使用这种方法创建的视图就称为投影视图，即表中列的子集。【例9-2】创建一个名为“studdent_view2”的视图，它从数据库JWGL的student表中查询出性别为“男”的所有学生的姓名、性别、家庭住址资料。USE jwglGOCREATE VIEW student_view2ASSELECT student_id AS 学号,student_name AS 姓名,sex
24、 AS 性别,class_id AS 班级,home_addr AS 家庭住址,entrance_date AS 入学时间 ,birth AS 出生年月FROM studentWHERE sex=1 WITH CHECK OPTION 343.2.5 创建联合视图 使用视图的一个原因是简化多表查询,可以生成从多个表中提取数据的联合视图(joined View)把查询结果表示为一个单独的“可见表”。【例9-3】创建一个名为“student_view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及作者的
25、视图。USE jwglGOCREATE VIEW student_view3 WITH ENCRYPTION /*加密视图*/ASSELECT course.course_name AS 课程名,book.book_name AS 书名,book.publish_company AS 出版社,book.author AS 作者FROM course,book,class_courseWHERE(course.book_id=book.book_id AND class_course.course_id=course.course_id)AND (class_course.class_id=g9
26、9402)353.2.6 创建包含集合函数的视图 在视图定义中可以包含GROUP BY和集合函数，从而将这些汇总数据放到一个“可见”的表中，允许用户对它们做进一步的查询。要注意，出现在SELECT子句中的列名，要么包含在集合函数中，要么包含在 GROUP BY 子句中。【例9-4】使用集合函数SUM和GROUP BY子句以student_course表为基表，创建一个名为“student_sum_view4”、能显示所有学生学号和总成绩的视图。USE jwglGOCREATE VIEW student_sum_view4 (学号,总成绩)AS SELECT student_id,sum(gra
27、de)FROM student_courseGROUP BY student_id 注意：与水平视图和投影视图不同，本例产生的视图中的行与基本表中的行不是一一对应的，它是一些行数据的汇总，因此，不能通过视图来修改数据。363.2.7 创建视图的视图 前面创建的视图都是在表的基础上创建的，在视图的基础上还可以创建视图。【例9-5】从视图student_view1创建一个名为“student_view5”，能查询出班级名为“g99402”的所有学生资料的视图。USE jwglGOCREATE VIEW student_view5AS SELECT *FROM student_view1 WHERE
28、 class_id=g99402GO 373.2.8 查看视图信息 使用系统存储过程查看视图信息 sp_help 数据库对象名称 sp_helptext 视图（触发器、存储过程）sp_depends 数据库对象名称 1.使用系统存储过程 查看视图的名称、拥有者及创建日期等信息 sp_help student_view1 查看视图的定义脚本 sp_helptext student_view1 查看数据的来源 sp_depends student_view1 2.使用SQL Server Management Studio查看视图383.2.9 视图的修改 1使用SQL Server Manage
29、ment Studio修改视图(略)2使用Transact-SQL语句修改视图 ALTER VIEW view_name（column,.n）WITH ENCRYPTIONAS select_statement WITH CHECK OPTION 在以上语句的语法中：view_name：被修改的视图的名字。column_name：在视图中包含的列名。WITH CHECK OPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。39 table_name：视图基表的名字。WITH ENCRYPTION：对包含创建视图的SQL脚本进行加密。【例9-6】修改视图student_
30、view1的定义，使其从student表中查询出性别为“女”的所有学生的资料。USE jwgl GOALTER VIEW student_view1ASSELECT *FROM student WHERE sex=0 403.2.10 视图的删除 删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的数据并没有被删除。删除视图参考表的时候，视图不会被删除，视图必须被单独删除。删除视图有两种方法，一是使用SQL Server Management Studio删除，二是用DROP VIEW语句删除。1.用DROP VIEW语句删除视图 DROP VIEW语句的语法形式如下：DR
31、OP VIEW view_namel,view_name2,使用DROP VIEW语句可以一次删除多个视图。下面的例子是将student_view5视图删除。DROP VIEW student_view541 2.使用SQL Server Management Studio删除视图 进入SQL Server Management Studio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。在右侧的视图资料显示窗口上，右击视图student_view1，在弹出的快捷菜单中，单击“删除”菜单命令，再单击“全部除去”按钮即可将选定的视图删除
32、。423.2.12 视图数据查询、插入、修改与删除用T-SQL语句进行视图数据的查询、插入、修改与删除 使用T-SQL语句可以进行视图数据的查询、插入、修改与删除。其语法形式和对表中数据的查询、插入、修改与删除操作几乎一样。1.从视图中查询数据【例9-7】从视图student_view2中查询出学生姓名为“钱利”的学生资料。USE jwglGOSELECT *FROM student_view2WHERE 姓名=钱利 想一想，为什么WHERE子句不能用student_name=钱利?43 2.向视图插入数据【例9-8】向视图student_view2中插入一行数据。学号、姓名、性别、班级、家庭
33、住址入学时间,出生年月分别是“g9940210”，“赵青”,“男”,“g99402”，“南京中山北路10号”，1999-09-01,1985-01-09。USE jwglGOINSERT INTO student_view2(学号,姓名,性别,班级,家庭住址,入学时间,出生年月)VALUES (g9940210,赵青,1,g99402,南京中山北路10号,1999-09-01,1985-01-09)44 3.修改视图中的数据【例9-9】将视图student_view2中“钱利”同学的家庭住址改为“扬州市南京路8号”USE jwglGOUPDATE student_view2SET 家庭住址=扬
34、州市南京路8号WHERE 姓名=钱利 4.删除视图中的数据【例9-10】将视图student_view2中“钱利”同学的资料删除USE jwglGODELETE FROM student_view2WHERE 姓名=钱利45 用SQL Server Management Studio进行视图数据的插入、修改与删除 使用SQL Server Management Studio对视图插入、修改与删除数据 进入SQL Server Management Studio，展开相应的服务器组和相应的服务器节点。展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。右击要操作的视图名称，分别单
35、击“打开视图”、“返回所有行”，便会出现图9-3的视图数据显示窗口。插入数据：直接在视图的最后一行进行数据的插入。修改数据：直接点击要修改的数据进行修改。删除数据：右击图9-3的窗口的要删除数据行的最左列，在弹出的快捷菜单上点击“删除”操作完成后，点击工具栏上的“！”，确认视图数据的修改。463.3 存储过程 存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。系统存储过程系统存储过程
36、 系统存储过程是指安装SQL Server时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。扩展存储过程扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如C或C+等）创建自己的扩展过程。用户定义的存储过程用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。47存储过程的优点 存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，
37、可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：存储过程提供了处理复杂任务的能力 存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的
38、质量和效率。48 减少网络数据流量减少网络数据流量 存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。加快系统运行速度加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。加强系统安全性加强系统安全性 SQL Server可
39、以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。493.3.1 使用使用T-SQL语句创建存储过程语句创建存储过程 1.创建存储过程的创建存储过程的SQL语句语法语句语法 创建一个存储过程的语法如下：CREATE PROC EDURE OWNER.procedure_name(parameter data_type VARYING=default OUTPUT),.n WITH RECOMPILE|ENCRY
40、PTION|RECOMPILE,ENCRYPTION ASsql_statement.n 只有两个必需的参数必须传递给CREATE PROCEDURE语句：创建存储过程所需的procedure_name和和sql_statements。50 在以上语句的语法中：procedure_name：为新创建的存储过程所指定的名字，它必须遵循标准SQL Server命名约定，且必须在同一个数据库中是唯一的。parameter：存储过程的输入或输出参数。default：参数缺省值。WITH RECOMPILE：重编译选项。sql_statements：存储过程中实现功能的SQL语句。2.创建步骤创建步骤
41、一般来说，创建一个存储过程应按照以下步骤进行：编写SQL语句。测试SQL语句是否正确，并能实现功能要求。若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。执行该存储过程，验证其正确性。51 3.存储过程创建示例存储过程创建示例【例11-1】使用使用Transact-SQL语句在语句在books数据库中创建一个数据库中创建一个名为名为p_books的存储过程。该存储过程返回的存储过程。该存储过程返回books表中所有表中所有的图书记录。的图书记录。create proc p_books as select*from books 执行以上脚本，便可创建存储过程p_books。如
42、要执行该存储过程，可在查询分析器中执行如下语句：EXEC p_books52 4.创建存储过程的注意事项创建存储过程的注意事项 在创建存储过程的时候，需要注意如下几点：每个存储过程应该完成一项单独的工作。为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITH ENCRYPTION。一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。533.3.2 创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，其参数值在执行该存储过程时由调用该存储过程的语句给出。具体语法如下:parameter_name dat
43、aype=default其中：parameter_name：存储过程的输入参数名，必须以符号为前缀。执行该存储过程时，应该向输入参数提供相应的值。datatype：该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。default：如果执行存储过程时未提供该参数值，则使用DEFAULT值。54【例例11-2】继续上一节的例子。建立一个存储过程，选择某一个指定出版社的名称。create proc p_books_publisher publisher varchar(50)as select*from books where publisher=publisher55
44、用户可以运行下面的语句来执行它，以查询清华大学出版社出版的所有图书：exec p_books_publisher 清华大学出版社 这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。如将上例中第二行：publisher varchar(50)替换为：publisher varchar(50)=%，重新创建存储过程p_books_publisher，如果执行该存储过程时不提供任何参数，则执行返回的结果集将是空集，而不会产生错误。56
45、【例例11-3】为bookShop数据库建立一个存储过程，通过执行存储过程将用户信息添加到Users表。CREATE PROCEDURE spAddUser Userid varchar(50)=NULL,Pwd varchar(50)=NULL,Username varchar(50)=NULL,sex char(2)=NULL,address varchar(500)=NULL,email varchar(50)=NULL,telphone varchar(50)=NULL,mobile varchar(50)=NULL AS IF Userid IS NULL OR Pwd IS NUL
46、L OR Username IS NULL OR sex IS NULL57 BEGIN PRINT 请重新输入该用户信息！请重新输入该用户信息！PRINT 你必须提供用户的用户名、用户密码、你必须提供用户的用户名、用户密码、用户姓名及性别。用户姓名及性别。PRINT（用户地址、邮箱、固定电话和移动手（用户地址、邮箱、固定电话和移动手机可以为空）机可以为空）RETURN END else INSERT Users(userid,pwd,username,Sex,address,email,telphone,mobile)VALUES (userid,pwd,username,Sex,addre
47、ss,email,telphone,mobile)PRINT 用户用户+userid+的信息成功添加到表的信息成功添加到表Users中。中。583.3.3 创建带输出参数的存储过程 具体语法如下：parameter_name dataype=default OUTPUT 其中：parameter_name：存储过程的输出参数名，必须以符号为前缀。datatype：输出参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参
48、数的当前值。为了保存这个返回值，在调用该过程时在调用该过程时SQL调用脚本必须使用调用脚本必须使用OUTPUT关关键字。键字。59【例例11-4】创建一个实现加法计算并将运算结果作为输出参数的存储过程。CREATE PROCEDURE spAddValue1 INT,Value2 INT,ResultValue INT OUTPUTASSELECT ResultValue=Value1+Value2GO 创建了上面的存储过程之后，下面我们来看看怎样使用它。为了使用spAdd，接受其输出参数的返回值，调用它的程序中也必须定义一个变量，并使用OUTPUT关键字指定它为调用输出参数。60【例11-5
49、】执行spAdd存储过程，输入参数由value1和value2提供，输出参数valueTotal。DECLARE value1 INTDECLARE value2 INTDECLARE valueTotal INTSET value1=125SET value2=3SET valueTotal=34EXEC spAdd value1,value2,valueTotal OUTPUTPRINT CONVERT(CHAR(5),value1)+与+CONVERT(CHAR(5),value2)+的和等于:+CONVERT(CHAR(5),valueTotal)GO61 结果消息显示：125 与3
50、的和等于:128 在上述例子中，若调用中省略OUTPUT，调用仍能执行，但valueTotal值仍为34。若在存储过程的定义中省略OUTPUT，调用时会出错。实际上，每个存储过程的执行，都将自动返回一个返回状态（可以通过return_status获得），用于告诉调用程序“执行该存储过程的状况”。调用程序可根据返回状态作相应的处理。一般而言，系统使用0表示该存储过程执行成功。用户也可以在存储过程中使用RETURN来返回指定的值。623.3.4 查看、修改和删除存储过程查看存储过程查看存储过程 存储过程被创建以后，它的名字存放在当前数据库的系统表sysobjects中，创建它的源代码存放在sysc
