第9章 SQL 程序设计1.doc

第9章 SQL 程序设计91 批处理、脚本、事物9.1.1批处理k个语句组成的语句组称为一个批处理。一个批处理以关键字GO为结束标志。如例9.1,统计学生人数和男女生人数Use 学生选课系统Select 学生总人数=count(*) from 学生基本情况表GoSelect 性别,学生总人数=count(*) from 学生基本情况表Group by 性别Go（1）解释：USE 数据库A 其功能是选中数据库A 并打开它。（2）批处理的执行过程：设应用程序A由k个批处理组成，系统的执行过程是：将第一个批处理调入内存、语法检查、编译、执行、释放内存。对第二个批处理作同样的处理，直到最后一个批处理执行完毕。 （3）批处理的优点：如果一个应用程序很长，例如1000行。如果不采用批处理，那么，必须同时将这1000行程序调入内在，进行语法检查，编译、执行。这将花费很大的内存，对机器的要求较高。但如果采用批处理，例如，每次调入50行程序进入内存，进行语法检查、编译、执行、释放内存空间。再处理下一个批处理，这样，所占用的内存较小，使得机器可以处理更大的问题。二变量92 变量与临时表 C中有全局变量与局部变量的概念，SQL SERVER 2005 中也同样有全局变量与局部变量的概念.所不同的是,C 中,用户可定义全局变量与局部变量,但SQL SERVER 2005 中,只允许用户定义局部变量,不允许用户定义全局变量。全局变量是系统自带的，有特定的赋值。921 全局变量全局变量是系统自带的变量，不能由用户来定义。用户只能定义局部变量。全局变量以二个开头.SQL SERVER 2000 自带有33个全局变量，每个全局变量有和的赋值。这里我们给出几个：全局变量数据类型功能VersionNvarchar返回当前版本的信息errorNvarchar返回最后执行语句的代码servernameNvarchar返回SQLSER本地服务器的名称同学们在实习中可以体会这些全局变量的取值情况。Go/*例9.4,利用全局变量查看本机所使用的SQL 版本信息，当前服务器名称*/Use 教学Print 当前的SQL server版本信息如下:Print version -显示版本信息Print 当前工作的服务器的名称是:print servername -显示服务器名称Go执行结果：当前的SQL server版本信息如下:Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation （9-1）Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)当前工作的服务器的名称是:AF418C173E48419 （9-2）全局变量对于用户而言，是只读变量，它的值是由DBMS自动装入的。（9-1）、（9-2）是全局变量version与servername的值。9.2.2 局部变量局部变量是由用户定义的变量，其作用域只限于它所定义的存储过程、函数、批处理之中。局部变量定义的一般格式： Declare 变量名 数据类型给局部变量赋值有二种方法，分别使用关键字Set 与 Select：Set 变量名=表达式Select 变量名=表达式 from 表名 where 条件表达式例9.51,以不同方式给局部变量赋值，批处理如下Declare sname char(16) -定义一个变量Set sname=张家口 -使用Set赋值Print sname -显示变量的当前值Select sname=Sname from StudentWhere Sno=S0807001 -使用SELECT 语句给变量赋值Print sname -显示当前的值Go执行结果：张家口 张红 /*例9.52,局部变量作用域实验*/Declare sname char(16) -定义一个变量Set sname=张家口 -使用Set赋值Print sname -显示变量的当前值Select sname=Sname from StudentWhere Sno=S0807001 -使用SELECT 语句给变量赋值Print sname -显示当前的值GoSet sname=刘河岛 (9-3)go语句(9-3)不能执行，因为在第1个GO中所定义的变量sname其作用域仅限于第1个批处理之中，在第2个批处理中已经失效。/*例9.6 以不同方式给局部变量赋值，批处理如下*/Declare sno char(16) -定义一个变量Set sno =S0807001Print sno -显示变量的当前值Select sno=Sno from StudentWhere Sname=张红 -使用SELECT 语句给变量赋值Print sno -显示当前的值GoS0807001 S08070019.2.3 表变量表变量定义的一般格式：Declare 表变量名TABLE (字段名1 数据类型1，,字段名k 数据类型k)表变量定义好之后，可以使用”Insert into 表变量A values（值1，值k）”为表变量A插入一行值。表变量定义好之后，可以使用“使用“Insert into 表变量 Select 字段名 from 基表名”结构对表变量A插入多行值。例9.7, 定义一个表变量mk(课程号,人数,平均分)，使用“Insert into 表变量 values(x1,x2,x3)来使表变量中增添一行数据，并查看表变量中的数据存储情况。Declare k -定义一个表变量TABLE ( 课程号 CHAR(16), 人数 Smallint, 平均分 Smallint )Insert into k values (c01,18,85) -向k插入一行值Select * from kGo例9.8-1,定义一个表变量mu(课程号,人数,平均分)，使用“Insert into 表变量 Select 字段名 from 基表名”来使表变量存储多行数据，并查看表变量中的数据存储情况。Declare mu -定义一个表变量TABLE ( 课程号 CHAR(16), 人数 Smallint, 平均分 Smallint )Insert into muSelect 课程号,总人数=count(学号),平均成绩=avg(成绩) from 学生选课表group by 课程号Select * from muGo/*例9.8-2,使用表变量存储多行数据*/declare ktable ( Cname char(14), Snum smallint, Aavpoint smallint)INSERT INTO k values (c01,18,29)select * from kGoP168,例9.8-3declare ktable ( Cname char(14), Snum smallint, Aavpoint smallint)INSERT INTO k Select Cno,count(Sno), AVG(Grade) FROM SCGroup by CnoSelect * FROM kGo这里使用了基表SC（Sno,Cno,grade）/*例 创建一个临时表，用于存储各课程的课程号、选课人数、平均成绩*/Create table #C_N_avg( Cname char(14), Snum smallint, Aavpoint smallint)GoINSERT INTO #C_N_avg Select 课程名,count(学号), AVG(成绩) FROM 学生选课表,课程表Where 学生选课表.课程号=课程表.课程号Group by 课程名GoSelect * FROM #C_N_avgGo这里，使用了如下的表课程表（课程号，课程名，学时，学分），学生选课表（学号，课程号，成绩）93 程序控制结构931 Begin end 结构 Begin end 结构将若干个平行的SQL语句结合在一起，形成一个程序块。一个块只能于一个GO处理之中，不能跨批处理。一个批处理中可以有若干个块。Begin end 结构的格式：Begin SQL 语句1,SQL 语句2,SQL 语句nEnd 例931BeginDeclare Sname char(16), Sno char(8) SET Sno=S0807003SELECT Sname=姓名 from 学生基本情况表 Where 学号=SnoPrint SnameEnd BeginDeclare Sname char(16), Sno char(8) - SET Sno=S0807003SELECT Sname=姓名from 学生基本情况表 - Where 学号=SnoPrint SnameEnd print Sno执行结果：刘晨三 S0807003说明定义二个变量。其执行过程是在内存开辟二个存储空间，分别为16个字节与8个字节，用于定长存储字符型数据。称为查询赋值语句。其执行过程是先执行查询语句，获取其查询值，再赋给变量。其查询过程是：对于学生基本情况表，取第一条记录，如果该记录的“学号”字段的值与Sno的值相同，则将该记录中的字段“姓名”的值赋给变量Sname，考虑下一条记录。如果该记录的“学号”字段的值与Sno的值不相同，则考虑下一条记录。如此考虑完学生基本情况表中的所有记录。SQL中的的赋值符“=”与C 中的赋值符是兼容的。但关于打印输出，C 中是函数printf(),而SQL 中是打印命令print。 Begin end 结构中定义的局部变量，其作用域是它所在的批处理全过程。如语句（9-6）一样可以被执行。BeginDeclare Sname char(16), Sno char(8) SET Sno=S0807003SELECT Sname=姓名 from 学生基本情况表 Where 学号=SnoPrint SnameEnd BeginDeclare Sname char(16), Sno char(8) SET Sno=S0807003SELECT Sname=姓名from 学生基本情况表 - Where 学号=SnoPrint SnameEnd print Sno （9-6）刘晨三 S0807003例910 如果有选修8门课以上的学生就列出学生名单Begin Declare Num int Set Num=3If exists(select count(课程号) from 学生选课表Group by 学生选课表.学号 having count(课程号)=Num )BEGIN SELECT 选课+CAST (Num AS CHAR(2)+门以上课程的的名单: Select 姓名,count(课程号) 选项课门数 from 学生基本情况表 S, 学生选课表 Where S.学号=学生选课表.学号 group by 姓名 having count(课程号)=NumOrder by count(课程号) DESCEND ELSE PRINT +没有选课+CAST (Num AS CHAR(2)+门以上的学生End Go/*P170，例9.11.1*/BeginDeclare Sname char(16), Sno char(8)SET Sno=S0807003SELECT Sname=姓名 from 学生基本情况表 Where 学号=SnoPrint SnameEnd /*p172,例9.11.2查询学生所选课程的课程名*/select 课程名 from 学生选课表 join 课程表 on 学生选课表.课程号=课程表.课程号goselect 课程名 from 学生选课表,课程表 where 学生选课表.课程号=课程表.课程号go/*p172,例9.11.3*/select count(*) from 学生选课表go/*p172,例9.11.4将学生选课表中的成绩转换为等级*/Select 学号,课程号,成绩= caseWhen 成绩=90 then 优 When 成绩=80 then 良When 成绩=70 then 中等When 成绩=60 then 及格Else 不及格endFrom 学生选课表go/*p172,例9.11.5*/Select 学生号=学生选课表.学号,姓名,课程名,成绩=caseWhen 成绩=90 then 优 When 成绩=80 then 良When 成绩=70 then 中等When 成绩=60 then 及格Else 不及格End From 学生基本情况表,学生选课表,课程表Where 学生选课表.学号=学生基本情况表.学号 and 课程表.课程号=学生选课表.课程号Go/*p172,例9.11.7*/Select 学生选课表.学号,姓名,课程名,成绩=caseWhen 成绩=90 then 优 When 成绩=80 then 良When 成绩=70 then 中等When 成绩=60 then 及格Else 不及格End From 学生基本情况表,学生选课表,课程表Where 学生选课表.学号=学生基本情况表.学号 and 课程表.课程号=学生选课表.课程号And 学生选课表.课程号=C090807004/*p172,例9.11.8=例9.11*/Begin declare c_name char(20), Cno char(12)set Cno=C090807004IF exists (select count (*) FROM 学生选课表 WHERE 课程号=Cno) BEGIN SET c_name=(select 课程名 from 课程表 Where 课程号=Cno)Select 选修课程+c_name +的学生成绩单:Select 学生号=学生选课表.学号,姓名,成绩=caseWhen 成绩=90 then 优 When 成绩=80 then 良When 成绩=70 then 中等When 成绩=60 then 及格Else 不及格End From 学生基本情况表,学生选课表where 学生基本情况表.学号=学生选课表.学号 and 课程号=CnoEndElse print 没有选修+c_name +的学生EndGo选修课程计算机基础 的学生成绩单:S0807003王珍英及格S0807033赵初当良S0807034黄忠好不及格S0807040龚生王及格S0807042赵东河良S0807043肖平红中等S0807074肖东成及格S0807078苏顶成良S0807079张工琥优p178，例9.12Create function SC_pass(grade int)Returns char(8)As Begin Declare info char(8) If grade=90 set info=优 else If grade=87 set info=良 else If grade=60 set info=通过 else set info=不通过 Return infoend gop178，例9.12.1USE 学生信息管理数据库goselect 姓名,课程名, dbo.SC_pass(成绩)from 学生基本情况表 S,学生选课表,课程表where S.学号=学生选课表.学号 and 学生选课表.课程号=课程表.课程号and 课程表.课程号=C090807001p179，例9.13 通过一个表值函数，通过课程名、系名称 、可以查询某系中选项修了该课程的全部学生的和成绩。USE 学生信息管理数据库GoCreate function D_course1(depart char(16),Cna varchar(20) Returns tableAs return (select 姓名,课程名,成绩 from 学生基本情况表 S,学生选课表 SC,课程表 Cwhere S.学号=SC.学号 AND SC.课程号=C.课程号 AND Cna=C.课程名 AND depart=所属系)gop179，例9.13USE 学生信息管理数据库GoSELECT * from dbo.D_course4(D03003,c语言)go95 自定义函数951 标量函数例9-12 标量函数实例Create function SC_pass(grade int)Returns char