第9章T-SQL程序设计
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
[@owner =] 'owner_name‘:指定新数据类型的创建者或所有者。当没 有指定时，owner_name 为当前用户。
目录
创建用户自定义数据类型（续）
例：为Sales数据库创建—个不允许为NULL值的test_add用
户定义数据类型。 USE Sales GO EXEC sp_addtype test_add,'Varchar(10)','NOT NULL' GO
目录
用户自定义类型的使用
1.查看用户自定义数据类型 可以用系统存储过程sp_help或管理平台来查看 用户定义的数据类型的特征。语法格式： exec sp_help 自定义数据类型名称 2.删除用户定义数据类型 使用过程sp_droptype 来删除一个已经定义且 未被使用的用户自定义数据类型。语法格式： exec sp_droptype 自定义数据类型名 必须注意不能删除正在被表或其他数据库使用 的用户自定义数据类型。
SELECT 5^2+3*2
目录
9.2函数
函数 函数是—组编译好的Transact-SQL语句，它们可以 带一个或一组数值做参数，也可不带参数，它返回 一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免不断重写代 码。 内置函数 1字符串函数 2数学函数 3日期时间函数 4类型转换函数 5系统函数 用户自定义函数 目录
@@error
例9-5 捕捉例9-4中服务器产生的错误号，并显示 出来。
DECLARE @my_error int RAISERROR('miscellaneous error message',16,1) SELECT @my_error=@@error IF @my_error<>0 SELECT @my_error as 'last error'
目录
9.1.2 变量
类型
局部变量 局部变量一般在批处理中被声明、定义、赋值和 引用，批处理结束后，局部变量就消失了。 可以保存程序执行过程中的中间数据值，保存由 存储过程返回的数据值等。 全局变量 全局变量在整个SQL Server系统内使用。存储的 通常是一些SQL Server的配置设定值和统计数据。
(2)使用SQL Server管理平台创建自定义数据类型
进入SQL Server管理平台 进入相应数据库 右击‚用户自定义数据类型‛ 点击‚新建用户自定义数据类型‛ 后出现‚用户自定义数据类型‛窗口 输入要定义的数据名称，选择数据类型，输入数据长度，确 定是否允许为空 点击‚确定‛。
[,[@owner=]’owner_name’]
type_name：是用户定义的数据类型的名字。 Phystype：是用户自定义数据类型所基于的系统数据类型，可以包括长 度、精度。
[@nulltype =] ‘null_type‘:用户自定义数据类型的性质，分别为允许空 值、不允许为空值、具有标识列性质，默认为not null 。
9.1.4 运算符与表达式
1 算术运算符与表达式 2 位运算符与表达式 3 比较运算符与表达式 4 逻辑运算符与表达式 5 连接运算符与表达式 6 运算符的优先级
目录
9.1.4.1 算术运算符与表达式
算术表达式
5+2 5*2 5%2
运算符 + * / %
数据类型 整型 浮点数 精确数 货币型 整型
--This is a comment.Whole line will be ignored. SELECT employee_name, address --查询所有姓钱的员工 FROM employee WHERE employee_name LIKE '钱%'
注释的部分不会被SQL Server执行。
目录
局部变量
例 DECLARE @int_var int SELECT @int_var =12 /*给@int_var赋值*/ SELECT @int_var /*将@int_var的值输出到屏 幕上*/ Print @a SET与SELECT 命令区别：
SET 命令一次只能给一个变量赋值
目录
9.1.4.5字符串串联运算符及表达式
”+” 表达式
‘中国’+ ‘共产党’ 结果:’中国共产党’ ‘Dr.’+‘Computer’ 运算结果:'Dr. Computer'
目录
9.1.4.6 运算符的优先级
括号：（）； 取反：~ 位运算符：^，&，|； 乘、除、求模运算符：*，/，%； 加减运算符：＋，－； 逻辑运算符：NOT； 逻辑运算符：AND； 逻辑运算符：OR
运行结果：
服务器: 消息 50000，级别 16，状态 1，行 2 miscellaneous error message last error 50000 目录
9.1.3 注释符
1．单行注释： ‚--” 在语句中，使用两个连字符‚--”开头，则从此 开始的整行或者行的一部分就成为了注释，注释 在行的末尾结束。
第9章 T-SQL程序设计
Chapter 9 T-SQL Programming
第九章
T-SQL程序设计
9.1 数据与表达式 9.2 函数 9.3 流程控制命令 9.4 用户自定义函数 9.5小结
目录
9.1 数据与表达式
9.1.1 数据类型 (长度、精度)
字符型、二进制型、逻辑型、日期时间型、数字型、 其他类型 自定义数据类型
目录
9.1.4.2位运算符与表达式
位表达式 5^2, 5&2,5|2 其结果为：7, 0,7
运算符 ^ & |
含义 按位异或 按位与 按位或
按位与（&）:两个位为１时，结果为１，否则为０
按位或（|）：只要一个位为１时，结果为１，否则 为０
按位异或（^）：两个位值不同时为１，否则为０
操作
定义（声明） 赋值 显示 引用
目录
局部变量
1．定义 必须先用DECLARE命令定义后才可以使用。 DECLARE @variable_name data_type [,@variable_name data_type] … 初值为NULL 2．赋值 SET @variable_name = expression SELECT @variable_name = expression [,@variable_name = expression] ...
位运算符用来对整型数据或者二进制数据（image 数据类型除外）之间执行位操作。要求在位运算符左 右两侧的操作数不能同时是二进制数据。 目录
9.1.4.3比较运算符与表达式
运算符 = > < 含义 等于 大于 小于 备注
>=
<= <> != !> !<
大于等于
小于等于 不等于 不等于 不大于 不小于 非SQL-92标准 非SQL-92标准 非SQL-92标准
例 DECLARE @a int,@b int SELECT @a =12,@b=13 SET @a=12 SET @b=13
目录
变量（续）
3.局部变量的显示 select @variable_name
[例] 定义一个整型局部变量和一个可变长字符型 局部变量并赋值。
declare @variable_int varchar(20) set @variable_int=40 set @variable_char='Welcome to Shijiazhuang' select @variable_int int ,@variable_char
SELECT LEN(‘A ’),LEN(‘ A’), LEN(‘ABC’) SELECT DATALENGTH(‘ABC’) ,DATALENGTH(‘ ’)
目录
9.2.1 字符串函数（续１）
大小写转换函数 UPPER(),LOWER() SELECT UPPER(‘Abc’),LOWER(‘ABC’) 重复字符函数
目录
创建用户自定义数据类型
(１)使用系统存储过程sp_addtype创建
sp_addtype
［@typename=]type_name,
[@phystype=] system_data_type [,[@nulltype=]’null | not null | identity’]
目录
9.2.1 字符串函数
字符串长度计算
LEN(),DATALENGTH()
datalength函数主要用于判断可变长字符串 的长度，对于定长字符串将返回该列的长 度。要得到字符串的真实长度，通常需要 使用rtrim函数截去字符串尾部的空格。 Len函数可以获取字符串的字符个数，而不 是字节数，也不包含尾随空格。
1. 比较运算 结果为逻 辑值 2. 如:2=3的 结果为 false
目录
9.1.4.4 逻辑运算符与表达式
逻辑运算符 NOT:取反 AND:两个操作数全真为真,否则为假 OR:两个操作数全假为假,否则为真 如： NOT TRUE TRUE AND FALSE TRUE OR FALSE
目录
@@error P173
如果@@error为非0值，则表明执行过程中产生了错误， 此时应当在程序中采取相应的措施加以处理。 @@error的值与@@rowcount一样，会随着每一条 SQL Server语句的变化而改变。 例9-4 使服务器产生服务，并显示错误号。 raiserror('miscellaneous error message',16,1) /*产生一个 错误*/ if @@error<>0 SELECT @@error as 'last error' 运行结果： 服务器: 消息 50000，级别 16，状态 1，行 1 miscellaneous error message last error 目录 0
目录
多行注释
2．块注释： ‚/*……*/”，/* 用于注释文字的开头，*/用 于注释文字的结尾，可在程序中标识多行文字为注释。 /* This is a commnet． All these lines will be ignored. */ /* List all employees.*/ SELECT * FROM employee 注释并没有长度限制。SQL Server文档禁止嵌套多行注 释，但单行注释可以嵌套在多行注释中。 /* --List all employees. SELECT * FROM employee 目录 */
9.1.2变量 9.1.3注释符 9.1.4运算符及表达式
ቤተ መጻሕፍቲ ባይዱ目录
9.1.1.7 用户定义数据类型
用户自定义数据类型的好处 定义用户自定义数据类型 使用用户自定义数据类型
目录
9.1.1.7 用户自定义数据类型
创建自定义数据类型的好处：
利用自定义数据类型可使不同表不重复出现的各列 数据具有相同的特性。如Student表中学号，SC中 学号 如果把规则和缺省捆绑到一个自定义的数据类型上， 那么该规则和缺省也适合于采用此用户自定义数据 类型的每个列。
9.2.1 字符串函数
功能：字符串函数用来实现对字符型数据的转换、 查找、分析等操作，通常用做字符串表达式的一 部分。 取子串函数
LEFT(),RIGHT(),SUBSTRING() SELECT LEFT(‘ABC’,2), RIGHT(‘ABC’,2), SUBSTRING(‘ABC’,2,1)
select @variable_char
目录
全局变量
由系统提供且预先声明的变量,任何程序均可以调用, 但不能显式的被声明或赋值 存储SQL SERVER的配置设定值和统计数据 用户可以在程序中用全局变量来测试系统的设定值或 者命令执行后的状态值。 @@ERROR, @@VERSION, @@SERVERNAME见P172表9-2 用select命令来显示其值的 全局变量是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符‚@@”开头。 全局变量对用户来说是只读的。 局部变量的名称不能与全局变量的名称相同
目录
创建用户自定义数据类型（续）
例：为Sales数据库创建—个不允许为NULL值的test_add用
户定义数据类型。 USE Sales GO EXEC sp_addtype test_add,'Varchar(10)','NOT NULL' GO
目录
用户自定义类型的使用
1.查看用户自定义数据类型 可以用系统存储过程sp_help或管理平台来查看 用户定义的数据类型的特征。语法格式： exec sp_help 自定义数据类型名称 2.删除用户定义数据类型 使用过程sp_droptype 来删除一个已经定义且 未被使用的用户自定义数据类型。语法格式： exec sp_droptype 自定义数据类型名 必须注意不能删除正在被表或其他数据库使用 的用户自定义数据类型。
SELECT 5^2+3*2
目录
9.2函数
函数 函数是—组编译好的Transact-SQL语句，它们可以 带一个或一组数值做参数，也可不带参数，它返回 一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免不断重写代 码。 内置函数 1字符串函数 2数学函数 3日期时间函数 4类型转换函数 5系统函数 用户自定义函数 目录
@@error
例9-5 捕捉例9-4中服务器产生的错误号，并显示 出来。
DECLARE @my_error int RAISERROR('miscellaneous error message',16,1) SELECT @my_error=@@error IF @my_error<>0 SELECT @my_error as 'last error'
目录
9.1.2 变量
类型
局部变量 局部变量一般在批处理中被声明、定义、赋值和 引用，批处理结束后，局部变量就消失了。 可以保存程序执行过程中的中间数据值，保存由 存储过程返回的数据值等。 全局变量 全局变量在整个SQL Server系统内使用。存储的 通常是一些SQL Server的配置设定值和统计数据。
(2)使用SQL Server管理平台创建自定义数据类型
进入SQL Server管理平台 进入相应数据库 右击‚用户自定义数据类型‛ 点击‚新建用户自定义数据类型‛ 后出现‚用户自定义数据类型‛窗口 输入要定义的数据名称，选择数据类型，输入数据长度，确 定是否允许为空 点击‚确定‛。
[,[@owner=]’owner_name’]
type_name：是用户定义的数据类型的名字。 Phystype：是用户自定义数据类型所基于的系统数据类型，可以包括长 度、精度。
[@nulltype =] ‘null_type‘:用户自定义数据类型的性质，分别为允许空 值、不允许为空值、具有标识列性质，默认为not null 。
9.1.4 运算符与表达式
1 算术运算符与表达式 2 位运算符与表达式 3 比较运算符与表达式 4 逻辑运算符与表达式 5 连接运算符与表达式 6 运算符的优先级
目录
9.1.4.1 算术运算符与表达式
算术表达式
5+2 5*2 5%2
运算符 + * / %
数据类型 整型 浮点数 精确数 货币型 整型
--This is a comment.Whole line will be ignored. SELECT employee_name, address --查询所有姓钱的员工 FROM employee WHERE employee_name LIKE '钱%'
注释的部分不会被SQL Server执行。
目录
局部变量
例 DECLARE @int_var int SELECT @int_var =12 /*给@int_var赋值*/ SELECT @int_var /*将@int_var的值输出到屏 幕上*/ Print @a SET与SELECT 命令区别：
SET 命令一次只能给一个变量赋值
目录
9.1.4.5字符串串联运算符及表达式
”+” 表达式
‘中国’+ ‘共产党’ 结果:’中国共产党’ ‘Dr.’+‘Computer’ 运算结果:'Dr. Computer'
目录
9.1.4.6 运算符的优先级
括号：（）； 取反：~ 位运算符：^，&，|； 乘、除、求模运算符：*，/，%； 加减运算符：＋，－； 逻辑运算符：NOT； 逻辑运算符：AND； 逻辑运算符：OR
运行结果：
服务器: 消息 50000，级别 16，状态 1，行 2 miscellaneous error message last error 50000 目录
9.1.3 注释符
1．单行注释： ‚--” 在语句中，使用两个连字符‚--”开头，则从此 开始的整行或者行的一部分就成为了注释，注释 在行的末尾结束。
第9章 T-SQL程序设计
Chapter 9 T-SQL Programming
第九章
T-SQL程序设计
9.1 数据与表达式 9.2 函数 9.3 流程控制命令 9.4 用户自定义函数 9.5小结
目录
9.1 数据与表达式
9.1.1 数据类型 (长度、精度)
字符型、二进制型、逻辑型、日期时间型、数字型、 其他类型 自定义数据类型
目录
9.1.4.2位运算符与表达式
位表达式 5^2, 5&2,5|2 其结果为：7, 0,7
运算符 ^ & |
含义 按位异或 按位与 按位或
按位与（&）:两个位为１时，结果为１，否则为０
按位或（|）：只要一个位为１时，结果为１，否则 为０
按位异或（^）：两个位值不同时为１，否则为０
操作
定义（声明） 赋值 显示 引用
目录
局部变量
1．定义 必须先用DECLARE命令定义后才可以使用。 DECLARE @variable_name data_type [,@variable_name data_type] … 初值为NULL 2．赋值 SET @variable_name = expression SELECT @variable_name = expression [,@variable_name = expression] ...
位运算符用来对整型数据或者二进制数据（image 数据类型除外）之间执行位操作。要求在位运算符左 右两侧的操作数不能同时是二进制数据。 目录
9.1.4.3比较运算符与表达式
运算符 = > < 含义 等于 大于 小于 备注
>=
<= <> != !> !<
大于等于
小于等于 不等于 不等于 不大于 不小于 非SQL-92标准 非SQL-92标准 非SQL-92标准
例 DECLARE @a int,@b int SELECT @a =12,@b=13 SET @a=12 SET @b=13
目录
变量（续）
3.局部变量的显示 select @variable_name
[例] 定义一个整型局部变量和一个可变长字符型 局部变量并赋值。
declare @variable_int varchar(20) set @variable_int=40 set @variable_char='Welcome to Shijiazhuang' select @variable_int int ,@variable_char
SELECT LEN(‘A ’),LEN(‘ A’), LEN(‘ABC’) SELECT DATALENGTH(‘ABC’) ,DATALENGTH(‘ ’)
目录
9.2.1 字符串函数（续１）
大小写转换函数 UPPER(),LOWER() SELECT UPPER(‘Abc’),LOWER(‘ABC’) 重复字符函数
目录
创建用户自定义数据类型
(１)使用系统存储过程sp_addtype创建
sp_addtype
［@typename=]type_name,
[@phystype=] system_data_type [,[@nulltype=]’null | not null | identity’]
目录
9.2.1 字符串函数
字符串长度计算
LEN(),DATALENGTH()
datalength函数主要用于判断可变长字符串 的长度，对于定长字符串将返回该列的长 度。要得到字符串的真实长度，通常需要 使用rtrim函数截去字符串尾部的空格。 Len函数可以获取字符串的字符个数，而不 是字节数，也不包含尾随空格。
1. 比较运算 结果为逻 辑值 2. 如:2=3的 结果为 false
目录
9.1.4.4 逻辑运算符与表达式
逻辑运算符 NOT:取反 AND:两个操作数全真为真,否则为假 OR:两个操作数全假为假,否则为真 如： NOT TRUE TRUE AND FALSE TRUE OR FALSE
目录
@@error P173
如果@@error为非0值，则表明执行过程中产生了错误， 此时应当在程序中采取相应的措施加以处理。 @@error的值与@@rowcount一样，会随着每一条 SQL Server语句的变化而改变。 例9-4 使服务器产生服务，并显示错误号。 raiserror('miscellaneous error message',16,1) /*产生一个 错误*/ if @@error<>0 SELECT @@error as 'last error' 运行结果： 服务器: 消息 50000，级别 16，状态 1，行 1 miscellaneous error message last error 目录 0
目录
多行注释
2．块注释： ‚/*……*/”，/* 用于注释文字的开头，*/用 于注释文字的结尾，可在程序中标识多行文字为注释。 /* This is a commnet． All these lines will be ignored. */ /* List all employees.*/ SELECT * FROM employee 注释并没有长度限制。SQL Server文档禁止嵌套多行注 释，但单行注释可以嵌套在多行注释中。 /* --List all employees. SELECT * FROM employee 目录 */
9.1.2变量 9.1.3注释符 9.1.4运算符及表达式
ቤተ መጻሕፍቲ ባይዱ目录
9.1.1.7 用户定义数据类型
用户自定义数据类型的好处 定义用户自定义数据类型 使用用户自定义数据类型
目录
9.1.1.7 用户自定义数据类型
创建自定义数据类型的好处：
利用自定义数据类型可使不同表不重复出现的各列 数据具有相同的特性。如Student表中学号，SC中 学号 如果把规则和缺省捆绑到一个自定义的数据类型上， 那么该规则和缺省也适合于采用此用户自定义数据 类型的每个列。
9.2.1 字符串函数
功能：字符串函数用来实现对字符型数据的转换、 查找、分析等操作，通常用做字符串表达式的一 部分。 取子串函数
LEFT(),RIGHT(),SUBSTRING() SELECT LEFT(‘ABC’,2), RIGHT(‘ABC’,2), SUBSTRING(‘ABC’,2,1)
select @variable_char
目录
全局变量
由系统提供且预先声明的变量,任何程序均可以调用, 但不能显式的被声明或赋值 存储SQL SERVER的配置设定值和统计数据 用户可以在程序中用全局变量来测试系统的设定值或 者命令执行后的状态值。 @@ERROR, @@VERSION, @@SERVERNAME见P172表9-2 用select命令来显示其值的 全局变量是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符‚@@”开头。 全局变量对用户来说是只读的。 局部变量的名称不能与全局变量的名称相同