SQLServer存储过程、触发器.ppt

1. 标识符、常量和变量 （1）标识符：由用户定义的名称，用来标识各种对象如服务器、数据库、数据库对象、变量等。 如果标志符中包含空格，要用双引号（“”）或方括号()扩起来。如：Student Name不合法,必须将其表示为Student Name或Student Name。,4.3.4 T-SQL的运算功能和控制流程 T-SQL虽然和高级语言不同，但它也有运算、控制等功能，以支持复杂的数据检索和集合操纵,（2）常量：在程序运行过程中值不变的量。 字符串常量、日期时间常量要用单引号扩起，例如Li Ping ，1990-05-04。 常量可分为字符串常量、整型常量、实型常量、日期时间常量、货币常量和唯一标识常量。 单引号中的字符串包含单引号，可以使用两个单引号表示嵌入的单引号。,（3）变量：在程序运行过程中其值可以被改变的量 。 1)变量的分类 局部变量：由用户定义和使用，其名称前有一个符号。 全局变量：全局变量由系统定义和维护，其名称前有两个符号。,2）局部变量的定义和赋值 定义格式： DECLARE 局部变量名 数据类型 【例】DECLARE x float, var char(8) 该定义语句定义了变量x是浮点实型，变量var是长度为8的定长字符数据类型。 如在一条语句中声明多个变量，各变量之间用“,”分隔。 局部变量被定义后其初始值为NULL。,局部变量赋值的基本语法格式： SET 局部变量名=表达式 SELECT 局部变量名=表达式,【例4-53】定义变量c_code为长度为6的定长字符串，score为精度为1的浮点型，并分别用SET和SELECT语句为它们赋值，然后用这两个变量查询T_Grade表中课程代码为“110006”且成绩低于90分的记录。,DECLARE c_code char(6), score Numeric(3,1) SET c_code=130001 SELECT score=80 SELECT * FROM T_Grade WHERE CourseCode=c_code AND Gradescore,图4-51 变量查询示例1,【例4-54】将学号为“05101103”的学生姓名存放到变量sname中。,DECLARE sname varchar(16) SELECT sname= (SELECT StudentName FROM T_Student WHERE StudentCode=05101103) SELECT sname as 姓名,2运算符和表达式 (1) 运算符 1)算术运算符：加（+）、减（-）、乘（*）、除（/）、模运算（%） 2)比较运算符：比较两个表达式值之间的关系，运算结果是布尔数据类型（TRUE或FALSE）。通常用在查询的WHERE或HAVING子句中。 。 3）逻辑运算符：对某个条件进行判断。运算结果与比较运算符相同，也是运算结果是布尔数据类型（TRUE或FALSE）。 4）字符串连接运算符：+ , 例如:ABCD & 1234 =ABCD1234,(2) 表达式：表达式是由运算对象、运算符及圆括号组成。 可在SQL查询分析器中使用Select语句查看表达式的结果 例：SELECT ABCD+1234,3常用函数 (1) 数学函数,表4-15常用数学函数,(2) 日期时间函数,(3) 字符串函数,表4-15常用数学函数,(4)聚合函数,4流程控制语句 T-SQL语言也提供了一些流程控制语句，使得对数据库中数据的检索、更新、插入等操作更加方便和容易,(1) BEGINEND语句 多条T-SQL语句使用BEGINEND组合起来形成一个语句块。BEGINEND可以嵌套使用。 语法格式： BEGIN SQL语句1 SQL语句2 END,（2）IFELSE语句 通过判定给定的条件来决定执行哪条语句或语句块。 语法格式： IF 条件表达式 SQL语句1 ELSE SQL语句2,说明： 该语句计算条件表达式的值，如果为TRUE，则执行IF后面的语句块，否则执行ELSE后面的语句块。 如果是单分支流程，可不含ELSE。 如果条件表达式中包含SELECT语句，则必须用圆括号将SELECT语句括起来。 IFELSE可嵌套。,【例4-55】统计学号为“05101101”的学生的选课数目，如果不少于三门课就显示“你选了门课。很好，你完成了任务！“否则显示“你选了门课。选课太少，加油！”。（其中表示选课数目）。,USE School DECLARE cn smallint, text varchar(100) SET cn=(SELECT count(StudentCode) FROM T_Grade WHERE StudentCode=05101101) IF cn=3 BEGIN SET text=你选了+CAST(cn AS char(2) /* CAST函数将cn的值转换为长度为2的字符数据*/ SET text=text+门课。很好，你完成了任务！ END ELSE BEGIN SET text=你选了+CAST(cn AS char(2) SET text=text+门课。选课太少，加油！ END SELECT text AS 选课提示,图4-53 统计选课数目结果,（3）WHILE语句 实现一条SQL语句或SQL语句块重复执行。 语法格式： WHILE 条件表达式 SQL语句1 BREAK SQL语句2 CONTINUE,说明： 该语句计算条件表达式的值，如果为TRUE，则执行WHILE后的语句块。 BREAK为从本层WHILE循环中退出，当存在多层循环嵌套时，使用BREAK语句只能退出其所在的内层循环，然后重新开始外层的循环。 CONTINUE为结束本次循环，开始下一次循环的判断。,【例4-56】计算10！。,DECLARE p int, i smallint, text varchar(100) SET p=1 SET i=1 WHILE i=10 BEGIN SET p= p*i SET i=i+1 END SET text=12310=+CAST(p AS char(10) SELECT text AS 计算结果,图4-54计算10！的结果,（4）RETURN语句 语法格式： RETURN 表达式 使程序从一个查询或存储过程中无条件返回，并返回表达式的值，其后的语句不再执行。,（4）CASE语句 CASE语句是多分支的选择语句。该语句具有两种形式：,简单 CASE 函数形式：将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数形式：计算一组条件表达式以确定结果。,1）简单CASE函数 语法格式： CASE 输入表达式 WHEN 情况表达式 THEN 结果表达式 . ELSE 结果表达式 END,说明： 1)当输入表达式的值与某一个WHEN子句的情况表达式的值相等时，就返回该WHEN 子句中结果表达式的值 。 2)如果所有WHEN 子句中的情况表达式的值都没有与输入表达式的值相等，则返回 ELSE子句后的结果表达式的值，若没有ELSE子句，则返回NULL值。,【例4-57】查询07级女同学的住校情况。,SELECT StudentName AS 姓名, CASE LiveInDorm WHEN 0 THEN 未住校 WHEN 1 THEN 住校 END AS 是否住校 FROM T_Student WHERE Sex=女 AND LEFT(StudentCode,2)=07,字段：是否住校的表达,2）CASE 搜索函数 语法格式： CASE WHEN 条件表达式 THEN 结果表达式 . ELSE结果表达式 END,按顺序计算WHEN子句的条件表达式，当布尔表达式的值为TRUE，则返回THEN后面的结果表达式的值，然后跳出CASE语句。,【例4-58】统计每个学生平均成绩并划分等级。,SELECT StudentCode AS 学号, STR(AVG(Grade),5,2) AS 平均成绩, CASE WHEN AVG(Grade)=90 THEN A WHEN AVG(Grade)=80 THEN B WHEN AVG(Grade)=70 THEN C WHEN AVG(Grade)=60 THEN D WHEN AVG(Grade)60 THEN E END AS 等级 FROM T_Grade GROUP BY StudentCodeSELECT,图4-56 成绩等级部分结果,5注释 T-SQL中提供两种注释方法，分别用于单行和多行注释。,（1）单行注释（-） 语法格式：- 注释文本内容 文本字符串与要执行的代码可同处一行，也可另起一行。 （2）多行注释（/* */） 语法格式： /* 注释文本内容*/,例如： USE School -打开School数据库. SELECT * FROM T_Student /*执行一个SELECT语句，显示学生表中所有学生信息*/,4.4 视图,(1) 视图是一个虚拟表，其内容来自对表查询的基础上。 (2) 视图的行和列数据来自于定义视图的查询所引用的表，并在引用表时动态生成。 (3) 视图的结构和显示的数据行并没有实际地以视图结构存储在数据库中，而是存储在视图所引用的基本表中。 (4)视图集中、简化和自定义每个用户对数据库的不同认识,是数据库的外模式。用户可以将数据库中自己感兴趣的特定数据展现在视图中。 (5) 视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。,数据库的三级模式结构,视图示例：,4.4.1创建视图 创建视图的方法： 1）在企业管理器中创建； 2）使用T-SQL的CREATE VIEW语句。 使用企业管理器创建好视图后，可以使用“生成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-59 V-StudentGrade视图的建立,【例4-60】在数据库School中，建立视图V_StudentAvgGrade, 查询学生的平均成绩。 1）在新建视图窗口中上格选择表T_Student和T_Grade； 2）在字段网格中选择字段和设定汇总字段，或直接在SQL语句窗格中辅助完成语句： SELECT StudentName, AVG(Grade) AS 平均成绩 FROM T_Grade INNER JOIN T_Student ON T_Grade.StudentCode = T_Student.StudentCode GROUP BY StudentName 3）运行查看视图结果，保存该视图为V_StudentAvgGrade。,图4-60 V-StudentAvgGrade视图的建立,【例4-61】在数据库School中，建立视图V_StudentAvgGradeExcellent, 查询平均成绩大于等于90学生。 该例可以参照前面方法在基本表T_Student和T_Grade上创建。但由于视图的内容也可以来自另一个视图，本例基于V-StudentAvgGrade完成。各窗格设置及查询结果如图4-61所示。,图4-61 V-StudentAvgGradeExcellent视图的建立,4.4.2 使用视图 创建后的视图跟的表的用法相同，可以通过视图查询和修改数据库。,【例4-62】在数据库School中，从视图V_StudentGrade查询陈佳迪的成绩。 SELECT * FROM V_StudentGrade WHERE StudentName=陈佳迪,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”和“PROCEDURE NAME”参数分别替换成存储过程所有者的名称和存储过程的名称。默认为dbo和PROCEDURE NAME。,3）从“文本”列表框的第二行开始输入存储过程的T-SQL语句。这里输入的T-SQL语句仅有一条: SELECT * FROM T_Course。,4）单击【语法检查】按钮可以检查创建存储过程的T-SQL语句的语法是否正确。单击【另存为模板】按钮可将所创建的存储过程设置为模板。,5）完成后，单击【确定】按钮即创建和保存了存储过程proc_Course。,图4-64 创建存储过程示例,2使用T-SQL语句创建存储过程 可以直接在SQL查询分析器中定义和调用存储过程。,常用存储过程的语法格式： CREATE PROCDURE 存储过程名 形式参数 数据类型VARYING=默认值OUTPUT AS SQL语句1 SQL语句n,说明： “形式参数”名称必须符合标识符规则；OUTPUT表示该参数是可以返回的，可将信息返回调用者；如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,”隔开。,【例4-64】在School中创建一个的存储过程proc_SearchStudent,查询指定学生的选课情况。,CREATE PROC proc_SearchStudent stcode char(8) AS SELECT T_Student.StudentName,T_Course.CourseName FROM T_Student JOIN T_Grade JOIN T_Course ON T_Grade.CourseCode=T_Course.CourseCode ON T_Student.StudentCode=T_Grade.StudentCode WHERE T_Student.StudentCode=scode,4.5.2 执行存储过程 存储过程定义后，可通过EXECTE语句来执行。,语法格式： EXECUTE 存储过程名形参=实参值|变量OUTPUT|DEFAULT,1)“形参”是创建存储过程时定义的形参名； 2)“实参值”是输入参数的值； 3)“变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数； 4)DEFAULT表示使用该参数的默认值作为实参。 5)如有多个参数，可依次按以上参数定义规则列出，用逗号“，”隔开。,调用【例4-63】创建的存储过程proc_Course的语句为： EXEC proc_Course,调用【例4-64】创建的存储过程proc_SearchStudent，查询05101101学生的选课情况。 EXEC proc_SearchStudent 05101101 或 EXEC proc_ SearchStudent scode=05101101,采用“参数=值”的形式，各个参数的顺序可以任意排列。,实参顺序和定义时的参数顺序一致。,【例4-65】在School中创建一个的存储过程proc_SearchStudentAvgGrade，查询某个学生的选课数目和平均成绩。,CREATE PROC proc_SearchStudentAvgGrade stcode char(8), stcount int OUTPUT,stavg int OUTPUT AS SELECT stcount=COUNT(T_Grade.StudentCode),stavg=AVG(T_Grade.Grade) FROM T_Grade WHERE T_Grade.StudentCode=stcode GROUP BY T_Grade.StudentCode,本例是带一个输入参数和二个输出参数的存储过程，在定义输出参数时要用OUTPUT进行说明。,调用存储过程proc_SearchStudentAvgGrade，查询05101101学生的选课数目和平均成绩。,DECLARE scount int ,savg int EXEC proc_SearchStudentAvgGrade 05101101,scount OUTPUT,savg OUTPUT PRINT 学生+ 05101101+的选课数目为+CAST(scount AS char(2)+门 PRINT 学生+ 05101101+的平均成绩为+STR(savg,5,2)+分,4.5.3 查看、修改和删除存储过程 用户可以利用企业管理器或T-SQL语句来查看、修改和删除用户创建的存储过程。,（1）查看和修改存储过程 在企业管理器中，展开存储过程所属的数据库，单击“存储过程”文件夹，在右边窗格中就可看到所有存储过程； 右击相应的存储过程名，在弹出的快捷菜单上选择“属性”命令，打开存储过程属性窗口，出现和创建存储过程相似的窗口，在中间的文本编辑子窗口中可以查看或修改该存储过程的文本。,1使用企业管理器创建视图,（2）删除存储过程 右击要删除的存储过程名，在弹出的快捷菜单上选择“删除”命令，弹出“除去对象”对话框。单击【显示相关性】按钮可查看该存储过程所依附的对象，从而了解删除该存储过程对数据库的影响。单击【全部除去】按钮，即删除了相应的存储过程。,4.6 触发器 触发器是一种特殊的存储过程。触发器的创建主要用来维护数据表中的数据一致性，当对数据表进行插入、删除、更新等操作时，触发器可自动执行。,SQL Server提供了两种类型的触发器： （1）AFTER触发器：是在表中数据被修改之后才被触发，触发器对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。 （2）INSTEAD OF触发器：在数据修改以前被触发，并取代修改数据的操作，转去执行触发器定义的操作。,4.6.1 创建触发器 1使用企业管理器创建触发器,【例4-68】在School数据库中创建一个简单的触发器tri_StudentInsDel，当用户插入或删除T_Student表中学生记录时，能自动显示表中的内容。,1）展开School“数据库/表”文件夹，在右边窗格中表中，右击T_Student表，在弹出的快捷菜单上选择“所有任务/管理触发器”命令，打开触发器属性对话框。,2）系统创建触发器语句了CREATE TRIGGER的框架。修改框架中的内容为： CREATE TRIGGER tri_StudentInsDel ON dbo.T_Student FOR INSERT, DELETE AS SELECT * FROM T_Student,3）单击【语法检查】按钮可以检查创建触发器的T-SQL语句的语法是否正确。 4）单击【确定】按钮即保存和创建了名称为“tri_StudentInsDel”的触发器。,当对表T_Student的数据进行插入或删除操 作时，触发器“tri_StudentUpdate”将会自动执行。,使用T-SQL创建触发器