第讲-SQL-server--PLSQL.ppt

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,中南大学信息学院,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第讲 SQL server PLSQL,第讲 SQL server PLSQL第讲 SQL server PLSQL7.1 TSQL的编程基础一、程序注释语句,-：单行注释；,/*。*/：多行注释。此多行注释中不能包括go语句。,二、变量,、分类：局部变量与全局变量,、局部变量使用前要先定义，赋值、再使用,declare 变量名 数据类型每次可定义多个变量,set|select 变量名常量,（）set 与select 赋值的区别为：set 只能一次一个变量,select 则可以一次给多个变量赋值。,Declare x int,Declare y int,Set x=10,（）变量赋值时要注意数据类型要匹配。,7.1 TSQL的编程基础,一、程序注释语句,-,：单行注释；,/*,。*,/,：多行注释。此多行注释中不能包括,go,语句。,二、变量,、分类：局部变量,与全局变量,、局部变量使用前要先定义，赋值、再使用,declare,变量名 数据类型,每次可定义多个变量,set|select,变量名常量,（）,set,与,select,赋值的区别为：,set,只能一次一个变量,select,则可以一次给多个变量赋值。,Declare x,int,Declare y,int,Set x=10,（）变量赋值时要注意数据类型要匹配。,7.1 TSQL的编程基础,四、程序结构,1语句组,BEGIN,语句1,语句2,END,2、if else 结构条件分支结构,（1）if 逻辑表达式,单条语句or beginend 语句组（多条语句）,else,单条语句or beginend 语句组（多条语句）,4.5.2 IF.ELSE 语句,例3:declare x int,y int,z int,set x=40,set y=30,if(xy),set z=x-y,else,set z=y-x,set x=0,print x,print y,print z,4.5.2 IF.ELSE 语句,例4:declare x int,y int,z int,set x=40,set y=30,if(xy),set z=x-y,else,begin,set z=y-x,set x=0,end,print x,print y,print z,7.1 TSQL的编程基础,例1：查询某个教师是否是高级职称,use 教师库,go,declare myN char(8),set myN=周建林,if(select 职称 from 教师表 where 教师姓名=myN)=教授 or(select 职称 from 教师表 where 教师姓名=myN)=副教授,print myN+是高级职称！,else,print myN+不是高级职称！,7.1 TSQL的编程基础,例2：查看周建林老师的工资是否大于2000，如果大于，则显示工资大于2000，否则加到2000。,use 教师库,go,declare myN char(8),set myN=周建林,if(select 工资 from 教师表 where 教师姓名=myNO)=2000,print myN+的工资已过2000！,else,begin,update 教师表 set 工资=2000 where 教师姓名=myNO,print myN+工资已加到2000！,end,CASE表达式,语法格式:,CASE 测试表达式,When 简单表达式1 then 结果表达式1,When 简单表达式2 then 结果表达式2,.,When 简单表达式n then 结果表达式n,else 结果表达式n+1,END,CASE的例子,declare a int,answer char(10),set a=10,set answer=case a,when 1 then A,when 2 then B,when 3 then C,when 4 then D,else others,end,print is+answer,CASE表达式,语法格式:,CASE,When 布尔表达式1 then 结果表达式1,When布尔表达式2 then 结果表达式2,.,When布尔表达式n then 结果表达式n,else 结果表达式n+1,END,CASE的例子,declare a int,answer char(10),set a=10,set answer=case,when a700 then A,when a500 then B,when a300 then C,else D,end,print answer,7.1 TSQL的编程基础,、多重分支结构,有两种结构,例：select 教师姓名,出生年月,年龄层次=,case,when 出生年月1950-01-01 then 老,when 出生年月 between 1950-01-01and 1969-12-31 then 中,else 青,end,from 教师表,7.1 TSQL的编程基础,例；,-use xs,select 教师姓名,出生年月,年龄层次=,(case,when 出生年月1970-01-01 then 青,else ,end,),工资层次=,(,case when 工资2000 then 高,else ,end,),from 教师表,7.1 TSQL的编程基础,3、while 循环结构：,语法结构,while 逻辑表达式,begin,T-SQL语句组,break无条件终止整个循环语句，即跳出循环,continue结束本次循环，回到while处再判断是否重新开始下一次循环,end,7.1 TSQL的编程基础,例：declare a int,b int,c int,select a=1,b=1,c=20,while(c0),begin,set b=b+3,set a=a+b,if b0,begin,set bb+3,set aa+b(此后加入 select a,b,c return，结果又如何？),if b60 order by 课程号,成绩-定义一个滚动游标，其内结果集为成绩表中及格的同学记录,open pass,fetch first from pass 第一条记录,fetch next from pass 第二条记录,fetch last from pass 最后一条记录,FETCH PRIOR from pass 倒数第二条记录,7.1 TSQL的编程基础,6、游标进程,Transact-SQL 游标和 API 游标有不同的语法，但下列一般进程可用于所有 SQL Server 游标：,A.把游标与 Transact-SQL 语句的结果集相关联，并且定义游标的特征，如是否能够更新游标中的行。（声明游标）,B.执行 Transact-SQL 语句以填充游标。（声明游标）,C.检索想要查看的游标中的行。从游标中检索一行或多行的操作称为提取。执行一系列的提取操作以向前或向后检索行的操作称为滚动。,D.根据需要，对游标中当前位置的行执行修改操作（更新或删除）。,E.关闭游标。,7.1 TSQL的编程基础,例3：,declare in_c1 cursor for select 教师姓名,职称,工资 from 教师表 order by 工资 for update of 工资,open in_c 声明一个只进游标，但可以更新工资列。,declare nin money,name char(8),zc char(10)-定义变量,fetch next from in_c into name,zc,nin-从游标中取得第一记录内容存入变量,while fetch_status=0-判断获取是否成功，即记录是否取得完成,begin,if zc=教授or zc=副教授,update 教师表 set 工资=工资+200 where current of in_c,else,update 教师表 set 工资=工资+100 where current of in_c,fetch next from in_c into name,zc,nin-再次取数据，这条语句不可少，否则,无法循环。,end,Go,Close in_c1 关闭游标,这只是一个范例，采用遍历表的方式对数据表中的所有记录进行扫描更新，对大型库这个程序执行时间要考虑好。,7.1 TSQL的编程基础,7、打开游标、关闭游标、释放游标,open close deallocate,注意关闭与释放的区别P：261,8、使用游标取数：,fetch.from 游标 into 变量（表）,注意：INTO允许将提取的数据放入变量表中，各变量的数据类型要与游标选择列表中的相同。同时变量的数目要与列表中列的数目一样。,7.1 TSQL的编程基础,9、与游标相关的系统全局变量：,A、fetch_status：返回FETCH语句的最后状态 0表示获取成功，-1表示获取失败或此行不在结果集中，-2表示被提取的行不存在。,B、cursor_rows：返回本次在服务器连接中打开游标取回的数据行的数目。,例：fetch first from pass,fetch next from pass,select fetch_status-返回0,fetch last from pass,fetch next from pass,select fetch_status-返回-1,select cursor_rows-返回10,7.1 TSQL的编程基础,例9-12:为了实现表9-2的结果,可使用游标编写以下代码:,USE xs-打开教学管理数据库,-创建T学生表,create TABlE 学生表(序号 int,学号 char(8),姓名 char(8),年龄 int),DECLARE I int-声明计数器变量,-声明MyN0,MYName,MyAge变量用于存储学号、姓名、年龄字段信息1,DECLARE MyN0 char(8),MYName char(8),MyAge int,DECLARE MyCursor SCROLL CURSOR-定义游标,游标名为MYcursor,FOR SElect 学生证号,姓名,年龄 FROM 学生基本信息表ORDER BY 年龄,OPEN MyCUrsor-打开游标,SET I=1-计数器设初值1,FETCH FIRST FROM MyCursor,into MyN0,MYName,MyAge -取学生表第一行记录,并对变量赋值,-通过fetcj_STATUS系统全局变量,判断是否到学生表的尾部,否则开始循环,7.1 TSQL的编程基础,While FETCH_STATUS=0,begin,INSERT INTO 学生表(序号,学号,姓名,年龄)-向学生表中插入数据,VALUES(I,MyN0,MYName,MyAge),fetch NEXT FROM MyCursor INto MyN0,MYName,MyAge-取下一行记录,SET I=I+1-计数器值加1,End,close MyCursor -关闭并释放游标,DEALLOCATE MyCursor,SElect*FROM 学生表-一查询T学生表数据,DROP TABLE T学生表 -删除T学生表,GO,7.1 TSQL的编程基础,10、利用游标修改数据：,（1）、更新操作：,update 表名 set 列名=表达式 where current of 游标名,（2）、删除操作：,delete from 表名 where current of 游标名,current of 游标名：指对当前游标数据行操作,7.1 TSQL的编程基础,六、事务,1、事务的作用：,transaction是并发控制的基本单位。SQL Server通过事务机制，将逻辑相关的一组操作捆绑在一起，以便服务器保持数据的完整性,例9-16，9-17,事务机制的的解决方法：要么全部处理，要么全都不处理。,2、事务处理控制语句,begin tain-开始一个事务名,commit tain 提交一个事务,rollback 回滚一个事务。（在发生情况时，撤消提交）,7.2 存储过程,一、存储过程（stored procedure）,1、存储过程是一组预先编译好的T-SQL代码。,2、存储过程的作用,二、创建存储过程,1、系统存储过程以SP_或DT_开头,2、创建存储过程的语法：,create procedure 存储过程名 参数名 数据类型=defaultoutput,参数名 数据类型=defaultoutput,AS,Select 语句,例9-19,例：CREATE PROCEDURE xs AS select*from 学生表,7.2 存储过程,三、执行存储过程,在查询分析器中使用。当是第一条语句时，可以直接用存储过程名，否则之前加上exec。,如：exec xs,四、修改存储过程,alter procedure 存储过程名 AS select 语句,例：alter procedure xs as select 学号,姓名 from 学生表,五、删除存储过程,drop procedure 存储过程名,例1,create procedure student_grade1,as,select sname,cname,grade,from student s inner join sc on s.sno=sc.sno,inner join course c on o=o,where sdept=计算机系,exec student_grade1,例2,create procedure student_grade2 dept char(20),as,select sname,cname,grade,from student s inner join sc on s.sno=sc.sno,inner join course c on o=o,where sdept=dept,exec student_grade2 计算机系,例3,create procedure student_grade3,student_name char(10),course_name char(20)=数据库原理,as,select sname,cname,grade,from student s inner join sc on s.sno=sc.sno,inner join course c on o=o,where sname=student_name,and cname=course_name,exec student_grade3 王五,exec student_grade3 student_name=王五,course_name=数据库原理,例4,drop proc p_student,go,create proc p_student,dept char(20)=计算机系,sex char(2)=男,age int=10,as,select*from student,where sdept=dept,and ssex=sex,and sage=age,exec p_student,exec p_student 计算机系,男,2,exec p_student sex=男,例5,drop proc proc1,go,create proc proc1,var1 int,var2 int,var3 int output,as,set var3=var2*var1,declare result int,exec proc1 5,7,result output,print result,例6,drop proc proc2,go,create proc proc2,cn char(20),avg_grade int output,as,select avg_grade=avg(grade)from sc,join course c on o=o,where cname=cn,declare avg_grade int,exec proc2 数据库原理,avg_grade output,print avg_grade,例7,drop proc proc3,go,create proc proc3,cn char(20),avg_grade int output,total int output,as,select avg_grade=avg(grade),total=count(*),from sc join course c on o=o,where cname=cn,declare avg_grade int,sum int,exec proc3 数据库原理,avg_grade output,sum output,print avg_grade,print sum,例8,drop proc changeteacher,go,CREATE proc changeteacher (tno1 int),AS,begin,declare counter int,declare returnvalue int,set returnvalue=0,select counter=count(*)from teacher where tno=tno1,if(counter0),BEGIN,set returnvalue=1,update teacher set proff=tno1 where tno=tno1,end,return returnvalue,end,go,EXECUTE changeteacher 804,创建一个新的表。然后创建一个存储过程，存储每个系每个分数段的学生统计成果。,分数段为90-100，每个系每门课有多少人？,分数段为80-89，每个系每门课有多少人？,分数段为70-79，每个系每门课有多少人？,分数段为60-69，每个系每门课有多少人？,分数段为(select avg(grade)from sc),2:select*into 表名 from sc where grade(select avg(grade)from sc),9.2.2使用企业管理创建存储过程,9.2.2使用企业管理创建存储过程,7.3 触发器,一、定义：,触发器是一种特殊的存储过程。它与表紧密相连，主要用来保证数据的完整性。,二、触发器的建立,1、语句：create trigger 名 on 表名 for afterinstead of insert|delete|update,AS,SQL语句,If update 测试insert 和update语句是否对指定字段有影响,Inserted表,Deleted表,7.3 触发器,2、例1,create trigger mytr on spk1 for delete 设置一个删除触发器,as,declare OID char(10)-定义一个变量，用于从逻辑表中存储被删除的记录信息,select OID=商品代号 from deleted-从逻辑表中检索被删除的商品代号,delete from spk2 where 商品代号=OID,GO,delete from spk1 where 商品代号=DSJ-120-此条语句执行后，相应的在SPK2中的DSJ-120记录也被删除。,7.3 触发器,例2：建立一个更新触发器,create trigger mytr on spk1 for update 设置一个触发器，相连删除与插入操作,as,declare OID char(10)-定义两个变量，用于存储被删除的记录信息,declare NID char(10)-存储被插入的记录信息,select OID=商品代号 from deleted,select NID=商品代号 from inserted,update spk2 set 商品代号=NID where 商品代号=OID,return-返回语句,update spk1 set 商品代号=DSJ-111 where 商品代号=DSJ-120,例1:,Create trigger tri_insert_sc on sc for insert,As,print 在SC表中插入了数据,select*from inserted,-插入的成绩是多少?在屏幕上打印出来,例2,Create trigger tri_job_salary1,On 工作表 for insert,update as,If exists(select*from inserted where 最低工资=最高工资),begin,print 最低工资必须小于最高工资,rollback,end,例4,Create trigger tri_emp_salary on 职工表,For insert,update as,If exists(select*from inserted a join 工作表 b on a.工作编号=b.工作编号 where 工资 not between 最低工资 and 最高工资),rollback,例5,Create trigger tri_grade on sc for update as,If update(grade),if exists(select*from inserted join deleted on inserted.sno=deleted.sno where inserted.grade=60 and deleted.grade60),rollback,101 001 95,101 002 100,101 003 95,101 004 93,101 005 77,101 006 65,101 007 87,deleted,101 001 90,101 002 90,101 003 90,101 004 90,101 005 90,101 006 90,101 007 90,inserted,例6,Create trigger tri_del_grade,on sc for delete,as,if exists(select*from deleted where grade60),Rollback,7.3 触发器,3、触发器的建立必须是批处理中的第一条语句。触发器建立好后，由SQL Server系统自动执行。（与索引一样）,一个表中可以建立很多个触发器。一般一种操作建立一个。,三、删除触发器,drop trigger 触发器名,四、修改触发器：与建立语句一样。,第263面:10章的练习,10小题:在第5章建立的学生表、课程表和修课表上完成如下操作。,1：除主码约束外，删除其它约束,2：在选课表中定义一个更新和插入的触发器，保证插入的成绩在0-100之内,3:在sc表中定义一个保证课程号在课程表的课程范围内的插入触发器,4:在sc表上定义一个限制学生选课门数不能超5门的触发器,5:在course表上定义一个限制删除有学生选课程的触发器.,第263面:10章的练习,10小题:在第5章建立的学生表、课程表和修课表上完成如下操作。,1：除主码约束外，删除其它约束,第263面:10章的练习,2：在选课表中定义一个更新和插入的触发器，保证插入的成绩在0-100之内,第263面:10章的练习,3:在sc表中定义一个保证课程号在课程表的课程范围内的插入触发器,第263面:10章的练习,4:在sc表上定义一个限制学生选课门数不能超5门的触发器,第263面:10章的练习,5:在course表上定义一个限制删除有学生选课程的触发器.,谢谢观赏,