8.数据库行为特征设计-- SQL程序设计

(3) CASE表达式
CASE WHEN <条件表达式> THEN <结果表达式> [...n] ELSE <结果表达式> END 注意CASE是一个表达式而不是流程控制。当 条件表达式成立,则返回相应的结果表达式,若 条件表达式均不成立,则返回ELSE后的表达式
例：把百分制成绩以“优、良、中、 差”显示。
(5) 其他常用函数
ISNULL(check_expre,replacement_value):ch eck_expre为空，返回replacement_value，否 则返回check_expre。 CAST(expre as data_type):把expre转化成 data_type类型。
例：查询学生编号，姓名和年龄 select id,name,year(getdate())-year(birthday) as age from students 例：查询学生编号，姓名和电话，若电话为空， 不显示。 Select id,name,isnull(telephone,‟‟) as telephone from students 例：把姓名分成姓和名两列显示 select left(name,1),substring(name,2,len(name)-1) from students
(7) 自定义函数- 内嵌表值函数
内嵌用户定义函数是返回 table 的用户定义函数的子 集。内嵌函数可用于实现参数化视图的功能。 例：完成一个对某个学生所有学科的成绩查询，用视图 实现的方法为： CREATE VIEW v_AllGrade AS SELECT a.id, ,b.subname,c.grade FROM students a,subjects b,grade c where a.id=c.id and b.subid=c.subid and a.id=„学生id‟ 由于视图中不能包含参数，上述视图只能查询某个学 生的所有学科成绩，显然在实际应用中不可行。
例：在查询分析器中输入下列内容，运行 后看结果。 1.DECLARE @vname varchar(10) SET @vname=„WangWeiHua‟ PRINT @vname 或SELECT @vname 2.SET语句可以为： SELECT @vname=name FROM students WHERE id=„000001‟
(6) 自定义函数-标量函数
例:查询学生编号,姓名和性别,要求性别显 示“男”和“女”。 建立一个函数f_conv，把0和1转换成 “女”和“男”。 然后用Select语句查询: SELECT id,name,dbo.f_conv(sex) FROM students
建立函数的命令为：
CREATE FUNCTION f_conv(@sex int) RETURNS CHAR(2) AS BEGIN DECLARE @vs CHAR(2) IF @sex=0 SET @vs=„女’ ELSE SET @vs=„男’ RETURN @vs END
进一步的技能说明：
事实上上述问题单用视图也可以解决，使用单 语句表值函数提供了另一种解决方法。 CREATE VIEW v_AllGrade AS SELECT a.id, ,b.subname,c.grade FROM students a,subjects b,grade c where a.id=c.id and b.subid=c.subid SELECT * FROM v_AllGrade WHERE and id=„学生id‟
(3) 日期函数
GETDATE():系统日期 DAY(date):日期的日 MONTH(date):日期的月 YEAR(date):日期的年
(4) 系统函数
CURRENT_USER:当前用户名 SYSTEM_USER：当前用户登入帐户 HOST_ID:运行SQL_SERVER的计算机标识 HOST_NAME：运行SQL_SERVER的计算机 名 DB_NAME/DB_ID：数据库名/标识
SELECT subid,grade=CASE WHEN grade<100 AND grade>=90 THEN '优' WHEN grade<90 AND grade>=80 THEN '良' WHEN grade<80 AND grade>=70 THEN '中' WHEN grade<70 AND grade>=60 THEN '及格' ELSE '不及格' END FROM grade
(2) 字符串函数
LEN(str_expre):取长度 LEFT/RIGHT(char_expre，int_expre):取子串 SUBSTRING(char_expre，start,length):取子 串 UPPER/LOWER (char_expre):转成大/下写 SPACE (int_expre):空格组成字符串 LTRIM/RTRIM(character_expresion):去空格 REVERSE(char_expr):颠倒顺序 STR(float_expr[,length,[,decimal]]):数转字符 串
8.3 流程控制语句 (1) IF语句
IF <条件表达式> BEGIN <语句>[...n] END [ELSE] BEGIN <语句>[...n] END //如果IF和ELSE后只有一个语句,可以省略BEGIN和END
例：比较男女同学的平均成绩。 （或把较高成绩记入某数据表中某字段）
DECLARE @a INTEGER DECLARE @b INTEGER SELECT @a=AVG(grade) FROM students a,grade b WHERE a.id=b.id and a.sex=0 SELECT @a=AVG(grade) FROM students a,grade b WHERE a.id=b.id and a.sex=1 if @a>@b PRINT ‘女同学平均成绩高于男同学’ else PRINT ‘男同学平均成绩高于男同学’
单语句表值函数
内嵌用户定义表值函数可用于支持在 WHERE 子句中 指定的搜索条件的参数。下面是示例： CREATE FUNCTION fn_AllGrade ( @id char(6) ) RETURNS table AS RETURN (SELECT a.id, ,b.subname,c.grade FROM students a,subjects b,grade c where a.id=c.id and b.subid=c.subid and a.id= @id) 内嵌表值函数的调用：SELECT * FROM fn_AllGrade(„学生编号')。 单语句表值函数通过单个 SELECT 语句定义 TABLE 返回值。
WHILE (SELECT AVG(grade) FROM grade)<85 BEGIN UPDATE grade SET grade=99 WHERE grade *1.1>99 UPDATE grade SET grade=grade*1.1 WHERE grade *1.1<=99 END 注意两个Update语句顺序不能颠倒，颠倒后85分的学 生加一次分变成了99分，而应该是85+8.5
8.2 变量的定义和使用
局部变量生存期为定义它的批处理或存储过程 局部变量的定义： DECLARE @<变量名> <数据类型>[，...n] 例：DECLARE @num INTEGER 局部变量赋值：SET @变量名=<表达式>或 SELECT @<变量名>=〈表达式〉[FROM <表 名>[，...n] WHERE <子句>] 显示局部变量：PRINT @<局部变量>
使用事务可以保证一组SQL语句的执行，要么 全部成功，若有一句语句不成功，则全部语句 均不执行。 事务的开始使用BEGIN TRANSACTION 事务的结束可以是：

COMMIT：提交，即所有语句均执行 ROLLBAK：回滚，即取消所有语句
例：用一个事务完成下列工作 students中address全部为ShangHai 删除‘000005’号学生（先添加） 分别用COMMIT和ROLLBACK结束该事务。
(2) 循环语句
WHILE <条件表达式> BEGIN 语句[...n] [BREAK] [CONTINUE] END BREAK语句使循环结束 CONTINUE语句进入下一循环
例：每个学生成绩加10%，但不能超过99(若超 过99则加到99)，然后判别平均分数是否达到85， 若没有达到，则继续加分。
操作步骤是： 在查询分析器中运行： 1.START TRANSACTION UPDATE students SET address=„ShangHai‟ DELETE FROM students WHERE id=„000005‟ 2.SELECT * FROM students --观察执行结果 3.ROLLBACK --回滚上列修改 4.SELECT * FROM students -- 观察执行结果 重复以上步骤，ROLLBACK换成COMMIT。
成绩列列名显示为grade， “grade= ”省略列名 显示为“无列名”。
(4) RETURN和注释
RETURN <整数表达式>：结束程序并返 回表达式的值 行注释使用“-- 函数
(1) 数学 函数
CEILING(数值表达式)：进位取整 FLOOR (数值表达式)：去尾取整 SIGN (数值表达式)：取符号 ROUND (数值表达式)：四舍五入 ABS/SQRT/SQUARE：绝对值/开根/平方 ACOS/ASIN/ATAN/LOG/LOG10/PI
第八章
数据库行为特征设计-- SQL程序设计
批处理和事务 变量的定义和使用 流程控制语句 函数 触发器 存储过程 游标 临时表
8.1 批处理和事务
批处理：批处理是把一组SQL语句,作为一个整 体提交该数据库服务器执行 执行批处理：在查询分析器中输入组成批处理 的SQL语句，然后执行。 语法错误：批处理语句中只要其中有一句有语 法错误，则所有语句都不执行。 逻辑错误：若一个批处理中的某个语句有逻辑 错误（如数据溢出完整性错误），该语句将不 能正常运行，但不会影响其他语句执行。这样 可能会造成数据完整性上的逻辑错误。
ALTER FUNCTION fn_Gather (@classid CHAR(6)) RETURNS @tb_Gather TABLE (subid1 CHAR(6) PRIMARY KEY,num1 INT,num2 INT) AS BEGIN --插入班级各课程记录 INSERT into @tb_Gather SELECT subid,0,0 FROM grade WHERE id in (SELECT id FROM students WHERE classid=@classid) group by subid --计算及格和不及格人数 UPDATE @tb_Gather SET num1=(SELECT count(*) FROM grade WHERE subid=subid1 AND grade<60 AND id IN (SELECT id FROM students WHERE classid=@classid)) UPDATE @tb_Gather SET num1=(SELECT count(*) FROM grade WHERE subid=subid1 AND grade>=60 AND id IN (SELECT id FROM students WHERE classid=@classid)) RETURN END
若SELECT返回多行，则最后一行的name赋 给变量vname
全局变量
SQL Server提供了30多个全局变量，可 在任何批处理程序或存储过程中引用。 全局变量的引用方法为：@@变量名。 例:可用下列语句获得SQL Server的版本 等信息 SELECT @@VERSION PRINT @@VERSION
例：修改以上程序，如果有人加分后达到或超过 99，则不再继续加分。
WHILE (SELECT AVG(grade) FROM grade)<85 BEGIN UPDATE grade SET grade=99 WHERE grade *1.1>99 UPDATE grade SET grade=grade*1.1 WHERE grade *1.1<=99 IF (SELECT COUNT(*) FROM GRADE WHERE GRADE>=99)>0 BREAK END
多语句表值函数
CREATE FUNCTION 函数名 (@参数名 参数类型，……) RETURNS @返回表值 的表名retTableName TABLE (返回表结 构) AS BEGIN …… END 通过retTableName返回表值,例如: SELECT * FROM 函数名(参数)
例：统计某班各门课成绩及格和不及格的人数