【4/1-13】速进！快速理解SQL 语句CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

最新推荐文章于 2023-09-23 11:34:57 发布

summer__kkkk

最新推荐文章于 2023-09-23 11:34:57 发布

阅读量2.5k

点赞数 7

文章标签：数据库 sql

本文链接：https://blog.csdn.net/summer__kkkk/article/details/105292831

版权

文章目录

在介绍上述语句之前
先完成关于完整性的几个测试

完整性

实体完整性
[例5.1] 将Student表中的Sno属性定义为码
（1）在列级定义主码

CREATE TABLE Student
   (Sno  CHAR(9)  PRIMARY KEY,
	Sname  CHAR(20) NOT NULL,     
	Ssex  CHAR(2),
	Sage  SMALLINT,
	Sdept  CHAR(20)
	);

（2）在表级定义主码

CREATE TABLE Student
    (Sno  CHAR(9),  
     Sname  CHAR(20) NOT NULL,
     Ssex  CHAR(2),
     Sage  SMALLINT,
     Sdept  CHAR(20),
     PRIMARY KEY (Sno)
    );

[例5.2] 将SC表中的Sno，Cno属性组定义为码

CREATE TABLE SC
       (Sno   CHAR(9)  NOT NULL, 
        Cno   CHAR(4)  NOT NULL,  
        Grade SMALLINT,
        PRIMARY KEY (Sno,Cno)    /*只能在表级定义主码*/
       );

参照完整性定义

关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码
[例5.3]定义SC中的参照完整性

CREATE TABLE SC
   (Sno    CHAR(9)  NOT NULL, 
    Cno    CHAR(4)  NOT NULL,  
    Grade  SMALLINT,
    PRIMARY KEY (Sno, Cno),   /*在表级定义实体完整性*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),  
          /*在表级定义参照完整性*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno)    
          /*在表级定义参照完整性*/
    );

[例5.4] 显式说明参照完整性的违约处理示例
之前没有显式说明的时候，采用的都是隐式的，只不过没有写出来而已。现在我们把违约处理在FOREIGN KEY后面写出来

CREATE TABLE SC
        (Sno   CHAR(9)  NOT NULL,
         Cno   CHAR(4)  NOT NULL,
         Grade SMALLINT,
         PRIMARY KEY(Sno,Cno), 			
         FOREIGN KEY (Sno) REFERENCES Student(Sno) 
		     ON DELETE CASCADE        /*级联删除SC表中相应的元组*/
             ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
         FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    
             ON DELETE NO ACTION 	
          /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
             ON UPDATE CASCADE   
      	  /*当更新course表中的cno时，级联更新SC表中相应的元组*/
        );

这里说明一下：违约处理的显式执行后，当我去删除Course中某一个Cno而造成与SC表不一致时，拒绝删除，当我去修改Student的Sno或者Course的Cno时，SC表中也会同时更新相应元组。

用户定义的完整性

（1）不允许取空值（NOT NULL）

[例5.5] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

CREATE TABLE SC
  (Sno CHAR(9)  NOT NULL,	
   Cno CHAR(4)  NOT NULL,	
   Grade  SMALLINT NOT NULL,	
   PRIMARY KEY (Sno, Cno),  
     … 
   /* 如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义 可以不写 */
 );

（2）列值唯一

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
这里既可以设置表级完整性，也可设置为列级完整性

CREATE TABLE DEPT
       (Deptno  NUMERIC(2),
        Dname  CHAR(9)  UNIQUE NOT NULL,
        /*要求Dname列值唯一, 并且不能取空值*/
        Location  CHAR(10),
        PRIMARY KEY (Deptno)
       );

好了以上完整性内容介绍完毕，下面我们来看以下语句。
CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

CHECK

CHECK<约束条件>

用CHECK短语指定列值应该满足的条件
[例5.7] Student表的Ssex只允许取“男”或“女”。
这个有点 CHECK+(条件)的意思
CHECK (Ssex IN(‘男’,‘女’))

CREATE TABLE Student
      (Sno   CHAR(9) PRIMARY KEY,
       Sname CHAR(8) NOT NULL,                     
       Ssex  CHAR(2)  CHECK (Ssex IN ('男','女')), 
       /*性别属性Ssex只允许取'男'或'女' */
       Sage  SMALLINT,
       Sdept  CHAR(20)
      );

CHECK语句指定条件之后，当Ssex取其他值的时候，会提示报错：

未更新任何行。
未提交行2中的数据。
错误源: .Net SqlClient Data Provider.
错误消息: INSERT语句与CHECK约束"CK_ _S_ Ssex_ _300424B4"冲
突。该冲突发生于数据库"SCHOOL" ,表"dbo.S", column 'Ssex'.
语句已终止。.
请更正错误并重试，或按Esc取消更改.

[例5.8] SC表的Grade的值应该在0和100之间。

CREATE TABLE  SC
           (Sno    CHAR(9) ,
            Cno    CHAR(4),
	        Grade  SMALLINT CHECK (Grade>=0 AND Grade <=100),
	        /*Grade取值范围是0到100*/
            PRIMARY KEY (Sno,Cno),
            FOREIGN KEY (Sno) REFERENCES Student(Sno),
            FOREIGN KEY (Cno) REFERENCES Course(Cno)
           );

若性别取0-100以外其他值的时候，提示报错：

未更新任何行。
未提交行2中的数据。
错误源: .Net SqlClient Data Provider.
错误消息: INSERT语句与CHECK约束"CK_ _S_ Ssex_ _300424B4")冲
突。该冲突发生于数据库"SCHOOL" ,表"dbo.S", column 'Ssex'.
语句已终止。.
请更正错误并重试，或按Esc取消更改。

上述都是CHECK列级约束，下面介绍一下元组级约束
在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
[例5.9]当学生的性别是男时，其名字不能以Ms.打头。

CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')

这里条件的意思是：要么性别为女无约束条件，要么男生姓名不能以MS.开头，说白了就是男生的姓名不可以以MS.打头

CREATE TABLE Student
       (Sno    CHAR(9), 
        Sname  CHAR(8) NOT NULL,
        Ssex    CHAR(2),
        Sage   SMALLINT,
        Sdept  CHAR(20),
        PRIMARY KEY (Sno),
        CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
        /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
      );

CONSTRAINT

完整性约束命名子句
完整性约束条件包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

CONSTRAINT <完整性约束条件名><完整性约束条件>

[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
学号：
C1 为完整性条件约束名
CHECK及后为完整性约束条件
其他类比

Sno  NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)

CREATE TABLE Student
     (Sno  NUMERIC(6)
      CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
      Sname  CHAR(20)  
      CONSTRAINT C2 NOT NULL,
      Sage  NUMERIC(3)
      CONSTRAINT C3 CHECK (Sage < 30),
      Ssex  CHAR(2)
      CONSTRAINT C4 CHECK (Ssex IN ( '男','女')),
      CONSTRAINT StudentKey PRIMARY KEY(Sno)
    );

当没有标明约束名的时候，是系统默认的约束名，用CONSTRAINT为CHECK约束主动定义一个约束名，方便后续改动。这里注意名字不能重复
[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。
应发工资是工资列Sal与扣除项Deduct之和。

CREATE TABLE TEACHER
         (Eno    NUMERIC(4)  PRIMARY KEY,   /*在列级定义主码*/
          Ename  CHAR(10),
          Job    CHAR(8),
	      Sal    NUMERIC(7,2),
	      Deduct NUMERIC(7,2),
	      Deptno NUMERIC(2),
	      CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) 	REFERENCES DEPT(Deptno),
	      CONSTRAINT C1 CHECK (Sal + Deduct >= 3000) 
         );

[例5.12]去掉例5.10 Student表中对性别的限制。

ALTER + TABLE +表名
DROP + CONSTRAINT + 约束名

这里DROP一个CONSTRAINT，然后就突出了起名字的好处了，能够直接去修改和删除。

ALTER TABLE Student 
DROP CONSTRAINT C4;

[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
先和【5.12】一样，DROP删除后，然后再ADD 添加。

ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40);

ASSERTION

创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句>

删除断言的语句格式为

DROP ASSERTION <断言名>;

每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。

这里注意：
T-SQL中没有ASSERTION
类似的有RULE，但使用方法不同：
上下文中不允许使用子查询，只允许使用标量表达式。
例如：

CREATE RULE sex_rule
AS @sex in (‘男’,‘女’)

使用Constraint基本能完成功能，不建议使用RULE。
[例5.18] 限制数据库课程最多60名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >= (SELECT count(*)
              FROM Course,SC
		      WHERE SC.Cno=Course.Cno and Course.Cname ='数据库')
		   	);

CHECK里是一个连接查询。当第61个同学选择该课，将被拒绝

[例5.19]限制每一门课程最多60名学生选修

CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60 >= ALL (SELECT count(*) 
                 FROM	 SC 
			     GROUP BY cno)
    		    );

这里涉及聚集操作count 和分组函数group by的SQL语句

TRIGGER

触发器：上课的第一感觉就是特别像JAVA里面的动作监听器（Actionlistener），一旦监听到相应动作就会出发相应操作
CREATE TRIGGER语法格式

CREATE TRIGGER <触发器名>  
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH  {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

删除触发器的SQL语法：

DROP TRIGGER <触发器名> ON <表名>;

[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

CREATE TRIGGER  SC_T		
AFTER UPDATE OF Grade ON SC
REFERENCING
	 OLD row  AS  OldTuple,
	 NEW row AS  NewTuple
FOR EACH ROW 	
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
	 INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)  
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

CREATE TRIGGER Student_Count
AFTER INSERT ON Student  	         
REFERENCING
 	NEW TABLE AS DELTA
FOR EACH STATEMENT  
    INSERT INTO StudentInsertLog (Numbers)
	SELECT COUNT(*) FROM DELTA

[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON Teacher  
 			             /*触发事件是插入或更新操作*/
FOR EACH ROW        /*行级触发器*/
BEGIN                             /*定义触发动作体，是PL/SQL过程块*/
  IF (new.Job='教授') AND (new.Sal < 4000) 
     THEN  new.Sal :=4000;                
   	 END IF;
END;

BEGIN-END 就是一个大括号
IF 要和 END IF 相对应

在SQL serever中，基本语法：

CREATE TRIGGER <触发器名>
on {<表名>|<视图名>}
{FOR|AFTER|INSTEAD OF}
[INSERT,UPDATE,DELETE]
AS
sql_statement

触发器使用说明

例5.2
1.参考教材内容，新建Student表
2. 新建表StudentInsertLog
存储学生人数

CREATE TABLE StudentInsertLog
(
Numbers INT
)

3.新建表StudentInsertLogUser
存储用户名和操作时间

CREATE TABLE StudentInsertLogUser
(
  UserName nchar(10),
  DateAndTime datetime
)

4.新建触发器Student_Count
当插入新的学生记录时，触发器启动，自动在StudentInsertLog记录学生人数

CREATE TRIGGER Student_Count
ON Student  	         
AFTER
INSERT
AS 
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM Student

这里统计的是Student表中的数，与书上有所不同，所以最后测试Number为学生总数

5.新建触发器Student_Time
当插入新的学生记录时，触发器启动，自动在StudentInsertLogUser记录用户名和操作时间

CREATE TRIGGER Student_Time
ON Student  	         
AFTER
INSERT
AS 
	declare @UserName    nchar(10)
	declare @DateTime    datetime

	select @UserName = system_user
	select @DateTime = CONVERT(datetime,GETDATE(),120) --2018-04-11 16:33:10
	
	INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
	VALUES (@UserName,@DateTime)

declare：声明，相当于定义一个变量名
变量名：@+名字
system_user：当前系统用户

6. 测试触发器效果

INSERT
INTO  Student
VALUES ('201215135','王五','男',18,'CS');
SELECT * FROM Student
SELECT * FROM StudentInsertLog
SELECT * FROM StudentInsertLogUser

PROCEDURE

创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

执行存储过程

  CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])；

使用CALL或者PERFORM等方式激活存储过程的执行
数据库服务器支持在过程体中调用其他存储过程

修改存储过程

ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

删除存储过程

DROP  PROCEDURE 过程名()；

[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) 
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
	AS DECLARE		/*定义变量*/
	    totalDepositOut Float;
        totalDepositIn Float;
		inAccountnum INT;
BEGIN                         	    /*检查转出账户的余额 */
	       SELECT Total INTO totalDepositOut FROM Accout
	            WHERE accountnum=outAccount;
	       IF totalDepositOut IS NULL THEN  
            	   /*如果转出账户不存在或账户中没有存款*/
	                ROLLBACK; 	   /*回滚事务*/
	                RETURN
	       END IF;
   IF totalDepositOut< amount THEN    	/*如果账户存款不足*/
	       ROLLBACK; 				/*回滚事务*/
	       RETURN
   END IF
   SELECT Accountnum INTO inAccountnum FROM Account
      WHERE accountnum=inAccount;
   IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
	  ROLLBACK; 	         	 		/*回滚事务*/
	  RETURN;
   END IF;
   UPDATE Account SET total=total-amount
    WHERE accountnum=outAccount; /* 修改转出账户余额，减去转出额 */
   UPDATE Account SET total=total + amount 
    WHERE   accountnum=inAccount; /* 修改转入账户余额，增加转入额 */
   COMMIT;                       	/* 提交转账事务 */
END;

[例8.9] 从账户01003815868转10000元到01003813828账户中。

CALL PROCEDURE  TRANSFER(01003813828,01003815868,10000);

该标准SQL语句无法在T-SQL中运行

那么在SQL serever中，步骤：
1.建立新表Account，并写入两个用户

DROP TABLE IF EXISTS Account;

CREATE TABLE Account
(
accountnum CHAR(3),	-- 账户编号
total FLOAT		-- 账户余额
);

INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);

SELECT * FROM Account

2. 建立存储过程

IF (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
    DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER 
@inAccount INT,@outAccount  INT,@amount FLOAT
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
AS
BEGIN TRANSACTION TRANS   
   	DECLARE		/*定义变量*/
	@totalDepositOut Float,
	@totalDepositIn Float,
	@inAccountnum INT;
	 /*检查转出账户的余额 */     
	SELECT @totalDepositOut = total FROM Account	WHERE accountnum = @outAccount;
	/*如果转出账户不存在或账户中没有存款*/
	IF @totalDepositOut IS NULL               	   
		BEGIN
			PRINT '转出账户不存在或账户中没有存款'
			ROLLBACK TRANSACTION TRANS; 	   /*回滚事务*/
			RETURN;
		END;
	/*如果账户存款不足*/
	IF @totalDepositOut < @amount     	
		BEGIN
			PRINT '账户存款不足'
			ROLLBACK TRANSACTION TRANS; 				/*回滚事务*/
			RETURN;
		END
	/*检查转入账户的状态 */  
	SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount;
	/*如果转入账户不存在*/ 
	IF @inAccountnum IS NULL   		                       
		BEGIN
			PRINT '转入账户不存在'
			ROLLBACK TRANSACTION TRANS; 	         	 		/*回滚事务*/
			RETURN;
		END;
	/*如果条件都没有异常，开始转账。*/ 
	BEGIN
		UPDATE Account SET total = total - @amount	WHERE	accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
		UPDATE Account SET total = total + @amount	WHERE   accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
		PRINT '转账完成，请取走银行卡'
		COMMIT TRANSACTION TRANS;                       	/* 提交转账事务 */
		RETURN;
	END

3.执行存储过程
账户不存在情况（输入、输出账户都测试一下）
正常情况:

SELECT * FROM Account
EXEC	Proc_TRANSFER
		@inAccount = 101,	--转入账户
		@outAccount = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM Account

FUNCTION

下期更新

16:48-14:10=两个半小时，其中休息了一会，做前面的时候还好，基本都会没什么问题，后面到了触发器，还有长篇代码的存储过程，有点头疼. 慢慢理解吧在这里插入图片描述

summer__kkkk

关注

7
点赞
踩
24

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫