数据库存储过程和触发器.

存储过程，触发器T-SQL编程基础存储过程触发器T-SQL语言基础主要数据类型：−smallint，int，numeric，decimal，char，−varchar，nchar，nvarchar，datetime运算符及优先级常用函数变量：−局部变量（内存变量，字段变量）−全局变量（系统变量）内容回顾在SQLServer中，可以使用两种类型的注释：以“--”开头ANSI标准的注释符，用于单行注释。/*注释内容*/用于单行或多行注释。T-SQL编程基础注释输出语句PRINT作用：是在屏幕上显示指定信息。语法：PRINT常量|变量|表达式|函数SELECT作用：以列表结构形式在屏幕上显示指定信息。语法：SELECT常量|变量|表达式|函数[AS别名][,…n]示例:PRINT‘服务器的名称：’+@@SERVERNAMESELECT@@SERVERNAMEAS服务器名称T-SQL编程基础批是包含一个或多个Transact-SQL语句的集合，从应用程序一次性地发送到服务器执行。SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。一般是将一些逻辑相关的业务操作语句放置在同一批中，这完全由代码编写者决定。GO是批处理结束标志。批处理T-SQL编程基础CREATE语句不能在批处理中与其他语句组合使用。不能在同一个批处理中更改表，然后引用新列。不能在同一个批中删除一个数据库对象又重建它。不能在同一个批中将规则或默认绑定给列又使用它们。如果EXECUTE语句是批处理中的第一句，则不需要EXECUTE关键字。（1）批处理使用应遵守的规则T-SQL编程基础批处理编译错误使执行计划无法编译，会导致批处理中的任何语句都无法执行。运行时错误产生以下两种影响之一：停止执行批处理中当前语句和它之后的语句。停止执行当前语句，继续执行其它所有语句。在遇到运行时错误之前执行的语句不受影响。（2）批处理运行时错误的影响T-SQL编程基础批处理语法：BEGIN执行的SQL语句END说明：定义一组语句，通常在IF、ELSE或WHILE后面。（1）复合语句（BEGIN…END）（2）判断语句（IF…ELSE）语法：IF条件表达式SQL语句1[ELSESQL语句2]说明：当条件为真时，执行一条或一组语句。T-SQL编程基础流程控制语句示例：求英语课程的平均分数。如果在80以上，则显示“成绩达到预期目标”，并查询高于平均分的学号和分数；否则，显示还需继续努力”，并查询低于平均分的学号和分数。USE教学成绩管理数据库GOSETNOCOUNTON--不显示统计信息DECLARE@平均分FLOATSELECT@平均分=AVG(分数)FROM教学成绩表WHERE课程编号=(SELECT编号FROM课程信息表WHERE名称='英语')PRINT'英语课程平均分数'+STR(@平均分,5,1)IF(@平均分80)BEGINPRINT'成绩达到预期目标!'SELECT学号,分数FROM教学成绩表WHERE分数@平均分AND课程编号=(SELECT编号FROM课程信息表WHERE名称='英语')ENDELSEBEGINPRINT'还需继续努力！低于平均分的学生：'SELECT学号,分数FROM教学成绩表WHERE分数@平均分AND课程编号=(SELECT编号FROM课程信息表WHERE名称='英语')ENDGO语法1：CASEWHEN条件表达式0THEN结果表达式0[,...n][ELSE结果表达式n]END语法2：CASE测试表达式WHEN简单表达式0THEN结果表达式0[,...n][ELSE结果表达式n]END（3）选择语句（CASE）T-SQL编程基础流程控制语句示例1：根据分数计算出成绩等级。USE教学成绩管理数据库GOSETNOCOUNTONSELECT学号,分数,成绩INTO#CJBFROM教学成绩表UPDATE#CJBSET成绩=CASEWHEN分数=90AND分数=100THEN'优秀'WHEN分数=80AND分数90THEN'良好'WHEN分数=70AND分数80THEN'中等'WHEN分数=60AND分数70THEN'及格'ELSE'不及格'ENDSELECT*FROM#CJBGO示例2：根据分数计算出成绩等级。USE教学成绩管理数据库GOSETNOCOUNTONSELECT学号,分数,成绩INTO#CJBFROM教学成绩表UPDATE#CJBSET成绩=CASEFLOOR(分数/10)WHEN10THEN'优秀’WHEN9THEN'优秀'WHEN8THEN'良好'WHEN7THEN'中等'WHEN6THEN'及格'ELSE'不及格'ENDSELECT*FROM#CJBGO语法：WHILE条件表达式SQL语句|SQL语句组说明：设置重复执行SQL语句或语句组的条件，只要指定的条件为真，就重复执行语句。在循环内部可使用如下控制语句：BREAK:跳出循环之后执行CONTINUE:转到循环开始之处执行（4）循环语句（WHILE）T-SQL编程基础流程控制语句USE教学成绩管理数据库GOSETNOCOUNTONSELECT学号,分数INTO#CJBFROM教学成绩表WHERE课程编号='900002'DECLARE@nintWHILE(1=1)BEGINSELECT@n=COUNT(*)FROM#CJBWHERE分数60AND分数55IF(@n0)UPDATE#CJBSET分数=分数+5WHERE分数95ELSEBREAKENDSELECT*FROM#CJBORDERBY分数GO编写存储过程、自定义函数和触发器是SQLServer程序设计的灵魂，应用好它们，将使数据库的管理和应用更加方便和灵活。存储过程，触发器和函数T-SQL编程基础存储过程触发器自定义函数小结在数据库实际应用中，存在有带变量数据处理需求，如：某班学生信息表、某老师带过的学生、某班某门课不及格学生等。如何更好实现这样的应用功能？可采用SQLServer中应用最广泛、最灵活的存储过程技术来实现。存储过程存储过程是存储在SQLServer服务器中的一组预编译的T-SQL语句，用于完成某项任务。存储过程是一个独立的数据库对象，可被应用程序调用，以减少重复编写代码。SQLServer系统为用户提供一组实用的系统存储过程，同时支持用户自定义的存储过程。存储过程存储过程由系统提供，用以管理和显示有关数据库和用户的信息。存储在master数据库中并以sp_为前缀，在任何数据库中都可以调用。存放在用户数据库中以dt_为前缀的存储过程也是一种系统存储过程。这类存储过程一般在用户创建数据库时由系统自动生成，主要用于处理用户数据库中系统自动生成的一些系统表，起到实时维护的作用系统存储过程存储过程以sp_为前缀的系统存储过程存储过程以dt_为前缀的系统存储过程存储过程存储过程的概念用户自定义存储过程由数据库开发人员或管理员编写的完成特定任务的存储过程，存储在创建时的数据库中。我们将重点学习自定义存储过程的创建和使用方法。存储过程----------------单个SELECT语句SELECT语句块可以包含SELECT语句与逻辑控制语句存储过程中的语句存储过程创建存储过程（SSMS）存储过程创建存储过程的模板，只需修改相应的参数就可创建存储过程存储过程创建存储过程（SSMS）创建存储过程（T-SQL语句）CREATEPROCDURE存储过程名[@参数参数的数据类型][=默认值][OUTPUT][,...n]AS任意数量的T-SQL语句CREATEPROCEDURE语句中可以声明一个或多个变量作为参数参数分两种：输入参数:用来向存储过程中传入值，执行存储过程时必须提供参数的值，除非定义了参数的默认值。输出参数:由“OUTPUT”标记，存放存储过程执行后的返回值并提供给调用者使用。存储过程创建不带参数的存储过程示例：设计存储过程[补考表]显示所有课程的补考学生信息。USE教学成绩管理数据库GOCREATEPROCEDURE补考表ASSELECT学号,姓名,课程名称,分数FROM教学成绩表视图WHERE分数60ORDERBY课程名称,学号存储过程示例：设计存储过程[某班某门课程成绩表]，按学号排序。USE教学成绩管理数据库GOCREATEPROCEDURE某班某门课程成绩表@班名VARCHAR(16),@课程名VARCHAR(50)ASSELECT学号,姓名,课程名称,分数FROM教学成绩表视图WHERE课程名称=@课程名AND班级=@班名ORDERBY学号创建带输入参数的存储过程存储过程示例：设计存储过程实现统计某班某课程学生成绩分布。USE教学成绩管理数据库GOCREATEPROCEDURE某班某门课程成绩分布表@班名VARCHAR(16),@课程名VARCHAR(50),@人数INTOUTPUT,@优INTOUTPUT,@良INTOUTPUT,@中INTOUTPUT,@及INTOUTPUT,@不INTOUTPUTASBEGINDECLARE@成绩CHAR(10),@分数DECIMALSET@优=0SET@良=0创建带输入和输出参数的存储过程存储过程(续）SET@中=0SET@及=0SET@不=0DECLAREc成绩游标CURSORKEYSETFORSELECT成绩,分数FROM教学成绩表视图WHERERTRIM(班级)=@班名ANDRTRIM(课程名称)=@课程名OPENc成绩游标SET@人数=@@cursor_rowsFETCHc成绩游标INTO@成绩,@分数WHILE@@fetch_status=0BENGINSET@成绩=RTRIM(@成绩)(续）IF@分数90AND@分数=80SET@良=@良+1IF@分数80AND@分数=70SET@中=@中+1IF@分数70AND@分数=60SET@及=@及+1IF@分数60AND(@成绩LIKE'[0-9]%'OR@成绩='不及格')SET@不=@不+1FETCHc成绩游标INTO@成绩,@分数ENDCLOSEc成绩游标DEALLOCATEc成绩游标END存储过程存储过程的执行（SSMS）填写执行过程必须的参数自动产生的执行语句执行结果[EXEC[UTE]]存储过程名[参数1，…，参数n]示例1:查看所有课程的补考情况USE教学成绩管理数据库GOEXEC补考表示例2:查看’03电子商务’班级的’大学语文’课程成绩表USE教学成绩管理数据库GOEXEC某班某门课程成绩表'03电子商务','大学语文'如果存储过程是批处理中的第一条语句，该关键字可省略。存储过程的执行（T-SQL)存储过程存储过程的执行示例3:查看’03网络工程’班级的’SQLServer管理与开发’课程的成绩分布情况。USE教学成绩管理数据库GODECLARE@yINT,@lINT,@zINT,@jINT,@bINT,@rsINTEXEC某班某门课程成绩分布表'03网络工程','SQLServer管理与开发',@rsOUTPUT,@yOUTPUT,@lOUTPUT,@zOUTPUT,@jOUTPUT,@bOUTPUTPRINT'总人数：'+CONVERT(CHAR,@rs)PRINT'优秀：'+CONVERT(CHAR,@y)PRINT'良好：'+CONVERT(CHAR,@l)PRINT'及格：'+CONVERT(CHAR,@y)PRINT'不及格：'+CONVERT(CHAR,@b)PRINT'其他：'+CONVERT(CHAR,@rs-(@y+@l+@z+@j+@b))存储过程的执行要使用输出参数，必须在CREATEPRODECURE语句和EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但不返回值存储过程存储过程的修改（SSMS)选择‘修改’命