公司管理数据库系统实例.ppt

《公司管理数据库系统实例.ppt》由会员分享，可在线阅读，更多相关《公司管理数据库系统实例.ppt（238页珍藏版）》请在三一办公上搜索。

1、实现公司管理数据库系统的编程,项 目 7,【教学目标】了解SQL编程的基础知识和SQL Server 2005提供的常用系统函数，具有常用代码的编写能力。,【能力目标】（1）能够正确应用T-SQL的表达式和基本控制语句。（2）能够根据项目需求分析编写简单的T-SQL程序。,【学习导航】使用T-SQL语句编写的程序可以通过SQL Server提供的SQL Server Management Studio查询分析器运行，也可以存储在数据库服务器上运行。本项目学习T-SQL编程基础知识，为后面存储过程、触发器的编程打好基础。,【工作任务】为了完成公司数据库管理系统的管理、维护和开发，用户需要编写一些

2、代码，来管理雇员的工作业绩信息和实现订购订单功能，以提高系统的效率。,理论知识准备,7.1.1 T-SQL编程元素 Transact-SQL（简称T-SQL）在支持标准SQL的同时，还对其进行了扩充，引入了变量定义、流程控制和自定义存储过程等语句，极大地扩展了SQL Server 2005的功能。使用T-SQL语句编写的程序可以通过SQL Server的查询分析器运行，也可以存储在数据库服务器上运行。任何应用程序，不管它是用什么形式的高级语言编写的，只要目的是向SQL Server的数据库管理系统发出命令以获得数据库管理系统的响应，最终都必须体现为以T-SQL语句为表现形式的指令。因此，无论是

3、数据库管理员，还是数据库应用程序的开发人员，要想深入掌握SQL Server 2005，认真学习T-SQL是必要的。,1T-SQL语法规则,T-SQL的语法规则如表所示。,2有效标识符 标识符用来标识服务器、数据库和数据库对象（如表、视图、索引、过程等）。T-SQL的保留字不能作为标识符。,SQL Server的标识符分为常规标识符和分隔标识符。（1）常规标识符。第一个字符必须是下列字符之一：26个大小写字母，以及来自其他语言的字母字符，还可以是下画线（_）、或者#。其他字符可以是大小写字母或其他国家/地区字符中的十进制数字、$、#、_。常规标识符不允许嵌入空格或其他特殊字符。,（2）分隔标识

4、符。用双引号“”或者方括号 分隔标识符。在SQL Server中，以符号开始的标识符表示局部变量或者参数；以开始的标识符表示全局变量或配置函数；以#开始的标识符表示临时表或过程；以#开始的标识符表示全局临时对象。标识符的字符长度不能超过128个字符，临时表标识符的长度不能超过116个字符。,3注释注释有两个作用：其一，对程序代码的功能及实现方式进行简要的解释和说明，以便于将来对程序代码进行维护；其二，可以把程序中暂时不用的语句加以注释，使它们暂时不被执行，等需要执行这些语句时，再将它们恢复。T-SQL支持以下两种类型的注释。,（1）多行注释。使用“/*”和“*/”可以将连续书写的多行语句设与注

5、释，如下所示。/*设置产品ID为外键，删除主键表行时级联删除从表相应行：*/（2）单行注释。使用“-”可以将单行书写的语句设与注释，如下所示。-声明局部变量,7.1.2 常用函数 函数对于任何程序设计语言都是非常关键的组成部分。SQL Server提供的函数分为聚合函数、配置函数、游标函数、日期函数、数学函数、元数据函数、行集函数、安全函数、字符串函数、系统函数、文本和图像函数几类。一些函数还提供了获得信息的快捷方法。函数有返回值，返回值的类型取决于所使用的函数。,1聚合函数 聚合函数也称为统计函数，它对一组值进行计算并返回一个数值。聚合函数经常与SELECT语句子句一起使用。例7.1 统计e

6、mployee表共有多少员工。实例分析：在employee表中的一行数据就是一个员工的信息，统计有多少员工就是统计employees表中有多少行。统计多少行使用count(*)的函数。在查询分析器中执行如下SQL语句。,USE companyinfoGOSELECT COUNT(*)AS员工总人数FROM employeeGO,执行结果如图7-2所示。,例7.2 统计p_order表中订购的所有产品的数量。实例分析：要统计p_order表所订购产品的数量，就要对“数量”列计算总和，这需要使用SUM函数。,图7-2 查询employee表中有多少员工,在查询分析器中执行如下SQL语句。USE c

7、ompanyinfoGOSELECT SUM(数量)FROM p_orderGO,例7.3 统计p_order表中订购量最少的产品。实例分析：统计产品订购量最少的产品需要使用MIN（）函数。在查询分析器中执行如下SQL语句。,USE companyinfoGOSELECT MIN(数量)FROM p_orderGO,2字符串函数字符串函数用于对字符串进行连接、截取等操作。表7-2列出了常用的字符串函数。,常用的字符串函数及其功能,例7.4 给出字符串“数据库”字符串在“大型数据库技术”中的位置。实例分析：该题需要给出“数据库”在“大型数据库技术”中的开始位置，使用CHARINDEX函数，字符表

8、达式1为“信息”，字符表达式2为“计算机信息工程系”。从字符表达式2的开始位置查找。本题不使用表。,在查询分析器中执行如下SQL语句。SELECT CHARINDEX(信息,计算机信息工程系)GO,例7.5 计算字符串“SQL Server数据库管理系统”的字符个数。实例分析：该题需要使用计算字符长度函数LEN，表示为：LEN(SQL Server数据库管理系统)。在查询分析器中执行如下SQL语句。,SELECT LEN(SQL Server数据库管理系统)GO,例7.6 显示信息：将“Hello”显示两次，然后间隔10个空格，再将“World”显示两次。实例分析：本题使用REPLICATE函

9、数，显示两次“Hello”表示为REPLICATE(HellO,2)；显示两次“World”表示为REPLICATE(World,2)。间隔10个空格使用SPACE函数表示为SPACE(10)。,在查询分析器中执行如下SQL语句。SELECT REPLICATE(HellO,2),SPACE(10),REPLICATE(World,2)GO,执行结果如图7-3所示。,图7-3 使用REPLICATE函数和SPACE函数,3日期函数 日期函数用来显示日期和时间的信息。它们处理dataUme和smaUdatatime的值，并对其进行算术运算。表7-3列出了常用的日期函数。,表7-3中日期元素的缩写

10、和取值范围在表7-4中给出。,日期元素及其缩写和取值范围,例7.7 显示服务器当前系统的日期与时间。实例分析：该题需要使用GETDATE函数，表示为GETDATA()。在查询分析器中执行如下SQL语句。,SELECT GETDATE()GO,例7.8 显示服务器当前系统的月份和月份名称。实例分析：显示当前系统的月份使用DATEPART(MONTH，GETDATE()，或者DATE-PART(MM，GETDATE()；显示当前系统的月份名称使用DATENAME(MONTH，GETDATE()，或者DATENAME(MM，GETDATE()。在查询分析器中执行如下SQL语句,SELECT DATE

11、PART(MONTH，GETDATE()SELECT DATENAME(MONTH，GETDATE()GO,执行结果如图7-4所示。,图7-4 使用DATEPART函数和DATENAME函数,例7.9 小张的生日为“1979/12/23”，使用日期函数计算小张的年龄。实例分析：本题计算服务器当前系统时间GETDATE()与小张生日“1979/12/23”之差，并以日期元素YEAR(或YY)的形式返回，用函数表示为,DATEDIFF(yy,1979/12/23,GETDATE()。在查询分析器中执行如下SQL语句。SELECT 年龄=DATEDIFF(yy,1979/12/23,GETDATE(

12、)GO,4系统函数,系统函数用来获取SQL常用的Serrer的有关信息。表7-5列出了常用的系统函数。,常用的系统函数,例7.10 显示主机名称。实例微软用户1分析：使用HOST_ NAME()函数。在查询分析器中执行如下SQL语句。,SELECT HOST_NAME()GO执行结果为：HAND。,例7.11 将字符串103456转换为数字。实例分析：需要使用转换函数CONVERT，转换后的数据类型为Decimal(10，4)。在查询分析器中执行如下SQL语句。SELECT CONVERT(Decimal(10,4),10.3456)GO执行结果为：10.3456。,5数学函数数学函数用来对数

13、值型数据进行数学运算。表7-6列出了常用的数学函数。,常用的数学函数,例7.12 返回大于或等于134.3933的最小整数；返回小于或等于134.393的最大整数。实例分析：该题需要使用CEILING和FLOOR函数，分别表示为CEILING(134393)和FLOOR(134.393)。在查询分析器中执行如下SQL语句。SELECT CEILING(134.393)SELECT FLOOR(134.393)GO 执行结果为：135 134,例7.13 计算34和,的值。,实例分析：该题使用POWER、SQRT函数，34表示为POWER(3，4)，,表示为SQRT(16)。在查询分析器中执行如

14、下SQL语句。,SELECT POWER(3,4)SELECT SQRT(16)GO,执行结果为：81 4.0。,7.1.3 常量与变量,常量和变量是程序设计中不可缺少的元素，下面介绍常量和变量的具体使用方法。,1常量,T-SQL的常量主要有以下几种。,（1）字符串常量。字符串常量包含在单引号内，由字母、数字字符（az、AZ和09）以及特殊字符（如!、和#）组成。例如，SQL Server 2005实例与应用。如果字符串常量中包含一个单引号，如Im a Student，可以使用两个单引号表示这个字符串常量内的单引号，即表示为：Im a Student。在字符串常量前面加上字符N，则表明该字符串

15、常量是Unicode字符串常量。例如，NMary是Unicode字符串常量；Mary是字符串常量。Unicode数据中的每个字符都使用2字节存储。字符数据中的每个字符则都使用一字节进行存储。,（2）数值常量。数值常量包括时间常量（datetime、smalldatetime）、整型常量（bigint、int、smallint、tinyint）、带有精度的常量（decimal、numeric）、浮点型常量（float）、实型常量（real）和货币型常量（money、smallmoney）几种。数值常量不需要使用引号。,datetime：范围在1753年1月1日9999年12月31日的日期和时间数

16、据。smalldatetime：范围在1900年1月1日2079年6月6日的日期和时间数据。bigint：范围在9 223 372 036 854 775 808（2631）9 223 372 036 854 775 807（2631）之间的整数。int：范围在2 147 483 648（231）2 147 483 647（2311）之间的整数。smallint：范围在32 768（215）32 767（2151）的整数数据。tinyint：0255范围内的整数。decimal：范围在1038+110381之间可以带有小数位的数值常量。例如，187 6.21。float：使用科学记数法表示1.

17、79E+3081.79E+308范围的数据。real：使用科学记数法表示3.40E+383.40E+38范围的数据。例如，101.5E6、54.8El0等。money：货币常量，范为2262261，存储大小为8字节。以$作为前缀，它可以 包含小数点。例如，$12.54、$768.32。smallmoney：范围为214 748364 8+214748 364 7，存储大小为4字节。,（3）日期常量。使用特定格式的字符日期值表示，并用单引号括起来，例如：19831231、1976/04/23、14:30:24、04:24PM、May 04，1998。,（4）uniqueidentifier常量。

18、表示全局唯一标识符（GUID）值的字符串。可以使用字符或二进制字符串格式指定。例如，6DF9B89F4513-4CAF-A5ADCEB24CC90CA5。,2变量,变量对应内存中的一个存储空间，它与常量不同。变量的值在程序执行过程中可能随时有所改变。变量有两种类型：局部变量和全局变量。（1）局部变量。局部变量是用户在程序中定义的变量，一次只能保存一个值，它仅在定义的程序范围内有效。局部变量可以临时存储数据。局部变量名总是以符号开始，最长为128个字符。使用DECLARE语句定义局部变量，内容包括定义局部变量名、数据类型和长度。局部变量的初值为NULL（空）。需要使用SELECT语句或SET语句

19、给局部变量赋值。SET语句一次只能给一个局部变量赋值；SELECT语句可以同时给一个或多个局部变量赋值。,例7.14 编写计算50与60之和的程序。实例分析：首先需要定义3个局部变量，分别用来存储两个整数以及两个整数之和。定义的3个局部变量分别为i、j、sum，数据类型均为int（整型）。定义局部变量之后，需要使用SET语句或者SELECT语句给局部变量赋值。使用PRINT语句显示局部变量的值。为了便于理解，给出了每行代码的注释。在查询分析器中执行如下SQL语句。,DECLARE i int,j int,sum int-定义3个局部变量SET i=50-给变量i赋值SET j=60-给变量j赋

20、值SELECT sum=i+j-将i和j的和赋值给变量sumPRINT sum-显示sum变量的内容GO,（2）全局变量。全局变量以开头，实际上它就是SQL Server的配置函数。对于全局变量只能使用，不能进行修改。,7.1.4 运算符,T-SQL的运算符和其他高级语言的运算符类似，用于将变量、常量和函数连接起来并指定在一个或多个表达式中执行的操作。表7-7列出了T-SQL的运算符。,字符串连接运算符（+）用来连接字符串。例如，SELECT SQL Server 2005+数据库技术显示结果为：SQL Server 2005数据库技术,7.1.5 批处理和流控语句,1批处理,批处理是由一个或

21、多个T-SQL语句组成的，应用程序将这些语句作为一个单元提交给SQL Server，并由SQL Server编译成一个执行计划，然后作为一个整体来执行。批处理的大小有一定的限制，批处理结束的符号或标志是GO。批处理可以交互地运行或在一个文件中运行。提交给T-SQL的文件可以包含多个批处理，其中每个批处理以GO命令结束。批处理的使用需要遵守以下规则。（1）除了CREATE DATABASE（创建数据库）、CREATE TABLE（创建表）和CREATE INDEX（创建索引）之外的其他大多数的CREATE命令要单独作为一个批处理。（2）调用（执行）存储过程时，如果它不是批处理中的第一个语句，则在

22、其前面必须加上EXE-CUTE（执行）。例7.15 创建一个显示employee表信息的视图V_Test，然后显示课程表的信息。实例分析：创建显示employee表信息的视图V_Test的语句如下。,CREATE VIEW V_TestASSELECT*FROM employee,显示雇员表的信息的语句为：SELECT*FROM employee 在查询分析器中执行如下SQL语句。,USE companyinfoCREATE VIEW V_TestASSELECT*FROM employeeSELECT*FROM employeeGO,执行后显示出错信息为：CREATE VIEW 必须是查询批

23、次中的第一个语句。在CREATE VIEW语句前面增加GO，使得USE companyinfo为一个批处理，CREATE VIEW为另一个批处理的第一条语句。执行如下SQL语句。,USE companyinfoGOCREATE VIEW V_TestASSELECT*FROM employeeSELECT*FROM employeeGO,执行后显示出错信息为：关键字SELECT 附近有语法错误。因为创建视图要单独作为一个批处理，所以应在SELECT*FROM employee语句前面加GO。修改后的SQL语句有3个批处理执行如下SQL语句后，其结果是正确的。USE companyinfoGOC

24、REATE VIEW V_TestASSELECT*FROM employeeGOSELECT*FROM employeeGO,（3）将声明一个局部变量和给该变量赋值的语句分别放在两个批处理中，观察系统给出的信息。,DECLARE MyVar INTGOSELECT MyVar=33GO,显示出错信息为：必须声明标量变量“MyVar”。对一个局部变量而言，其声明和赋值必须在同一个批处理中进行，正确的语句如下。,DECLARE MyVar INTSELECT MyVar=33GO,2流控语句,流程控制语句简称流控语句，它与T-SQL语句一起使用可以控制程序的流程。在批处理中可以使用流控语句，也可

25、以在存储过程、脚本或特定的查询内部使用它。下面首先给出一个例题，然后再逐步介绍流控语句。例7.16 计算1+2+3+4+10000的值，并显示计算结果。实例分析：首先声明两个局部变量：i和sum，两者均为int数据类型。其中，i为计数单元，sum用来保存计算结果。声明局部变量的语句为：DECLARE i int，sum int。然后给两个局部变量赋值，i初值为1，sum初值为0，赋值语句为：SELECT i=1，sum=0。该题需要采用循环程序结构，循环终止条件为 i10000。在查询分析器中执行如下SQL语句。,DECLARE i int,sum int SELECT i=1,sum=0 W

26、HILE i=10000 BEGIN SELECT sum=sum+i SELECT i=i+1 END SELECTl+2+3+4+10 000的和=sum GO,执行结果如图7-5所示。,图7-5 l+2+3+4+10000的计算结果,图7-5 l+2+3+4+10000的计算结果 从上面的两个程序可以看出，流控语句可以实现程序的3种基本结构：顺序结构、选择结构和循环结构。下面介绍T-SQL的流控语句。（1）IF语句。IF语句用来实现程序的选择结构，其语法如下。,IF逻辑表达式|语句1|语句块l|ELSE|语句2|语句块2|,IF语句的功能是：如果逻辑表达式的条件成立（为真），则执行语句1

27、或语句块1；否则，执行语句2或语句块2。语句块由一个以上的SQL语句构成，要用BEGIN和END将SQL语句括起来。ELSE部分在省略的情况下，当逻辑表达式不成立（为假）时，执行语句1或语句块1中的SQL语句。（2）BEGINEND语句。BEGIN和END用来定义语句块，它们必须成对出现。它将多个SQL语句括起来，相当于一个单一语句。（3）WHILE语句。WHILE语句用来实现循环结构，其语法如下。,WHILE逻辑表达式 语句块,WHILE语句的功能是：当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。（4）DECLARE语句。DECLARE语句用来声明局部变量，声明后的局部变量初值为NUL

28、L（空）。声明局部变量的语法如下。,DECLARE variable_name datatype,variable_name datatype,使用SELECT语句或SET语句给局部变量进行赋值，SELECT语句一次性可以给多个变量赋值，SET语句一次只能给一个变量赋值。使用SELECT语句赋值的语法如下。,SELECT local_variable=expression，n,局部变量必须在同一个批处理或过程中被声明和使用。（5）RETURN语句。RETURN语句实现无条件退出执行批处理、存储过程和触发器。RETURN语句可以返回个整数给调用它的过程或应用程序。其语法如下。,RETURN 整型

29、表达式,（6）WAITFOR语句。WAITFOR语句用于延迟或暂缓程序的执行，其语法如下。,WAITFOR DELAY time|TIMEtime,DELAY表示数据库服务器一直处于等待状态，直到经过所指定的时间过去，最长可达24小时。TIME设置SQLServer等待的时间。例如，在20：00执行SELECT*FROM employee的语句如下。,WAITFOR TIME20:00:00SELECT*FROM employee,再如，等待10 s，再执行SELECT*FROM employee微软用户1的语句如下。,WAITFOR DELAY00:00:10SELECT*FROM empl

30、oyee,（7）CASE语句。用于计算多个条件并为每个条件返回单个值，简化SQL表达式。CASE语句的语法如下。,CASE input_expresslOn WHEN when_expression THEN result_expression nELSE else_result_expressionEND,例7.17 对产品进行分类显示，要求显示产品ID、产品类别、产品名。实例分析：该题使用product表。如果类别ID是1的，显示产品类别“饮料”；如果类别ID是2的，显示产品类别“计算机耗材”。因此，需要用CASE语句判断类别ID的值，根据类别ID的取值不同，来显示相应的信息。在查询分析器

31、中执行如下SQL语句。,USE companyinfoGOSELECT 产品ID,产品类别=CASE 类别IDWHEN 1 THEN 饮料WHEN 2 THEN 计算机耗材ELSE 其他类产品END,产品名FROM productGO,执行结果如图7-6所示。,图7-6 使用CASE语句示例,编程获取某雇员的工作业绩信息 例7.18 使用T-SQL编程，获取“王孔若”的姓名、性别、出生年月及雇佣日期等信息；然后输出“王孔若”所做订单的明细表、订单数目及订单总金额。实例分析：由于订单明细表具体信息分布在p_order表（订单表）、product表（产品表）、customer表（客户表）中，故需要

32、用SELECT语句提取出这些表中的有用信息。所以，本题的完成涉及多个表操作。在查询查询器中执行如下SQL语句。,编程获取某雇员的工作业绩信息,USE companyinfo-定义SQL server的变量，必须以开头命名变量，用DECLARE定义变量DECLARE emp_Id int DECLARE name varchar(8)DECLARE sex char(2)DECLARE date1 datetimeDECLARE date2 datetime-为变量赋值SET name=王孔若-通过查询语句将字段的值赋值给变量SELECT emp_Id=雇员id,sex=性别,date1=出生日

33、期,date2=雇佣日期FROM employee WHERE 姓名=name-用PRINT语句结合函数格式化输出变量的值,PRINT 姓名:+name+性别：+sex+出生年月：+convert(char(4),year(date1)+年+convert(char(2),month(date1)+月+convert(char(2),day(date1)+日+雇用日期：+convert(char(4),year(date2)+年+convert(char(2),month(date2)+月+convert(char(2),day(date2)+日-输出雇员王孔若完成的订单明细SELECT cu

34、stomer.公司名称,customer.联系人姓名,product.产品名,P_order.数量,P_order.订货日期,employee.姓名,product.单价FROM employee,customer,product,P_orderWHERE employee.雇员id=emp_Id and P_order.雇员id=employee.雇员id and P_order.产品id=product.产品id and P_order.客户id=customer.客户id-输出雇员王孔若完成的订单数目以及订单总金额SELECT count(*)as 订单数目,sum(P_order.数量

35、*product.单价)as 总金额FROM employee,product,P_orderWHERE employee.雇员id=emp_Id and P_order.雇员id=employee.雇员id and P_order.产品id=product.产品id,执行结果如图7-7所示。,图7-7 显示雇员工作业绩信息,编程实现订购订单,例7.19 雇员“王孔若”签订了一个供应给“鹿城中学”50个优盘的订单，编程实现将订单涉及的相关信息写入数据库中。实例分析：一条订单会涉及产品、客户及雇员的有关信息，且这些信息分别存放在不同的表中，所以，要将订单涉及的相关信息写入数据库中，需要完成以下几

36、方面的操作。先要获取该订单的相关信息。然后在将这条订单信息添加到订单表（p_order）中的同时，还必须修改其他表中的信息，包括在客户表（customer)中添加对应于该订单记录对应的客户信息，在产品表（product)中修改产品的库存量，其值必须为当前库存量的值减去刚增加的订单记录中包含的数量值。同理，如果某雇员取消了一个订单，在各表中也必须修改或删除相应的记录信息。在查询分析器中执行如下SQL语句。,USE companyInfoDECLARE userId int-保存雇员IDDECLARE max_cusId int-保存客户ID的最大值DECLARE max_ordId int-保存

37、订单ID的最大值DECLARE storePro int-保存产品的库存量,DECLARE id_product int-保存供应产品的产品编号SELECT max_cusId=max(客户ID)FROM customerSELECT max_cusId=max_cusId+1-获取新插入客户的IDINSERT customer VALUES(max_cusId,鹿城中学,李汉明,05778235423,温州市学院路号,325000)SELECT storePro=库存量,id_product=产品ID FROM product WHERE 产品名=优盘SELECT max_ordId=max

38、(订单ID)FROM P_orderSELECT max_ordId=max_ordId+1-获取要插入的订单信息的订单IDSELECT userId=雇员ID FROM employee WHERE 姓名=王孔若 IF storePro50 PRINT 库存量不够ELSEBEGIN INSERT P_order VALUES(max_ordId,id_product,50,userId,max_cusId,getdate()UPDATE product SET 库存量=库存量-50 WHERE 产品ID=id_productEND,实训,7.4.1 实训目的（1）编写简单的T-SQL语句进行

39、基本语法练习。（2）根据项目需求分析编写简单的T-SQL语句，以提高系统的效率。7.4.2 实训要求（1）能正确理解和使用SQL Server变量。（2）能正确理解和使用SQL Server函数。（3）能使用流程控制语句编写顺序结构、选择结构和循环结构的程序。,7.4.3 实训内容与步骤 1函数及基本语句（1）计算你来到人世的总天数。（2）计算年龄为77岁对应的总天数。（3）计算出生日期为1983年12月21日的人的年龄。（4）将数值型数据转换成字符型。（5）查询课程表中课程类别是4个字的课程信息。（6）查询课程表中课程名称的第3个字是“信”的课程信息。（7）利用变量和打印语句显示最大报名人数

40、和最小报名人数。（8）求10！。,2综合编程（1）对课程进行分类统计，要求显示课程类别、课程名称和报名人数，计算各类课程的平均报名人数。查询结果要按照课程类别、报名人数升序排序。该题显示效果如图7-8所示。,图7-8 显示课程分类统计信息,（2）编写程序，用户可查询任意课程的报名人数，并把它返回给用户。用户调用该过程（分别用两门课程“世界旅游”和“智能建筑”测试），如报名人数大于25，则显示：“XX课程可以开班”，否则显示：“抱歉，XX课程不能开班”。,小结 本项目首先介绍了T-SQL的编程知识，着重介绍了批处理及流控语句。在此基础上详细介绍了两个能够提高公司管理效率的实用程序，从而进一步达到

41、综合编程的目的。,公司管理数据库系统中存储过程的应用,项 目 8,【教学目标】（1）了解存储过程的概念。（2）了解使用存储过程的优点。（3）了解系统存储过程的特点及用途。【能力目标】（1）能够创建、管理存储过程。（2）能够掌握执行存储过程的方法。（3）能够灵活运用存储过程来提高系统工作效率。【学习导航】本项目在介绍了存储过程的概念、优点及其与视图区别的基础上，着重讲解了利用Management Studio和T-SQL语句两种方式创建、执行无参数和有参数存储过程的方法，并对存储过程的重命名、修改和删除进行了介绍。在图8-1中，可以看到本项目在公司管理数据库系统开发中的环节及位置。,图8-1 本

42、项目在公司管理数据库系统开发中的环节及位置【工作任务】为了完成公司管理数据库系统的开发，需要创建相应的存储过程，以实现产品、产品订单、客户等信息的添加、删除、修改和查询等功能。,理论知识准备,8.1.1 存储过程的概念存储过程是T-SQL语句和流程控制语句的集合，存储过程能被编译和优化。当首次执行存储过程时，SQL Server为其产生查询计划并将其保留在内存中，以后在调用该存储过程时就不必再进行编译，这在一定程度上能改善系统的性能。当客户程序需要访问服务器上的数据时，如果直接执行T-SQL语句，一般要经过如下几个步骤。（1）将T-SQL语句发送到服务器。（2）服务器编译T-SQL语句。（3）

43、优化产生查询执行计划。（4）数据库引擎执行查询计划。（5）执行结果返回客户程序。使用存储过程可以将一些固定的操作集中起来交给SQL Server数据库服务器，以完成某个任务。存储过程有3种：用户自定义存储过程、系统存储过程、扩展存储过程。,8.1.2 存储过程的优点（1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。（2）通过通用编程结构和过程重用实现编程框架。如果业务规则发生了变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端应用程序。这样所有调用该存储过程的应用程序就会遵循新的业务规则。（3）通过隔离和加密的方法提高了数据库的安全性。数据库用户可以通过获得权限来执行存

44、储过程，而不必授予用户直接访问数据库对象的权限。这些对象将由存储过程来进行操作。另外，存储过程可以加密，这样用户就无法阅读存储过程中的T-SQL命令。这些安全特性将数据库结构和数据库用户隔离开来，进一步保证了数据的完整性和可靠性。8.1.3 存储过程与视图的比较（1）可以在单个存储过程中执行一系列T-SQL语句。存储过程可包含程序流、逻辑以及对数据库查询的T-SQL语句，而视图中只包含SELECT语句。,（2）视图不能接收参数，只能返回结果集，而存储过程可以接收参数，包括输入、输出参数，并能返回单个或多个结果集以及返回值，这样大大提高了应用的灵活性。一般，将经常用到的多个表的连接查询定义为视图

45、，而由存储过程完成复杂的一系列处理，在存储过程中也会经常用到视图。,8.2.1 利用Management Studio创建存储过程利用Management Studio创建存储过程的步骤如下。（1）展开“数据库”“可编程性”“存储过程”节点，用鼠标右键单击“存储过程”选项，在弹出的快捷菜单中选择“新建存储过程”命令，如图8-2所示。,创建存储过程,图8-2 选择“新建存储过程”命令,（2）打开“新建存储过程”对话框，在查询分析器中输入定义存储过程的T-SQL语句，如图8-3所示。（3）单击“执行”按钮或按【Alt】+【X】”组合键执行存储过程，结果如图8-4所示。（4）用鼠标右键单击“存储过程

46、”选项，在弹出的快捷菜单中选择“刷新”命令，查看存储过程，如图8-5所示。,图8-3 编辑存储过程窗口,图8-4 创建存储过程窗口,图8-5 查看存储过程,8.2.2 利用T-SQL语句创建存储过程可以使用T-SQL语句中的CREATE PROCEDURE 命令创建存储过程，创建存储过程前，应该注意下列几个事项。CREATE PROCEDURE 语句不能与其他的SQL语句在单个批处理中组合使用。必须具有数据库的CREATE PROCEDURE权限，以及对架构（在其下创建过程）的ALTER权限。存储过程是架构作用域内的对象，它们的名称必须遵循标识符的命名规则。只能在当前数据库中创建存储过程。不要

47、创建任何使用sp_作为前缀的存储过程。SQL Server 使用sp_前缀指定系统存储过程。利用T-SQL语句创建存储过程的基本语法如下。,其中，各参数的意义如下。schema_name：过程所属架构的名称。procedure_name：新存储过程的名称。过程名称必须遵循有关标识符的命名规则，并且在架构中必须唯一。,CREATE PROC|PROCEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING=default OUT|OUTPUT,.n WITH ENCRYPTION WITH RECO

48、MPILE AS;.n;:=BEGIN statements END,parameter：过程中的参数。在CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有2100个参数。通过将符号用作第一个字符来指定参数名称。参数名称必须符合有关标识符的命名规则。每个过程的参数仅用于该过程本身，其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。,type_schema_name.data_type：参数以及所

49、属架构的数据类型。除table 之外的其他所有数据类型均可以用做存储过程的参数。但是，cursor 数据类型只能用于OUTPUT 参数。可以为cursor 数据类型指定多个输出参数。VARYING：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适用于cursor 参数。default：参数的默认值。如果定义了default 值，则无须指定此参数的值，即可执行过程。默认值必须是常量或NULL。如果过程使用带LIKE关键字的参数，则可包含通配符：%、_、和。OUTPUT：指定参数是输出参数。此选项的值可以返回给调用EXECUTE 的语句。使用OUTPUT 参数将值返

50、回给过程的调用方。ENCRYPTION：指示SQL Server 将 CREATE PROCEDURE 语句的原始文本加密。RECOMPILE：对存储过程重新编译。：要包含在过程中的一条或多条SQL语句。【例8.1】使用T-SQL语句在companyinfo数据库中创建一个名为p_product的存储过程。该存储过程返回 product 表中商品类别ID为“1”的所有记录。在查询分析器中执行如下 SQL 语句。,USE companyinfoGOCREATE PROCEDURE p_productASSELECT*FROM product WHERE 类别ID=1GO,存储过程创建成功后，保存