7. Transact-SQL程序设计
|
本章主题 l 使用SQL查询分析器 l Transact-SQL的组成元素 l 局部变量 l 运算符 l 表达式 l 如何为程序加上注释 l 函数 l 流程控制语句 l 动态创建语句
精彩内容不容错过！这一章内容非常重要
|
所谓“程序设计”就是学习如何以合理的方式将一系列的命令结合起来。
准备工作
准备工作：在示例代码ch8目录中，数据库(NorthwindSQL)
文件名：NorthwindSQL.mdf、NorthwindSQL.ldf
7.1. 使用SQL查询分析器
7.2. Transact-SQL的组成元素
SQL语言不具备创建窗体、菜单、报表等的能力，SQL Server扮演一个后台数据库的角色，客户端的开发工具可以选择VB、Access、VFP、VC、Delphi、C++Builder等。
Transact-SQL的组成元素：
Ø 数据定义语言（Data Definition Language，DDL）
Ø 数据操作语言（Data Manipulation Language，DML）
Ø 数据控制语言（Data Control Language，DCL）
Ø 系统存储过程（System Stored Procedure）
Ø 其它组成元素
数据定义语言
DDL：用来定义和管理数据库及其所含种类对象的语句。
DDL的形式：
Ø CREATE object_name
Ø ALTER object_name
Ø DROP object_name
数据操作语言
DML：用来查询、添加、修改、删除数据库中数据的语句。
DML的种类：
Ø SELECT
Ø INSERT
Ø UPDATE
Ø DELETE
数据控制语言
DCL：用来设置或更改数据库用户或角色权限的语句。
DCL的种类：
Ø GRANT
Ø DENY
Ø REVOKE
系统存储过程
系统存储过程存放在系统数据库master中且名称以sp_开头，如sp_addtype、sp_rename、sp_configure等。回忆一下先前讲过的sp_addtype系统存储过程
其它组成元素
Ø 注释
Ø 局部变量
Ø 运算符
Ø 函数
Ø 流程控制语句
7.3. 常量
字符串常量
示例：
‘温一军’
‘处理中，请稍后…’
‘输入值必须介于3000~10000’
Ø 如果字符串中包含单引号，可以用两个单引号表示字符串本身内嵌的单引号
n 如：’It’’s legs are long’
Unicode字符串常量
必须以大定的字母N作为前缀，如：N’Michaё’,Unicode数据的每一个字符使用2个字节存储。
二进制常量
以0x作为前缀，再加上16进制数值来表示。
如：
0xAE
0x12Ef
Bit常量
以0或1表示
日期时间常量
Ø 日期时间常量可能只包含日期、只包含时间或日期、时间都有。
Ø 在日期部分可采用字母日期格式、数字日期格式、未分隔字符串格式。
Ø 不论采用哪一种格式的日期，年份都可以使用2位或4位的数字。
Ø 时间部分采用时间格式。
Ø 如果只指定日期，时间默认是12:00AM（午夜）
Ø 如果只指定时间，日期默认是1900年1月1号
特别注意：
日期时间常量必须包含在一对单引号中。
最好一致使用4位数的年份。
数字日期格式：
Ø 在年份、月份、日期之间的分隔符
n 斜线 /
n 连字符 –
n 英文的句点 .
Ø 示例：
n ‘2005-3-19’
n ‘2005/3/19’
n ‘2005/03/19’
n ‘2005-3-19’同‘2005-03-19’ （可加上前置0）
Ø 年份、月份、日的顺序
n SET DATEFORMAT格式
n 格式：mdy、dmy、ymd、ydm、myd、dym
/* 档案名称: Demo81.sql */
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '2001/21/09'
SELECT @datevar
GO
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '09/21/2001'
SELECT @datevar
GO
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '2001/09/21'
SELECT @datevar
GO
时间格式：
Ø 格式：Hours[:Minutes[:Seconds][:Milliseconds]]][AM | PM]
Ø 示例：
n ’14:30:20:559’
n ‘2:30:20.5 AM’ –其中20.5表示20分又十分之5秒
n AM与PM可大写或小写表示，且与前面时间值之间可空格或不空格
敬告大家：
最好统一采用固定一种格式，方便开发、维护、调试！
整数常量
不包含小数点，如：
Ø 2991
Ø 1969
Ø 10
Decimal常量
可以包含小数点，如：
Ø 256.456
Ø 2
Ø 23.11
Float和Real常量
使用科学计数法表示，如：
Ø 101.5E5
Ø 0.5E-2
货币值常量
可以包含或不包含小数点，以一货币符号$作为前缀，如：
Ø $234
Ø $345.33
指定正数或负数
以负号- 表示负数，不加符号，表示正数。
常量的使用方式
Ø 常量用于算术表达式
n SELECT Salary+$500 FROM myTable
Ø 常量作为WHERE子句中的条件值
n SELECT * FROM myTable WHERE name=’温一军’
Ø 将常量赋值给变量
n SET @datetimevar=’2005-01-01’
Ø 当使用UPDATE命令时，用于SET子句中
n UPDATE myTable
SET salary=$6000
WHERE birthday=’1970-12-05’
Ø 在IF语句或CASE函数等条件语句中
n IF (@storageAmount<5000)
PRINT ‘低于安全库存’
7.4. 局部变量
是一个能够拥有特定数据类型的对象，是程序中各数据的临时存储处。
声明局部变量
Ø 格式：DECLARE {@local_variable data_type} […n]
Ø 赋予局部变量一个名称，必须以@符号开头。
Ø DECLARE命令在创建局部变量之后，会将其初始值设成NULL
示例：
Ø DECLARE @searchName varchar(12)
Ø DECLARE @birthday datetime
Ø DECLARE @height decimal(5,2)
Ø DELCARE @name nvarchar(12),@phone nvarchar(11)
赋值数据值
Ø 格式：SET @local_variable=表达式
/* 档案名称: Demo82.sql */
--声明一个局部变量
DECLARE @mString varchar(40)
--将文字串赋值给局部变量
SET @mString = '嗨! 谢谢使用本系统'
--显示局部变量所持有的值
SELECT @mString
/* 档案名称: Demo83.sql */
--声明一个局部变量
DECLARE @mString nvarchar(40)
--将文字串赋值给局部变量
SET @mString = N'嗨! 谢谢使用本系统'
--显示局部变量所持有的值
SELECT @mString
/* 档案名称: Demo84.sql */
USE NorthwindSQL
--声明一个局部变量
DECLARE @NameVariable char(10)
--将文字串常数赋值给局部变量
SET @NameVariable = '许建仁'
--将局部变量使用于 SELECT 命令的 WHERE 引数中
SELECT 身份证字号,姓名,电话号码,住址
FROM 飞狐工作室
WHERE 姓名 = @NameVariable
/* 档案名称: Demo85.sql */
USE NorthwindSQL
--声明三个局部变量
DECLARE @LastName char(2),
@CityVariable char(6),
@BirthdayVariable datetime
--将常数赋值给局部变量
SET @LastName = '李'
SET @CityVariable = '台北市'
SET @BirthdayVariable = '9/24/1973'
--将局部变量使用于 SELECT 命令的 WHERE 引数中
SELECT 姓名,电话号码,住址,出生日期
FROM 飞狐工作室
WHERE 姓名 LIKE @LastName + '%' AND
住址 LIKE '%' + @CityVariable + '%' AND
出生日期 = @BirthdayVariable
/* 档案名称: Demo86.sql */
USE NorthwindSQL
DECLARE @AvgSalary money
--将计算所得之平均薪资储存至局部变量 @AvgSalaryVariable 中
SELECT @AvgSalary = AVG(目前薪资)
FROM 飞狐工作室
--将局部变量 @AvgSalary 所持有的值显示出来
SELECT @AvgSalary
/* 档案名称: Demo87.sql */
USE NorthwindSQL
DECLARE @MaxSalary money
SELECT @MaxSalary = 目前薪资
FROM 飞狐工作室
ORDER BY 目前薪资 ASC
--将局部变量 @MaxSalary 所持有的值显示出来
SELECT @MaxSalary
适用范围
局部变量只能在声明它们的批处理、存储过程中使用。
/* 档案名称: Demo88.sql */
USE NorthwindSQL
DECLARE @VlHere datetime
SET @VlHere = '09/26/1967'
GO
-- 此时区域性变数 @VlHere 已经不在适用范围之内且已不存在
-- 下面这一道SELECT命令叙述之所以会发生错误，系因它
-- 存取已经不存在的区域性变数 @VlHere
SELECT * FROM 飞狐工作室
WHERE 出生日期 = @VlHere
table数据类型变量
Ø table数据类型是SQL Server2000新添加的数据类型，该数据类型不适用于表的字段，仅适用于局部变量。
Ø table数据类型非常象一个临时表，实际上主要用途是作为多条数据记录的临时存储之处。
衷心建议：
如果允许的话，应该尽可能以table数据类型的变量获得临时表。
/* 档案名称: Demo872.sql */
USE NorthwindSQL
/* 宣告一个名为 @MyTableVariable 的 table 资料型态变数 */
DECLARE @MyTableVariable TABLE
(
部门 varchar(10) PRIMARY KEY,
员工人数 bigint,
部门最高薪资 money,
部门最低薪资 money,
部门平均薪资 money
)
/* 将资料新增至 @MyTableVariable */
INSERT INTO @MyTableVariable
SELECT 部门,
COUNT(*) AS 部门员工人数,
MAX(目前薪资) AS 部门最高薪资,
MIN(目前薪资) AS 部门最低薪资,
AVG(目前薪资) AS 部门平均薪资
FROM 飞狐工作室
GROUP BY 部门
/* 列示出 @MyTableVariable 的内容 */
SELECT * FROM @MyTableVariable
7.5. 运算符
运算符的类型
Ø 算术运算符
Ø 赋值运算符
Ø 位运算符
Ø 比较运算符
Ø 逻辑运算符
Ø 字符串串联运算符
算术运算符
|
运算 |
符号 |
|
加法 |
＋ |
|
减法 |
- |
|
乘法 |
* |
|
除法 |
/ |
|
求模 |
% |
/* 档案名称: Demo89.sql */
DECLARE @number1 int
DECLARE @number2 int
DECLARE @number3 decimal(4,2)
SET @number1 = 13
SET @number2 = 5
SET @number3 = 3
SELECT @number1 + 10
SELECT @number2 - 10
SELECT @number1 + @number2
SELECT @number1 - @number2
SELECT @number1 * @number2
SELECT @number1 / @number2
SELECT @number1 / @number3
SELECT (@number1 * @number2) % (@number1 + @number2)
/* 档案名称: Demo810.sql */
USE NorthwindSQL
SELECT 产品, 库存量 + 已订购量
FROM 产品资料
ORDER BY 产品 ASC
/* 档案名称: Demo811.sql */
USE NorthwindSQL
SELECT 姓名,(目前薪资/起薪 - 1) * 100 AS 调薪幅度百分比
FROM 飞狐工作室
/* 档案名称: Demo812.sql */
SET DATEFORMAT mdy
DECLARE @vlNow datetime
SET @vlNow = '01/01/1999'
SELECT @vlNow + 10 --加上10天
/* 档案名称: Demo813.sql */
SET DATEFORMAT mdy
DECLARE @vlNow datetime
SET @vlNow = '01/01/1999 13:00:00'
SELECT @vlNow + 10.55 --加上10天＋0.55天，
--结果为'01/12/1999 02:12:00'
赋值运算符
Ø 运算符为：＝ （等号）
/* 档案名称: Demo814.sql */
USE NorthwindSQL
DECLARE @Variable1 int
DECLARE @Variable2 money
SET @Variable1 = 10
SELECT @Variable2 = MIN(目前薪资) FROM 飞狐工作室
SELECT @Variable1
SELECT @Variable2
/* 档案名称: Demo815.sql ，此处用于指定查询结果的字段标题*/
USE Northwind
SELECT 员工姓名 = FirstName + ' ' + LastName,
年龄 = DATEDIFF(year,Birthdate,getdate())
FROM Employees
比较运算符
|
运算 |
符号 |
|
小于 |
< |
|
大于 |
> |
|
等于 |
= |
|
不等于 |
<> |
|
不等于 |
!= |
|
小于等于 |
<= |
|
大于等于 |
>= |
|
不小于 |
!< |
|
不大于 |
!> |
/* 档案名称: Demo817.sql */
USE NorthwindSQL
DECLARE @vlCity nvarchar(3)
SET @vlCity = '台北市'
IF (@vlCity <> '')
SELECT 公司名称,连络人,电话
FROM 客户
WHERE 城市 = '台北市'
逻辑运算符
|
运算 |
符号 |
|
与（并且） |
AND |
|
或（或者） |
OR |
|
非（否定） |
NOT |
/* 档案名称: Demo818.sql */
USE NorthwindSQL
SELECT 姓名,性别,电话号码,出生日期
FROM 飞狐工作室
WHERE 性别 = 0 AND
DATEDIFF(year,出生日期,GETDATE()) < 30 AND
部门 = '资讯部'
字符串串联运算符
Ø 运算符为：＋ （加号）
如：SELECT ‘我是温一军’+’大家好！’
运算符的优先顺序
优先顺序从高到低：
Ø ＋（正号）、-（负号）、～
Ø *、/、$
Ø ＋（加）、＋（字符串串联）、-（减号）
Ø 关系运算符
Ø ^（按位异或）、&（按位与）、|（按位或）
Ø NOT
Ø AND
Ø ALL 、ANY 、BETWEEN、 IN 、LIKE 、 OR 、SOME
Ø 赋值运算符
一个众所周知的小技巧：
使用小括号可以改变运算的优先顺序。
7.6. 表达式
一个表达式可以是一个或一个以上的字段、函数、运算符、局部变量、常量、子查询的组合。
7.7. 如何为程序加上注释
单行注释与行内注释
--档案名称: Demo824.sql
--撰写人:温一军
--日期:08/24/99
--设定作用资料库
USE NorthwindSQL
--列出资讯部的员工资料
SELECT * FROM 飞狐工作室 WHERE 部门 = '资讯部'
--档案名称: Demo825.sql
--设定作用资料库
USE NorthwindSQL
--列出资讯部的员工资料
SELECT 姓名,
电话号码,
部门,
年龄 = DATEDIFF(year,出生日期,GETDATE())
--此运算式能计算出年龄
FROM 飞狐工作室
--不列出财务部的员工资料
WHERE 部门 <> '财务部'
ORDER BY 3 DESC --按照年龄高低来排序
段落注释
/*
档案名称: Demo826.sql
系统属性: Database Web
撰写人: 温一军
日期: 08/24/99
*/
/* 设定作用资料库 */
USE NorthwindSQL
/* 列出资讯部的员工资料 */
SELECT * FROM 飞狐工作室 WHERE 部门 = '资讯部'
7.8. 函数
Ø 函数（Function）被用来执行一些特殊的运算。
Ø 每一函数包含一个名称，而在名称之后的是一对小括号。如GETDATE( )
Ø 大部分的函数在小括号中需要一个或一个以上的参数。
一个狠恰当的比喻：
一个函数象一个自动售货机，机身象是函数本身，投币口是函数的一对小括号，投入到自动售货机的钱币就是传递给函数的参数，从自动售货机吐出的饮料是函数的返回值。
字符串函数
Ø LEN( )
格式：LEN(字符串)
用途：取得字符串的长度（先除去字符串尾部的空格），如果是空字符串，则返回零。
/* 档案名称: Demo831.sql */
SELECT LEN('我叫ALEX')
SELECT LEN('我叫ALEX ')
SELECT LEN('')
DECLARE @vlString1 nvarchar(10)
DECLARE @vlString2 nvarchar(16)
SET @vlString1 = '我是温一军'
SET @vlString2 = 'My name is Alex. '
SELECT LEN(@vlString1 + @vlString2)
Ø SUBSTRING( )
格式：SUBSTRING(字符串，开始位置，长度)
用途：从指定的字符串的开始位置开始，获取指定长度的字符串。
/* 档案名称: Demo828.sql */
USE NorthwindSQL
--建立一个资料表
CREATE TABLE DemoTable
(
uid int IDENTITY(1,1),
content ntext
)
--新增资料记录至资料表
INSERT INTO DemoTable
(content)
VALUES(N'ABC我是ALEX章立民')
INSERT INTO DemoTable
(content)
VALUES(N'123我爱Internet')
SELECT uid,SUBSTRING(content,3,5)
FROM DemoTable
--返回：
--C我是AL
--3我爱IN
DROP TABLE DemoTable
/* 档案名称: Demo829.sql */
USE NorthwindSQL
--建立一个资料表
CREATE TABLE DemoTable
(
uid int IDENTITY(1,1),
content text
)
--新增资料记录至资料表
INSERT INTO DemoTable
(content)
VALUES('ABC我是ALEX章立民')
INSERT INTO DemoTable
(content)
VALUES('123我爱Internet')
SELECT uid,SUBSTRING(content,3,5)
FROM DemoTable
--返回：
--C我是
--3我爱
DROP TABLE DemoTable
此处省略了一些常用函数的介绍
无参数函数
特点：
Ø 名称以＠＠开头
Ø 不需要任何参数，函数名之后不需要加上一对小括号
如：@@ROWCOUNT
从形式上看，象一个变量，在7.0版之前，称之为全局变量，从7.0版开始，称之为函数，常用它来取得一些系统信息。
/* 档案名称: Demo853.sql */
USE NorthwindSQL
SELECT * FROM 飞狐工作室
SELECT '查询的资料笔数: ',@@ROWCOUNT
UPDATE 飞狐工作室 SET 姓名 = '章立民' WHERE 身份证字号 = 'Z00990000'
SELECT '姓名被修改的资料笔数: ',@@ROWCOUNT
7.9. 流程控制语句
BEGIN…END
用于将多个SQL语句组合成一个语句块，该语句块可视为一个整体处理。
/* 档案名称: Demo854.sql */
USE NorthwindSQL
Declare @Today int
SET @Today = DAY(GETDATE())
IF (@Today = 1) --如果今天是本月 1 号
BEGIN
PRINT '今天将举办庆生会,本月寿星如下:'
SELECT 姓名,出生日期
FROM 飞狐工作室
WHERE MONTH(出生日期) = MONTH(GETDATE())
END
/* 档案名称: Demo855.sql */
USE NorthwindSQL
IF (SELECT COUNT(*) FROM 飞狐工作室 WHERE 性别 = 1) > 0
BEGIN
PRINT '男性员工的平均薪资:'
PRINT ''
SELECT AVG(目前薪资) FROM 飞狐工作室 WHERE 性别 = 1
IF (SELECT COUNT(*) FROM 飞狐工作室
WHERE 性别 = 1 AND 婚姻状况 = 1) > 0
BEGIN
PRINT '已婚之男性员工的平均薪资:'
PRINT ''
SELECT AVG(目前薪资) FROM 飞狐工作室
WHERE 性别 = 1 AND 婚姻状况 = 1
END
END
循环WHILE
Ø 用于重复多次执行某项工作
Ø 可用CONTINUE使程序直接跳回WHILE命令行做条件判断
Ø 可用BREAK命令，直接退出所在循环。
Ø 循环可以嵌套
/* 档案名称: Demo856.sql */
DECLARE @Number int, @Total int
SET @Number = 0
SET @Total = 0
WHILE(@Number < 11)
BEGIN
SET @Total = @Total + @Number
SET @Number = @Number + 1
END
PRINT '1+2+...+10 = '+CAST(@Total AS char(2)
/* 档案名称: Demo858.sql */
EXEC sp_dboption 'NorthwindSQL', 'select into/bulkcopy', 'TRUE'
--当为 true 时，允许使用 SELECT INTO 语句进行快速大容量复制。
--select into/bulkcopy 选项设置为真以启用无日志记录的操作
USE NorthwindSQL
-- 建立一个新的资料表 TempTable 以利稍后练习之用
SELECT身份证字号,姓名,目前薪资
INTO TempTable
FROM 飞狐工作室
EXEC sp_dboption 'NorthwindSQL', 'select into/bulkcopy', 'FALSE'
PRINT ''
SELECT未加薪前员工的平均薪资 = AVG(目前薪资) FROM TempTable
-- 如果平均薪资小于 $70000, 则全体加薪 1.2 倍
WHILE (SELECT AVG(目前薪资) FROM TempTable) < $70000
BEGIN
UPDATE TempTable
SET 目前薪资 = 目前薪资 * 1.2
SELECT 最高薪 = MAX(目前薪资) FROM TempTable
-- 如果最高薪大于 $150000 ,则不再全体加薪
IF (SELECT MAX(目前薪资) FROM TempTable) > $150000
BREAK
ELSE
CONTINUE
END
PRINT ''
SELECT 全体加薪后员工的平均薪资 = AVG(目前薪资) FROM TempTable
-- 将练习用的资料表 TempTable 删除
DROP TABLE TempTable
条件语句IF…ELSE
该语句使程序能够有不同的条件分支，从而完成在不同的条件下执行不同的操作。
/* 档案名称: Demo859.sql */
USE NorthwindSQL
-- 先检查名称为 MyDemoTable 的使用者资料表是否存在
IF EXISTS (SELECT * FROM sysobjects
WHERE type = 'U' AND name = 'MyDemoTable')
DROP TABLE MyDemoTable
CREATE TABLE MyDemoTable
(
代码 int NOT NULL IDENTITY(1,1),
姓名 nvarchar(20) NOT NULL
)
/* 档案名称: Demo860.sql */
USE NorthwindSQL
DECLARE @Name varchar(10),
@Sex bit,
@Married bit,
@Phone char(10),
@ChineseSex char(2),
@ChineseMarried char(4)
DECLARE Studio_Cursor CURSOR FOR //声明一个游标
SELECT 姓名, 性别, 婚姻状况, 电话号码 FROM 飞狐工作室
OPEN Studio_Cursor //有关游标操作，以后讲解
FETCH NEXT FROM Studio_Cursor //取下一行值
INTO @Name, @Sex, @Married, @Phone
-- 检查 @@FETCH_STATUS 之值以便决定是否
-- 要继续执行回圈以便提取更多的资料记录
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '员工姓名: ' + @Name
IF (@Sex = 1)
SET @ChineseSex = '男'
ELSE
SET @ChineseSex = '女'
IF (@Married = 1)
SET @ChineseMarried = '已婚'
ELSE
SET @ChineseMarried = '未婚'
PRINT '性 别: ' + @ChineseSex
PRINT '婚姻状况: ' + @ChineseMarried
PRINT '电话号码: ' + @Phone
PRINT REPLICATE('-',30)
FETCH NEXT FROM Studio_Cursor
INTO @Name, @Sex, @Married, @Phone
END
CLOSE Studio_Cursor
DEALLOCATE Studio_Cursor //释放游标
/* 档案名称: Demo861.sql */
USE NorthwindSQL
DECLARE @msg varchar(255)
IF (SELECT COUNT(*) FROM 飞狐工作室
WHERE 姓名 LIKE '许%' AND 目前薪资 BETWEEN 15000 AND 20000) > 0
BEGIN
SET NOCOUNT ON
SET @msg = '有一些 ''许'' 姓员工的薪资介于 15000 ～ 20000。这些员工是：'
PRINT @msg
PRINT REPLICATE('-',40)
SELECT 姓名,目前薪资 FROM 飞狐工作室
WHERE 姓名 LIKE '许%' AND 目前薪资 BETWEEN 15000 AND 20000
END
ELSE
BEGIN
SET @msg = '没有任何 ''许'' 姓员工的薪资介于 15000 ～ 20000。'
PRINT @msg
END
/* 档案名称: Demo862.sql */
USE NorthwindSQL
DECLARE @Name varchar(10),
@Salary money,
@Comment varchar(20)
DECLARE Studio_Cursor CURSOR FOR
SELECT 姓名, 目前薪资 FROM飞狐工作室
OPEN Studio_Cursor
FETCH NEXT FROM Studio_Cursor
INTO @Name, @Salary
-- 检查 @@FETCH_STATUS 之值以便决定是否
-- 要继续执行回圈以便提取更多的资料记录
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '员工姓名: ' + @Name
PRINT '目前薪资: ' + CONVERT(varchar(10),@Salary,1)
IF (@Salary >= 60000)
SET @Comment = '哇塞!!超越巅峰耶..'
ELSE
IF (@Salary >= 50000)
SET @Comment = '领先群雄耶...'
ELSE
IF (@Salary >= 40000)
SET @Comment = '还过得去啦!!'
ELSE
IF (@Salary >= 20000)
SET @Comment = '剃刀编缘!!'
ELSE
SET @Comment = '准备卷铺盖吧!!'
PRINT '薪资评比: ' + @Comment
PRINT REPLICATE('-',30)
FETCH NEXT FROM Studio_Cursor
INTO @Name, @Salary
END
CLOSE Studio_Cursor
DEALLOCATE Studio_Cursor
CASE函数
Ø 简单CASE函数
/* 档案名称: Demo863.sql */
DECLARE @nType int
SET @nType = 1
SELECT CASE @nType
WHEN 1 THEN '资讯部'
WHEN 2 THEN '生产制造部'
WHEN 3 THEN '会计部'
WHEN 4 THEN '业务部'
ELSE '设立中...'
END
/* 档案名称: Demo864.sql */
USE NorthwindSQL
SELECT 姓名,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
婚姻 = CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END,
电话号码,住址
FROM 飞狐工作室
/* 档案名称: Demo865.sql */
USE pubs
SELECT 姓名 = au_fname + ' ' + au_lname,
州名 = CASE state //蓝色字指定列标题文字
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
WHEN 'TN' THEN 'Tennessee'
WHEN 'OR' THEN 'Oregon'
WHEN 'MI' THEN 'Michigan'
WHEN 'IN' THEN 'Indiana'
WHEN 'MD' THEN 'Maryland'
WHEN 'UT' THEN 'Utah'
END
FROM authors
ORDER BY au_lname
Ø 搜索CASE函数
/* 档案名称: Demo866.sql 给各等级的薪资一个评语*/
USE NorthwindSQL
SELECT 姓名,
目前薪资,
评价 = CASE
WHEN (目前薪资 >= 60000) THEN '哇塞!!超越巅峰耶..'
WHEN (目前薪资 >= 50000) THEN '领先群雄耶...'
WHEN (目前薪资 >= 40000) THEN '还过得去啦!!'
WHEN (目前薪资 >= 20000) THEN '剃刀编缘!!'
ELSE '准备卷铺盖吧!!'
END
FROM 飞狐工作室
/* 档案名称: Demo867.sql 计算出不同的等级的薪资应该缴纳的税额*/
USE NorthwindSQL
SELECT 姓名,
目前薪资,
应缴税额 =目前薪资 * CASE
WHEN (目前薪资 >= 60000) THEN 0.35
WHEN (目前薪资 >= 50000) THEN 0.25
WHEN (目前薪资 >= 40000) THEN 0.2
WHEN (目前薪资 >= 20000) THEN 0.15
ELSE 0.01
END
FROM 飞狐工作室 ORDER BY 目前薪资 DESC
RETURN命令
Return命令能够无条件地终止一个查询、存储过程或批处理，在存储过程中，可以使用return将数据返回给调用调用者。
/* 档案名称: Demo870.sql 定义一个存储过程,返回三个数的积*/
USE NorthwindSQL
GO
CREATE PROCEDURE MultipleTwoValue @Number1 int,
@Number2 int,
@Number3 int
AS
RETURN @Number1 * @Number2 * @Number3
/* 档案名称: Demo871.sql */
USE NorthwindSQL
DECLARE @CountResult int
//调用存储过程，取得返回值
EXECUTE @CountResult = MultipleTwoValue 19,34,39
//显示该返回值
SELECT '19 乘 34 乘 39 = ',@CountResult
|
第7章 结束 |
