Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --4.1 sp_ThongtinDocGia
- create proc sp_ThongtinDocGia
- (@ma_docgia smallint)
- as
- begin
- if(exists(select * from NguoiLon where ma_docgia=@ma_docgia))
- select * from DocGia d join NguoiLon n on d.ma_docgia=n.ma_docgia where n.ma_docgia=@ma_docgia
- else
- select * from DocGia d join TreEm k on d.ma_docgia=k.ma_docgia where k.ma_docgia=@ma_docgia
- end
- --câu4.2 sp_ThongtinDausach
- create proc sp_ThongtinDausach
- as
- begin
- select * from DauSach d join TuaSach t on d.ma_tuasach=t.ma_tuasach
- end
- --câu4.5 sp_DocGiaCoTreEmMuon
- create proc sp_DocGiaCoTreEmMuon
- as
- begin
- select ma_docgia from NguoiLon nl
- where exists(select *from Muon m1 where m1.ma_docgia=nl.ma_docgia and m1.ngay_hethan>getdate())
- and exists(select * from Muon m2 join TreEm tre on m2.ma_docgia=tre.ma_docgia
- where nl.ma_docgia=tre.ma_docgianguoilon and m2.ngay_hethan>getdate())
- end
- --câu4.6 sp_CapnhatTrangthaiDausach
- create proc sp_CapnhatTrangthaiDausach
- (
- @isbn int
- )
- as
- begin
- set @isbs=0
- if(exists(select * from DauSach where inbs=@inbs))
- set trangthai='Y' from DauSach
- else
- set trangthai='N' from DauSach
- end
- --câu4.9 sp_ThemNguoiLon
- create proc sp_ThemNguoiLon
- (
- @ho nvarchar(15),
- @tetlot nvarchar(10),
- @ten nvarchar(15),
- @ngaysinh date,
- @sonha nvarchar(15),
- @duong nvarchar(50),
- @quan nvarchar(20),
- @dienthoai nvarchar(14),
- @ham_sd date
- )
- as
- begin
- declare madg int, madg_t int
- set madg_t=0
- declare cur_magd cursor for select ma_docgia from DocGia
- open cur_magd
- fetch next from cur_magd into @madg
- while(@@petch_status=0 and @madg=@madg_t+1)
- begin
- set @madg_t=@madg_t+1
- fetch next from cur_madg into @madg
- end
- close cur_madg
- deallocate cur_madg
- set
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement