《数据库程序设计》PPT课件.ppt

《《数据库程序设计》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《数据库程序设计》PPT课件.ppt（87页珍藏版）》请在装配图网上搜索。

1、第四章 T-SQL 语言,4.1 常量、变量与数据类型 4.2 运算符号与表达式 4.3 流程控制语句 4.4 系统内置函数 4.5用户定义函数,Transact-SQL（T-SQL）是微软公司在SQL Server数据库管理系统中ANSI SQL-99的实现。在SQL Server数据库中，T-SQL语言由以下几部分组成： 数据定义语言（DDL）。用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。 DDL包括的主要语句及功能如表.1所示。,(2) 数据操纵语言（DML）。用于操纵数据库中各种对象，检索和修改数据。DML包括的主要语句及功能如表.2所示。,(3)

2、数据控制语言（DCL）。用于安全管理，确定哪些用户可以查看或修改数据库中的数据，DCL包括的主要语句及功能如表.3所示。,4.1.1 常量,字符串常量 分为ASCII字符串常量和Unicode 字符串常量。 ASCII字符串常量是用单引号括起来，由ASCII字符构成的符号串。 Unicode 字符串常量与ASCII字符串常量相似，N 前缀必须大写字母。 ASCII字符串常量举例： China How do you! OBbaar /*如果单引号中的字符串包含引号，可以使用两个单引号表示嵌入的单引号。*/ Unicode 字符串常量举例： NChina NHow do you! NOBbaar

3、Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。,4.1.1 常量,2. 整型常量 按照整型常量的不同表示方式，又分为二进制整型常量、十六进制整型常量和十进制整型常量。 十六进制整型常量的表示：前辍 0 x 后跟十六进制数字串表示。十六进制常量的举例： 0 xEBF 0 x12Ff 0 x69048AEFDD010E 0 x /*空十六进制常量*/ 二进制整型常量的表示：即数字 0 或1，并且不使用引号。如果使用一个大于 1 的数字，它将被转换为1。 十进制整型常量即不带小数点的十进制数，例如： 1894 2 +145345234 -2147483648,4

4、.1.1 常量,3. 实型常量 实型常量有定点表示和浮点表示两种方式。举例如下： 定点表示： 1894.1204 2.0 +145345234.2234 -2147483648.10 浮点表示： 101.5E5 0.5E-2 +123E-3 -12E5,4日期时间常量 日期时间常量：用单引号将表示日期时间的字符串括起来构成。 SQL Server 可以识别如下格式的日期和时间： 字母日期格式，例：April 20, 2000 数字日期格式，例：4/15/1998、April 20，2000 未分隔的字符串格式，例：20001207、December 12，1998 如下是时间常量的例子： 14

5、:30:24 04:24:PM 如下是日期时间常量的例子： April 20, 2000 14:30:24,4.1.1 常量,5. money 常量 money 常量是以”$”作为前缀的一整型或实型常量数据。下面是 money 常量的例子： $12 $542023 -$45.56 +$423456.99 6. uniqueidentifier 常量 uniqueidentifier 常量是用于表示全局唯一标识符 (GUID) 值的字符串。可以使用字符或十六进制字符串格式指定。例如： 6F9619FF-8A86-D011-B42D-00004FC964FF 0 xff19966f868b11d0

6、b42d00c04fc964ff,4.1.2 数据类型,系统数据类型 (P38) 用户自定义数据类型,用户自定义数据类型student_num后，可以重新设计学生成绩管理数据库表XS、XS_KC结构中的学号字段。如下表5.45.6所示。,4.1.2 数据类型,创建用户自定义数据类型时首先应考虑如下三个属性： (1) 数据类型名称 (2) 新数据类型所依据的系统数据类型（又称为基类型） (3) 为空性 如果为空性未明确定义，系统将依据数据库或连接的 ANSI Null 默认设置进行指派。,下面介绍用户如何自定义数据类型。创建用户自定义数据类型的方法如下：,1）利用企业管理器定义 步骤如下： 第1

7、步 启动企业管理器，并登录到指定的SQL Server服务器，选定的用户数据库图标如：XSCJ，双击，进入企业管理器界面；,4.1.2 数据类型,第2步 选择“用户定义的数据类型” 图标右击，出现一快捷菜单，如图4.1所示。选择“新建用户定义数据类型”，进入用户数据类型定义属性窗口，如图4.2所示。,4.1.2 数据类型,第3步 在图4.2中各位置输入相应内容： 在“数据类型”列表中，选择所依赖的系统数据类型。 键入允许的最大长度。长度可变的数据类型有：binary、char、nchar、nvarchar、varbinary 和 varchar。 若允许此数据类型接受空值，请选择“允许空值”。

8、 选择“确定”，这样数据类型即定义好了。,2） 利用命令定义数据类型,在各语法格式中出现的sp表示存储过程（stored procedure）。 语法格式： sp_addtype typename = type, /*定义自定义类型名称*/ phystype = system_data_type /*定义自定义类型的基类型*/ , nulltype = null_type /*定义为空性*/ , owner = owner_name /*定义新类型的创建者或所有者*/,例如,Use xscj Exec sp_addtype student _num,char(6),not null go,在企

9、业管理器中删除,3) 删除用户自定义数据类型,4.1.2 数据类型,利用命令删除用户自定义数据类型 语法格式： sp_droptype typename= type type用户自定义数据类型的名称，应用单引号括起来。 例如：删除前面定义的student_num类型的语句为： USE XSCJ /*打开数据库*/ EXEC sp_drop type student _num /*调用存储过程*/ GO /*执行语句*/,4) 执行权限 执行权限默认授予 sysadmin 固定服务器角色、db_ddladmin 和 db_owner 固定数据库角色成员以及数据类型所有者。,例如：在企业管理器中，

10、对于XS表学号字段的定义如图所示。,3利用自定义类型定义字段 不同点只是数据类型为用户自定义类型，而不是系统类型。,利用命令定义XS表结构如下： USE XSCJ CREATE TABLE XS ( 学号 student_num, /*将学号定义为student_num类型*/ 姓名 char(8) NOT NULL, 专业名 char(10) NULL, 性别 bit NOT NULL, 出生时间 smalldatetime NOT NULL, 总学分 tinyint NULL, 备注 text NULL ) GO,4.1.3 变量,1. 变量 变量名必须是一个合法的标识符。 1）标识符 (

11、1) 常规标识符 以ASCII字母、Unicode字母、下划线 (_)、或#开头，后续可跟一个或若干个ASCII字符、Unicode字符、下划线 (_)、美元符号（$）、或#，但不能全为下划线(_)、或#。 (2) 分隔标识符 包含在双引号 () 或者方括号 ( ) 内的常规标识符或不符合常规标识符规则的标识符。,2）变量的分类 (1) 全局变量 全局变量由系统提供且预先声明，通过在名称前加两个“”符号区别于局部变量。 (2) 局部变量 局部变量用于保存单个数据值。例如，保存运算的中间结果，作为循环变量等。,4.1.3 变量,3. 局部变量的使用 1) 局部变量的定义与赋值 (1) 局部变量的

12、定义 在批处理或过程中用 DECLARE 语句声明局部变量，所有局部变量在声明后均初始化为NULL。 语法格式： DECLARE local_variable data_type ,.n,用SET语句赋值 将DECLARE语句创建的局部变量设置为给定表达式的值。 语法格式： SET local_variableexpression,【例4.1】创建局部变量var1、var2，并赋值，然后输出变量的值。 DECLARE var1,var2 char(20) SET var1中国 /*一个SET语句只能给一个变量赋值*/ SET var2var1+是一个伟大的国家 SELECT var1,var2

13、 Go,修正【例4.1】,创建局部变量var1、var2，并赋值，然后输出变量的值。 DECLARE var1 char(20),var2 char(20) SET var1=中国 /*一个SET语句只能给一个变量赋值*/ -var1右边填充空格了 SET var2=(rtrim(var1)+是一个伟大的国家) SELECT var2 - 中国是一个伟大的国家,4.1.3 变量,【例4.2】创建一个名为sex的局部变量，并在 SELECT 语句中使用该局部变量查找表XS中所有女同学的学号、姓名。 USE XSCJ DECLARE sex bit SET sex=0 SELECT 学号,姓名 F

14、ROM XS WHERE 性别=sex GO,【例4.3】使用查询给变量赋值。 USE XSCJ DECLARE student char(8) SET student=(SELECT 姓名 FROM XS) GO 用SELECT语句赋值 语法格式： SELECT local_variable=expression ,n,4.1.3 变量,【例4.4】在 XS 表中不存在ID_number 字段，因此对该表的查询不返回结果，变量var1将保留原值。 USE XSCJ DECLARE var1 nvarchar(30) SELECT var1 = 刘丰 SELECT var1 = 姓名 FROM

15、 XS WHERE 学号 = 64122312111 SELECT var1 AS NAME,4.1.3 变量,【例4.5】例中子查询用于给 var1 赋值。在 XS 表中ID_number不存在，因此子查询不返回值并将变量var1设为 NULL。 USE XSCJ DECLARE var1 nvarchar(30) SELECT var1 = 刘丰 SELECT var1 = (SELECT 姓名 FROM XS WHERE 学号 = 64122312111) SELECT var1 AS NAME,4.1 常量、变量与数据类型 4.2 运算符号与表达式 4.3 流程控制语句 4.4 系统内

16、置函数 4.5用户定义函数,4.2 运算符与表达式,1算术运算符 算术运算符有：+（加）、-（减）、*（乘）、/（除）和%（求模）五种运算。+ (加) 和(减) 运算符也可用于对 datetime 及 smalldatetime 值进行算术运算。 【例4.7】求学生的年龄。 USE XSCJ SET NOCOUNT ON DECLARE startdate datetime SET startdate = getdate() SELECT startdate 出生时间 AS 年龄 FROM XS,修正【例4.7】,【例4.7】求学生的年龄。 DECLARE startdate datetime

17、 SET startdate = getdate() SELECT datediff(yyyy,出生时间,getdate() FROM XS,2位运算符 位运算符在两个表达式之间执行位操作，这两个表达式的类型可为整型或与整型兼容的数据类型（如：字符型，等，但不能为 image 类型），位运算符如表4.7所示。 按位与 & 按位或 | 按位互斥 ,4.2 运算符与表达式,4.2 运算符与表达式,3. 比较运算符 比较运算符(又称关系运算符)如表4.8所示，用于测试两个表达式的值是否相同，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE 及 UNKNOWN。,4.2 运算符与表达式,用于查

18、询指定学号的学生在XS表中的信息。 USE XSCJ DECLARE student student_num SET student = 990202 IF (student 0) SELECT * FROM XS WHERE 学号 = student,4.2 运算符与表达式,4. 逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为 TRUE 或 FALSE。SQL Server提供的逻辑运算符如表4.9所示。,4.2 运算符与表达式,3）LIKE的使用 语法格式： match_expression NOT LIKE pattern ESCAPE escape_character ,4.

19、2 运算符与表达式,【例4.13】查询课程名以C或A开头的情况。 USE XSCJ SELECT * FROM KC WHERE 课程名 LIKE AC%,4.2 运算符与表达式,5. 字符串联接运算符 通过运算符“+”实现两个字符串的联接运算。 【例4.16】多个字符串的联接。 USE XSCJ SELECT (学号+ , + SPACE(1) + 姓名) AS 学号及姓名 FROM XS WHERE SUBSTRING(学号, 1, 2) = 19,6. 一元运算符 一元运算符有+（正）、-（负）和（按位取反）三个。+、-一远运算符是大家熟悉的。对于按位取反运算符举例如下： 设a的值为12

20、（0000 0000 0000 1100），计算：a的值为：1111 1111 1111 0011 7. 赋值运算符 指给局部变量赋值的SET和SELECT语句中使用的“=”。,运算符优先级如表4.11所示。在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。,8. 运算符的优先顺序 当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序会影响所得到的运算结果。,4.1 常量、变量与数据类型 4.2 运算符号与表达式 4.3 流程控制语句 4.4 系统内置函数 4.5用户定义函数,4.3 流程控制语句,在SQL Server中提供了如表4.12所示

21、的流程控制语句。,【例4.17】如下程序用于查询总学分40的学生人数。 USE XSCJ DECLARE num int SELECT num=(SELECT COUNT(姓名) FROM XS WHERE 总学分40) IF num0 SELECT num AS 总学分40的人数,4.3.1 IF.ELSE语句,在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的 T-SQL 语句，可用IF.ELSE语句实现。 语法格式： IF Boolean_expression /*条件表达式*/ sql_statement | statement_block /*条件表达式为真时执行*/

22、 ELSE sql_statement | statement_block /*条件表达式为假时执行*/,上述语法格式，可看出条件语句分带ELSE部分和不带ELSE部分两种使用形式： (1) IF 条件表达式 A /* T-SQL语句或语句块*/ ELSE B /*T-SQL语句或语句块*/ 当条件表达式的值为真时执行A，然后执行IF语句的下一语句；条件表达式的值为假时执行B，然后执行IF语句的下一语句。,4.3.1 IF.ELSE语句,(2) IF 条件表达式 A /*T-SQL语句或语句块*/ 当条件表达式的值为真时执行A，然后执行IF语句的下一条语句；条件表达式的值为假时直接执行IF语句

23、的下一条语句。,4.3.1 IF.ELSE语句,【例4.18】如果“数据库原理课程”的平均成绩高于75分，则显示“平均成绩高于75分”。 USE XSCJ DECLARE text1 char(20) SET text1=平均成绩高于75. IF ( SELECT AVG(成绩) FROM XS,XS_KC,KC WHERE XS.学号= XS_KC.学号 AND XS_KC.课程号=KC.课程号 AND KC.课程名=数据库原理 ) 75 SELECT text1=平均成绩低于75. ELSE SELECT text1,4.3.1 IF.ELSE语句,【例4.19】IF.ELSE语句的嵌套使

24、用。 USE XSCJ IF ( SELECT AVG(成绩) FROM XS,XS_KC,KC WHERE XS.学号= XS_KC.学号 AND XS_KC.课程号=KC.课程号 AND KC.课程名=数据库原理 ) 75 SELECT 平均成绩高于75,4.3.3 WHILE、BREAK和CONTINUE语句,1. WHILE循环语句 如果需要重复执行程序中的一部分语句，可使用WHILE循环语句实现。 语法格式： WHILE Boolean_expression /*条件表达式*/ sql_statement | statement_block /*T-SQL语句序列构成的循环体*/,4

25、.3.3 WHILE、BREAK和CONTINUE语句,【例4.20】显示字符串China中每个字符的 ASCII 值和字符。 DECLARE position int, string char(8) SET position = 1 SET string = China WHILE position = DATALENGTH(string) BEGIN SELECT ASCII(SUBSTRING(string, position, 1), CHAR(ASCII(SUBSTRING(string, position, 1) SET position = position + 1 END,2.

26、 BREAK语句 退出本层循环 语法格式： BREAK,3. CONTINUE语句 结束本次循环,重新转到下一次循环 语法格式： CONTINUE,5.3.4 RETURN语句,语法格式： RETURN integer_expression integer_expression将整型表达式的值返回。,【例4.21】检查学生的平均成绩，若75，将返回状态代码 1，将返回状态代码 2。 USE XSCJ CREATE PROCEDURE checkavg param varchar(10) AS IF (SELECT AVG(成绩) FROM XS_KC WHERE XS_KC学号 = param

27、 GROUP BY 学号) 75 RETURN 1 ELSE RETURN 2,4.3.5 WAITFOR语句,指定触发语句块、存储过程或事务执行的时刻、或需等待的时间间隔。 语法格式： WAITFOR DELAY time | TIME time 说明： DELAY time：用于指定SQL Server必须等待的时间，最长可达 24 小时，time可以用datetime 数据格式指定，用单引号括起来，但在值中不允许有日期部分，也可以用局部变量指定参数。 TIME time：指定SQL Server 等待到某一时刻， time值的指定同上。 执行 WAITFOR 语句后，在到达指定的时间之前

28、将无法使用与 SQL Server 的连接。若要查看活动的进程和正在等待的进程，使用 sp_who。 【例4.22】如下语句设定在早上八点执行存储过程 Manager。 BEGIN WAITFOR TIME 8:00 EXECUTE sp_addrole Manager END,4.1 常量、变量与数据类型 4.2 运算符号与表达式 4.3 流程控制语句 4.4 系统内置函数 4.5用户定义函数,4.4.1 系统内置函数介绍,在程序设计过程中,常常调用系统提供的函数，T-SQL 编程语言提供三种系统内置函数： 行集函数、聚合函数和标量函数，所有的函数都是确定性或非确定性的。 (1) 确定性函数

29、：每次使用特定的输入值集调用该函数时，总是返回相同的结果。 (2) 非确定性函数：每次使用特定的输入值集调用时，它们可能返回不同的结果。 例如，DATEADD 内置函数是确定性函数，因为对于其任何给定参数总是返回相同的结果。GETDATE 是非确定性函数，因其每次执行后，返回结果都不同。,4.4.1 系统内置函数介绍,1. 行集函数 (P155) 2. 聚合函数 聚合函数对一组值操作，返回单一的汇总值。聚合函数在如下情况下，允许作为表达式使用： (1) SELECT 语句的选择列表（子查询或外部查询）。 (2) COMPUTE 或 COMPUTE BY 子句。 (3) HAVING 子句。,4

30、.4.1 系统内置函数介绍,3. 标量函数 标量函数的特点：输入参数的类型为基本类型，返回值也为基本类型。SQL Server包含如下几类标量函数： 配置函数 系统函数 系统统计函数 数学函数 字符串函数 日期和时间函数 游标函数 文本和图像函数 元数据函数 安全函数,4.4.2 常用系统内置函数,1配置函数 配置函数用于返回当前配置选项设置的信息。全局变量是以函数形式使用的，配置函数都是全局变量录。 2数学函数 数学函数可对 SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行数

31、学运算并返回运算结果。,1）ABS 语法格式： ABS ( numeric_expression ),【例4.23】显示ABS 函数对三个不同数字的效果。 SELECT ABS(-5.0), ABS(0.0), ABS(8.0),2）ACOS 语法格式： ACOS ( float_expression ),4.4.2 常用系统内置函数,【例4.24】如下程序返回给定角的 ACOS 值。 DECLARE angle real SET angle = 0 SELECT The ACOS = + CONVERT(varchar, ACOS(angle),3）RAND 语法格式： RAND ( see

32、d ),【例4.25】如下程序通过 RAND 函数随机值。 DECLARE count int SET count = 5 SELECT RAND(count) Rand_Num GO,3字符串处理函数 字符串函数用于对字符串进行处理。 1) ASCII 语法格式： ASCII ( character_expression ),4.4.2 常用系统内置函数,2) CHAR 语法格式： CHAR ( integer_expression ) 将ASCII码转换成字符,3) LEFT 语法格式： LEFT ( character_expression , integer_expression )

33、返回从字符串左边开始指定个数的字符。,【例4.26】返回课程名最左边的 8 个字符。 USE XSCJ SELECT LEFT(课程名, 8) FROM KC ORDER BY 课程号 GO,4) LTRIM 语法格式： LTRIM ( character_expression ),4.4.2 常用系统内置函数,【例4.27】使用 LTRIM 字符删除字符变量中的起始空格。 DECLARE string varchar(40) SET string = 中国，一个古老而伟大的国家 SELECT LTRIM(string) GO,5) REPLACE 语法格式： REPLACE ( string

34、_expression1 , string_expression2 , string_expression3 ),6) SUBSTRING 语法格式： SUBSTRING ( expression , start , length ),4.4.2 常用系统内置函数,【例4.28】如下程序在一列中返回XS表中的姓氏，在另一列中返回表中学生的名。 USEXSCJ SELECT SUBSTRING(姓名, 1,1), SUBSTRING(姓名, 2, LEN(姓名)-1) FROM XS ORDER BY 姓名,7) STR 语法格式： STR ( float_expression , length

35、 , decimal ) 将数字数据转换为字符数据。,4. 系统函数 系统函数用于对SQL Server中的值、对象和设置进行操作并返回有关信息。 1) CASE CASE有两种使用形式：一种是简单的CASE函数，另一种是搜索型的CASE函数。 简单的 CASE 函数,4.4.2 常用系统内置函数,简单的 CASE 函数 语法格式： CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END,CASE 搜索函数： 语法格式： CASE WHEN Bool

36、ean_expression THEN result_expression .n ELSE else_result_expression END,4.4.2 常用系统内置函数,【例4.29】使用 CASE 函数对学生按性别分类。 /* 使用带有简单 CASE 函数的 SELECT 语句*/ USE XSCJ SELECT 学号,sex= CASE 性别 WHEN 1 THEN 男生 WHEN 0 THEN 女生 END FROM XS GO 【例4.30】根据学生的年龄范围显示相应信息。 /*使用CASE 搜索函数的 SELECT 语句*/ USE XSCJ SELECT 学号,年龄= CAS

37、E WHEN getdate()-出生时间 20 THEN 年龄适中 ELSE 年龄偏大 END FROM XS GO,4.4.2 常用系统内置函数,2) CAST 和 CONVERT 常用的类型转换有以下几种情况： 日期型字符型：如将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。 字符型日期型：如将字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）转换为datetime 或 smalldatetime 数据

38、。 数值型字符型：如将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。,语法格式： CAST ( expression AS data_type ) CONVERT (data_type(length), expression , style),4.4.2 常用系统内置函数,【例4.31】 如下程序将检索总学分3039分的学生姓名，并将总学分转换为 char(20)。 /* 如下例子同时使用 CAST 和 CONVERT - 使用CAST实现. USE XSC

39、J SELECT 姓名, 总学分 FROM XS WHERE CAST(总学分AS char(20) LIKE 3_ and总学分=30 GO - 使用 CONVERT实现. USE XSCJ SELECT 姓名, 总学分 FROM XS WHERE CONVERT(char(20), 总学分) LIKE 3_ and总学分=30 GO,4.4.2 常用系统内置函数,日期型与字符型转换时style的常用取值及其作用,float 或 real转换为字符数据时style的取值,4.4.2 常用系统内置函数,从 money 或 smallmoney转换为字符数据时style的取值,5. 日期时间函数

40、 日期函数可用在 SELECT 语句的选择列表或用在查询的 WHERE 子句中，在此介绍一下GETDATE ( )函数。 语法格式： GETDATE ( ),4.1 常量、变量与数据类型 4.2 运算符号与表达式 4.3 流程控制语句 4.4 系统内置函数 4.5用户定义函数,4.5 用户定义函数,根据用户定义函数返回值的类型，可将用户定义函数分为如下三个类别： (1) 返回值为可更新表的函数 若用户定义函数包含单个 SELECT 语句且该语句可更新，则该函数返回的表也可更新，这样的函数称为内嵌表值函数。 (2) 返回不可更新数据表的函数 若用户定义函数包含多个 SELECT 语句，则该函数返

41、回的表不可更新。这样的函数称为多语句表值函数。 (3) 返回标量值的函数 用户定义函数返回值为标量值，这样的函数称为标量函数。 用户定义函数不支持输出参数。用户定义函数不能修改全局数据库状态。 利用ALTER FUNCTION对用户定义函数修改，用 DROP FUNCTION 删除。,4.5.1 用户函数的定义与调用,1. 标量函数 (1) 标量函数的定义 语法格式： CREATE FUNCTION owner_name. function_name /*函数名部分*/ ( parameter_name AS scalar_parameter_data_type = default ,.n )

42、 /*形参定义部分*/ RETURNS scalar_return_data_type /*返回参数的类型*/ WITH , .n /*函数选项定义*/ AS BEGIN function_body /*函数体部分*/ RETURN scalar_expression /*返回语句*/ END := ENCRYPTION | SCHEMABINDING ,4.5.1 用户函数的定义与调用,删除了函数。 在未指定 SCHEMABINDING 选项的情况下更改了函数。 从上述语法形式，归纳出标量函数的一般定义形式如下： CREATE FUNCTION 所有者名. 函数名 ( 参数1 AS 类型1

43、= 默认值 ) ,.参数n AS 类型n = 默认值 ) RETURNS 返回值类型 WITH ENCRYPTION | SCHEMABINDING , .n AS BEGIN 函数体 RETURN 标量表达式 END,4.5.1 用户函数的定义与调用,【例4.34】计算全体学生某门功课的平均成绩。 USE XSCJ CREATE FUNCTION average(cnum char(20) RETURNS int AS BEGIN DECLARE aver int SELECT aver= ( SELECT avg(成绩) FROM xs_kc WHERE 课程号=cnum GROUP BY

44、 课程号 ) RETURN aver END GO,4.5.1 用户函数的定义与调用,(2) 标量函数的调用 当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（所有者名.函数名）。可有以下方式调用标量函数： 在SELECT语句中调用 调用形式：所有者名.函数名(实参1,实参n) 实参可为已赋值的局部变量或表达式。 【例4.35】如下程序对上例定义的函数调用。 USE XSCJ /*用户函数在此数据库中已定义*/ /* 定义局部变量 */ DECLARE course1 char(20) DECLARE aver1 int /* 给局部变量赋值 */ SELECT course1 =

45、101 /* 调用用户函数，并将返回值赋给局部变量 */ SELECT aver1=dbo.average(course1) /* 显示局部变量的值 */ SELECT aver1 AS 101课程的平均成绩,4.5.1 用户函数的定义与调用,利用EXEC语句执行 用T-SQL EXECUTE语句调用用户函数时，参数的标识次序与函数定义中的参数标识次序可以不同。 调用形式： 所有者名.函数名 实参1,实参n 或 所有者名.函数名 形参名1=实参1, 形参名n=实参n,【例4.36】调用上述计算平均成绩的函数。 USE XSCJ /* 用户函数在此数据库中已定义 */ DECLARE cours

46、e1 char(20) DECLARE aver1 int /* 显示局部变量的值 */ EXEC aver1 = dbo.average cnum = 101 /*通过EXEC调用用户函数,并将返回值赋给局部变量*/ SELECT aver1 AS 101课程的平均成绩 GO,5.5.1 用户函数的定义与调用,【例4.37】在XSCJ中建立一个course表，并将一个字段定义为计算列。 USE XSCJ /*用户函数在此数据库中已定义*/ CREATE TABLE course ( cno int, /*课程号*/ cname nchar(20), /*课程名*/ credit int, /

47、*学分*/ aver AS /*将此列定义为计算列*/ ( dbo.average(cno) ) ),2. 内嵌表值函数,内嵌函数可用于实现参数化视图。例如，有如下视图： CREATE VIEW View1 AS SELECT 学号, 姓名 FROM XSCJ.dbo.XS WHERE 专业名= 计算机,5.5.1 用户函数的定义与调用,(1) 内嵌表值函数的定义 语法格式： CREATE FUNCTION owner_name. function_name /*定义函数名部分*/ ( parameter_name AS scalar_parameter_data_type = default

48、 ,.n ) /*定义参数部分*/ RETURNS TABLE /*返回值为表类型*/ WITH , .n /*定义函数的可选项*/ AS RETURN ( ) select-stmt /*通过SELECT语句返回内嵌表*/ := ENCRYPTION | SCHEMABINDING ,【例5.38】对于XSCJ数据库，为了让学生每学期查询其各科成绩及学分，可以利用XS、KC、XS_KC三个表，创建视图，程序如下： USE XSCJ2 GO /*此处的GO不能省略*/ CREATE VIEW ST_VIEW AS SELECT dbo.XS.学号, dbo.XS.姓名, dbo.KC.课程名,

49、 dbo.XS_KC.成绩 FROM dbo.KC INNER JOIN dbo.XS_KC ON dbo.KC.课程号 = dbo.XS_KC.课程号 INNER JOIN dbo.XS ON dbo.XS_KC.学号 = dbo.XS.学号,在上述视图的基础上定义内嵌值表函数,CREATE FUNCTION st_score ( student_ID char(6) ) returnS table AS RETURN ( SELECT * FROM XSCJ2.dbo.ST_VIEW WHERE XSCJ2.dbo.ST_VIEW.学号=student_ID ),5.5.1 用户函数的定义

50、与调用,(2) 内嵌表值函数的调用 内嵌表值函数只能通过SELECT语句调用，内嵌表值函数调用时，可以仅使用函数名。 【例5.39】调用st_score()函数，查询学号为“001101”学生的各科成绩及学分。 SELECT * FROM XSCJ.dbo.st_score(001101) GO,3多语句表值函数 内嵌表值函数和多语句都返回表，二者不同之处在于：内嵌表值函数没有函数主体，返回的表是单个SELECT语句的结果集；而多语句表值函数在 BEGIN.END 块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表中，最后返回表。,5.5.1 用户函数的定义与调用,(1) 多

51、语句表值函数定义 语法格式： CREATE FUNCTION owner_name. function_name /*定义函数名部分*/ ( parameter_name AS scalar_parameter_data_type = default ,.n ) /*定义函数参数部分*/ RETURNS return_variable TABLE /*定义作为返回值的表* WITH , .n /*定义函数的可选项*/ AS BEGIN function_body /*定义函数体*/ RETURN END := ENCRYPTION | SCHEMABINDING := /*定义表，请参考第二章

52、*/ ( column_definition | table_constraint ,.n ),return_variable：为表变量，用于存储作为函数值返回的记录集； function_body：为T-SQL语句序列，function_body只用于标量函数和多语句表值函数。,4.5.1 用户函数的定义与调用,【例4.40】在XSCJ数据库中创建返回table的函数，通过以学号作为实参，调用该函数，可显示该学生各门功课的成绩和学分。 CREATE FUNCTION score_table() RETURNS score TABLE ( xs_ID char(6), xs_Name char

53、(8), kc_Name char(16), cj tinyint, xf tinyint ) AS BEGIN INSERT score SELECT S.学号, S.姓名,P.课程名,O.成绩, P.学分 FROM XSCJ2.dbo.XS AS S INNER JOIN XSCJ2.dbo.XS_KC AS O ON (S.学号= O.学号) INNER JOIN XSCJ2.dbo.KC AS P ON (O.课程号= P.课程号) RETURN END,修正【例4.40】,CREATE FUNCTION score_table1 (student_ID char(6) RETURNS

54、 score TABLE ( xs_ID char(6), xs_Name char(8), kc_Name char(16), cj tinyint, xf tinyint ) AS BEGIN INSERT score SELECT S.学号, S.姓名,P.课程名,O.成绩, P.学分 FROM XSCJ2.dbo.XS AS S INNER JOIN XSCJ2.dbo.XS_KC AS O ON (S.学号= O.学号) INNER JOIN XSCJ2.dbo.KC AS P ON (O.课程号= P.课程号) WHERE O.学号=student_ID RETURN END,5.

55、5.1 用户函数的定义与调用,(2) 多语句表值函数的调用 多语句表值函数的调用与内嵌表值函数的调用方法相同。如下例子是上述多语句表值函数score_table（）的调用。 【例5.41】如下语句查询学号为”001101”学生的各科成绩和学分。 SELECT * from XSCJ1.dbo.score_table(001101),5.5.1 用户函数的定义与调用,4用户函数的建立 用户函数的建立可利用查询分析器完成，也可利用企业管理器完成。 (1) 利用查询分析器创建用户定义函数 例如在XSCJ数据库中要建立前面求立方体体积的用户函数，可在查询分析器窗口输入例中的程序并执行，然后在查询分析器的目录树上XSCJ数据库对应的函数子目录图标上右击，选择“刷新”，即可看到函数SphereVolume（）对象的图标。 定义用户函数SphereVolume（）后，在查询分析器中即可调用该函数。,4.5.1 用户函数的定义与调用,(2) 利用企业管理器创建用户定义函数 第1步 在选中企业管理器中的“用户定义函数图标”右击，出现如图4.8的快捷菜单。,4.5.1 用户函数的定义与调用,第2步 选择“新建用户函数”，进入如图4.9所示的函数定义模板界面.。,第3步 在此界面中根据函数定义，替换模板中的相应内容，然后选择“确定”，如图4.10所示。,