《存储过程和触发器》PPT课件.ppt

1,第四章存储过程和触发器,2,第4章 存储过程和触发器,4.1 存储过程4.2 触发器,3,4.1 存储过程,4.1.1 存储过程基本概念4.1.2 创建和执行存储过程4.1.3 存储过程的修改和删除,4,4.1.1 存储过程基本概念,存储过程是指经过预先编译的SQL语句的集合，可以以一种可执行的形式永久地存储在数据库中。需要时只需调用该过程就可以完成相应的操作。存储过程类似于程序设计语言中的过程和函数。,5,4.1.1 存储过程基本概念(续),存储过程具有以下优点。（1）运行速度快（2）模块化编程，增强代码的重用性和共享性（3）减少网络通信量（4）保证系统的安全性,6,4.1.1 存储过程基本概念(续),在SQL Server中，存储过程有两种类型：系统存储过程用户定义的存储过程系统存储过程存放在master数据库中并以sp_为前缀，主要是从系统表中获取信息，为系统管理员管理SQL Server提供支持。用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程。对于用户创建的存储过程，尽量不要以sp_作为其名称的前缀。,7,4.1.2 创建和执行存储过程,一、创建存储过程T-SQL语句格式：CREATE PROCEDURE procedure_name parameter data_type=default OUTPUT,WITH ENCRYPTION AS sql_statement；procedure_name：存储过程名parameter：存储过程中定义的输入/输出参数data_type：参数的数据类型default：定义参数的默认值OUTPUT：输出参数WITH ENCRYPTION：加密存储过程sql_statement：存储过程中要执行的T-SQL语句,8,一、创建存储过程(续),创建存储过程时，需要注意以下几点：（1）CREATE PROCEDURE不能与其他的T-SQL语句位于同一个批处理中。（2）创建存储过程的权限默认属于数据库拥有者。（3）只能在当前数据库中创建存储过程。（4）成功执行CREATE PROCEDURE语句后，存储过程名称将存储在sysobjects系统表中，而CREATE PROCEDURE语句的文本将存储在syscomments中。（5）存储过程可以嵌套调用。（6）存储过程中不允许使用创建数据库对象的语句。,9,一、创建存储过程(续),例4.1 在学生-课程数据库中创建一个存储过程，查看“张明”同学的有关基本信息和选修课程的情况。CREATE PROCEDURE student_course AS SELECT Student.Sno,Sname,Cname,Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=张明;为了避免出现重复的存储过程名称而无法建立该存储过程，可以先使用以下语句：IF EXISTS（SELECT name FROM sysobjects WHERE name=student_course AND type=P)DROP PROCEDURE student_course；,10,一、创建存储过程(续),系统存储过程sp_helptext：查看存储过程中的SQL语句系统存储过程sp_depends：查看存储过程使用的对象以及调用该存储过程的其他存储过程的名称。,11,二、执行存储过程,EXECUTE语句格式如下：EXECUTE return_status=parameter=value|variable OUTPUT,.n;-return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在EXECUTE语句之前，必须声明过。-procedure_name：被调用执行的存储过程名。-parameter：是CREATE PROC语句中定义的存储过程参数。在以parameter_name=value格式使用时，参数名称和常量不一定按照CREATE PROC语句中定义的顺序出现，但是若有一个参数使用parameter_name=value格式，则其他所有参数都必须使用这种格式。-variable：保存输入参数或者输出参数值的变量。-OUTPUT：输出参数。存储过程的匹配参数也须指定关键字OUTPUT。,12,二、执行存储过程(续),例4.2 调用例4.1定义的存储过程的方法为EXECUTE student_course;即可查询“张明”同学的相关信息。,13,三、通过存储过程传递参数,参数：输入参数和输出参数输入参数：当程序执行存储过程时，可以通过输入参数向该存储过程传递值，这些值可作为SQL编程语言中的标准变量使用 输出参数：存储过程也可以通过OUTPUT参数将值返回至调用程序。一个存储过程可以定义多达1024个参数，每个参数必须指定参数名和数据类型，并且参数名必须以“”符号作为前缀，也可以为参数指定默认值。,14,三、通过存储过程传递参数(续),（1）带输入参数的存储过程 例4.3 可以查询任意一个学生的选修信息。可将学生姓名作为输入参数把值传递给存储过程。CREATE PROCEDURE student_course1 StudentName VARCHAR(10)AS SELECT Student.Sno,Sname,Cname,Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=StudentName；,15,三、通过存储过程传递参数(续),调用该存储过程的方法为EXECUTE student_course1 张明；或 EXECUTE student_course1StudentName=张明；,16,三、通过存储过程传递参数(续),例4.4 创建一个存储过程用于向学生表中插入记录。CREATE PROCEDURE student_insertxh CHAR(6),name VARCHAR(8),sex CHAR(2),age INT,sdep CHAR(10)AS INSERT INTO Student VALUES(xh,name,sex,age,sdep);该存储过程通过参数将学生的有关情况传递给存储过程。调用该存储过程的方法为EXECUTE PROCEDURE student_insert 950002,李明,男,20,CS；,17,三、通过存储过程传递参数(续),（2）使用默认参数值例4.5 创建一个存储过程，若没有给出学生姓名，则返回所有学生的有关情况。CREATE PROCEDURE student_course2 StudentName VARCHAR(10)=NULLAS IF StudentName IS NULLBEGIN SELECT Student.Sno,Sname,Cname,Grade FROM Student,Course,SCWHERE Student.Sno=SC.Sno ANDCourse.Cno=SC.Cno END ELSE,18,三、通过存储过程传递参数(续),(续上页)ELSE BEGIN SELECT Student.Sno,Sname,Cname,Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=StudentName END,19,三、通过存储过程传递参数(续),（2）使用输出参数 例4.6 创建一个存储过程，返回选修某门课程的学生人数。CREATE PROCEDURE student_countCourseName VARCHAR(20),StudentSum INT OUTPUTAS SELECT StudentSum=COUNT(*)FROM Coures,SC WHERE Course.Cno=SC.Cno AND Cname=CourseName；调用该存储过程的例子：DECLARE studentNum INT;EXECUTE student_count 数据库原理及应用,studnetNum OUTPUT;SELECT The result is：,studentNum;,20,三、通过存储过程传递参数(续),例4.7 创建一个存储过程，输出学生的基本情况。CREATE PROCEDURE student_queryxh CHAR(6),name VARCHAR(8)OUTPUT,sex CHAR(2)OUTPUT,age INT OUTPUTAS Select name=Sname,sex=Ssex,age=Sage FROM Student WHERE Sno=xh;调用该存储过程，查询学号为“950001”同学的相关信息DECLARE name VARCHAR(8);DECLARE sex CHAR(2);DECLARE age INT;EXECUTE student_query 950001,name OUTPUT,sex OUTPUT,age OUTPUT;,21,4.1.3 存储过程的修改和删除,一、修改存储过程T-SQL语句格式：ALTER PROCEDURE procedure_name parameter data_type=default OUTPUT,WITH ENCRYPTION AS sql_statement；二、删除存储过程T-SQL语句格式：DROP PROCEDURE procedure_name;,22,4.2 触发器,4.2.1 触发器基本概念4.2.2 定义触发器4.2.3 激活触发器4.2.3 删除触发器,23,4.2 触发器,触发器（Trigger）是由用户定义在关系表上的一类特殊过程，该过程一经定义，任何用户对表的增加、删除、更新操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施比约束更加复杂的检查和操作，具有更强大的数据控制能力。,24,4.2.1 触发器基本概念,触发器的定义：满足某个特定条件时自动触发执行的特殊存储过程，用于保证表中的数据遵循数据库设计者确定的规则和约束。存储过程是通过名称而被显式调用执行，而触发器不能被显式调用，只能通过事件触发而执行,25,4.2.1 触发器基本概念(续),触发器常用于强制业务规则和数据完整性，常常用于下列几种情况：（1）触发器可以通过级联的方式对相关的表进行修改。（2）触发器可以实施比CHECK约束更复杂的约束。（3）触发器可以根据改变前后表中数据的不同来进行相应的操作。（4）对于一个表上的不同操作可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器完成不同的操作。,26,4.2.1 触发器基本概念(续),SQL Server中提供两种触发：AFTER 触发器 INSTEAD OF 触发器一个触发器与3部分的内容有关：定义触发器的表 激活触发器的操作语句 触发器被激活时进行的操作,27,4.2.1 触发器基本概念(续),在使用触发器的过程中，SQL Server用到了两个特殊的临时表：inserted表和deleted表。它们与创建触发器的表具有相同的结构。触发器执行完毕后，这两个表自动被删除。inserted表：该表中存放的是由于执行INSERT或UPDATE语句而要向表中插入的新数据行。deleted表：该表中存放的是由于执行DELETE或UPDATE语句而要从表中删除的数据行。,28,4.2.2 定义触发器,创建触发器的语句格式为：CREATE TRIGGER trigger_name ONtable_name|view_nameWITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS sql_statement,29,4.2.2 定义触发器(续),例4.8 创建一个触发器，当学生表中的记录被更新时，显示表中的所有记录。CREATE TRIGGER student_change ON Student AFTER INSERT,UPDATE,DELETE AS SELECT*FROM Student;,30,4.2.2 定义触发器(续),例4.9 在Student表上创建DELETE触发器，实现Student表和SC表的级联删除。CREATE TRIGGER studentDelete ON Student AFTER DELETEAS DELETE FROM SC WHERE Sno IN(SELECT deleted.SnoFROM deleted);,31,4.2.2 定义触发器(续),例4.10 在SC表上创建INSERT触发器，当向SC表中添加学生选课记录时，检查该学生的Sno是否存在。若不存在，则不能将记录插入。CREATE TRIGGER sc_insert ON SC AFTER INSERTAS IF(SELECT COUNT(*)FROM Student,inserted WHERE Student.Sno=inserted.Sno)=0 BEGIN PRINT 学号不存在，不能插入该记录 ROLLBACK TRANSACTION END;,32,4.2.2 定义触发器(续),例4.11 创建UPDATE触发器，禁止对Student表中学生的学号进行修改。CREATE TRIGGER student_updateON Student AFTER UPDATEAS IF UPDATE(Sno)BEGIN PRINT 学生的学号不能修改 ROLLBACK TRANSACTION END;,33,4.2.2 定义触发器(续),例4.12 在SC表上创建触发器，当一次向SC表中添加多个记录时，删除学号在Student表中不存在的记录，从而保证数据的一致性。注意，不能在SC表中定义外键约束。CREATE TRIGGER sc_insert ON SC AFTER INSERTAS IF(SELECT COUNT(*)FROM Student,inserted WHERE Student.Sno=inserted.Sno)ROWCOUNT BEGIN DELETE FROM SC WHERE Sno NOT IN(SELECT Sno FROM Student)END;,34,4.2.2 定义触发器(续),例4.13 在视图上定义INSTEAD OF触发器。在第3章曾经介绍，视图中有导出列时不能对视图进行更新，但是，可以利用INSTEAD OF触发器对视图进行更新。假设有一个反映学生出生年份的视图CREATE VIEW birth_view(Sno,Smame,Ssex,Sbirth,Sdept)AS SELECT Sno,Sname,Ssex,2008-Sage,Sdept FROM student;,35,4.2.2 定义触发器(续),CREATE TRIGGER birth_view_insert ON birth_viewINSTEAD OF INSERTAS DECLARE Sno CHAR(6)DECLARE Sname VARCHAR(10)DECLARE Ssex CHAR(2)DECLARE birth INT DECLARE Sage INT DECLARE Sdept CHAR(20)SELECT Sno=Sno,Sname=Sname,Ssex=Ssex,birth=Sbirth,Sdept=Sdept FROM inserted SET Sage=2008birth INSERT INTO Student（Sno,Sname,Ssex,Sage,Sdept）VALUES（Sno,Sname,Ssex,Sage,Sdept）;,36,4.2.2 定义触发器(续),修改触发器命令格式为：ALTER TRIGGER trigger_nameON table_name|view_name WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEAS sql_statement,37,4.2.3 激活触发器,在SQL Server中，对于每个触发操作（如UPDATE、DELETE和INSERT），每个表或视图只能有一个INSTEAD OF触发器，但可以有多个AFTER触发器。AFTER触发器和INSTEAD OF触发器的区别在于它们被激活时机不同，如表：,38,4.2.4 删除触发器,1使表上的触发器无效和重新有效使用DISABLE TRIGGER关键字可以使表上的某个触发器无效，具体格式为ALTER TABLE table_name DISABLE TRIGGER trigger_name;使用ENABLE TRIGGER关键字可以使表上的触发器重新有效，具体格式为ALTER TABLE table_name ENABLE TRIGGER trigger_name;,39,4.2.4 删除触发器(续),2删除触发器使用DROP TRIGGER语句可以删除当前数据库中的一个或多个触发器，具体格式为：DROP TRIGGER trigger_name,.n;,40,作业,P100：1，2，5,