【数据库】第五章例题-完整性-断言-触发器-存储过程以及函数

1、存储过程以及函数我们之前建表的时候已经过这些，实体完整性和参照完整性我们之前已经做过例，这只进简单的演。例5.1将Student表中的Sno属性定义为码在列定义：Sage smallint,Sdept char(20);它也可以在级定义，且有些情况下， 如两个列共同作为主码时，只能在级定义。Sage smallint,Sdept char(20),PRIMARY(Sno);例5.2将SC表中的Sno，Cno属性组定义为码CREATETABLESC(Sno (11),Cno (4),Grade SMALLINT,PRIMARY(Sno,Cno),FOREIGN KEY(Sno) REFERENC

2、ESStudent(Sno),FOREIGN KEY(Cno) REFERENCESCourse(Cno);例定义SC中的参照完整性例码同上参照完整性的违约处理例5.4显式说明参照完整性的违约处理例要说明的是，这是针对被参照表的操作。参照表SC可以delete，insert个不符合外键规则的直接拒绝；被参照表，如Student表，个学的信息修改了，那么sc表是否跟着修改还是？上图说明了有三种设置，拒绝，级联修改/删除，设置为空值。create Table SC2PRIMARYKEY(Sno, Cno),on delete cascade,FOREIGN KEY(Cno) REFERENCESC

3、ourse(Cno)on update cascadeon delete no action, -拒绝);户定义的完整性这分为属性上约束还有元组的约束。列上的：notnull,unique,check元组上的：check关于not null和unique，我们之前就做过例，在这不做题关于checkCHECK关于列的（也就是只独处理个属性的）例5.7 Student表的Ssex只允许取“男”或“”。(Sno char(9) PRIMARY,Sname char(9) NOT NULL,Ssex char(2) check(Ssex IN(,),-这发了变化Sage smallint,Sdept

4、char(20);例5.8 SC表的Grade的值应该在0和100之间。FOREIGN KEY(Sno) REFERENCESStudent(Sno),FOREIGN KEY(Cno) REFERENCESCourse(Cno);check就是写了个条件关于级的例当学的性别是男时，其名字不能以打头。check (Ssex= OR Sname NOT like Mr.%);完整性约束命名句CONSTRAINT 注意是句，他也是嵌到create写的。其实也就是多了个关键字和个你命名的名字。这是列级的);级类似constraint SC_PKPRIMARYKEY(Sno, Cno),constrai

5、nt SC_FK1 FOREIGN KEY(Sno) REFERENCESStudent(Sno),constraint SC_FK2 FOREIGN KEY(Cno) REFERENCESCourse(Cno);alter table Student4drop constraint Student_PK_Sno;通过先删除旧的，再添加新的法修改。alter table Student4add constraint Student_PK_Sno PRIMARY(Sno,Sname);assertion断是标准sql的东西，t-sql并没有这个关键词。师说可以rule替代。这只做标准sql的例。

6、都是约束，为啥要断啊？直接check不好吗？断可以定义较复杂的约束。如下的例：我们之前的约束似乎没有办法做。例5.18限制数据库课程最多60名学选修create assertion ASSE_SC_DB_NUMcheck (60(select count()where course.Cnosc.Cno ANDcourse.Cname=);例5.19限制每门课程最多60名学选修group by cno);删除断drop assertion asse_name注意：T-SQL 中没有 ASSERTION 功能。 类似的有RULE，但使法不同： 上下中不允许使查询，只允许使标量表达式。 例如： CR

7、EATE RULE sex_rule AS sex in (男,)使Constraint基本能完成功能，不建议使RULE。定义触发器BEFORE| AFTER ON REFERENCING |OLD ROWFOR EACH ROW| STATEMENTWHEN这是标准sql哈。触发事件触发器类型例5.21当对表SC的Grade属性进修改时，若分数增加了10%则将此次操作记录到下表中：SC_U(Sno,Cno,Old,New)先创建表。Old smallint,New smallint);这是操作。CREATETRIGGER SC_TOLD row AS OldTuple,NEWrow AS N

8、ewTupleFOR EACH ROWWHEN(NewTuple.Grade =1.1OldTuple.Grade)INSERTINTO (Sno,Cno,OldGrade,NewGrade)(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);但是我们发现sqlserver中上的语句中报了错。他的语法格式类似这种 NOT FOR REPLICATIONAS sql_statement ; ,.n | EXTERNAL NAMEmethod specifier ; 查了好多博客，缺缺补补写出来了，但是不知道是否是标准写法。先refe

9、rencing了inserted和deleted表替代了；when语句我也不知道是否被替代了，但是可以if来实现类似功能，但是是在那个过程执的。总之是实现那个功能了。CREATETRIGGER SC_TON SCAFTER UPDATEasbeginbegininto (Sno,Cno,Old,New)(sno,cno,old,new)print-SC_Uendend;（这个之前是80分）（这个之前是95）看来是成功了。还有就是关于for each row和for each statement，本来师说可以做个实验加深下印象，但是发现似乎sqlserver不能使这个东西。（oracle持）例5

10、.22将每次对表Student的插操作所增加的学个数记录到表StudentInsertLog中。标准sql：t-sqlcreate trigger Student_Counton Studentafter insertasbegindeclare oldnum int , newnum intselect oldnum=count() from deletedselect newnum=count() from insertedprint old:print oldnumprint newprint newnumend由此发现，deleted是删除的表，不是我刚开始想的 旧表；inserted

11、是插数据的表，不是我以为的新表。未完待续还差触发器这个例以及存储过程和函数部分。今天就到此为了，明天还想要早起。明天上午定写完这个。还有就是触发器这t-sql和sql差的太多了。我百度了好久。还有就是 五竟然不放假？！真的开学到现在还没歇过天。呜呜呜，晚安终究还是我了，上午没能写完。现在是下午两点，继续：例5.23定义个BEFORE级触发器，为教师表Teacher定义完整性规则“教授的资不得低于4000元，如果低于4000元，动改为4000元”。BEGINENDIF;于删除触发器DROPTRIGGER ON ;tsql不加on和表明，也不能加。创建存储过程先说标准sqlCREATEOR REP

12、LACEPROCEDURE(1,2,.)ASSQL；模拟转账先建表插数据哈create table Account(Accountnum char(3),total float);insert into Account values(01,100),(02,100);aswhere Accountnum =outAccount;if totalDespositOutamount then-转出账户钱不够select Accountnum into inAccountnum from Account where AccountnuminAccount;if inAccountnum is nul

13、l then-没有转账户rollback;update Account set total total -amountwhere AccountnumoutAccout;update Account set total total +amountwhere Accountnum =inAccount;commit;end;然后是t-sqldrop procedure Proc_Transferasif totalOutamountbeginselect inAccountnumaccountnum from account where accountnuminAccount;endbeginupdate Account set total =total -amount where accountnum=outAccount;update Account set total =total amount where accountnum inAccount;printOK!commit transaction trans;执存储过程CALL/PERFORM PROCEDURE(1,2,.)；t-sql修改存储过程ALTER PROCEDURE1 RENAMETO 2;t-sqlALTER PROC| PROCEDURE schema_name. procedure_