数据库第五章

5.5 存储过程()
例4 创建带有多个输入参数并有默认值的存储过程:查询某 个学生的某门课程的考试成绩,列出学生的姓名,课程名 和成绩,若没有指定课程,则默认课程为数据库基础. CREATE PROC STUDENT_GRADE4 @STUDENT_NAME CHAR(10), @COURSE_NAME CHAR(20)='数据库基础' AS SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND O=O AND SNAME=@STUDENT_NAME AND CNAME=@COURSE_NAME 执行存储过程: EXEC STUDENT_GRADE4 '刘晨'
数据库原理与应用
周蓓(zhp_jdy@) 常熟理工学院计算机科学与工程系
第5章 数据库完整性
数据库完整性机制 触发器 存储过程
数据库的完整性是指数据的正确性和相容性. 完整性检查和控制的防范对象是不合语义的,不 正确的数据. DBMS对完整性的维护机制: 1,提供定义完整性约束条件的机制 2,提供完整性检查的方法 3,违约处理
5.5 存储过程
存储过程:SQL语句和控制流语句的预编译 存储过程名 集合.
过程的参数,存 储过程最多可以 有 2100 个参数. 参数不能用于代 替表名,列名或 其它数据库对象 的名称.
CREATE PROC [ EDURE ] procedure_name [ ;number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
使用变量和参数
1,Transact-SQL 局部变量: Transact-SQL 变量是 Transact-SQL 批处理和脚本中能 够保存数据值的对象.声明或定义变量后,批处理中的一个 Transact-SQL 语句可以设置变量值,而该批处理中后面的 语句可以从变量获得此值.例如: DECLARE @EmpIDVar INT SET @EmpIDVar = 1234 SELECT * FROM Employees WHERE EmployeeID = @EmpIDVar
5.5 存储过程(续)
例7 建立存储过程check_Xk,功能是根据提供的 参数学号和课程号,完成选课记录的插入功能.要 求如下: 首先检查该课程选课人数是否已满(可自己根据表 中数据的情况定义一个限定值),若满,则返回一 个0;若不满,继续检查该学生是否已经选满3门课 程,若满,则返回-1,否则将选课记录插入到SC 表中,并返回1.
例4 创建一个包括学生和教师编号,姓名以及类别 的视图,建立更新触发器,使更新结果返回到源基 本表中. 什么的视图是可更新的? 行列子集视图 建立INSTEAD OF 触发器,根据类别更新相应 的基本表.
CREATE TRIGGER [UPDATE_1] ON [JS_AND_ST1] INSTEAD OF UPDATE AS IF UPDATE(XM) BEGIN DECLARE @LB NVARCHAR(15) DECLARE @PID NVARCHAR(255) DECLARE @XM NVARCHAR(255) SET @LB = (SELECT LB FROM INSERTED) SET @PID = (SELECT PID FROM INSERTED) SET @XM = (SELECT XM FROM INSERTED) IF @LB='STUDENT' BEGIN UPDATE XS$ SET XM=@XM WHERE XH=@PID END IF @LB='TEACHER' BEGIN UPDATE JS$ SET XM=@XM WHERE GH=@PID END END
5.5 存储过程(续)
例5 创建带有输出参数存储过程:统计指定课程的平均成 绩,并将统计的结果用输出参数返回. CREATE PROC AVGGRADE @CN CHAR(10),@AVGGRADE INT OUTPUT AS SELECT @AVGGRADE=AVG(GRADE) FROM SC,COURSE WHERE O=O AND CNAME=@CN 执行存储过程: DECLARE @ACGGRADE INT EXEC AVGGRADE 'VB',@AVGGRADE OUTPUT PRINT @AVGGRADE
5.5 存储过程(续)
例3 创建带有多个输入参数的存储过程:查询某个学生的 某门课程的考试成绩,列出学生的姓名,课程名和成绩 CREATE PROC STUDENT_GRADE3 @STUDENT_NAME CHAR(10), @COURSE_NAME CHAR(20) AS SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND O=O AND SNAME=@STUDENT_NAME AND CNAME=@COURSE_NAME 执行存储过程: EXEC STUDENT_GRADE3 '刘晨','VB'
1,不允许取空值 NOT NULL 2,列值惟一 UNIQUE 3,CHECK子句 CHECK(约束条件表达式)
5.4 触发器
1,触发器(Trigger) 触发器是用户定义在关系表上的一类由事 件驱动的特殊存储过程. 当发生表的UPDATE,DELETE,INSERT操 作时,触发器生效.
5.4 触发器(续)
5.4 触发器(续)
例3 在订单表(ORDER)中插入或修改订购数量(NUM),必须 满足客户相应等级的数量上限和下限.
5.4 触发器(续)
CREATE TRIGGER [CHECK_NUM] ON [ORDER] FOR INSERT, UPDATE AS IF UPDATE(NUM) BEGIN IF EXISTS( SELECT * FROM INSERTED ,CUSTOMER,CUSTOMERLEVEL WHERE INSERTED.CID=CUSTOMER.CID AND CUSTOMERLEVEL.CLEVEL=CUSTOMER.CLEVEL AND NUM NOT BETWEEN CUSTOMERLEVEL .NUMDOWN AND CUSTOMERLEVEL .NUMUP ) BEGIN PRINT '订购数量违反了客户的限额!' ROLLBACK END END
5.5 存储过程(续)
例6 建立带通配符参数的存储过程:查询学生的基 本情况. CREATE PROC S_NAME @SN VARCHAR(10)='%' AS SELECT * FROM STUDENT WHERE SNAME LIKE @SN 执行存储过程: EXEC S_NAME '李%'
5.4 触发器(续)
4,AFTER和INSTEAD OF的区别 AFTER:触发发生在SQL操作后,若发生错误,可 利用回滚恢复原数据. INSTEAD OF:触发发生在SQL操作前,且如何操 作由触发器操作来实现. INSTEAD OF 触发器的主要优点是可以使不能更 新的视图支持更新.基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的 插入,更新和删除操作. 每个表只能有一个同类型的INSTEAD OF触发器
使用变量和参数
2,Transact-SQL 参数: 参数是在存储过程和执行该存储过程的批处理或脚本之间传递 数据的对象.参数可以是输入参数也可以是输出参数.例如: CREATE PROCEDURE ParmSample @EmpIDParm INT AS SELECT * FROM Employees WHERE EmployeeID = @EmpIDParm GO EXEC ParmSample @EmpIDParm = 1234 GO
{ [ INSERT ] [,] [ UPDATE ] [,] [ DELETE ] } AS sql_statement [ ...n ] 触发器激活后所 作的操作,例如 检查,回滚,插 入,删除等
5.4 触发器(续)
例1 在COURSE表上创建触发器,检查插入和更新时是否出 课程名相同的记录,若有则不插入. CREATE TRIGGER [C_INSERT] ON [DBO].[COURSE] FOR INSERT, UPDATE 触发器名字 AS IF 判断插入或更新的数据 (SELECT COUNT(*) FROM 是否与表中课程名相同 [DBO].[COURSE] ,INSERTED WHERE [DBO].[COURSE].CNAME=AME)>1 BEGIN PRINT 'NOT INSERT' 操作回滚 ROLLBACK TRANSACTION END
5.4 触发器(续)
例2 插入选课记录时,检查若选课人数已满5人,则拒绝插
CREATE TRIGGER [XK] ON [SC] FOR INSERT, UPDATE AS IF(SELECT COUNT(*) FROM [SC] ,INSERTED WHERE [SC].CNO=O )>5 BEGIN PRINT '选课已满' ROLLBACK END
2,使用触发器的优点 触发器是自动的; 触发器可以通过数据库中的相关表进行层 叠更改. 触发器可以强制限制,这些限制比用 CHECK 约束所定义的更复杂.
5.4 触发器(续)
3,定义触发器
CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {FOR | AFTER | INSTEAD OF } 加密 触发器激活的顺 序,AFTER表示 执行操作后, INSTEAD OF 操 作前.