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

1、存储过程，触发器和函数存储过程，触发器和函数 T-SQL编程基础存储过程触发器自定义函数 小结T-SQL语言基础主要数据类型： smallint，int，numeric，decimal，char， varchar，nchar，nvarchar ，datetime运算符及优先级常用函数变量： 局部变量（内存变量，字段变量） 全局变量（系统变量）内容回顾在SQL Server中，可以使用两种类型的注释：以“ - - ”开头 ANSI标准的注释符，用于单行注释。 /* 注释内容 */ 用于单行或多行注释。T-SQL编程基础注 释输出语句PRINT作用：是在屏幕上显示指定信息。语法：PRINT 常量

2、| 变量| 表达式| 函数SELECT作用：以列表结构形式在屏幕上显示指定信息。语法：SELECT 常量 | 变量| 表达式 | 函数 AS 别名, n示例:PRINT 服务器的名称： + SERVERNAMESELECT SERVERNAME AS 服务器名称T-SQL编程基础批是包含一个或多个 Transact-SQL 语句的集合，从应用程序一次性地发送到服务器执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。一般是将一些逻辑相关的业务操作语句放置在同一批中，这完全由代码编写者决定。GO是批处理结束标志。 批处理T-SQL编程基础CREATE 语句不能在批处

3、理中与其他语句组合使用。不能在同一个批处理中更改表，然后引用新列。不能在同一个批中删除一个数据库对象又重建它。不能在同一个批中将规则或默认绑定给列又使用它们。如果 EXECUTE 语句是批处理中的第一句，则不需要 EXECUTE 关键字。（1） 批处理使用应遵守的规则T-SQL编程基础 批处理编译错误使执行计划无法编译，会导致批处理中的任何语句都无法执行。运行时错误产生以下两种影响之一：停止执行批处理中当前语句和它之后的语句。停止执行当前语句，继续执行其它所有语句。 在遇到运行时错误之前执行的语句不受影响。（2）批处理运行时错误的影响T-SQL编程基础 批处理语法： BEGIN 执行的SQL语

4、句 END说明：定义一组语句，通常在IF、ELSE或WHILE后面。 （1）复合语句（BEGINEND）（2） 判断语句（IFELSE）语法：IF 条件表达式 SQL语句1 ELSE SQL语句2 说明：当条件为真时，执行一条或一组语句。 T-SQL编程基础流程控制语句示例：求英语课程的平均分数。如果在80以上，则显示 “成绩达到预期目标”，并查询高于平均分的学号和分数；否则，显示还需继续努力”，并查询低于平均分的学号和分数。 USE 教学成绩管理数据库 GO SET NOCOUNT ON - - 不显示统计信息 DECLARE 平均分 FLOAT SELECT 平均分=AVG(分数) FRO

5、M 教学成绩表 WHERE 课程编号= ( SELECT 编号 FROM 课程信息表 WHERE 名称=英语) PRINT 英语课程平均分数 + STR(平均分,5,1)IF (平均分 80) BEGIN PRINT 成绩达到预期目标! SELECT 学号,分数 FROM 教学成绩表 WHERE 分数平均分 AND 课程编号= ( SELECT 编号 FROM 课程信息表 WHERE 名称=英语) END ELSE BEGIN PRINT 还需继续努力！低于平均分的学生： SELECT 学号,分数 FROM 教学成绩表 WHERE 分数=90 AND 分数=80 AND 分数=70 AND 分

6、数=60 AND 分数70 THEN 及格 ELSE 不及格 END SELECT * FROM #CJB GO示例2：根据分数计算出成绩等级。 USE 教学成绩管理数据库 GO SET NOCOUNT ON SELECT 学号,分数,成绩 INTO #CJB FROM 教学成绩表 UPDATE #CJB SET 成绩= CASE FLOOR(分数/10) WHEN 10 THEN 优秀 WHEN 9 THEN 优秀 WHEN 8 THEN 良好 WHEN 7 THEN 中等 WHEN 6 THEN 及格 ELSE 不及格 END SELECT * FROM #CJB GO语法： WHILE

7、条件表达式 SQL语句 | SQL语句组说明： 设置重复执行 SQL 语句或语句组的条件，只要指定的条件为真，就重复执行语句。在循环内部可使用如下控制语句： BREAK :跳出循环之后执行CONTINUE :转到循环开始之处执行（4）循环语句（WHILE）T-SQL编程基础 流程控制语句USE 教学成绩管理数据库GOSET NOCOUNT ONSELECT 学号,分数 INTO #CJB FROM 教学成绩表 WHERE 课程编号=900002DECLARE n intWHILE(1=1) BEGIN SELECT n=COUNT(*) FROM #CJB WHERE 分数55 IF (n0)

8、 UPDATE #CJB SET 分数=分数+5 WHERE 分数95 ELSE BREAK ENDSELECT * FROM #CJB ORDER BY 分数GO 编写存储过程、自定义函数和触发器是SQL Server程序设计的灵魂，应用好它们，将使数据库的管理和应用更加方便和灵活。存储过程，触发器和函数 T-SQL编程基础 存储过程 触发器自定义函数小 结在数据库实际应用中，存在有带变量数据处理需求，如：某班学生信息表、某老师带过的学生、某班某门课不及格学生等。如何更好实现这样的应用功能？可采用SQL Server中应用最广泛、最灵活的存储过程技术来实现。存储过程 存储过程是存储在SQL

9、Server服务器中的一组预编译的T-SQL语句 ，用于完成某项任务。 存储过程是一个独立的数据库对象，可被应用程序调用，以减少重复编写代码。SQL Server系统为用户提供一组实用的系统存储过程，同时支持用户自定义的存储过程。 存储过程存储过程 由系统提供，用以管理和显示有关数据库和用户的信息。存储在master数据库中并以sp_为前缀，在任何数据库中都可以调用。 存放在用户数据库中以dt_为前缀的存储过程也是一种系统存储过程。 这类存储过程一般在用户创建数据库时由系统自动生成，主要用于处理用户数据库中系统自动生成的一些系统表，起到实时维护的作用 系统存储过程存储过程以sp_为前缀的系统存

10、储过程存储过程以dt_为前缀的系统存储过程存储过程存储过程的概念 用户自定义存储过程由数据库开发人员或管理员编写的完成特定任务的存储过程，存储在创建时的数据库中。我们将重点学习自定义存储过程的创建和使用方法。存储过程-单个 SELECT 语句SELECT 语句块可以包含SELECT语句与逻辑控制语句存储过程中的语句 存储过程 创建存储过程（SSMS）存储过程创建存储过程的模板，只需修改相应的参数就可创建存储过程存储过程 创建存储过程（SSMS） 创建存储过程（T-SQL语句）CREATE PROCDURE 存储过程名 参数 参数的数据类型 =默认值 OUTPUT ,.nAS 任意数量的T-SQ

11、L 语句 CREATE PROCEDURE 语句中可以声明一个或多个变量作为参数 参数分两种：输入参数:用来向存储过程中传入值，执行存储过程时必须提供参数的值，除非定义了参数的默认值。 输出参数:由“ OUTPUT ”标记，存放存储过程执行后的返回值并提供给调用者使用。存储过程 创建不带参数的存储过程示例：设计存储过程补考表显示所有课程的补考学生信息。 USE 教学成绩管理数据库 GO CREATE PROCEDURE 补考表 AS SELECT 学号,姓名,课程名称,分数 FROM 教学成绩表视图 WHERE 分数60 ORDER BY 课程名称,学号存储过程示例：设计存储过程某班某门课程成

12、绩表，按学号排序。 USE 教学成绩管理数据库 GO CREATE PROCEDURE 某班某门课程成绩表 班名 VARCHAR(16),课程名 VARCHAR(50) AS SELECT 学号,姓名,课程名称,分数 FROM 教学成绩表视图 WHERE 课程名称 = 课程名 AND 班级=班名 ORDER BY 学号 创建带输入参数的存储过程存储过程示例：设计存储过程实现统计某班某课程学生成绩分布。 USE 教学成绩管理数据库 GO CREATE PROCEDURE 某班某门课程成绩分布表 班名 VARCHAR(16), 课程名 VARCHAR(50), 人数 INT OUTPUT, 优 I

13、NT OUTPUT, 良 INT OUTPUT, 中 INT OUTPUT, 及 INT OUTPUT, 不 INT OUTPUT AS BEGIN DECLARE 成绩 CHAR(10), 分数 DECIMAL SET 优 = 0 SET 良 = 0 创建带输入和输出参数的存储过程存储过程(续） SET 中 = 0 SET 及 = 0 SET 不 = 0 DECLARE c成绩游标 CURSOR KEYSET FOR SELECT 成绩, 分数 FROM 教学成绩表视图 WHERE RTRIM(班级)=班名 AND RTRIM(课程名称)=课程名 OPEN c成绩游标 SET 人数 = cu

14、rsor_rows FETCH c成绩游标 INTO 成绩, 分数 WHILE fetch_status = 0 BENGIN SET 成绩 = RTRIM(成绩)(续） IF 分数 = 80 SET 良 = 良 + 1 IF 分数 = 70 SET 中 = 中 + 1 IF 分数 = 60 SET 及 = 及 + 1 IF 分数 60 AND (成绩 LIKE 0-9% OR 成绩=不及格) SET 不 = 不 + 1 FETCH c成绩游标 INTO 成绩, 分数 END CLOSE c成绩游标 DEALLOCATE c成绩游标END存储过程存储过程的执行（SSMS）填写执行过程必须的参数

15、自动产生的执行语句执行结果 EXECUTE 存储过程名 参数1，参数n 示例1:查看所有课程的补考情况 USE 教学成绩管理数据库 GO EXEC 补考表示例2:查看03电子商务班级的大学语文课程成绩表 USE 教学成绩管理数据库 GO EXEC 某班某门课程成绩表 03电子商务,大学语文如果存储过程是批处理中的第一条语句，该关键字可省略。存储过程的执行（T-SQL)存储过程存储过程的执行示例3:查看03网络工程班级的SQL Server管理与开发课程的成绩分布情况。 USE 教学成绩管理数据库 GO DECLARE y INT,l INT, z INT,j INT, b INT,rs INT

16、 EXEC 某班某门课程成绩分布表 03网络工程,SQL Server管理与开发, rs OUTPUT,y OUTPUT,l OUTPUT, z OUTPUT, j OUTPUT, b OUTPUT PRINT 总人数：+ 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)存储过程的执行 要使用输出参数

17、，必须在CREATE PRODECURE 语句和EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但不返回值存储过程存储过程的修改（SSMS)选择修改命令后自动产生的语句组。完成存储过程的修改后要执行该组语句。示例:修改 某班某门课程成绩表存储过程，按分数从大到小排序。 USE 教学成绩管理数据库 GO ALTER PROCEDURE 某班某门课程成绩表 班名 VARCHAR(16),课程名 VARCHAR(50) AS SELECT 学号,姓名,课程名称,分数 FROM 教学成绩表视图 WHERE 课程名称 = 课程名 AND 班级=

18、班名 ORDER BY 学号 DESCALTER PROCDURE 存储过程名 参数 参数的数据类型 =默认值 OUTPUT ,.nAS 任意数量的T-SQL 语句 存储过程存储过程的修改（T-SQL)存储过程存储过程的删除（SSMS)示例:删除 补课表存储过程。 USE 教学成绩管理数据库 GO DROP PROC 补考表DROP PROCEDURE 存储过程名 ,. 存储过程存储过程的修改（T-SQL)一旦创建并存储后可被任意多次调用，支持程序模块化的设计。 第一次被调用后就驻留内存，再次调用时不再需要编译，执行速率较高 。存储过程存储在后端数据库中不需要通过网络传输，可减少网络流量 。即

19、使对于没有直接执行存储过程中语句权限的用户，也可授予他执行该存储过程的权限，可作为安全机制使用 。存储过程的优点:存储过程增加复杂性。当业务逻辑比较复杂时不建议使用存储过程，这样会增加复杂性。可移植性低。（不同的数据库系统）存储过程的缺点:存储过程 课堂练习(1) 编写一个存储过程，输入“学号”，显示该学生的考试情况，包括：“姓名”、“课程名”，“分数”。 (2) 编写一个存储过程，修改课程信息表中的课程名称，带两个参数：“课程编号”和修改后的“课程名称”。存储过程，触发器和函数 T-SQL编程基础存储过程触发器自定义函数 小结 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPD

20、ATE、DELETE) 触发而被自动执行。 触发器可以实现比约束更复杂的数据完整性，经常用于加强数据的完整性约束和业务规则。 触发器本身是一个特殊的事务单位。 触发器触发器 与表相关联：必须定义在表上。 自动触发：由INSERT、DELETE、UPDATE操作触发 不能直接调用，也不能传递或接受参数 是事务的一部分：触发器和触发语句作为可在触 发器内回滚的单个事务。 触发器的特点触发器 按触发时刻分 AFTER触发器：执行触发操作和处理完约束后激发， 只能定义于表上，同一个表上可定义多个。 INSTEAD触发器：由触发器的程序代替触发语句的执行，在处 理约束之前激发，可在表或视图上创建，同 一

21、个表或视图只可定义一个。 按触发语句分 INSERT触发器、 UPDATE触发器、 DELETE触发器 触发器的类型触发器 触发器被触发时，系统自动在内存中创建deleted表或inserted表。只读，不允许修改；触发器执行完成后，自动删除。 inserted表 和 deleted 表触发器修改操作inserted表deleted表增加(INSERT)记录存放新增的记录-删除(DELETE)记录-存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录触发器 触发器的创建（SSMS）创建触发器的模板，只需修改相应的参数就可创建触发器CREATE TRIGGER 触发器名ON

22、表名或视图名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETEAS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL语句触发器 触发器的创建（T-SQL） 主要参数说明 AFTER:执行指定操作语句和处理完约束后激发触发器。 FOR:如果仅指定 FOR 关键字，则 AFTER 是默认设置。 INSTEAD OF:指定语句激发触发器并被代替执行。 DELETE、INSERT、UPDATE：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。 AS：指定触发器要执行的操作。

23、 IF UPDATE(列名1) AND|OR UPDATE(列名2):指定何列数据修改时激发触发器。不能用于DELETE语句。触发器 创建触发器时需要指定如下内容: 触发器名称：触发器名。 何处触发：表名或视图名。 何时激发：FOR|AFTER |INSTEAD OF 。 何种数据修改语句触发：INSERT|UPDATE|DELETE 。 何列数据修改时触发: IF UPDATE(列名） 如何触发：SQL语句指定触发器触发时所作的操作。触发器示例1：在学院信息表中创建一个名为T修改学院信息表编号的触发器实现：当修改学院信息表中编号时则下列内容自动作出相应的修改。 系部信息表中的编号 专业信息表

24、中的院系编号 课程信息表中的院系编号 教研信息表中的编号 教师信息表中的教研室编号 前2位是有效的学院编号有效的系部编号，外键有效的系部编号，外键前4位是有效的系部编号有效的教研室编号，外键触发器CREATE TRIGGER T修改学院信息表编号 on 学院信息表AFTER UPDATE AS IF UPDATE(编号) BEGIN SET NOCOUNT OFF DECLARE 旧编号 CHAR(2), 新编号 CHAR (2) SELECT 旧编号=编号 FROM DELETED SELECT 新编号=编号 FROM INSERTED UPDATE 系部信息表 SET 编号=新编号+SUB

25、STRING(编号,3,2) WHERE 旧编号=LEFT(编号,2) （续） UPDATE 专业信息表 SET 院系编号=新编号+SUBSTRING(院系 编号,3,2) WHERE 旧编号=LEFT(院系编号,2) UPDATE 课程信息表 SET 院系编号=新编号+SUBSTRING(院系 编号,3,2) WHERE 旧编号=LEFT(院系编号,2) UPDATE 教研室信息表 SET 编号=新编号+SUBSTRING(编号,3,4) WHERE 旧编号=LEFT(编号,2) UPDATE 教师信息表 SET 教研室编号=新编号+SUBSTRING(编 号,3,4) WHERE 编号=L

26、EFT(教研室编号,2) END（续）触发器示例2：在学院信息表中创建一个名为T删除学院信息表编号的触发器。 CREATE TRIGGER T删除学院信息表编号 ON 学院信息表FOR DELETE AS BEGIN SET NOCOUNT OFF DECLARE BH CHAR(2) SELECT BH=编号 FROM DELETED IF EXISTS (SELECT * FROM 系部信息表 WHERE BH=left(编号,2) BENGIN RAISERROR ( 系部编号正在使用, 不可删除!, 16, 1) ROLLBACK TRANSACTION ENDEND事务回滚，撤销整个

27、触发器已进行的操作。 修改触发器（SSMS）触发器 修改触发器（T-SQL） ALTER TRIGGER 触发器名ON 表名或视图名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETEAS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL语句触发器 禁用或启用触发器（SSMS）触发器ALTER TABLE 表名 ENABLE | DISABLE TRIGGER ALL | 触发器名,n 示例：禁用学院信息表的T删除学院信息表编号触发器。 ALTER TABLE 学院信息表 DISABLE TRIGGER T删除学院

28、信息表记录 禁用或启用触发器（T-SQL）触发器 删除触发器（SSMS）触发器 如果删除表，则表中所有的触发器将被自动删除。 DROP TRIGGER 触发器名 删除触发器（T-SQL）触发器 触发器使用限制: CREATE TRIGGER 必须是批处理中的第一条语句。 在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。 不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。 触发器中不允许以下 T-SQL 语句：ALTER DATABASECREATE DATABASEDISK INITDISK RESIZEDROP DATABASELOAD DATA

29、BASELOAD LOGRECONFIGURERESTORE DATABASERESTORE LOG触发器存储过程，触发器和函数 T-SQL编程基础存储过程触发器自定义函数 小结 函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重复使用。 在SQL Server中，除了系统内置的函数外，用户还可以自定义函数，用以补充和扩展系统支持的内置函数。 函数必须有返回值，根据函数返回值形式的不同，自定义函数分为：标量值函数、内联表值函数、多语句表值函数三种关于自定义函数自定义函数 标量值函数返回一个确定类型的标量值，其返回值类型为除TEXT、 NTEXT、 IMAGE、

30、CURSOR、 TIMESTAMP 和TABLE 类型外的其它数据类型。 函数体语句定义在BEGIN-END语句内，其中包含了可以返回值的T-SQL 命令。（1）标量值函数自定义函数关于自定义函数 内联表值函数的返回值是一个表。 内联表值函数没有由BEGIN-END 语句括起来的函数体，只有一个 SELECT 语句。其返回的表位于RETURN 子句中的SELECT 语句从数据库中筛选出来。 内联表值函数功能相当于一个参数化的视图。 自定义函数（1）内联表值函数关于自定义函数 是标量型和内联表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体

31、，返回值的表数据是由函数体中的语句插入的。 它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。 （1）多语句表值函数自定义函数关于自定义函数创建函数（SSMS）自定义函数创建函数的模板，只需修改相应的参数就可创建自定义函数格式1： CREATE FUNCTION 所有者.自定义函数名(参数n) RETURNS 返回参数的类型 AS BEGIN 函数体 RETURN 函数返回的标量值END 该语法格式用于创建标量值函数； RETURN子句返回单个数据值，如同使用系统内置的函数一样。创建函数（T-SQL）自定义函数示例：创建一个自定义函数is_中文字符串，判断自变量是否是纯

32、中文字符串，返回字符串：是或否。 CREATE FUNCTION is中文字符串(字符串 NCHAR(255) RETURNS NCHAR(1) AS BEGIN DECLARE I TINYINT, J TINYINT SET I=LEN(字符串) SET J=1 WHILE (J=I) BEGIN IF (UNICODE(SUBSTRING(字符串,J,1)256) RETURN 否 SET J=J+1 END RETURN 是 END自定义函数格式2： CREATE FUNCTION 所有者.自定义函数名(参数n RETURNS TABLE AS RETURN ( SELECT 查询语句

33、 ) 该语法格式用于创建表值函数； 返回值是一个Select语句查询结果的表。自定义函数创建函数（T-SQL）示例：创建一个自定义函数：某班某课不及格表输入参数班级名称和课程名称，返回：表。 CREATE FUNCTION 某班某课不及格表 (class VARCHAR(16),course VARCHAR(50) RETURNS TABLE AS RETURN ( SELECT 学号,姓名,课程名称,分数,学年学期,考试类别 FROM 教学成绩表视图 WHERE 班级=class AND 课程名称=course AND 分数60)自定义函数格式3： CREATE FUNCTION 所有者.自

34、定义函数名(参数,n RETURNS 数据表名 TABLE (列名 数据类型,n) AS BEGIN 有效的语句 RETURN END 该语法格式用于创建多语句表值函数； 返回值是一个表。自定义函数创建函数（T-SQL）多语句表值函数的主体中有效的语句是： 赋值语句。 流程控制语句。 DECLARE 语句，定义函数局部的数据变量和游标。 SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。 INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 TALBE 变量。 EXECU

35、TE 语句调用扩展存储过程。自定义函数示例1：创建一个自定义函数：某班某课不及格表2输入参数班级名称和课程名称，返回：表。 CREATE FUNCTION 某班某课不及格表2 (class VARCHAR(16),course VARCHAR(50) RETURNS bk TABLE (学号 CHAR(6),姓名 NCHAR(4),课程名 VARCHAR(50),分数 NUMERIC(5,1) ,学年学期 CHAR(11) ,考试类别 NCHAR(4) AS BEGIN INSERT bk SELECT 学号,姓名,课程名称,分数,学年学期,考试类别 FROM 教学成绩表视图 WHERE 班级

36、=class AND 课程名称=course AND 分数60 RETURN END 示例2：创建一个自定义函数学生会干部表，建立学生会干部信息表并插入数据，返回表。 CREATE FUNCTION 学生会干部表() RETURNS gb TABLE(姓名 NVARCHAR(4),职务 NVARCHAR(5),性别 NCHAR(1) AS BEGIN INSERT gb VALUES(任重,主席,男) INSERT gb VALUES(张驰,副主席, 男) INSERT gb VALUES(陈均, 组织部长, 女) INSERT gb VALUES(梁美娟,文艺部长, 女) INSERT gb VALUES(乔亮, 组织部长, 男) RETURN END自定义函数的调用 用户自定