11 SQL中数据交换之自含方式

第十一章SQL中数据交换之自含方式11.1自含式SQL概述11.2SQL/PSM介绍11.3T-SQL介绍11.4存储过程11.5T-SQL编程本章小结11.1自含式SQL概述将传统的程序设计语言与SQL相结合主要用于服务器中的应用程序编制以及后台脚本的编制以过程或模块形式长久存储于服务器内供应用程序调用11.1自含式SQL概述自含式SQL的内容：SQL的基本内容：数据定义、数据操纵、数据控制传统程设计语言的主要成分控制类语句、输出语句SQL中数据交换部分游标、诊断、动态SQL服务性内容函数库、类库11.1自含式SQL概述自含式SQL分类标准SQL中，自含式SQL称为SQL/PSM，即SQL持久存储模块SQLServer2000中称为T-SQL11.2SQL/PSM介绍1SQL/PSM语句声明语句SQL的变量声明条件声明声明一个状态以及一个与状态名对应的SQLSTATE值句柄声明将一个过程、模块或compound语句需要处理的状态与一个句柄关联起来11.2SQL/PSM介绍传统算法语言中的控制语句CompoundCaseIfIterateLeaveLoopWhileRepeatForAssignmentCallReturn11.2SQL/PSM介绍SQL中基本部分语句数据定义数据操作数据控制SQL中数据交换语句游标诊断动态SQL11.2SQL/PSM介绍2SQL/PSM中模块的建立定义模块语句Createmodule撤销模块语句Dropmodule创建过程语句Createprocedure撤销过程语句Dropprocedure11.3T-SQL介绍1数据类型、变量与表达式2基本SQL操作3部分数据交换操作4程序流程控制与输出语句5函数6文本、图像操作11.3T-SQL介绍1数据类型、变量与表达式数据类型SQLServer2000中的数据类型变量局部变量用户自定义，在程序内部使用需用declare声明，并以@开头Declare@变量名变量类型11.3T-SQL介绍全局变量系统预先定义和维护用@@作为前缀主要用来记录SQLServer2000的运行状态和有关信息变量的赋值Select@变量名=表达式[from表名|视图]Set@变量名=变量值11.3T-SQL介绍运算符算术运算符+-*/%比较运算符逻辑运算符Andornot字符运算符用+表示字符串的连接11.3T-SQL介绍表达式数值型表达式X+2*y+5字符型表达式“中国首都-”+”北京”日期型表达式#2002-07-01#-#1997-07-01#逻辑型表达式工资=1200and工资=180011.3T-SQL介绍注释符单行注释--多行注释/*…..*/11.3T-SQL介绍2基本SQL操作数据定义语句数据操纵语句数据控制语句11.3T-SQL介绍3数据交换操作游标DeclarecursorDeclare游标名[csroll]cursorforselect语句[for{readonly|updateof列名}]Csroll表示可使用包括查询、增、删、改在内的所有操作Forreadonly或forupdate说明游标为只读或可修改的11.3T-SQL介绍Open语句Open游标名Fecth语句Fetch定位取向from游标名[into@变量名…]Close语句Close游标名DeallocateDeallocate游标名11.3T-SQL介绍游标中的诊断诊断值放在全局变量fetch_status中游标应用的实例--打开northwind数据库usenorthwindgodeclareabccursorforselect*fromshippers/*定义一个游标*/11.3T-SQL介绍openabc/*打开游标*/fecthnextfromabcwhile(@@fetch_status=0)fetchnextfromabc/*游标的使用*/closeabcdeallocateabc/*关闭游标*/go11.3T-SQL介绍4算法程序设计语言中的程序流控制及输出语句Begin…andIf….elseCaseWhile…break…continueGoto11.3T-SQL介绍WaitforReturnPrintraiserror11.3T-SQL介绍5函数查询汇总函数类型转换函数日期函数数学函数字符串函数系统函数图像函数11.3T-SQL介绍6文本、图像操作ReadtextWritetextUpdatetext11.4存储过程存储过程的构作过程名参数过程体Createprocedure存储过程名(参数表)as过程体Dropprocedure存储过程名11.4存储过程存储过程的使用Execute存储过程名(参数表)存储过程的优点减少网络负担提高系统执行效率扩大共享资源11.4存储过程例1：创建一个名为Insert_Record的存储过程，其功能是向表s中插入一条记录，其记录值由参数提供。createprocedureInsert-Record(@snochar(5)@snvarchar(20)@sdvarchar(2)@sasmallint)asinsertintosvalues(@sno,@sn,@sd,@sa)return)11.4存储过程例2：调用存储过程Insert-Record,其参数为：sno=‘1357’,sn=‘王平’,sd=‘cs’,sa=18.executeInsert-Record@sno=‘1357’,@sn=‘王平’,@sd=‘cs’,@sa=1811.4存储过程例3：编制一个存储过程，该存储过程根据输入的学生年龄，输出该年龄的学生姓名。createproceduresearchname@agesmallintasdeclare@namevarchar(10)declareabccursorforselectsnamefromswheresage=@ageopenabcfetchnextfromabcinto@name11.4存储过程While(@@fetch_status=0)Beginprint@namefetchnextfromabcinto@nameEndCloseabcDeallocateabcreturn11.4存储过程调用此存储过程executeprint_score@age=1811.5T–SQL编程例4：编制一个存储过程，该存储过程根据输入的系别，输出该系所有学生的平均分createprocedureprint_score@deptvarchar(10)/*print_score为存储过程名，@dept为输入参数，表示所要查询的系名*/as/*表示存储过程体开始*/11.5T–SQL编程declare@snamevarchar(20),declare@snovarchar(10),declare@gradeint/*声明存储过程中将要用到的局部变量*/print‘----studentgradereport----’/*打印提示内容*/declaremy_cursorcursor/*声明游标*/11.5T–SQL编程ForSelects.sno,sn,isnull(avg(grade),-1)Froms,sc/*isnull(avg(grade),-1)表示若平均成绩为空，则取-1*/Wheresd=@deptands.sno=sc.snoGroupbys.sno,snForreadonly--此游标为只读游标Openmy_cursor11.5T–SQL编程Fetchnextfrommy_cursorinto@sno,@sname,@gradeWhile(@@fetch_status=0)/*(@@fetch_status=0表示取值成功*/Begin--打印学生的学号、姓名和平均成绩Print‘学号：’+@snoPrint‘姓名：’+@snamePrint‘成绩等级：’11.5T–SQL编程If@grade0--成绩中有空值print‘尚有未考科目’If@grade60and@grade0print‘不及格’If@grade90print‘优秀！’If@grade=90and@grade=60print‘通过’11.5T–SQL编程fetchnextfrommy_cursorinto@sno,@sname,@gradeEndclosemy_cursor--关闭游标deallocatemy_cursor--释放游标return11.5T–SQL编程例5：编制一个计算教师工资的存储过程，计算需求如下：教师工资清单：姓名、职称、基本工资、职称补贴、扣税费其中职称补贴：教授1000元、副教授800元、讲师及以下500元；税费：2000元以下0%、2000-4000元5%、4000-8000元8%、8000元以上10%；11.5T–SQL编程创建存储过程GetSalary，实现从teacher表中读取相应数据，按照要求进行计算处理，并将结果写入数据库。CREATEPROCEDUREGetSalaryAS11.5T–SQL编程/*声明变量*/DECLARE@nameVARCHAR(50);DECLARE@titleVARCHAR(50);DECLARE@basic_salaryFLOAT;DECLARE@allowanceFLOAT;DECLARE@taxFLOAT;11.5T–SQL编程/*声明游标myCursor*/DECLAREmyCursorCURSORFORSELECTname,title,basic_salaryFROMteacher/*打开游标*/OPENmyCursor;11.5T–SQL编程/*通过游标从表中读取一条数据并将值赋给相应变量*/FETCHNEXTFROMmyCursorINTO@name,@title,@basic_salary;/*进行数据处理*/WHILE@@FETCH_STATUS=0BEGIN11.5T–SQL编程/*计算职称补贴*/IF@title='教授'BEGINSET@allowance=1000;END;ELSEIF@title='副教授'BEGINSET@allowance=800;END;ELSEBEGINSET@allowance=500;END;11.5T–SQL编程/*计算税金*/IF@basic_salary+@allowance8000BEGINSET@tax=(@basic_salary+@allowance-8000)*0.1;END;ELSEIF@basic_salary+@allowance4000BEGINSET@tax=(@basic_salary+@allowance-4000)*0.08;END;ELSEIF@basic_salary+@allowance2000BEGINSET@tax=(@basic_salary+@allowance-2000)*0.05;END;ELSEBEGINSET@tax=0;END;11.5T–SQL编程/*更新teacher表中相关数据*/UPDATEteacherSETtax=@tax,allowance=@allowanceWHEREname=@name/*取下一条数据*/FETCHNEXTFROMmyCursorINTO@name,@title,@basic_salary;End11.5T–SQL编程/*关闭游标*/CLOSEmyCursor;DEALLOCATEmyCursor;Return本章小结自含