《TSQL程序设计》PPT课件.ppt

1、第6章 T-SQL程序设计,工商管理学院电子商务教研室 滕 岩(rock_) Autumn 2010,批处理、脚本 局部变量、全局变量 函数 流程控制语句,本章纲要,存储过程的基本概念 管理存储过程,6.1.1批处理、脚本,当要完成的任务不能由单独的SQL语句来完成时，SQL Server使用批处理、脚本、存储过程等来组织多条SQL语句,批处理就是一个或多个T-SQL语句的集合,使用GO语句作为批处理结束的标志,脚本包含一系列按顺序执行的批处理，可存储为脚本文件,如果在一个批处理中包含任何语法错误，则整个批处理就不能被编译和执行；如果在一个批处理中某句有执行错误，它仅影响该句的执行，并不影响其

2、他语句执行,学生-课程数据库中,print 学生信息如下： select * from student print 学生表记录个数为： select count(*) from student GO,学生表：Student (Sno, Sname, Ssex, Sage, Sdept) 课程表：Course (Cno, Cname, Cpno, Ccredit) 学生选课表：SC (Sno, Cno, Grade,USE studentcourse GO,6.1.1批处理、脚本,.CSV 结果集合,.sql 脚本文件,.rpt 查询报告,局部变量是一个能够保存特定数据类型实例的对象，是程序中各

3、类型数据的最佳临时存储处，用于在Transact-SQL语句之间传递数据,变量的数据类型可以是系统数据类型，也可以是用户自定义的数据类型，但不能把局部变量定义为text，ntext或image数据类型,局部变量是用户自定义的，它的名称以开头,6.1.2局部变量、全局变量,declare 局部变量名 数据类型 ,n,declare name char(10) declare phone char(20), birth datetime,例6.4：声明两个局部字符型变量，并对它们进行赋值，然后将变量的值显示出来。（今天的日期为：系统日期,例：声明一个局部整型变量，存储学生表记录个数,6.1.2局部

4、变量、全局变量,set 局部变量名=表达式 ,n select 局部变量名=表达式 ,n,declare date char(20), str varchar(30,set date = getdate(,set str = 今天的日期为,print str + date,select str + date,declare geshu int,select geshu = count(*) from student,如果select语句返回了多个值，则局部变量将取得该select语句所返回的最后一个值； Select中若省略“=表达式”，则会将局部变量的值输出，类似于print语句的功能,局部

5、变量的作用域是指可以引用该变量的范围,其作用域从声明它们的地方开始，到声明它们的批处理或存储过程的结尾,声明局部变量的批处理或存储过程结束时，局部变量会自动清除,6.1.2局部变量、全局变量,全局变量是SQL Server系统提供并赋值的变量,用户不能建立全局变量，也不能用set语句来修改全局变量,全局变量通常用来保存一些系统信息，如SQL Server的版本信息、SQL Server服务的相关信息等,6.1.2局部变量、全局变量,全局变量名,print VERSION -显示版本信息 print SERVERNAME -显示服务器名称,使用方法,在要查看的名称上双击,6.1.3函数,和存储过

6、程功能相似，重点掌握存储过程,每个函数都封装了一定的功能,每个函数都有一个名称，名称之后是一对小括号，大部分函数在小括号中需要一个或多个参数,系统函数,用户定义函数,SQL Server提供了数百个内置（系统）函数,可分为字符串函数、数学函数、日期和函数函数、数据类型转换函数等,使用Create Function语句创建,6.1.3函数,使用帮助自学系统函数,日期和时间函数,Beginend语句块,6.1.4流程控制语句,ifelse语句块,case表达式,while语句,数据类型转换函数,USE marketGO,If (select 库存量 from goods where 货品名称=彩电

7、)30 begin declare number1 int set number1=(select 库存量 from goods where 货品名称=彩电) print 彩电的库存量为：+ cast(number1 as varchar(10) print 该商品的库存量已不足，提醒进货！ end GO,6.1.4流程控制语句,goods(货品名称，库存量，供应商，状态，价格,应用情况：当要执行两个或两个以上T-SQL语句时；常用于if 或 else子句；while循环,当彩电的库存量低于30时，发出提醒信息,If exists(select * from goods where 状态=0)

8、 begin select 货品名称,库存量,状态 from goods where 状态=0 print 商场正在对以上产品进行调整，尚不能出售！ end else begin select 货品名称,库存量,状态 from goods where 状态=1 print 商场产品可以出售！ end,6.1.4流程控制语句,goods(货品名称，库存量，供应商，状态，价格,select 供应商, 货品= case 货品名称 when book then 图书 when pen then 铅笔 when desk then 课桌 else 货品名称 end from goods,6.1.4流程控

9、制语句,goods(货品名称，库存量，供应商，状态，价格,select 货品名称,库存量,供应商,状态= case 状态 when 0 then 正在调整，不能出售 when 1 then 正常销售 end from goods,select 货品名称,供应商,价格评价= case when (价格=5000) then 高档商品 when (价格=1500) then 中高档商品 when (价格=900) then 中档商品 when (价格=100) then 低档商品 else 小商品 end from goods,6.1.4流程控制语句,goods(货品名称，库存量，供应商，状态，价

10、格,declare i int, e int set i=1 set e=0 while 1=1 begin if i%7=0 begin set e=e+1 set i=i+1 continue end,6.1.4流程控制语句,if i=6000 break else set i=i+1 end print 1-6000之间能够被7整除的整数共有 +convert(varchar,e)+ 个,求出16000之间所有能被7整除的整数个数,批处理、脚本 局部变量、全局变量 函数 流程控制语句,本章纲要,存储过程的基本概念 管理存储过程,6.2.1存储过程的基本概念,存储过程是SQL Server

11、应用最灵活的技术,存储过程是已经存储在SQL Server服务器中的一组预编译过的T-SQL语句 ，存储过程可以接受参数，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它,例：EXEC sp_helpdb StudentCourse,参数,用户定义存储过程,系统存储过程,主要存储在master 数据库中并以sp_为前缀,在任何数据库中都可以调用，在调用时不必在存储过程前加上数据库名,由用户创建的，是用来完成某项任务的T-SQL语句的组合,存储在创建时指定的数据库中，只能在此数据库中调用,6.2.1存储过程的基本概念,模块化的程序设计； 执行速度快； 减少网络通信量； 保证

12、系统的安全性,6.2.1存储过程的优点,6.2.2管理存储过程,删掉，改成自己定义的存储过程名,as后添加为实现功能编写的SQL语句,若创建不带参数的存储过程：create procedure grade_cno as select * from sc where cno = c1,exec grade_cno （只能查出定义好的c1课程的学生成绩,Create Procedure 存储过程名 参数名 参数的数据类型,.nAS 任意数量的Transct-SQL 语句,6.2.2管理存储过程,执行存储过程：EXECUTE/EXEC 存储过程名 参数1, 参数2,例：创建带有参数的存储过程grad

13、e_cno，查询指定课程的学生成绩,create procedure grade_cno kch varchar(5) as select * from sc where cno = kch,exec grade_cno c2,不使用输出参数，查询结果以网格的形式显示出来； 使用输出参数，可以把查询结果和字符串组合，形成方便用户阅读的结果,例：创建带有参数的存储过程grade_sc，查询指定学生在指定课程上的成绩,6.2.2管理存储过程,create procedure grade_sc xh varchar(10),kch varchar(5), cj float output as sel

14、ect cj = grade from sc where sno = xh and cno = kch,exec grade_sc 14001, c2,declare g float exec grade_sc 14001, c2, g print g,output,练习：1. 创建并执行带输入和输出参数的存储过程p_cj，查询指定学号(作为输入参数)学生所选课程的平均成绩和最高成绩(两个作为输出参数)；2. 写出调用该存储过程的例子，并显示“xx号学生选修课的平均成绩为xx，最高成绩为xx,6.2.2管理存储过程,1.Create procedure p_cj xh varchar(10),pjcj float output, zgcj float output as select pjcj=avg(grade), zgcj=max(grade) from sc where sno=xh,2. declare avg float , max float declare a varchar(10) set a=14003 exec p_cj a, avg output, max output print a+号学生选修课的平均成绩为 + ca