数据库作业13：SQL练习8 - CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

【例5.1】 将Student表中的Sno属性定义为码

create tablr Student
 (
   Sno    char(9)  primary key,
  Sname  char(20) not null,     
  Ssex   char(2),
  Sage   smallint,
  Sdept  char(20)
 );
                       --在列级级定义主码：
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)    
 ); --只能在表级定义主码

【例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】显式说明参照完整性的违约处理示例

 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表中相应的元组*/
        );

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

create table SC
 (
  Sno char(9)  not null, /*Sno属性不能取空值*/
  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  vhar(9)  unique not null, /*要求Dname列值唯一, 并且不能取空值*/
Location  char(10),
primary key (Deptno)
); 

（3）用check短语指定劣质应该满足的条件：
【例5.7]】Student表的Ssex只允许取“男”或“女”。

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)
);

【例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)
);

【例5.9】 当学生的性别是男时，其名字不能以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两个属性值之间的约束条件*/
);

【例5.10】建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

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)
);

【例5.11】 建立教师表TEACHER，要求每个教师的应发工资不低于3000元。

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 TEACHERFKry foreign key (Deptno)  references DEPT(Deptno),
constraint C1 check(Sal + Deduct >= 3000) /*应发工资是工资列Sal与扣除项Deduct之和。*/
);

【例5.12】去掉例5.10 Student表中对性别的限制。

alter table Student 
drop constraint C4;

【例5.13】修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

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);

【例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 update 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; 

创建存储过程：

create or replace procedure过程名([参数1,参数2,...]) as <过程化SQL块>；

执行存储过程：

call / perform procedure过程名([参数1,参数2,...])；

修改存储过程：

alter procedure过程名1  rename to 过程名2;

删除存储过程：

drop procedure 过程名()；

【例8.9】利用存储过程来实现下面的应用：从账户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.10】 从账户01003815868转10000元到01003813828账户中。

call procedure  transfer(01003813828,01003815868,10000);

以上两个语句在SQL server中都无法运行，如果要在SQL server运行需要用到以下语句：

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
                                            /*建立存储过程*/
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 @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;
SELECT * FROM Account
EXEC	Proc_TRANSFER
@inAccount = 101,	--转入账户
 	@outAccount = 102,	/*转出账户*/
 	@amount = 50			
SELECT * FROM Account