SQLServer存储过程、触发器.ppt

1.标识符、常量和变量（1）标识符：由用户定义的名称，用来标识各种对象如服务器、数据库、数据库对象、变量等。如果标志符中包含空格，要用双引号（“”）或方括号()扩起来。如：StudentName不合法,必须将其表示为StudentName或StudentName。,4.3.4T-SQL的运算功能和控制流程T-SQL虽然和高级语言不同，但它也有运算、控制等功能，以支持复杂的数据检索和集合操纵,（2）常量：在程序运行过程中值不变的量。字符串常量、日期时间常量要用单引号扩起，例如LiPing，1990-05-04。常量可分为字符串常量、整型常量、实型常量、日期时间常量、货币常量和唯一标识常量。单引号中的字符串包含单引号，可以使用两个单引号表示嵌入的单引号。,（3）变量：在程序运行过程中其值可以被改变的量。1)变量的分类局部变量：由用户定义和使用，其名称前有一个符号。全局变量：全局变量由系统定义和维护，其名称前有两个符号。,2）局部变量的定义和赋值定义格式：DECLARE局部变量名数据类型【例】DECLARExfloat,varchar(8)该定义语句定义了变量x是浮点实型，变量var是长度为8的定长字符数据类型。如在一条语句中声明多个变量，各变量之间用“,”分隔。局部变量被定义后其初始值为NULL。,局部变量赋值的基本语法格式：SET局部变量名=表达式SELECT局部变量名=表达式,【例4-53】定义变量c_code为长度为6的定长字符串，score为精度为1的浮点型，并分别用SET和SELECT语句为它们赋值，然后用这两个变量查询T_Grade表中课程代码为“110006”且成绩低于90分的记录。,DECLAREc_codechar(6),scoreNumeric(3,1)SETc_code=130001SELECTscore=80SELECT*FROMT_GradeWHERECourseCode=c_codeANDGrade=3BEGINSETtext=你选了+CAST(cnASchar(2)/*CAST函数将cn的值转换为长度为2的字符数据*/SETtext=text+门课。很好，你完成了任务！ENDELSEBEGINSETtext=你选了+CAST(cnASchar(2)SETtext=text+门课。选课太少，加油！ENDSELECTtextAS选课提示,图4-53统计选课数目结果,（3）WHILE语句实现一条SQL语句或SQL语句块重复执行。语法格式：WHILE条件表达式SQL语句1BREAKSQL语句2CONTINUE,说明：该语句计算条件表达式的值，如果为TRUE，则执行WHILE后的语句块。BREAK为从本层WHILE循环中退出，当存在多层循环嵌套时，使用BREAK语句只能退出其所在的内层循环，然后重新开始外层的循环。CONTINUE为结束本次循环，开始下一次循环的判断。,【例4-56】计算10！。,DECLAREpint,ismallint,textvarchar(100)SETp=1SETi=1WHILEi=90THENAWHENAVG(Grade)=80THENBWHENAVG(Grade)=70THENCWHENAVG(Grade)=60THENDWHENAVG(Grade)60THENEENDAS等级FROMT_GradeGROUPBYStudentCodeSELECT,图4-56成绩等级部分结果,5注释T-SQL中提供两种注释方法，分别用于单行和多行注释。,（1）单行注释（-）语法格式：-注释文本内容文本字符串与要执行的代码可同处一行，也可另起一行。（2）多行注释（/*/）语法格式：/*注释文本内容*/,例如：USESchool-打开School数据库.SELECT*FROMT_Student/*执行一个SELECT语句，显示学生表中所有学生信息*/,4.4视图,(1)视图是一个虚拟表，其内容来自对表查询的基础上。(2)视图的行和列数据来自于定义视图的查询所引用的表，并在引用表时动态生成。(3)视图的结构和显示的数据行并没有实际地以视图结构存储在数据库中，而是存储在视图所引用的基本表中。(4)视图集中、简化和自定义每个用户对数据库的不同认识,是数据库的外模式。用户可以将数据库中自己感兴趣的特定数据展现在视图中。(5)视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。,数据库的三级模式结构,视图示例：,4.4.1创建视图创建视图的方法：1）在企业管理器中创建；2）使用T-SQL的CREATEVIEW语句。使用企业管理器创建好视图后，可以使用“生成SQL脚本”功能查看和保存其对应的SQL定义语句。,创建视图时必须遵循以下原则：只能在当前数据库中创建视图。视图在数据库中作为一个对象存储，视图名称不得与数据库中的表重名。,1使用企业管理器创建视图,【例4-59】在数据库School中，建视图V_StudentGrade，查询学生成绩情况。,1）展开School数据库文件夹，右击“视图”对象，在弹出的快捷菜单上选择“新建视图”命令，打开新建视图对话框，该对话框包括四个窗格：上格是表及关系窗口，中格为视图选择表中列的网格，然后是SQL语句窗格，下格是结果窗格。,2）右单击对话框窗口上格，在快捷菜单中选择“添加表”命令，显示添加表对话框。,图4-57新建视图对话框,图4-58添加表对话框,3）在添加表对话框中选择与视图有关的表、视图或函数（通过Ctrl或Shift键可选择多项），然后单击【添加】按钮；或者直接双击要添加的表等，即可将其添加到视图的查询中。这里选择表：T_Student、T_Course和T_Grade。,4）选择的表添加到窗口的上格，单击每个表字段前的复选框，可将该字段添加到视图中。也可在所示窗口的第二个子窗口选择视图字段，并可指定列的别名、排序方式和规则等。在选择过程中，第三个子窗口中的SELECT语句也会随之自动改变。也可直接在该子窗口中输入SELECT语句。这里选择StudentName、CourseName和Grade三个字段。,5）单击工具栏上的按钮执行，结果显示窗口的第三个子窗口。,6）单击工具栏上的按钮，在弹出的另存为对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。,图4-59V-StudentGrade视图的建立,【例4-60】在数据库School中，建立视图V_StudentAvgGrade,查询学生的平均成绩。1）在新建视图窗口中上格选择表T_Student和T_Grade；2）在字段网格中选择字段和设定汇总字段，或直接在SQL语句窗格中辅助完成语句：SELECTStudentName,AVG(Grade)AS平均成绩FROMT_GradeINNERJOINT_StudentONT_Grade.StudentCode=T_Student.StudentCodeGROUPBYStudentName3）运行查看视图结果，保存该视图为V_StudentAvgGrade。,图4-60V-StudentAvgGrade视图的建立,【例4-61】在数据库School中，建立视图V_StudentAvgGradeExcellent,查询平均成绩大于等于90学生。该例可以参照前面方法在基本表T_Student和T_Grade上创建。但由于视图的内容也可以来自另一个视图，本例基于V-StudentAvgGrade完成。各窗格设置及查询结果如图4-61所示。,图4-61V-StudentAvgGradeExcellent视图的建立,4.4.2使用视图创建后的视图跟的表的用法相同，可以通过视图查询和修改数据库。,【例4-62】在数据库School中，从视图V_StudentGrade查询陈佳迪的成绩。SELECT*FROMV_StudentGradeWHEREStudentName=陈佳迪,4.4.3修改和删除视图（1）在企业管理器中修改视图1）在企业管理器中展开相应数据库和视图，右单击要修改的视图，在弹出的快捷菜单上选择“设计视图”。2）在弹出的“设计视图”窗口中，可以用在企业管理器中创建视图相同的方法修改已定义好的视图。修改完毕存盘。,（2）在企业管理器中删除视图1）在企业管理器中展开相应数据库和视图，右单击要删除的视图，在弹出的快捷菜单上选择“删除”。2）在弹出的对话框中单击【确定】按钮，即可删除选择的视图。,4.5存储过程(1)存储过程是T-SQL语句的集合，它作为数据库对象之一被存储在数据库中。(2)存储过程的作用和使用方式类似于一些编程语言中的过程。,使用存储过程有以下优点：1)可以在一个存储过程中执行多条SQL语句；2)可通过输入参数的变化调用存储过程进行动态执行；3)存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间；4)提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。,4.5.1创建存储过程用户首先创建存储过程以实现特定的功能，然后可在程序中调用该存储过程执行。,1使用企业管理器创建视图,【例4-63】在School数据库中，创建存储过程proc_Course，查询所有课程信息。,1）打开企业管理器，展开School数据库文件夹，右单击“存储过程”，在弹出的快捷菜单上选择“新建存储过程”命令，打开新建存储过程对话框。,2）将“OWNER”和“PROCEDURENAME”参数分别替换成存储过程所有者的名称和存储过程的名称。默认为dbo和PROCEDURENAME。,3）从“文本”列表框的第二行开始输入存储过程的T-SQL语句。这里输入的T-SQL语句仅有一条:SELECT*FROMT_Course。,4）单击【语法检查】按钮可以检查创建存储过程的T-SQL语句的语法是否正确。单击【另存为模板】按钮可将所创建的存储过程设置为模板。,5）完成后，单击【确定】按钮即创建和保存了存储过程proc_Course。,图4-64创建存储过程示例,2使用T-SQL语句创建存储过程可以直接在SQL查询分析器中定义和调用存储过程。,常用存储过程的语法格式：CREATEPROCDURE存储过程名形式参数数据类型VARYING=默认值OUTPUTASSQL语句1SQL语句n,说明：“形式参数”名称必须符合标识符规则；OUTPUT表示该参数是可以返回的，可将信息返回调用者；如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。,【例4-64】在School中创建一个的存储过程proc_SearchStudent,查询指定学生的选课情况。,CREATEPROCproc_SearchStudentstcodechar(8)ASSELECTT_Student.StudentName,T_Course.CourseNameFROMT_StudentJOINT_GradeJOINT_CourseONT_Grade.CourseCode=T_Course.CourseCodeONT_Student.StudentCode=T_Grade.StudentCodeWHERET_Student.StudentCode=scode,4.5.2执行存储过程存储过程定义后，可通过EXECTE语句来执行。,语法格式：EXECUTE存储过程名形参=实参值|变量OUTPUT|DEFAULT,1)“形参”是创建存储过程时定义的形参名；2)“实参值”是输入参数的值；3)“变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数；4)DEFAULT表示使用该参数的默认值作为实参。5)如有多个参数，可依次按以上参数定义规则列出，用逗号“，”隔开。,调用【例4-63】创建的存储过程proc_Course的语句为：EXECproc_Course,调用【例4-64】创建的存储过程proc_SearchStudent，查询05101101学生的选课情况。EXECproc_SearchStudent05101101或EXECproc_SearchStudentscode=05101101,采用“参数=值”的形式，各个参数的顺序可以任意排列。,实参顺序和定义时的参数顺序一致。,【例4-65】在School中创建一个的存储过程proc_SearchStudentAvgGrade，查询某个学生的选课数目和平均成绩。,CREATEPROCproc_SearchStudentAvgGradestcodechar(8),stcountintOUTPUT,stavgintOUTPUTASSELECTstcount=COUNT(T_Grade.StudentCode),stavg=AVG(T_Grade.Grade)FROMT_GradeWHERET_Grade.StudentCode=stcodeGROUPBYT_Grade.StudentCode,本例是带一个输入参数和二个输出参数的存储过程，在定义输出参数时要用OUTPUT进行说明。,调用存储过程proc_SearchStudentAvgGrade，查询05101101学生的选课数目和平均成绩。,DECLAREscountint,savgintEXECproc_SearchStudentAvgGrade05101101,scountOUTPUT,savgOUTPUTPRINT学生+05101101+的选课数目为+CAST(scountASchar(2)+门PRINT学生+05101101+的平均成绩为+STR(savg,5,2)+分,4.5.3查看、修改和删除存储过程用户可以利用企业管理器或T-SQL语句来查看、修改和删除用户创建的存储过程。,（1）查看和修改存储过程在企业管理器中，展开存储过程所属的数据库，单击“存储过程”文件夹，在右边窗格中就可看到所有存储过程；右击相应的存储过程名，在弹出的快捷菜单上选择“属性”命令，打开存储过程属性窗口，出现和创建存储过程相似的窗口，在中间的文本编辑子窗口中可以查看或修改该存储过程的文本。,1使用企业管理器创建视图,（2）删除存储过程右击要删除的存储过程名，在弹出的快捷菜单上选择“删除”命令，弹出“除去对象”对话框。单击【显示相关性】按钮可查看该存储过程所依附的对象，从而了解删除该存储过程对数据库的影响。单击【全部除去】按钮，即删除了相应的存储过程。,4.6触发器触发器是一种特殊的存储过程。触发器的创建主要用来维护数据表中的数据一致性，当对数据表进行插入、删除、更新等操作时，触发器可自动执行。,SQLServer提供了两种类型的触发器：（1）AFTER触发器：是在表中数据被修改之后才被触发，触发器对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。（2）INSTEADOF触发器：在数据修改以前被触发，并取代修改数据的操作，转去执行触发器定义的操作。,4.6.1创建触发器1使用企业管理器创建触发器,【例4-68】在School数据库中创建一个简单的触发器tri_StudentInsDel，当用户插入或删除T_Student表中学生记录时，能自动显示表中的内容。,1）展开School“数据库/表”文件夹，在右边窗格中表中，右击T_Student表，在弹出的快捷菜单上选择“所有任务/管理触发器”命令，打开触发器属性对话框。,2）系统创建触发器语句了CREATETRIGGER的框架。修改框架中的内容为：CREATETRIGGERtri_StudentInsDelONdbo.T_StudentFORINSERT,DELETEASSELECT*FROMT_Student,3）单击【语法检查】按钮可以检查创建触发器的T-SQL语句的语法是否正确。4）单击【确定】按钮即保存和创建了名称为“tri_StudentInsDel”的触发器。,当对表T_Student的数据进行插入或删除操作时，触发器“tri_StudentUpdate”将会自动执行。,使用T-SQL创建触发器触发器语法格式：CREATETRIGGER触发器名ON表名|视图名FORINSERT,UPDATE,DELETEASSQL语句段,【例4-69】在School数据库的T_Student表上创建一个触发器tri_StudentCodeUpdate，当对学号列进行修改时，给出提示信息并取消修改操作。,CREATETRIGGERtri_St