课程sql.txt

来自「数据库课件」· 文本 代码 · 共 131 行

在“课堂”数据库创建D，S，C，SC，T，TC五个表：
CREATE TABLE D
     (Dnum CHAR(4) NOT NULL UNIQUE,
     Dname CHAR(20),
     Director CHAR(8),
     PRIMARY KEY (Dnum))
GO
CREATE TABLE S
     (Snum CHAR(4) NOT NULL UNIQUE,
     Sname CHAR(8),
     Ssex CHAR(2),
     Sage SMALLINT,
     Sphone CHAR(13),
     Dnum CHAR(4),
     PRIMARY KEY (Snum),
     FOREIGN KEY(Dnum) REFERENCES D(Dnum))
GO
CREATE TABLE C
       (Cnum CHAR(4),
       Cname CHAR(20),
       Cfreq NUMERIC(2,1),
       PRIMARY KEY (Cnum),
       CHECK((Cfreq IS NULL) OR (Cfreq BETWEEN 0 AND 10)))
GO
CREATE TABLE SC
       (Snum CHAR(4),
       Cnum CHAR(4),
       score SMALLINT,
       PRIMARY KEY (Snum,Cnum),
       FOREIGN KEY(Snum) REFERENCES S(Snum),
       FOREIGN KEY(Cnum) REFERENCES C(Cnum))
GO
CREATE TABLE T
     (Tnum CHAR(4) NOT NULL UNIQUE,
     Tname CHAR(8),
     Tsex CHAR(2),
     Tbirth CHAR(11),
     Ttitle CHAR(8),
     Tsalary NUMERIC(6),
     Tphone CHAR(13),
     Dnum CHAR(4),
     PRIMARY KEY (Tnum),
     FOREIGN KEY(Dnum) REFERENCES D(Dnum))
GO
CREATE TABLE TC
      (Tnum CHAR(4),
       Cnum CHAR(4),
       PRIMARY KEY (Tnum,Cnum),
       FOREIGN KEY(Tnum) REFERENCES T(Tnum),
       FOREIGN KEY(Cnum) REFERENCES C(Cnum))

建立游标一：
GO
USE studydb --如何建立游标
DECLARE @X smallint --声明局部游标
DECLARE s_cursor CURSOR FOR
   SELECT age FROM s WHERE SNO='3041531' --声明服务器游标
   FOR UPDATE
OPEN s_cursor  --打开游标
FETCH ABSOLUTE 1 FROM s_cursor INTO @X  --取游标，并将结果保存在@x中
   PRINT '修改前的年龄:'+CONVERT(VARCHAR,@X)
   UPDATE s SET age=age+1 WHERE CURRENT OF s_cursor
FETCH ABSOLUTE 1 FROM s_cursor INTO @X
   PRINT '修改后的年龄:'+CONVERT(VARCHAR,@X)
CLOSE s_cursor --关闭游标
DEALLOCATE s_cursor --释放游标
GO
建立游标二：（OK）
GO
USE studydb --如何建立游标
DECLARE @X_sno varchar(12)--声明局部游标
DECLARE @Y_age varchar(12)
DECLARE s_cursor CURSOR FOR
   SELECT sno,age FROM s --声明服务器游标
OPEN s_cursor  --打开游标
FETCH s_cursoe INTO @X_sno,@Y_age  --取游标，并将结果保存在@X_sno,@Y_age 中
   WHILE(@@FETCH_STATUS<>-1)
BEGIN
   SELECT @X_sno,@Y_age
   FETCH s_cursor INTO @X_sno,@Y_age
END
IF(@@FETCH_STATUS=-1)
   BEGIN
   CLOSE s_cursor --关闭游标
   DEALLOCATE s_cursor --释放游标
END

设置事务：（OK）
USE studydb
BEGIN TRANSACTION --设置事务
INSERT s(sno,sn,age,sex,nation,birthplace,dept)
    VALUES('3041688','徐微','25','女','汉','青岛','经管学院')
IF @@ERROR<>0 --全局变量
   BEGIN PRINT'添加学生记录时出现错误'
   RETURN
END
INSERT INTO sc(sno,cno,score,period)
   VALUES('3041688','9901002','88','210')
IF @@ERROR<>0
      BEGIN PRINT'登记成绩时出现错误'
   RETURN
END
COMMIT TRANSACTION

设置触发器：（OK）
USE studydb --设置触发器
GO
CREATE TRIGGER TRIGGER1 ON SC --建立触发器TRIGGER1
FOR INSERT AS 
  DECLARE @SNO CHAR(14),@XF INT
SELECT @SNO=SNO,@XF=credit
  FROM INSERTED JOIN C ON INSERTED.CNO=C.CNO
UPDATE S SET credit=ISNULL(credit,0)+@XF
  WHERE SNO=@SNO
GO

设置存储过程：（OK）
USE studydb --设置存储过程PROC_1
GO
CREATE PROCEDURE PROC_1(@SNO CHAR(14)) --设置局部变量＠SNO
AS SELECT S.SNO,SN,SEX,CN,SCORE
      FROM S JOIN SC ON S.SNO=SC.SNO
             JOIN C ON SC.SNO=C.CNO
      WHERE S.SNO=@SNO
GO
proc_1 '3041688' --使用存储过程proc_1 ''
DROP PROCEDURE PROC_1 --删除存储过程