Lab2 [PDF]

Zitiervorschau

USE QLDT ---A. STORED PROCEDUREDS VOI THAM SO VAO ---1.Tham số vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH. Trước khi INSERT dữ liệu cần kiểm tra MSGV đã tồn tại trong table HOCHAM chưa. Nếu chưa thì trả về giá trị 0. CREATE PROCEDURE ProcedureCau1 @msgv int, @tengv nvarchar(30), @diachi nvarchar(50), @sodt varchar(10),@mshh int, @namhh smalldatetime AS BEGIN DECLARE @mshh_ht int begin select @mshh_ht = MSHH from HOCHAM WHERE MSHH = @mshh if(@mshh_ht is null) PRINT '0' else begin INSERT INTO GIAOVIEN VALUES (@msgv, @tengv, @diachi, @sodt, @mshh, @namhh) print 'SUCCESSFULLY!' end end END EXEC ProcedureCau1 @msgv = 00206, @tengv = "Nguyễn Thị", @diachi = "Hà Nội", @sodt = 0123948, @mshh = 10, @namhh = 1997 ---2.Tham số vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH.Trước khi insert dữ liệu cần kiểm tra MSGV trong table GIAOVIEN có trùng không, nếu trùng trả về 0. CREATE PROCEDURE ProcedureCau2 @msgv int, @tengv nvarchar(30), @diachi nvarchar(50), @sodt varchar(10),@mshh int, @namhh smalldatetime AS BEGIN DECLARE @msgv_kt int select @msgv_kt = MSGV from GIAOVIEN where MSGV = @msgv if(@msgv_kt is null) BEGIN INSERT INTO GIAOVIEN VALUES (@msgv, @tengv, @diachi, @sodt, @mshh, @namhh) PRINT 'SUCCESSFULLY!' END else PRINT '0' END EXEC ProcedureCau2 @msgv = 00209, @tengv = "Nguyễn Thị", @diachi = "Hà Nội", @sodt = 0123948, @mshh = 1, @namhh = 1997 ---3.Giống (1) và (2) kiểm tra xem MSGV có trùng không? MSHH có tồn tại chưa Nếu MSGV trùng thì trả về 0. Nếu MSHH trùng thì trả về 1, ngược lại insert dữ liệu. CREATE PROCEDURE procedureCau3 @msgv int, @tengv nvarchar(30), @diachi nvarchar(50), @sodt varchar(10),@mshh int, @namhh smalldatetime AS BEGIN DECLARE @msgv_kt int, @mshh_kt int BEGIN SELECT @msgv_kt = MSGV FROM GIAOVIEN WHERE MSGV = @MSGV

SELECT @mshh_kt = MSHH FROM HOCHAM WHERE MSHH = @mshh IF(@msgv_kt IS NOT NULL) PRINT '0' ELSE IF(@mshh_kt IS NULL) PRINT '1' ELSE BEGIN INSERT INTO GIAOVIEN VALUES (@msgv, @tengv, @diachi, @sodt, @mshh, @namhh) PRINT 'SUCCESSFULLY!' END END END EXEC ProcedureCau3 @msgv = 00209, @tengv = "Nguyễn Thị", @diachi = "Hà Nội", @sodt = 0123948, @mshh = 1, @namhh = 1997 ---4.Đưa vào MSDT cũ, TENDT mới. Hãy cập nhật tên đề tài mới với mã đề tài cũ không đổi nếu tìm không tìm thấy trả về 0, ngược lại trả về 1. CREATE PROCEDURE ProcedureCau4 @msdt char(6), @tendt nvarchar(30) AS BEGIN DECLARE @msdt_kt nvarchar(30) select @msdt_kt = MSDT from DETAI where MSDT = @msdt if(@msdt_kt is null) print '0' else begin update DETAI set TENDT = @tendt WHERE MSDT = @msdt return 1 end END EXEC ProcedureCau4 @msdt = '97001', @tendt = N'Quản lý thư viện' ---5.Tham số đưa vào MSSV, TENSV mới, DIACHI mới thủ lục dùng để cập nhật sinh viên trên, nếu không tìm thấy trả về 0, ngược lại cập nhật và trả về 1 CREATE PROCEDURE ProcedureCau5 @mssv char(8), @tensv nvarchar(30), @diachi nchar(30) AS BEGIN DECLARE @mssv_kt char(8) select @mssv_kt = MSSV from SINHVIEN WHERE MSSV = @mssv IF(@mssv_kt IS NULL) PRINT '0' ELSE BEGIN UPDATE SINHVIEN SET TENSV = @tensv, DIACHI = @diachi WHERE MSSV = @mssv PRINT '1' END END EXEC ProcedureCau5 @mssv = 13520010, @tensv = N'Nguyễn Thị A',

@diachi = N'Quận 1, TP Hồ Chí Minh' EXEC ProcedureCau5 @mssv = 13520001, @tensv = N'Nguyễn Văn An', @diachi = N'THỦ ĐỨC' ---B.STORED PROCEDURES VOI THAM SO VÀO VÀ RA ---1. Đưa vào TENHV trả ra: Số GV thỏa học vị, nếu không tìm thấy trả về 0 CREATE PROCEDURE ProcedureBCau1 @tenhv nvarchar(20), @sogv int output AS BEGIN SET @sogv = 0 SELECT @sogv = COUNT(MSGV) FROM HOCVI A INNER JOIN GV_HV_CN B ON A.MSHV = B.MSHV WHERE TENHV = @tenhv END DECLARE @SOGV INT EXEC ProcedureBCau1 @tenhv = N'Kỹ sư', @sogv = @SOGV output print @SOGV ---2. Đưa vào MSDT cho biết: Điểm trung bình của đề tài, nếu không tìm thấy trả về không CREATE PROCEDURE ProcedureBCau2 @msdt char(6), @diem float output AS BEGIN DECLARE @diem1 float, @diem2 float, @diem3 float SET @diem = 0 SELECT @diem1 = avg(DIEM) FROM GV_HDDT WHERE MSDT = @msdt if (@diem1 is null) set @diem1 = 0 SELECT @diem2 = avg(DIEM) FROM GV_PBDT WHERE MSDT = @msdt if (@diem2 is null) set @diem2 = 0 SELECT @diem3 = avg(DIEM) FROM GV_UVDT WHERE MSDT = @msdt if (@diem3 is null) set @diem3 = 0 set @diem = (@diem1 + @diem2 + @diem3) /3 END DECLARE @DIEM FLOAT EXEC ProcedureBCau2 @msdt = '97003' , @diem = @DIEM output PRINT @DIEM --4.Đưa vào MSHD trả ra: Tên chỉ tịch hội đồng và SDT, nếu không tìm thấy trả về 0. CREATE PROCEDURE ProcedureBCau4 @mshd int, @tencthd nvarchar(30) output, @sdt varchar(10) output AS BEGIN DECLARE @mshd_kt int select @mshd_kt = MSHD from HOIDONG WHERE MSHD = @mshd IF(@mshd_kt IS NOT NULL) BEGIN

set @tencthd = '' set @sdt = '' select @tencthd = TENGV, @sdt = GV.SODT from HOIDONG HD INNER JOIN GIAOVIEN GV ON HD.MSGV = GV.MSGV WHERE hd.MSHD= @mshd_kt END ELSE PRINT '0' END DECLARE @TENCTHD NVARCHAR(30), @SDT VARCHAR(10) EXEC ProcedureBCau4 @mshd = 1, @tencthd = @TENCTHD output , @sdt = @SDT output PRINT @TENCTHD PRINT @SDT --C.TRIGGER --1. Tạo trigger thảo mãn điều kiện khi xóa một đề tài sẽ xóa các thông tin liên quan CREATE TRIGGER TG_XOADETAI ON DETAI FOR DELETE AS BEGIN DECLARE @msdt char(6) Select @msdt = MSDT from DELETED DELETE from SV_DETAI WHERE MSDT = @msdt DELETE FROM GV_HDDT WHERE MSDT = @msdt DELETE FROM GV_PBDT WHERE MSDT = @msdt DELETE FROM GV_UVDT WHERE MSDT = @msdt DELETE FROM HOIDONG_DT WHERE MSDT = @msdt END --3. Tạo Trigger thỏa mãn ràng buộc là một hội đồng không quá 10 đề tài. Dùng "Group by" có được không? Giải thích CREATE TRIGGER TriggerCau3 ON HOIDONG_DT FOR INSERT AS BEGIN DECLARE @mshd INT SET @mshd = (SELECT MSHD FROM INSERTED ) IF(SELECT COUNT(*) FROM HOIDONG_DT WHERE MSHD = @mshd GROUP BY MSHD) > 10 BEGIN PRINT 'MOT HOI DONG KHONG QUA 10 DE TAI' ROLLBACK TRANSACTION END END --5. Tạo trigger thỏa mãn ràng buộc là một giáo viên muốn có học hàm PGS phải là tiến sĩ -- CẬP NHẬT THEO MSGV CREATE TRIGGER Tg_ktragiaovien ON GIAOVIEN FOR UPDATE AS BEGIN DECLARE @mshh INT, @msgv INT, @tenhh NVARCHAR(20) SET @msgv = (SELECT MSGV FROM INSERTED) SELECT @mshh = MSHH FROM INSERTED SELECT @tenhh = TENHH FROM HOCHAM WHERE MSHH = @mshh IF(@tenhh = N'PHÓ GIÁO SƯ') BEGIN

DECLARE @mshv_ts INT SELECT @mshv_ts = MSHV FROM HOCVI WHERE TENHV = N'Tiến sĩ' IF(@mshv_ts not in (SELECT MSHV FROM GV_HV_CN WHERE MSGV = @msgv)) BEGIN print N'Học vị không thõa' ROLLBACK TRANSACTION END ELSE PRINT 'THANH CONG' END ELSE

PRINT 'THANH CONG'

END UPDATE GIAOVIEN SET MSHH = 1 WHERE MSGV = 00201 insert into GV_HV_CN VALUES(00201, 4, 3, 2018) delete GV_HV_CN where NAM = 2018